Блокировки транзакций

Когда вам надо поменять что-то в базе данных, вы должны приконнектиться к ней (установить соединение) и совершить некую работу. Которая называется транзакцией - упорядоченное множество операций, переводящих базу данных из одного согласованного состояния в другое.

Одной операции всегда соответствует одна транзакция, но в рамках одной транзакции можно совершить несколько операций.

Зачем это надо? Транзакцию надо открыть, совершить ее операции и закрыть - COMMIT (транзакция фиксируется) или ROLLBACK (транзакция откатывается).. Допустим, вы переводите весь свой капитал с одной карточки на другую. Выглядит это "внутри" системы как несколько операций:

delete from счет1  where счет = счет 1

insert into счет2  values ('сумма')

После удаления денег с одного счета и до того, как они поступят на второй, вполне может оборваться соединение. И деньги канут в лету. Канули. Бы. Но так как транзакция не была закрыта, то и ее изменения не сохранились. Баланс достигнут :)

Но! К каким эффектам может привести параллельная работа двух транзакций?

1 эффект: "Потерянная запись"

Есть некий счет А, на котором лежит 500 у.е.
Кассир 1 (К1 на рисунке) списал с него 300 у.е. Обозначим его действия красными стрелками. Списал 300 - на выходе получает 200.
Кассир 2 (К2) тоже решил обратиться к этому же счету, и записал туда 300 у.е., пока К1 еще не успел закрыть свою транзакцию.
Итог - мы "потеряли запись" первого кассира, ведь на выходе у нас А = 800, хотя должно быть 500. "Кто последний вписал результат - того и тапки". Получается так.

То, что кассир списал 300 у.е., для системы означает начало транзакции (первая стрелка), выполнение действия

update таблицаСчетов  set суммаСчета= '200' where счет = счет 1

И закрытие транзации - вторая красная стрелка.

Второй кассир выполняет операцию: 

update таблицаСчетов  set суммаСчета = '800'where счет = счет 1

Начало и конец которой указаны зелеными стрелками.

2 эффект: "Грязное чтение"

Есть некий счет А, на котором лежит 500 у.е.
Кассир 1 списал с него 300 у.е. Обозначим его действия красными стрелками. Списал 300. Потом передумал и сделал откат - на выходе остались те же 500 у.е.
Кассиру 2 (К2) понадобилась информация по этому счету и он ее считал до того, как К1 закрыл свою транзакцию.
Итог - второй кассир считал неверную сумму, построил неверный отчет/отказал в визе платежеспособному гражданину и тд.

3 эффект: "Повторимое чтение"
 

Есть некие данные.
Кассир 1 строит отчет. Операции идут последовательно для каждой колонки. Система считала данные, записала в первую колонку (например, взяв минимум от них).
Обозначим получение данных зеленым цветом, а изменение - красным.
Кассир 2 влез в эту таблицу данных и изменил некоторые счета в ней.
У кассира 1 продолжается построение отчета. И во вторую колонку система считывает уже новые данные.
Итог - отчет построен на основании разных данных.

4 эффект: "Фантомы"
 

Есть некие данные.
Кассир 1 строит отчет. Операции идут последовательно для каждой колонки. Система считала данные, записала в первую колонку (например, взяв минимум от них).
Обозначим получение данных зеленым цветом, а изменение - красным.
Кассир 2 влез в эту таблицу данных и добавил новые счета/удалил некоторые старые.
У кассира 1 продолжается построение отчета. И во вторую колонку система считывает уже новые данные.
Итог - отчет построен на основании разных данных.

Разница между 3-им и 4-ым эффектами в том, что в одном случае данные изменяются, а во втором - добавляются/удаляются. То есть меняется еще и их количество.

Как бороться с этими проблемами? Чтобы избежать вышеприведенных эффектов, применяются блокировки. Способов реализации изоляции транзакции существует несколько. Основные - блокировочники и версионники. Рассмотрим блокировочники, обеспечивающих различную блокировку транзакций.

Условные обозначения:

"+" - начало блокировки (когда ее ставить)
"-" - конец блокировки (когда ее снимать)

1 эффект: "Потерянная запись"

Решение проблемы потери записи транзакции, которая была открыта первой - ставить эксклюзивную блокировку на запись на все время редактировании. Экслюзивная блокировка на записи может быть только одна, она запрещает все остальные блокировки => Кассир 2 не сможет внести своих изменений, пока Кассир 1 не закроет свою транзакцию.

 Т1 - транзакция первого кассира, начало и конец обозначены красными стрелками.

Такой уровень изоляции носит название READ UNCOMMITED

2 эффект: "Грязное чтение"

Решение проблемы чтения неверных данных вследствие их изменения во время чтения - ставить разделяемую блокировку на чтение.
Разделяемая блокировка - обязательна => Кассир 2 не сможет прочитать данные, пока Кассир 1 не закроет свою транзакцию. Так как на записи уже стоит эксклюзивная блокировка и разделяемую поставить нельзя. Но при этом разделяемых блокировок на записи может быть несколько - чтение другими "лицами" она не запрещает.  

Такой уровень изоляции носит название READ COMMITED

3 эффект: "Повторимое чтение"

Решение проблемы чтения неверных данных вследствие чтения одних и тех же данных, которые изменили между прочтением - ставить разделяемую блокировку на чтение, но не на время чтения, а до конца транзакции.
Разделяемая блокировка разрешает только наличие других разделяемых блокировок, поставить экслюзивную туда, где уже стоит разделяемая - нельзя => Кассир 2 не сможет изменить данные, пока Кассир 1 не закроет свою транзакцию.
 

Такой уровень изоляции носит название REPEATABLE READ

4 эффект: "Фантомы"

Решение проблемы чтения неверных данных вследствие чтения одних и тех же данных, которых стало больше/меньше между прочтением - ставить предикатную блокировку от начала чтения до конца транзакции.
Предикатная блокировка ставится на условие, а не на конкретную запись/таблицу данных. Если ограничиться разделяемой/экслюзивной, то нельзя будет изменить данные в таблице, но можно будет добавить новые.
Если же поставить блокировку на условие - нельзя => Кассир 2 не сможет изменить данные, пока Кассир 1 не закроет свою транзакцию.

Такой уровень изоляции носит название SERIALIZABLE

PS — это выдержка из моей книги для начинающих тестировщиков, написана в помощь студентам моей школы для тестировщиков