Mysql Переход на транзакции в InnoDB с LOCK TABLES MyISAM

[Royal Flash]

Здравствуйте.

Помогите перейти на транзакции и блокировки строк в InnoDB с LOCK TABLES MyISAM

Я решил перейти на InnoDB с MyISAM.
Есть 3 таблицы в БД: balance_user, balance_log, balance_comment (комментарии к транзакциям по балансу, они могут быть, могут не быть, по этому заведена отдельная таблица).

Таблица balance_user:
| user_id | .... | balance_sum |
Т.е. в этой таблице хранятся суммы баланса пользователей. Каждому пользователю - одна строка.

Таблица balance_log
| id | user_id | sum | .... |

При приходе/расходе средств в balance_user изменяется значение колонки balance_sum для каждого пользователя. Причем, если с пользовательским балансом операция совершается впервые, в balance_user нужно добавить строку, если информация о балансе уже есть - обновить. В balance_log строки всегда только добавляются, т.е. эта таблица - журнал операций с балансом. В balance_comment строки могут быть добавлены или нет, в зависимости от наличия комментариев.
За один раз могут быть изменены балансы более одного пользователя.

На MyISAM все просто: в скрипте блокируются все 3 таблицы (LOCK TABLES ... WRITE), далее узнать MAX(id) balance_log, после сгененрировать sql-запросы insert/update для balance_user, создать insert для balance_log и, если требуется, insert для balance_comment.
И если с UPDATE все просто - сколько изменений в пользовательских балансах balance_user, столько и запросов UPDATE, то с INSERT немного сложнее: чтобы не заваливать БД пачкой запросов INSERT, все INSERT к одной таблице формируются в один запрос (INSERT INTO balance_log (id, user_id, sum) VALUES (1, 1, 30), (2, 195, 5598) ... ).
Поскольку после INSERT, в котором добавляется более одной строки, проблематично узнать ID созданный autoincremet для каждой строки, ID для balance_log формируется в скрипте, создающем запросы (для этого нужен первый запрос SELECT MAX(id) FROM balance_log).
По этому же ID balance_log привязывается и balance_comment.

Схема запросов MyISAM:

LOCK TABLES ... WRITE

SELECT MAX(id) FROM balance_log

// тут формируются запросы UPDATE/INSERT для balance_user и INSERT для balance_log, balance_comment

UPDATE balance_user SET balance_sum = 234 WHERE user_id = 1
UPDATE ...

INSERT INTO balance_user (user_id, balance_sum) VALUES (2, 304), (3, 58) , ...

INSERT INTO balance_log (id, user_id, sum) VALUES (1, 1, 30), (2, 195, 5598), ....

INSERT INTO balance_comment (balance_log_id, comment) VALUES (1, "Коментарий"), ....

UNLOCK TABLES

Подскажите, как переписать эту логику с учетом транзакций в InnoDB, чтобы блокировать не все таблицы, а только строки, с которыми будет проходить UPDATE и интервал ID, с которыми будет вставляться INSERT?

 

[WMix]

start transaction, select for update или о чем?

 

[Royal Flash]

Да, об именно этом. По скольку с InnoDB сталкиваюсь впервые, просьба расставить блокировки по схеме.

 

[WMix]

может глазками поглядишь на эти 2 запроса в мануале и конкретней вопрос задашь? заодно http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

 

[Royal Flash]

Поглядел, перечитал мануал, книжки, статьи в нете, но до конца так и не разобрался. Вопросов появилось больше, чем было до чтения всего вышеперечисленного. Тяжело дается понятие ISOLATION LEVEL.

Предполагаю делать так:

// Чтобы не зависеть от настроек сервера
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

START TRANSACTION

SELECT MAX(id) AS max_id FROM balance_log
// $max_id = MAX(id);

// Блокировка на запись интервала новых id таблицы balance_log, чтобы безопасно сгенерировать новые id в скрипте
SELECT id FROM balance_log WHERE id > $max_id FOR UPDATE

// Блокировка на запись интервала новых id таблицы balance_comment
SELECT id FROM balance_comment WHERE id > $max_id FOR UPDATE

// Блокировка id добавляемых пользователей
SELECT id FROM balance_user WHERE id IN (идентификаторы добавляемых пользователей) FOR UPDATE

// тут формируются запросы UPDATE/INSERT для balance_user и INSERT для balance_log, balance_comment

// Блокировка id обновляемых пользователей
SELECT id FROM balance_user WHERE id IN (идентификаторы обновляемых пользователей) FOR UPDATE

UPDATE balance_user SET balance_sum = 234 WHERE user_id = 1
UPDATE ...

INSERT INTO balance_user (user_id, balance_sum) VALUES (2, 304), (3, 58) , ...

INSERT INTO balance_log (id, user_id, sum) VALUES (1, 1, 30), (2, 195, 5598), ....

INSERT INTO balance_comment (balance_log_id, comment) VALUES (1, "Коментарий"), ....

COMMIT

Не уверен, что схема запросов верна. Больше всего беспокоит вопрос, что если 2 таких транзакции будут выполняться параллельно, $max_id у них окажется идентичным, и одна из транзакций откатится. Как за один запрос выбрать MAX(id) и заблокировать интервал строк id >= MAX(id)?

Также, подскажите, плз, как лучше хранить базы InnoDB на сервере: в одном файле, как установлено по умолчанию, или все таки имеет смысл разбивать все таблицы по файлам?

 

[WMix]

странная процедура, как и структура бд

если 2 таких транзакции будут выполняться параллельно, $max_id у них окажется идентичным.. да! предложу
Serializable, но ты явно намудрил. напрашивается sequence,

Также, подскажите, плз, как лучше хранить базы InnoDB на сервере: в одном файле, как установлено по умолчанию, или все таки имеет смысл разбивать все таблицы по файлам?

тебе не всеравно?

 

[Royal Flash]

странная процедура, как и структура бд
если 2 таких транзакции будут выполняться параллельно, $max_id у них окажется идентичным.. да! предложу
Serializable, но ты явно намудрил. напрашивается sequence,
тебе не всеравно?

Чем поможет Serializable для блокировки интервала => max(id)? Ведь это блокирующее чтение, параллельная транзакция все равно сможет прочитать тот же max(id)...
Sequence - это такой "велосипед" для MySQL? Нет, спасибо, и так "велосипедов" хватает Ничего лучше, чем блокировать всю таблицу запросом SELECT id FROM balance_user WHERE id > 0 FOR UPDATE, перед SELECT MAX(id) не придумал.

тебе не всеравно?

Было бы все равно - не спрашивал бы. Если такая возможность есть - значит это зачем-то нужно?

 

[AnrDaemon]

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

 

[Royal Flash]

Если ты не знаешь, зачем тебе это нужно, значит, тебе это не нужно.

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

 

[WMix]

Чем поможет Serializable

грубо, это значит транзакции будут работать поочереди

Sequence - это такой "велосипед" для MySQL

это поле auto_increment

 

[Royal Flash]

это значит транзакции будут работать поочереди

Даже в Wikipedia написано: Транза́кция - группа последовательных операций (т.е. операций "поочереди") с базой данных, так что уровень изоляции на это никак не влияет. Serializable - это добавление ко всем SELECT в интервале транзакции LOCK IN SHARE MODE, что ни как не спасет от параллельного чтения 2-мя параллельными транзакциями max(id) с идентичным результатом (проверил тестами). Спасибо, конечно, что пытаетесь мне помочь, но давать неправильные советы - не есть хорошо...

это поле auto_increment

- да, согласен, на счет "велосипеда" был не прав.

Методом тестирования, обнаружил, что достаточно запустить SELECT MAX(id) AS max_id FROM balance_log FOR UPDATE при уровне изоляции REPEATABLE READ и этот же запрос из параллельной транзакции будет ждать, пока не отработает первая транзакция. Также, что очень важно, пока стоит блокировка FOR UPDATE в balance_log, невозможно в другом запросе вставить в таблицу строку с id > MAX(id), ни автоинкрементом, ни явным указанием id, хотя в промежутки id < MAX(id) записи вставляются без проблем, но это уже никак не влияет на мою транзакцию.

 

[WMix]

так что уровень изоляции на это никак не влияет.

влияет,

что ни как не спасет от параллельного чтения 2-мя параллельными транзакциями max(id) с идентичным результатом

не спасет

Методом тестирования, обнаружил, что достаточно запустить SELECT MAX(id) AS max_id FROM balance_log FOR UPDATE при уровне изоляции REPEATABLE READ и этот же запрос из параллельной транзакции будет ждать, пока не отработает первая транзакция.

а вот это странно, у меня не получилось, все хуже, serializable строже, и если при REPEATABLE READ работает, то при serializable будет тем более

 

[fixxxer]

Royal Flash, разумеется, строка же заблокирована. Плюс в случае вставки еще и tail lock. Вот если в одном запросе id=1 а в другом id=2 - тогда без проблем.

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

 

[Royal Flash]

а вот это странно, у меня не получилось, все хуже, serializable строже, и если при REPEATABLE READ работает, то при serializable будет тем более

С serializable я пробовал без FOR UPDATE - так не работало как нужно, с FOR UPDATE, конечно, работало так же, как и при REPEATABLE READ.

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

У меня, это так и организовано. У пользователя есть текущий баланс (balance_user), чтобы его можно было быстро прочитать и лог операций (balance_log), в котором также хранится текущий баланс (в последней записи) и все операции по балансу. Когда происходит изменение баланса высчитывается сумма между балансом в balance_user и суммой операции, и уже новые данные вносятся в balance_log, а в balance_user изменяется баланс. Таким образом, размер БД немного вырастает, за счет избыточного хранения данных, но повышается надежность - можно отследить все операции по балансу. Хотя, возможно, я не совсем понял суть Вашего совета?

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

 

[fixxxer]

Royal Flash, я имел ввиду так

begin;
insert into user_billing_operations (user_id, operation_type_id, amount) values (1, 1, 100);
insert into user_billing_operations (user_id, operation_type_id, amount) values (1, 3, 200);
insert into user_billing_operations (user_id, operation_type_id, amount) values (1, 9, -100);
update user_billing_state set total_balance = (select sum(amount) from user_billing_operations where user_id = 1) where user_id = 1;
commit;

 

[Royal Flash]

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

begin;
insert into user_billing_operations (user_id, operation_type_id, amount) values (1, 1, 100), (2, 1, 200), (3, 1, 50);
update user_billing_state set total_balance =  total_balance + 100 where user_id = 1;
update user_billing_state set total_balance =  total_balance + 200 where user_id = 2;
update user_billing_state set total_balance =  total_balance + 50 where user_id = 3;
commit;

P.S. За один раз нужно пересчитывать баланс у разных пользователей, а не у одного, несколько операций по балансу за раз.

 

[fixxxer]

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

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

 

[Royal Flash]

Спасибо, приму на вооружение и Ваш вариант.