Курс по SQL

Урок 39: Блокировки: Понятие и виды блокировок

В этом уроке мы рассмотрим блокировки в SQL, их назначение и виды. Блокировки играют важную роль в обеспечении целостности данных и предотвращении конфликтов при одновременном доступе к данным несколькими транзакциями.

Понятие блокировок

Блокировка — это механизм, используемый СУБД для управления одновременным доступом к данным. Блокировки предотвращают ситуации, когда несколько транзакций пытаются изменить одни и те же данные одновременно, что может привести к некорректным результатам и потерям данных.

Виды блокировок

Существует несколько видов блокировок, используемых в SQL:

• Exclusive Lock (X-LOCK) — Эксклюзивная блокировка: Блокировка, которая позволяет только одной транзакции изменять данные. Другие транзакции не могут читать или изменять заблокированные данные.
• Shared Lock (S-LOCK) — Разделяемая блокировка: Блокировка, которая позволяет нескольким транзакциям читать данные, но не позволяет изменять их.
• Update Lock (U-LOCK) — Блокировка обновления: Блокировка, которая используется для подготовки данных к обновлению. Она предотвращает другие транзакции от одновременного обновления данных.
• Intent Lock (IX, IS, IU) — Намеренная блокировка: Блокировка, которая используется для обозначения намерения транзакции получить более сильную блокировку в будущем.

Эксклюзивная блокировка (Exclusive Lock)

Эксклюзивная блокировка используется для предотвращения одновременного доступа к данным другими транзакциями. Рассмотрим пример:

BEGIN TRANSACTION;

-- Устанавливаем эксклюзивную блокировку
UPDATE Счета
SET Баланс = Баланс - 100
WHERE НомерСчета = '123';

-- Блокировка будет удерживаться до выполнения COMMIT или ROLLBACK
COMMIT;

Результат выполнения

Эксклюзивная блокировка установлена, изменения зафиксированы.

Разделяемая блокировка (Shared Lock)

Разделяемая блокировка позволяет нескольким транзакциям одновременно читать данные. Рассмотрим пример:

BEGIN TRANSACTION;

-- Устанавливаем разделяемую блокировку
SELECT * FROM Счета WITH (HOLDLOCK);

-- Другие транзакции могут читать данные, но не изменять их

COMMIT;

Результат выполнения

Разделяемая блокировка установлена, данные прочитаны.

Блокировка обновления (Update Lock)

Блокировка обновления используется для подготовки данных к обновлению. Рассмотрим пример:

BEGIN TRANSACTION;

-- Устанавливаем блокировку обновления
SELECT * FROM Счета WITH (UPDLOCK)
WHERE НомерСчета = '123';

-- Выполняем обновление данных
UPDATE Счета
SET Баланс = Баланс - 100
WHERE НомерСчета = '123';

COMMIT;

Результат выполнения

Блокировка обновления установлена, данные обновлены.

Намеренная блокировка (Intent Lock)

Намеренные блокировки используются для обозначения намерения транзакции получить более сильную блокировку в будущем. Рассмотрим пример:

BEGIN TRANSACTION;

-- Устанавливаем намеренную эксклюзивную блокировку
SELECT * FROM Счета WITH (XLOCK)
WHERE НомерСчета = '123';

-- Выполняем обновление данных
UPDATE Счета
SET Баланс = Баланс - 100
WHERE НомерСчета = '123';

COMMIT;

Результат выполнения

Намеренная эксклюзивная блокировка установлена, данные обновлены.

Упражнения

BEGIN TRANSACTION;

-- Устанавливаем эксклюзивную блокировку
UPDATE Клиенты
SET Адрес = 'Новый адрес'
WHERE КлиентID = 1;

-- Блокировка будет удерживаться до выполнения COMMIT или ROLLBACK
COMMIT;

BEGIN TRANSACTION;

-- Устанавливаем разделяемую блокировку
SELECT * FROM Заказы WITH (HOLDLOCK);

-- Другие транзакции могут читать данные, но не изменять их

COMMIT;

BEGIN TRANSACTION;

-- Устанавливаем блокировку обновления
SELECT * FROM Продукты WITH (UPDLOCK)
WHERE ПродуктID = 1;

-- Выполняем обновление данных
UPDATE Продукты
SET Цена = Цена * 1.1
WHERE ПродуктID = 1;

COMMIT;

BEGIN TRANSACTION;

-- Устанавливаем намеренную эксклюзивную блокировку
SELECT * FROM Сотрудники WITH (XLOCK)
WHERE СотрудникID = 1;

-- Выполняем обновление данных
UPDATE Сотрудники
SET Зарплата = Зарплата * 1.1
WHERE СотрудникID = 1;

COMMIT;