В некоторых случаях использовать согласованное чтение нецелесообразно. Приведем пример. Допустим, что необходимо добавить новую строку в таблицу CHILD , предварительно убедившись, что для нее имеется родительская строка в таблице PARENT .
Предположим, что для чтения таблицы PARENT было использовано согласованное чтение, и в таблице была обнаружена родительская строка. Можно ли теперь безопасно добавить дочернюю строку в таблицу CHILD ? Нет, потому что в это время другой пользователь мог без вашего ведома удалить родительскую строку из таблицы PARENT .
В данной ситуации необходимо выполнить операцию SELECT в режиме блокировки, LOCK IN SHARE MODE .
Выполнение чтения в режиме совместного использования ( share mode ) означает, что считываются самые новые доступные данные и производится блокировка строк, чтение которых осуществляется. Если последние данные принадлежат еще не зафиксированной транзакции, мы ждем, пока транзакция не будет зафиксирована. Блокировка в режиме совместного использования не позволяет другим пользователям обновить или удалить читаемую строку. После того, как указанный выше запрос вернет родительскую строку ‘Jones’ , мы можем безопасно добавить дочернюю строку в таблицу CHILD и зафиксировать транзакцию. В этом примере показано, как использовать целостность ссылочных данных в своей программе.
Рассмотрим еще один пример. Пусть у нас есть поле целочисленного счетчика в таблице CHILD_CODES , которое мы используем для назначения уникального идентификатора каждой дочерней записи, добавляемой к таблице CHILD . Очевидно, что использование согласованного чтения или чтения в режиме совместного доступа для получения текущего значения счетчика не подходит, так как два пользователя базы данных могут получить одно и то же значение счетчика и создать дублирующиеся ключи при добавлении двух дочерних записей в таблицу.
Для этого случая возможны два способа произвести чтение и увеличить значение счетчика: (1) сначала обновить значение счетчика, увеличив его на 1, и только после этого прочитать его или (2) сначала прочитать счетчик в режиме блокировки FOR UPDATE , а после этого увеличить его значение:
Оператор SELECT . FOR UPDATE прочитает последние доступные данные с установкой отдельной блокировки на каждую считываемую строку. Таким образом, блокировка на строки устанавливается точно так же, как и в случае поиска по UPDATE .
in that page is a example that when use select for update and dont use lock in share mode and says
Here, LOCK IN SHARE MODE is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter
but first line of this page says
SELECT . LOCK IN SHARE MODE: The rows read are the latest available, ** so if they belong to another transaction ** that has not yet committed, the read blocks until that transaction ends.
is there a paradox?
i mean two users dont read the counter at the same time beacause if they belong to another transaction the read blocks until that transaction ends.
Представьте есть 4 таблицы
1. users(id, email, password);
2. users_statistics(id_user, money_spent_ad);
3. contest(id, start_date, end_date, bonus);
4. contest_points(id, id_user, id_contest, points);
Cвязи:
1. users. > 2. contest. > 3. contest_data. >
Задача: На проекте есть конкурсы для рекламодателей, каждый потраченная копеечка рекламодателем идет в users_statistics.money_spent_ad тип данных поля (double 14,6), задача состоит в следующем: на проекте есть конкурсы, за каждый потраченный рубль, система должна начислить 0.5 балла в таблицу в поле contest_points.points.
Допустим человек сначала потратил 0.40 руб, а потом еще 0.61, в общем он уже потратил 1.01 руб.
Как начислять баллы ?
Решение такое: Сначала мы читаем данные из таблицы и поля users_statistics.money_spent_ad далее рассчитываем кол-во баллов, расчет происходит так, нам за каждый потраченный рубль нужно начислять 0.5 балла.
Берём ту сумму которая уже потрачена, допустим потратил он уже 0.80 руб, далее берём ту сумму которая будет прибавлена к тем потраченным, допустим это 4.30, берём прибавляем уже у имеющейся 0.80 + 4.30 = 5.10
Затем от уже имеющейся цифр берём только целые цифры, то есть 5 и 0.
5 — 0 = 5. Далее 5 * 0.5 балла = 2.5 балла.
Думаю тут все понятно, проблема в том что, когда все эти расчеты происходят данные могут поменяться, потрачено было 0.80 изначально, но в момент расчетов которые производим выше, уже поменяется на допустим 2.30 и прибавится 5.50.
2.30 + 5.71 = 8.01, далее высчитываем 8 — 2 = 6. 6 * 0.5 = 3 балла. Итого получается, что в когда 1 расчет происходит могут поменяться данные, то есть затраченные деньги на рекламу, в первом расчеты мы получим на зачисление баллов 2.5, в 2 расчете 3 балла, 2.5 + 3 = 5.5 балла. Даже если эти запросы выполнятся паралелльно, то кол-во баллов вроде бы не поменяется, но меня это настораживает.
Алгоритм должен быть таким.
1. Взять данные из SELECT users_statistics
2. Посчитать сколько баллов нужно дать юзеру.
3. Зачислить баллы в таблицу contest_data.
4. Прибавить к уже потраченным деньгам сумму в таблице users_statistics
Я пошел в гугл, поискал про блокировки мускула.
Подходящая вещь как мне кажется это —
SELECT… LOCK IN SHARE MODE — Я понял это след. образом, что происходит первый расчет и когда происходит SELECT мы блочим конкретную запись на UPDATE в таблице users_statistics, далее если другие запросы пытаются ее поменять но там стоит блок, они ждут окончания завершения транзакции и выполняются. То есть создается список из очередей, как я понимаю.
Также из этой таблицы users_statistics может прочитать данные любой другой клиент.
SELECT… FOR UPDATE — все тоже самое, между переключениями блокировок может вклиниться транзакция и получит deadlock, это я так понимаю блокировка записи бесконечность.
Данном случае блокировка будет ставиться дважды, сначала совместная блокировка при чтении, затем исключительная при записи. Так как блокировок две, то есть теоретический шанс проскочить третьей между ними и вызвать deadlock. Также читать данные не получиться потом что блокировка, это не подходит.
Вообщем не знаю как верно поступить и туда ли я вообще иду.
Даже если применить SELECT… LOCK IN SHARE MODE я даже не понимаю как правильно это сделать.
Кто может помочь, подсказать направить в нужное русло, будут очень признателен, может мне вообще не нужны эти блокировки и транзакции.
