Как бороться с параллельными обновлениями в базах данных?

Каков общий способ решения параллельных обновлений в базе данных SQL?

Рассмотрим простую схему SQL (ограничения и значения по умолчанию не показаны), например

create table credits (
  int id,
  int creds,
  int user_id
);

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

Как работать с параллельными обновлениями этой таблицы? Несколько вариантов:

update credits set creds= 150 where userid = 1;

В этом случае приложение вернуло текущее значение, вычислило новое значение (150) и выполнило обновление. Это вызывает катастрофу, если кто-то другой делает то же самое в одно и то же время. Я предполагаю, что завершение обновления текущего значения и обновление в транзакции решат это, например. Begin; select creds from credits where userid=1; do application logic to calculate new value, update credits set credits = 160 where userid = 1; end; В этом случае вы можете проверить, будет ли новый кредит < 0 и просто усечь его до 0, если отрицательные кредиты не имеют смысла.
update credits set creds = creds - 150 where userid=1;

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

Так просто, какой принятый метод имеет дело с (довольно простой) проблемой, описанной выше, что, если db выдает ошибку?

Ответ 1

Использовать транзакции:

BEGIN WORK;
SELECT creds FROM credits WHERE userid = 1;
-- do your work
UPDATE credits SET creds = 150 WHERE userid = 1;
COMMIT;

Некоторые важные замечания:

Не все типы баз данных поддерживают транзакции. В частности, MySQL не поддерживает старый движок базы данных MySQL по умолчанию (по умолчанию до версии 5.5.5). Используйте InnoDB (новый по умолчанию), если вы находитесь на MySQL.
Транзакции могут быть прерваны по независящим от вас причинам. Если это произойдет, ваша заявка должна быть готова начать все заново, с НАЧАЛА РАБОТЫ.
Вам необходимо установить уровень изоляции SERIALIZABLE, иначе первый выбор может считывать данные, которые другие транзакции еще не зафиксировали (транзакции не похожи на мьютексы в языках программирования). Некоторые базы данных будут выдавать ошибку, если параллельно выполняются транзакции SERIALIZABLE, и вам придется перезапустить транзакцию.
Некоторые СУБД предоставляют SELECT.. FOR UPDATE, которая блокирует строки, полученные путем выбора, до завершения транзакции.

Объединение транзакций с хранимыми процедурами SQL может облегчить работу с последней частью; приложение просто вызовет одну хранимую процедуру в транзакции и повторно вызовет ее, если транзакция будет прервана.

Ответ 2

Для таблиц MySQL InnoDB это действительно зависит от уровня изоляции, который вы установили.

Если вы используете уровень по умолчанию 3 (REPEATABLE READ), вам нужно будет заблокировать любую строку, которая влияет на последующие записи, даже если вы находитесь в транзакции. В вашем примере вам нужно будет:

SELECT FOR UPDATE creds FROM credits WHERE userid = 1;
-- calculate --
UPDATE credits SET creds = 150 WHERE userid = 1;

Если вы используете уровень 4 (SERIALIZABLE), то достаточно простого SELECT, за которым следует обновление. Уровень 4 в InnoDB реализуется путем чтения-блокировки каждой прочитанной строки.

SELECT creds FROM credits WHERE userid = 1;
-- calculate --
UPDATE credits SET creds = 150 WHERE userid = 1;

Однако в этом конкретном примере, поскольку вычисление (добавление кредитов) достаточно просто для выполнения в SQL, просто:

UPDATE credits set creds = creds - 150 where userid=1;

будет эквивалентен SELECT FOR UPDATE, а затем UPDATE.

Ответ 3

В некоторых случаях оборачивать код внутри транзакции недостаточно, независимо от того, какой уровень изоляции вы определяете (например, при создании образа вы развернули свой код на 2 разных серверах в производстве).

Допустим, у вас есть эти шаги и 2 потока параллелизма:

1) open a transaction
2) fetch the data (SELECT creds FROM credits WHERE userid = 1;)
3) do your work (credits + amount)
4) update the data (UPDATE credits SET creds = ? WHERE userid = 1;)
5) commit

И этот график времени:

Time =  0; creds = 100
Time =  1; ThreadA executes (1) and creates Txn1
Time =  2; ThreadB executes (1) and creates Txn2
Time =  3; ThreadA executes (2) and fetches 100
Time =  4; ThreadB executes (2) and fetches 100
Time =  5; ThreadA executes (3) and adds 100 + 50
Time =  6; ThreadB executes (3) and adds 100 + 50
Time =  7; ThreadA executes (4) and updates creds to 150
Time =  8; ThreadB tries to executes (4) but in the best scenario the transaction
          (depending of isolation level) won't allow it and you get an error

Транзакция не позволяет переопределить значение creds неправильным значением, но этого недостаточно, потому что я не хочу, чтобы произошла ошибка.

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

Есть несколько способов сделать в SQL Server, и это один из них:

SELECT creds FROM credits WITH (UPDLOCK) WHERE userid = 1;

Если я воссоздаю предыдущую временную шкалу с этим улучшением, вы получите что-то вроде этого:

Time =  0; creds = 100
Time =  1; ThreadA executes (1) and creates Txn1
Time =  2; ThreadB executes (1) and creates Txn2
Time =  3; ThreadA executes (2) with lock and fetches 100
Time =  4; ThreadB tries executes (2) but the row is locked and 
                   it has to wait...

Time =  5; ThreadA executes (3) and adds 100 + 50
Time =  6; ThreadA executes (4) and updates creds to 150
Time =  7; ThreadA executes (5) and commits the Txn1

Time =  8; ThreadB was waiting up to this point and now is able to execute (2) 
                   with lock and fetches 150
Time =  9; ThreadB executes (3) and adds 150 + 50
Time = 10; ThreadB executes (4) and updates creds to 200
Time = 11; ThreadB executes (5) and commits the Txn2

Ответ 4

Для первого сценария вы можете добавить другое условие в where-clause, чтобы убедиться, что вы не будете перезаписывать изменения, сделанные одновременно пользователем. Например.

update credits set creds= 150 where userid = 1 AND creds = 0;

Ответ 5

Оптимистическая блокировка с использованием нового столбца timestamp может решить эту проблему concurrency.

UPDATE credits SET creds = 150 WHERE userid = 1 and modified_data = old_modified_date

Ответ 6

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

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

Ответ 7

Таблица может быть изменена, как показано ниже, ввести новую полевую версию для управления оптимистичной блокировкой. Это более экономичный и эффективный способ достижения более высокой производительности, чем использование блокировок на уровне базы данных создавать таблицы кредитов ( int id, int creds, int user_id, версия int );

выберите creds, user_id, версию из кредитов, где user_id = 1;

предположим, что это возвращает creds = 100 и version = 1

update credits set creds = creds * 10, version = version + 1 где user_id = 1 и version = 1;

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

Ответ 8

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

Чтобы избежать этого , вы должны уменьшить текущее значение кредита с запрошенной суммой (в нашем примере 1 кредит) и также проверить, где, если текущее значение минус запрашиваемая сумма больше или равна нулю

UPDATE credits SET creds = creds-1 ГДЕ creds-1 >= 0 и userid = 1

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

После этого запроса вы должны запустить ROW_COUNT(), который сообщает, был ли обновлен текущий критерий пользователя и критерии:

UPDATE credits SET creds = creds-1 WHERE creds-1>=0 and userid = 1
IF (ROW_COUNT()>0) THEN 
   --IF WE ARE HERE MEANS USER HAD SURELY ENOUGH CREDITS TO PURCHASE THINGS    
END IF;

Аналогичную вещь в PHP можно сделать так:

mysqli_query ("UPDATE credits SET creds = creds-$amount WHERE creds-$amount>=0 and userid = $user");
if (mysqli_affected_rows())
{
   \\do good things here
}

Здесь мы использовали, а SELECT... FOR UPDATE не TRANSACTION, но если вы поместили этот код внутри транзакции, убедитесь, что уровень транзакции всегда предоставляет самые последние данные из строки (в том числе и другие транзакции, уже выполненные). Вы также можете использовать ROLLBACK, если ROW_COUNT() = 0

Нижняя сторона WHERE credit- $amount >= 0 без блокировки строк:

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

Внимание:

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

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

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

Ответ 9

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