Skip to content

Instantly share code, notes, and snippets.

@codedokode

codedokode/mvcc.md Secret

Created August 14, 2016 21:50
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save codedokode/45f2961e7d68f7a2c501f4f893a45e17 to your computer and use it in GitHub Desktop.
Save codedokode/45f2961e7d68f7a2c501f4f893a45e17 to your computer and use it in GitHub Desktop.
Download ZIP
Транзакции, блокировки, MVCC, мультиверсионность
Raw
mvcc.md

Как вообще работает транзакция? Можно ли вставить миллион записей одной транзакцией, или оно пишется куда-то (в память, на диск, во временную таблицу), где есть лимит?

Транзакция это изолированный набор изменений, соответствующий принципам ACID:

Транзакции это важно. В идеале ты должен в своем приложении делать изменения, группируя запросы в транзакции по смыслу (чтобы не могло получиться так что часть изменений запишется, а часть нет). К примеру при вставке объявления и увеличения счетчика объявлений в категории, ты должен объединить это в одну транзакцию. Найди потом время пройтись по своему коду и проверить правильно ли расставлены границы транзакций.

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

В реальном мире частью требований можно жертвовать (менять уровень изоляции транзакций) в пользу производительности:

Вопросы по транзакциям любят задавать на собеседованиях. Я считаю это правильно.

Как реализовать изоляцию транзакций и параллельную их работу с минимумом задержек?

В MySQL в InnoDB используется так назваемая мультиверсионность:

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

Также, еще одна вещь, которую надо понимать, это то, что MySQL гарантирует сохранность данных после успешного коммита. То есть перед тем как отчитаться что команда COMMIT завершена успешно, mysql сбрасывает данные (данные таблицы, а также изменившиеся индексы) на диск и дожидается от ОС подтверждения что они физически сохранены. Даже если питание выключится, коммит не пропадет (если оно выключится до коммита, то он пропадет, но база останется в согласованном состоянии которое было до коммита, а такого что половина данных записалась, а половина нет, быть не может. Это называется атомарность транзакции).

Соответственно если ты выполняешь запросы по одному, каждый идет отдельной транзакцией и mysql после каждого должна ждать диск. Это неэффективно. Если ты делаешь 100 запросов одной транзакцией, то mysql имеет право накапливать данные в памяти (или писать на диск без подтверждения и без ожидания), а сбросить на диск только в конце транзакции, и это работает быстрее.

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

Возвращаясь к MVCC - там для каждой строчки таблицы добавляется несколько скрытых полей, вроде таймстампа (время добавления) и номера транзакции. При изменениях в таблице (update/delete/insert) исходные данные не удаляются, а просто добавляются новые строчки. При выборке если есть несколько версий строки, по id транзакции и таймстампу выбирается самая новая видимая данному пользователю версия. При коммите новая версия становится видна всем, старая становится неактуальной и специальный фоновый тред очищает такие строки и помечает занимаемое ими место как свободное.

Условно говоря, у нас есть таблица такого вида:

id | text
1 | hello

В InnoDB она хранится с id транзакции которая ее вставила и таймстампом:

txn | timestamp | id | text
1000 | 12:00:00 | 1 | hello

Как мы видим эта строчка вставлена транзакцией 1000, которая (допустим) давно закоммичена.

Допустим 3 пользователя подключенных к базе параллельно открывают 3 транзакции. Первый (транзакция 1001) удаляет строчку (и пока не закоммитил транзакцию), второй (1002) и третий (1003) добавляют вторую строчку.Таблица выглядит так:

txn | timestamp | id | text
1000 | 12:00:00 | 1 | hello
1001 | 13:00:00 | 1 | (deleted)
1002 | 13:00:00 | 2 | world1
1003 | 13:00:00| 3 | world2

Пользователь внутри транзакции видит только закоммиченные чужие изменения, а также все свои. То есть транзакция 1001 видит изменения из закомиченной транзакции 1000 и незакомиченной 1001. Если пользователь сделает SELECT из этой транзакции, то база будет брать только строки транзакций 1000 и 1001, причем если эти строки соответствуют одному id, то база берет самую новую версию.

Транзакция 1001 видит только эти строки:

txn | timestamp | id | text
1000 | 12:00:00 | 1 | hello
1001 | 13:00:00 | 1 | (deleted)

Так как id одинаковый то она берет последнюю версию, которая помечена как удаленная. Больше строк нет потому SELECT вернет 0 строк.

Транзакция 1002 видит только строки от транзакций 1000 и свои, 1002. Транзакция 1003 видит строки от транзакций 1000 и 1003. Что они получат при выборке всех строк, подумай сам.

Допустим теперь транзакции 1002 и 1003 отменяются, а 1001 коммитится. Строки для отмененных транзакций стали неактуальными, и их позже удалит сборщик мусора. А так как 1001 теперь закоммичена то любой пользователь видит эту строку. При выборке он получит такие строки:

txn | timestamp | id | text
1000 | 12:00:00 | 1 | hello
1001 | 13:00:00 | 1 | (deleted)

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

Строка транзакции 1000 после коммита тран. 1001 стала неактуальной, и ее позже удалит сборщик мусора. А также строку 1001 которую после этого нет смысла хранить.

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

Это позволяет выжать максимум производительности при параллельных операциях с таблицой. А ведь веб приложения как правило много процессные/многопоточные (если ты помнишь), и соответственно параллельно выполняется несколько SQL запросов от разных воркеров. Да и сервера как правило содержат много ядер и чтобы их загрузить нужно много потоков.

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

В redis тоже нет, но там однопоточное приложение (асинхронное) и параллельный доступ к данным невозможен. В MongoDB нету, там блокируется вся коллекция (до версии 3 - блокировалась вообще вся база) на время записи, но там и транзакций нет.

Обрати внимание что речь тут шла о блокировках для модификации данных. MVCC (почти) не требует их наличия. Но MySQL все равно блокирует строки, которые изменяются или удаляются. Это делается для того чтобы 2 транзакции не могли сделать противоречащие изменения (например записать разные значения в одну и ту же строку) и нарушить ACID. MySQL делает блокировки как минимум в таких случаях:

  • есть 2 писателя (UPDATE/DELETE) для 1 и той же строки
  • есть писатель + читатель (SELECT) для 1 и той же строки

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

В особо тяжелых слуаях можно словить взаимную блокировку - deadlock:

https://ru.wikipedia.org/wiki/%D0%92%D0%B7%D0%B0%D0%B8%D0%BC%D0%BD%D0%B0%D1%8F_%D0%B1%D0%BB%D0%BE%D0%BA%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B0

Например транзакция A хочет апдейтить строку 1, за ней строку 2. Транзакия B хочет апдейтить сначала 2, потом 1. Каждая их них захватит блокировку по одной строке и никогда не получит блокировку по второй. MySQL обнаруживает дедлоки и решает их принудительным откатом одной из транзакций.

Выше я написал что MVCC почти не требует блокировок. Но как написал один опытный анон, зашедший к нам в тред, небольшие блокировки там есть. Например при вставке записи надо выделить новый id и на время выделение счетчик автоинкремента блокируется. В момент коммита надо пометить транзакцию как закоммиченную и какая-то внутренняя структура блокируется.

Подробности о работе и состоянии движка InnoDB можно получить запросом (попробуй это сделать):

SHOW ENGINE InnoDB STATUS\G

Увидеть список соединений и выполняющихся запросов можно командой SHOW FULL PROCESSLIST\G. Убить поток-воркер можно командой KILL 123; (принципы ACID и целостность данных это не нарушит).

Наконец, вернемся к этому вопросу:

Можно ли вставить миллион записей одной транзакцией, или оно пишется куда-то (в память, на диск, во временную таблицу), где есть лимит? В InnoDB есть пул страниц (InnoDB buffer pool) в памяти: https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html

Размер пула задается в конфиге mysql. В нем хранятся страницы (строки таблиц и куски индексов). При выборке данных mysql читает данные с диска в пул (если их там нет) и в нем уже делает выборку. При записи данные пишутся в пул, а при нехватке места сбрасываются на диск, также они надежно сбрасываются на диск при коммите транзакции.

Потому ответ на твой вопрос: данные пишутся в память пока есть место и на диск. Размер базы ограничен местом на диске, настройками конфига и встроенными ограничениями (вроде того что размер строки ограничен 65536 байтами).

Как ты понимаешь, от объема пула зависит часто ли базе придется обращаться к диску, а диск очень медленный. На серверах БД под пул выделяют 80-90% свободной памяти, также есть мнение что размер пула должен быть не меньше чем размер индексов (и «горячих» данных). Не редкость иметь например 100 Гб пула если на сервере много памяти.

А теперь вопросы для проверки.

  1. Выше я написал что когда кто-то записывает данные (например изменяет строку) он должен брать эксклюзивную блокировку на эти данные, и другие не могут к ним обращаться, то есть читать или писать. Почему? Почему MyISAM блокирует таблицу на время апдейта одной строки? Если это важно, то в MyISAM данные хранятся так: для каждой таблицы и каждого индекса делается отдельный файл и в нем хранятся строки таблиц или записи индекса.
  2. Почему MVCC позволяет это не делать?
  3. Более сложный вопрос, почему InnoDB блокирует строки, хотя MVCC позволяет это не делать? Почему строки блокируются на все время транзакции (а не на время выполнения запроса)? Что будет если не блокировать?
  4. И еще, если программа выбирает какие-то данные (SELECT), меняет их и записывает назад в базу (UPDATE), как запретить другим в это время их менять?
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment