SQL Atom increment and locking Strategies - это безопасно?

У меня вопрос о SQL и стратегии блокировки. Например, предположим, что у меня есть счетчик просмотров изображений на моем веб-сайте. Если у меня есть sproc или аналогичный для выполнения следующих утверждений:

START TRANSACTION;
UPDATE images SET counter=counter+1 WHERE image_id=some_parameter;
COMMIT;

Предположим, что счетчик для конкретного image_id имеет значение "0" в момент времени t0. Если две сессии, обновляющие один и тот же счетчик изображений, s1 и s2, начинаются одновременно с t0, есть ли вероятность того, что эти два сеанса прочитают значение "0", увеличьте его до "1" , и оба попытаются обновить счетчик до "1" ', поэтому счетчик получит значение' 1 'вместо' 2 '?

s1: begin
s1: begin
s1: read counter for image_id=15, get 0, store in temp1
s2: read counter for image_id=15, get 0, store in temp2
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: write counter for image_id=15 to (temp2+1), which is also 1
s1: commit, ok
s2: commit, ok

Конечный результат: неверное значение '1' для image_id = 15, должно быть 2.

Мои вопросы:

Возможна ли эта сценария?
Если да, имеет ли уровень изоляции транзакции?
Существует ли конфликтный конфликт, который бы обнаружил такой конфликт как ошибку?
Можно ли использовать какой-либо специальный синтаксис, чтобы избежать проблемы (что-то вроде Compare and Swap (CAS) или явных методов блокировки)?

Мне интересен общий ответ, но если его нет, меня интересуют ответы на вопросы, связанные с MySql и InnoDB, так как я пытаюсь использовать этот метод для реализации последовательностей в InnoDB.

EDIT: Также возможен следующий сценарий, приводящий к такому же поведению. Я предполагаю, что мы находимся на изолированном уровне READ_COMMITED или выше, так что s2 получает значение с начала транзакции, хотя s1 уже написал "1" счетчику.

s1: begin
s1: begin
s1: read counter for image_id=15, get 0, store in temp1
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: read counter for image_id=15, get 0 (since another tx), store in temp2
s2: write counter for image_id=15 to (temp2+1), which is also 1
s1: commit, ok
s2: commit, ok

Ответ 1

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

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

Это означает, что в этом случае:

s1: read counter for image_id=15, get 0, store in temp1
s2: read counter for image_id=15, get 0, store in temp2
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: write counter for image_id=15 to (temp2+1), which is also 1

s2 будет ждать, пока s1 не решит написать счетчик или нет, и этот сценарий на самом деле невозможно.

Это будет:

s1: place an update lock on image_id = 15
s2: try to place an update lock on image_id = 15: QUEUED
s1: read counter for image_id=15, get 0, store in temp1
s1: promote the update lock to the exclusive lock
s1: write counter for image_id=15 to (temp1+1), which is 1 
s1: commit: LOCK RELEASED
s2: place an update lock on image_id = 15
s2: read counter for image_id=15, get 1, store in temp2
s2: write counter for image_id=15 to (temp2+1), which is 2

Обратите внимание, что в запросах InnoDB, DML не снимаются блокировки обновления из прочитанных записей.

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

Ответ 2

Если блокировка не выполнена должным образом, конечно, возможно получить условие гонки этого типа, и режим блокировки по умолчанию (чтение считается) позволяет это сделать. В этом режиме чтение только помещает общую запись в запись, поэтому они могут видеть 0, увеличивать ее и записывать 1 в базу данных.

Чтобы избежать этого состояния гонки, вам нужно установить исключительную блокировку операции чтения. Режимы "Serializable" и "Repeatable Read" concurrency будут делать это, а для операции в одной строке они в значительной степени эквивалентны.

Чтобы сделать его полностью атомным, вы должны:

Установите соответствующий , такой как Serializable. Обычно вы можете делать это из клиентской библиотеки или объяснять в SQL.
Начать транзакцию
Прочитать данные
Обновить его
Зафиксировать транзакцию.

Вы также можете принудительно блокировать чтение с помощью HOLDLOCK (T-SQL) или эквивалентного подсказки в зависимости от вашего диалекта SQL.

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

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

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

IIRC Oracle поддерживает автономные транзакции, но DB/2 до недавнего времени не работал, а SQL Server - нет. Я не знаю, поддерживает ли InnoDB их, но Grey и Reuter подробно рассказывают о том, насколько сложно они воплощать в жизнь. На практике я бы догадался, что это не так. YMMV.