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

Заключение кода в транзакцию недостаточно в некоторых случаях, независимо от того, какой уровень изоляции вы определяете (например, для создания образа, на котором вы развернули код на 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

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

Вместо этого я предпочитаю более медленный процесс, который никогда не завершится неудачей, и я решил проблему с «блокировкой строки базы данных» в тот момент, когда я получаю данные (шаг 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's 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

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

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

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

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

ОБНОВЛЕНИЯ кредитов УСТАНОВЛЕНО creds = creds-1 ГДЕ creds-1> = 0 и ИД пользователя = 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 без блокировки строк являются:

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

Внимание:

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

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

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

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

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

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

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

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

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

Если вы используете уровень по умолчанию 3 (ПОВТОРНОЕ ЧТЕНИЕ), вам потребуется заблокировать любую строку, которая влияет на последующие записи, даже если вы находитесь в транзакции. В вашем примере вам потребуется:

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.