Блокировка (СУБД) Пессимистическая Оптимистическая Совместная Исключительная Строчная Гранулярная

С блокировками в СУБД при взгляде с птичьего полета все очень просто (мы говорим для простоты только о блокировках строк) - они бывают либо shared, либо exclusive, как и везде, но вот ведут себя они не то, чтобы неожиданно, но своеобразно: во всякой СУБД (кроме таких экзотических, по крайней мере для меня, про которые я даже и не слышал) всякая транзакция все блокировки, возникающие в результате изменения данных (они, понятное дело, все exclusive), освобождает только и исключительно при выполнении commit или rollback. Это называется двухфазной блокировкой (2PL, - ни в коем случае не надо путать с двухфазным коммитом (2PC), это совсем другая история, о которой как-нибудь в следующий раз).
(Мне тут сообщают, что для знающего вопрос изложено примитивно, а для незнающего - непонятно. Ну... да. Так что приношу свои извинения).
Уровни изоляции.
Блокировки, возникающие при чтении данных - shared - освобождаются либо после окончания выполнения запроса, либо после завершения транзакции. Во втором случае мы получаем что? Правильно, уровень изоляции repeatable read - то, что мы залочили, пусть и на чтение, поменять уже никто не сможет; дальше уже сообразить нетрудно, что у нас уровень изоляции задается временем жизни блокировок:
1. Грязное чтение - то, что поменяли, держим до конца транзакции, то, что прочитали, отдаем; заодно при чтении не блокируемся об exclusive lock других транзакций, т.е. видим незакоммиченные данные.
2. read commited - все то же самое, но наши shared блокировки не дружат с exclusive других транзакций. Повторное чтение, как нетрудно видеть, не реализуется (прочитали про Пупкина, пошли куда-то еще, тут прибежала какая-то другая транзакция и Пупкин оказался Пипкиным - запросто).
3. повторное чтение - все то же самое, но еще и наши shared locks живут до конца транзакции. Обратите внимание, что другая транзакция Пупкина на Пипкина уже не переделает - shared lock остается.
4. serializable - эксклюзивно лочим таблицы, дабы не видеть фантомов.
5. serializable read only - это совершенно нестандартный режим, есть как минимум в оракле и постгресе. Нужен, если честно, нечасто, но если нужен, то вы восхвалите бога, что он у вас есть. Ну или наоборот, проклянете судьбу.

теорема о сериализации (Граф предшествования (граф сериализации)).
Если чуть-чуть отвлечься от вышесказанного и задать себе простой вопрос - вот есть таблица, активно изменяемая множеством транзакций; а, собственно, сколько в ней записей в данный момент времени? - то ответ будет довольно грустный: вообще говоря, неизвестно - потому что для подсчета числа записей надо иметь образ таблицы на какой-то момент времени, а она, вообще говоря, целиком никогда не доступна - что-нибудь да заблокировано. Об этом говорит сайт https://intellect.icu . Вывод отсюда довольно неприятный- оч. хорошо, но а как же делать бекап, если таблица в общем случае целиком никогда не доступна? Ставить общий лок на все? Ну, в принципе можно, но большие субд могут бекапиться сутками, и это, наверное, не самое лучшее решение. Что же делать?
К счастью, в области СУБД существует свое маленькое чудо: теорема о сериализации (это, кстати, совершенно нормальная математическая теорема, если что). Звучит она так:

(Формулировка отвратительная, я это прекрасно понимаю, но лучше что-то не придумывается. Transactional Information Systems: Theory, Algorithms, and the Practice of Concurrency Control and Recovery;

Таким образом: Если все транзакции в смеси подчиняются протоколу двухфазной блокировки, то для всех чередующихся графиков запуска существует возможность упорядочения. ).
Доказательство я не приведу - оно довольно громоздкое и несколько, если можно так сказать, нудноватое; объяснение, если подумать, относительно просто - если две транзакции модифицируют разные строки, то, в общем-то, неважно, в каком порядке они будут выполнены; если же они модифицируют общие строки, то либо одна транзакция будет выполнена позже другой (т.е. когда она дождется освобождения всех требуемых строк, заблокированной первой, то, соответственно, будет выполнена позже - блокировки-то освобождаются при коммите; нас, кстати, интересуют только операции модификации), либо они взаимно заблокируются и получится дедлок; дедлок, таким образом, это не только взаимоблокировка, это и невозможность совершить нормальный бекап; а нормальный бекап, таким образом, всегда имеет возможность бекапить именно по одной транзакции последовательно (там, конечно, все хитрее, но в логическом смысле именно так и есть).
Теперь, думаю, понятно, почему все СУБД используют 2PL.
Ну и отсюда же мое глубоко юмористическое отношение ко всяким редизам и монгам - все это творчество, по большому счету, один большой распределенный race condition; если же его, кондишн этот, изжить, то получится - правильно - обычная СУБД, и вопрос только в том, до какой степени она будет хуже того, что уже давным-давно сделано.

Транзакции

Транзакцией называется последовательность операций над данными имеющая начало и конец

Транзакция это последовательное выполнение операций чтения и записи. Окончанием транзакции может быть либо сохранение изменений (фиксация, commit) либо отмена изменений (откат, rollback). Применительно к БД транзакция это нескольких запросов, которые трактуются как единый запрос.

Транзакции должны удовлетворять свойствам ACID

Атомарность. Транзакция либо выполняется полностью либо не выполняется вовсе.

Согласованность. При завершении транзакции не должны быть нарушены ограничения накладываемые на данные (например constraints в БД). Согласованность подразумевает, что система будет переведена из одного корректного состояния в другое корректное.

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

Устойчивость. После фиксации изменения не должны быть утеряны.

Журнал транзакций

Журнал хранит изменения выполненные транзакциями, обеспечивает атомарность и устойчивость данных в случае сбоя системы

Журнал содержит значения, которые данные имели до и после их изменения транзакцией. Write-ahead log strategy обязывает добавлять в журнал запись о предыдущих значениях до начала, а о конечных после завершения транзакции. В случае внезапной остановки системы БД читает лог в обратном порядке и отменяет изменения сделанные транзакциями. Встретив прерванную транзакцию БД выполняет ее и вносит изменения о ней в журнал. Находясь в состоянии на момент сбоя, БД читает лог в прямом порядке и возвращает изменения сделанные транзакциями. Таким образом сохраняется устойчивость транзакций которые уже были зафиксированы и атомарность прерванной транзакции.

Простое повторное выполнение ошибочных транзакций недостаточно для восстановления.

Пример. На счету у пользователя 400$ и пользователь решает снять их через банкомат. Выполняются две транзакции. Первая читает значение баланса и если на балансе достаточно средств выдает деньги пользователю. Вторая вычитает из баланса нужную сумму. Допустим, произошел сбой системы и первая операция не выполнилась, а вторая выполнилась. В этом случае мы не можем повторно выдать деньги пользователю без возврата системы в изначальное состояние с положительным балансом.

Уровни изоляции

Чтение фиксированных данных (Read Committed)

Проблема грязного чтения (Dirty Read) заключается в том, что транзакция может прочесть промежуточный результат работы другой транзакции.

Пример. Начальное значение баланса 0$. Т1 добавляет к балансу 50$. Т2 считывает значение баланса (50$). Т1 отменяет изменения и завершается. T2 продолжает выполнение располагая неверными данными о балансе.

Решением является чтение фиксированных данных (Read Committed) запрещающее читать данные, измененные транзакцией. Если транзакция A изменила некоторый набор данных, то транзакция B при обращении за этими данными вынуждена ожидать завершения транзакции A.

Повторяемое чтение (Repeatable Read)

Проблема потерянных изменений (Lost Updates). Т1 сохраняет изменения поверх изменений Т2.

Пример. Начальное значение баланса 0$ и две транзакции одновременно пополняют баланс. T1 и T2 читают баланс равный 0$. Затем T2 прибавляет 200$ к 0$ и сохраняет результат. T1 прибавляет 100$ к 0$ и сохраняет результат. Итоговый результат 100$ вместо 300$.

Проблема неповторяемого чтения (Unrepeatable read). Повторное чтение одних и тех же данных возвращает разные значения.

Пример. Т1 читает значение баланса равное 0$. Затем Т2 добавляет к балансу 50$ и завершается. Т1 повторно читает данные и обнаруживает несоответствие с предыдущим результатом.

Повторяемое чтение (Repeatable Read) гарантирует что повторное чтение вернет тот же результат. Данные прочитанные одной транзакцией запрещено менять в других до завершения транзакции. Если транзакция A прочла некоторый набор данных, то транзакция B при обращении за этими данными вынуждена ожидать завершения транзакции A.

Упорядоченное чтение (Serializable)

Проблема фантомного чтения (Phantom Reads). Два запроса выбирающие данные по некоему условию возвращают разные значения.

Пример. T1 запрашивает количество всех пользователей баланс которых больше 0$ но меньше 100$. T2 вычитает 1$ у пользователя с балансом 101$. T1 повторно выполняет запрос.

Упорядоченное чтение (Serializable). Транзакции выполняются как полностью последовательные. Запрещается обновлять и добавлять записи, подпадающие под условия запроса. Если транзакция A запросила данные всей таблицы, то таблица целиком замораживается для остальных транзакций до завершения транзакции A.

Планировщик (Scheduler)

Устанавливает очередность в которой должны выполняться операции при параллельно протекающих транзакциях

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

Механизмы контроля параллельных заданий (Concurrency Control)

Оптимистический основан на обнаружении и разрешении конфликтов, пессимистический на предотвращении возникновения конфликтов

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

При пессимистическом подходе первый пользователь захвативший данные препятствует получению данных остальным. Если конфликты редки разумно выбрать оптимистическую стратегию, так как она обеспечивает более высокий уровень параллелизма.

Блокировка (Locking)

Если одна транзакция заблокировала данные, то остальные транзакции при обращении к данным обязаны ждать разблокировки

Блок может накладываться на базу данных, таблицу, ряд или аттрибут. Совместный захват (Shared Lock) может быть наложен на одни данные несколькими транзакциями, разрешает всем транзакциям (включая наложившую) чтение, запрещает изменение и монопольный захват. Монопольный захват (Exclusive Lock) может быть наложен только одной транзакцией, разрешает любые действия наложившей транзакции, запрещает любые действия остальным.

Взаимоблокировкой считается ситуация когда транзакции оказываются в режиме ожидания, длящемся бесконечно долго

Пример. Первая транзакция ждет освобождения данных захваченных второй, в то время как вторая ждет освобождения данных, захваченных первой.

Оптимистическое решение проблемы взаимоблокировок позволяет взаимоблокировке произойти, но затем восстанавливает систему откатывая одну из транзакций, участвующих во взаимоблокировке

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

Каждой транзакции T присваивается временная метка TS содержащая время начала выполнения транзакции.

Wait-Die.

Если TS(Ti) < TS(Tj), то Ti ждет, иначе Ti откатывается и начинается заново с той же временной меткой.

Если молодая транзакция захватила ресурс, а более старая запрашивает тот же ресурс, то старшей транзакции позволено ожидать. Если более старая транзакция захватила ресурс, то молодая транзакция запрашивающая этот ресурс будет откачена.

Wound-wait.

Если TS(Ti) < TS(Tj), то Tj откатывается и начинается заново с той же временной меткой, иначе Ti ждет.

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

Пессимистическое решение проблемы взаимоблокировок не позволяет транзакции начать выполнение если есть риск возникновения взаимоблокировки

Для обнаружения взаимоблокировки строится граф (граф ожидания, wait-for-graph), вершины которого транзакции, а ребра направлены от транзакций ожидающих освобождения данных к транзакции захватившим эти данные. Считается что взаимоблокировка произошла, если граф имеет зацикленность. Построение графа ожидания, особенно в распределенных БД, дорогостоящая процедура.

Двухфазная блокировка — предотвращение взаимоблокировок путем захвата всех ресурсов используемых транзакцией в начале транзакции и освобождения их в конце

Все блокирующие операции должны предшествовать первой разблокирующей. Имеет две фазы — Growing Phase при которой происходит накопление захватов и Shrinking Phase при которой происходит освобождение захватов. При невозможности захвата одного из ресурсов транзакция начинается сначала. Возможна ситуация когда транзакция не сможет захватить требуемые ресурсы, например если несколько транзакций будут конкурировать за одни ресурсы.

Двухфазный коммит обеспечивает выполнение коммита на всех репликах БД

Каждая БД вносит информацию о данных которые будут изменены в лог и отвечает координатору ОК (Voting Phase). После того как все ответили ОК координатор отсылает сигнал обязывающий всех произвести коммит. После коммита сервера отвечают ОК, если хоть один не ответил ОК, то координатор отсылает сигнал отмены изменений всем серверам (Completion Phase).

Метод временных меток

Более старая транзакция откатывается при попытке доступа к данным, задействованным более молодой транзакцией

Каждой транзакции назначается временная метка TS соответствующая времени начала выполнения. Если Ti старше Tj, то TS(Ti) < TS(Tj).

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

Когда транзакция T запрашивает чтение данных Q возможны два варианта.

Если TS(T) < W-TS(Q), то есть данные были обновлены более молодой транзакцией, то транзакция T откатывается.

Если TS(T) >= W-TS(Q), то чтение выполняется и R-TS(Q) становится MAX(R-TS(Q), TS(T)).

Когда транзакция T запрашивает изменение данных Q возможны два варианта.

Если TS(T) < R-TS(Q), то есть данные уже были прочитаны более молодой транзакцией и если произвести изменение, то возникнет конфликт. Транзакция T откатывается.

Если TS(T) < W-TS(Q), то есть транзакция пытается перезаписать более новое значение, транзакция T откатывается. В остальных случаях изменение выполняется и W-TS(Q) становится равным TS(T).

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

Одно из решений каскадных откатов. Транзакция выполняет все операции записи в конце, причем остальные транзакции обязаны ожидать завершения этой операции. Транзакции ожидают коммита перед чтением.

Thomas write rule — вариация метода временных меток при которой данные обновленные более молодой транзакцией запрещено перезаписывать более старой

Транзакция T запрашивает изменение данных Q. Если TS(T) < W-TS(Q), то есть транзакция пытается перезаписать более новое значение, транзакция T не откатывается как в методе временных меток.