По долгу службы мне приходится иногда проводить собеседования на позицию "[старший|младший] разработчик python/django", «тимлид». К моему великому удивлению я обнаружил, что 9 из 10 соискателей, в резюме которых значатся слова " Mysql/Innodb/transactions/triggers/stored proc etc.", абсолютно ничего не могут рассказать о своем прошлом опыте работы с ними. Ни одного описания варианта использования, к сожалению, я так и не получил.
Далее по собеседованию я предлагал попробовать предложить вариант решения для следующей ситуации:
Допустим, мы являемся онлайн сервисом, который в свою очередь пользуется каким-то внешним платным API (активация услуги, платный контент, или что вашей душе угодно), то есть наш сервис сам платит деньги за пользование API. Пользователь в нашей системе создает запрос на активацию услуги, заполняет все поля и на последней странице жмет кнопку «Активировать услугу». То есть на момент отправки HTTP запроса мы имеем в нашей БД запись (запрос на активацию услуги). Каков наш алгоритм?- спрашиваю я и сам продолжаю:
— достаем из базы баланс пользователя;
— если баланса достаточно, то дергаем API;
— если всё хорошо, то списываем с баланса сумму за услугу, делаем UPDATE, коммитим, иначе откатываемся;
— отвечаем пользователю.
Вроде бы всё тривиально, но когда привожу первую и самую очевидную проблему в виде 10 конкурентных запросов (что все они в начале получат одинаковый баланс и начнут звонить в API), решения начинают предлагать самые изощренные, начиная от выполнения 5 селектов (стоит признаться, я ничего не понял в этом варианте), использования автоинкрементных счетчиков, внешних кешей, новых таблиц в бд, слипов и еще не пойми чего.
Как известно (а это знали все кандидаты!), innodb в mysql предоставляет транзакционный механизм и возможность построчной блокировки. Для того, чтобы применить этот самый построчный лок, достаточно добавить в конце SELECT-а выражение FOR UPDATE, например так:
SELECT * FROM requests WHERE id=5 FOR UPDATE
Стартанет транзакция и все остальные сессии к базе данных не смогут выполнить аналогичный запрос до завершения нашей транзакции, просто будут ждать. На чтение же запись будет доступна в состоянии, которое зависит от уровня изоляции транзакции.
Также стоит заметить, что использование FOR UPDATE лучше делать с выключенным autocommit-ом, так как вне зависимости от того, что вы залочили, после первого апдейта лок снимется.
Вроде мелочь, вроде очевидно, но 9 из 10…
upd
прежнее название «Транзакции в mysql», не раскрытое в статье было заменено на " Транзакции в mysql и SELECT FOR UPDATE"
ЗЫ
в статье не говорится о том, что АПИ нужно дергать в рамках транзакции и что делать в случае сбоя и как обрабатывать исключительные ситуации.
Далее по собеседованию я предлагал попробовать предложить вариант решения для следующей ситуации:
Допустим, мы являемся онлайн сервисом, который в свою очередь пользуется каким-то внешним платным API (активация услуги, платный контент, или что вашей душе угодно), то есть наш сервис сам платит деньги за пользование API. Пользователь в нашей системе создает запрос на активацию услуги, заполняет все поля и на последней странице жмет кнопку «Активировать услугу». То есть на момент отправки HTTP запроса мы имеем в нашей БД запись (запрос на активацию услуги). Каков наш алгоритм?- спрашиваю я и сам продолжаю:
— достаем из базы баланс пользователя;
— если баланса достаточно, то дергаем API;
— если всё хорошо, то списываем с баланса сумму за услугу, делаем UPDATE, коммитим, иначе откатываемся;
— отвечаем пользователю.
Вроде бы всё тривиально, но когда привожу первую и самую очевидную проблему в виде 10 конкурентных запросов (что все они в начале получат одинаковый баланс и начнут звонить в API), решения начинают предлагать самые изощренные, начиная от выполнения 5 селектов (стоит признаться, я ничего не понял в этом варианте), использования автоинкрементных счетчиков, внешних кешей, новых таблиц в бд, слипов и еще не пойми чего.
Как известно (а это знали все кандидаты!), innodb в mysql предоставляет транзакционный механизм и возможность построчной блокировки. Для того, чтобы применить этот самый построчный лок, достаточно добавить в конце SELECT-а выражение FOR UPDATE, например так:
SELECT * FROM requests WHERE id=5 FOR UPDATE
Стартанет транзакция и все остальные сессии к базе данных не смогут выполнить аналогичный запрос до завершения нашей транзакции, просто будут ждать. На чтение же запись будет доступна в состоянии, которое зависит от уровня изоляции транзакции.
Также стоит заметить, что использование FOR UPDATE лучше делать с выключенным autocommit-ом, так как вне зависимости от того, что вы залочили, после первого апдейта лок снимется.
Вроде мелочь, вроде очевидно, но 9 из 10…
upd
прежнее название «Транзакции в mysql», не раскрытое в статье было заменено на " Транзакции в mysql и SELECT FOR UPDATE"
ЗЫ
в статье не говорится о том, что АПИ нужно дергать в рамках транзакции и что делать в случае сбоя и как обрабатывать исключительные ситуации.