Подскажите по MySQL transactions

почему нельзя залочить таблицы? производительность при этом никак не падает, кроме конечно гарантирования непротиворечивости таблиц. Вам ведь баланс надо обновить? Никто и не заметит, что таблица была залочена на малое время (если и «заметит», то как раз в том случае, в котором случился-бы сбой). Просто нужно как можно быстрее разлочить таблицы.

если баланс - это бабло, то схема таблицы спроектирована неправильно.

В финансовых/бухгалтерских приложениях баланс счета не хранится, вместо этого хранятся записи о движении денег между счетами, а баланс считается суммированием транзакций за определённый период

Что если другая транзакция в это время обновит balance?

Т.е. это единственный, увиденный тобой косяк? $amount = -1000?

1. s/mysql/postgresql/i 2. хранимка (там все просто, проверку можно не делать) 3. триггер ON UPDATE, INSERT в котором и делать проверку (и бросать исключение в случае чего)

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

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

баланс считается один раз, а потом он хранится в кэше до следующей записи в таблицу :)

Не очень понял то что вы предлагаете. А если аккаунт существует уже несколько лет и за это время по нему десятки тысяч транзакций это что все их суммировать каждый раз?

$amount конечно надо преверять но вопрос не в этом и проверятся на то что он > 0 он будет php скриптом так что нет смысла расписывать весь код.

Вижу вопрос обширный. Что можете посоветовать почитать на данную тему. Или в код каких open source проектов (желательно php) заглянуть где это реализовано.

dizza

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

на балансе 15руб. Первый скрипт снимает 15-10=5, второй скрипт ОДНОВРЕМЕННО снимает 15-10=5. Остаётся +5, хотя должно быть -5. В первом посте ведь 2 запроса, и первый из второго экземпляра может вклинится между первым и вторым из первого экземпляра. И триггер тут не поможет.

psp13

Или в код каких open source проектов (желательно php) заглянуть где это реализовано.

в любых. кроме хелловорда и быдлокода.

Но вы не нуждаетесь в блокировке, если вам удалось всё сделать одним UPDATE - он выполняется атомарно, ЕМНИП.

Первый скрипт снимает 15-10=5, второй скрипт ОДНОВРЕМЕННО снимает 15-10=5. Остаётся +5, хотя должно быть -5.

Шо, в этом вашем мускуле update автоматически не лочит строку? В постгре будет так: первый апдейт сделает 15-10, залочив строку, параллельный поток будет ждать первый (ну или на оборот первый будет ждать второй), попытается сделать 5 - 10, тут тригер обломает транзакцию. В итоге один update выполнится, а другой нет.

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

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

а потом он хранится в кэше до следующей записи в таблицу :)

Да, но потом я подумал, а с фига ли я постгру стока памяти отдал по кэш. Он и так с кэшом. Так нужно было наворачивать инфраструктуру, а так просто заюзал в качестве того кэша просто таблицу со счетами. По производительности устроило.

Мда InnoDB и правда не лочит. Ну че, select ... for update тогда

dizza

Шо, в этом вашем мускуле update автоматически не лочит строку?

лочит конечно. с одним UPDATE'ом проблем не будет.

dizza

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

если я правильно понял, это как раз и является проблемой.

Не очень понял то что вы предлагаете. А если аккаунт существует уже несколько лет и за это время по нему десятки тысяч транзакций это что все их суммировать каждый раз?

И в чем проблема? Все банки так делают, у них все работает - что они делают не так? :)

1. s/mysql/postgresql/i

Можно и mysql оставить т.к. хранимки и триггеры есть в нем.

Вот чтиво в тему: http://martinfowler.com/articles/lmax.html

В кратце: они делают ежедневные снепшоты состояния в памяти, поэтому нужно проигрывать лог не за несколько лет, а максимум за сутки. И это якобы занимает несколько минут. Но опять же, в той системе, что я пишу максимум несколько транзаций в секунду, и вся эта бадяга с event-sourcing была бы over engineering. Мой алгоритм таков (псевдокод):

  transfer_money(account_id1, account_id2, amount):
    begin transaction;
    balance1;
    balance2;
    if account_id1 > account_id2:
      balance1 := select balance from account where id = account_id1 for update;
      balance2 := select balance from account where id = account_id2 for update;
    else:
      balance2 := select balance from account where id = account_id2 for update;
      balance1 := select balance from account where id = account_id1 for update;
    assert balance1 >= amount;
    balance1 := balance1 - amount;
    balance2 := balance2 + amount;
    insert into transaction(account_id, diff, balance) values(account_id1, -amount, balance1);
    insert into transaction(account_id, diff, balance) values(account_id2, amount, balance2);
    update set balance = :balance1 where id = account_id1;
    update set balance = :balance2 where id = account_id2;
    commit transaction;

Заметьте, что код работает без триггеров за счет лока и явной проверки. Для защиты от дедлоков порядок локов строго определен.

Ваш код для чего? Для PostgreSQL или для MySQL? Почему имеет значение account_id1 > account_id2 ? И всё равно же в вашем коде balance в account, зачем тогда table transaction и смысл какой что то перещитывать?

Не так. Периодически (в конце каждого отчетного периода?) фиксируются остатки средств на счетах, от которых и пляшут весь текущий период. Смысл каждый раз пересчитывать тонны транзакций, если 1) это очень долго, 2) отчетный период закрыт для редактирования?

http://martinfowler.com/apsupp/accounting.pdf

Ходят слухи, что мусклевые хранимки и триггеры пишут отъявленные геи.

А если запись в таблицу происходит раз 100 в секунду, то вы каждый раз пересчитываете баланс за n предыдущих лет?

Для PostgreSQL или для MySQL?

Работает в PostgreSQL. Но и в MySQL должно сработать, там же есть select ... for update?

Почему имеет значение account_id1 > account_id2 ?

Что бы дедлок не словить.

зачем тогда table transaction и смысл какой что то перещитывать?

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

Отказаться от хранения в balance я думаю если захочется разменять update-ы на insert-ы. insert вроде как легче update для базы. Но тогда придется делать всю эту бадягу со снепшотами и кэшом. Пока не кусает, и хз когда укусит.

Не, каждый раз происходит вставка записи в БД и изменение значения в кэше. Кэш как бы в онлайне пересчитывается.

Спасибо за пояснения. Про deadlock я бы сам точно не догадался.

Пожалуйста. Я сам в этой области тока начал разбираться. Вот может тут публично мои наработки заодно кто-то покритикует.

Я тут подумал, что если таки написать триггер на проверку amount > 0, то можно написать более эффективную реализацию:

transfer_money(account_id1, account_id2, amount):
    begin transaction;
    balance1 = update account set balance = balance - :amount where id = account_id1 returning balance;
    balance2 = update account set balance = balance + :amount where id = account_id2 returning balance;
    insert into transaction(account_id, diff, balance) values(account_id1, -amount, balance1);
    insert into transaction(account_id, diff, balance) values(account_id2, amount, balance2);
    commit transaction;

Отличие в том, что локи будут implicit и только на вермя выполнения каждого update.

Ну не знаю. Когда я занимался всей этой финансовой ерундой, мы для каждого контрагента создавали операцию, в которую писали в т.ч. и баланс + обновляли баланс самого контрагента в таблице контрагентов. Так потом легко и просто можно вытащить баланс любого за любое число, так же это упрощает отлов ошибок. Моё скромное мнение: кэш по определению надёжным быть не может, чтобы на него опираться при работе с деньгами.

Ну да, я там отписался что я к такому же решению пришел. Всякие кэши это уже для каких-то супер нагруженных систем.

Про транзакции вам сюда, ИМХО:
http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html
Выберите подходящий transaction isolation level.

И кстати, вопрос ваш не совсем корректен: вы ведь один баланс уменьшаете, а второй увеличиваете (если конечно у вас значение $from не равно значению $to).
Так что транзакция/лок не гарантирует, что первый баланс (от которого вы отнимаете значение) не станет негативным.

Понятно что не гарантирует - в этом и весь вопрос.

А может в PostgreSQL есть какой то тип данных который может принимать только положительные значения и выдавать ошибку если значение присваивается отрицательное (таким образом ROLLBACK transaction)?

MySQL поступает совершенно по идиотски с BIGINT UNSIGNED:

mysql> create table test (
    -> id bigint unsigned auto_increment primary key,
    -> balance bigint unsigned not null
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> insert into test set balance = 5;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+---------+
| id | balance |
+----+---------+
|  1 |       5 |
+----+---------+
1 row in set (0.00 sec)

mysql> update test set balance = balance - 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+----+----------------------+
| id | balance              |
+----+----------------------+
|  1 | 18446744073709551611 |
+----+----------------------+
1 row in set (0.00 sec)

mysql>

Вот зачем спрашивается так сделали? Надо бы ошибку в этом случае. Хотя BIGINT UNSIGNED в любом случае не подходит, надо DECIMAL UNSIGNED.

А может в PostgreSQL есть какой то тип данных который может принимать только положительные значения и выдавать ошибку если значение присваивается отрицательное (таким образом ROLLBACK transaction)?

есть и не только. см. CHECK, например, здесь http://www.postgresql.org/docs/9.0/static/ddl-constraints.html

Почитал http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL Решил перевести проект на PostgreSQL. Он надёжнее, особенно для финансовых приложений. MySQL подходит только для простейших вещей. Надёжность и гарантии data consistency должны стоять на первом месте. А в MySQL одни gotchas. Вот те же trigger'ы можно обойти даже в InnoDB. InnoDB имеет множество limitations и при росте нагрузки судя по бенчмаркам PostgreSQL обходит mysql's InnoDB. Да и покупка MySQL ораклом настораживает.

Решил перевести проект на PostgreSQL.

благославляю