Блокировки и уровни изоляции транзакций InnoDB в MySQL

[evnuh]

Снова перевод документации на хабре. Но порадуемся за тех, кто программирует по харбрахабру, им будет полезно.

[DarkGift]

Я наверно еще слишком неопытный разработчик, и у меня не было необходимости разбираться в блокировках, лишь поверхностно почитав. Так вот, мне кажется что даже такие, как Вы выразились «переводы документации» все равно полезны, по крайней мере я углубил хоть и немного свои знания, и при необходимости буду знать куда копать)

[MastaEx]

Обязательно порадуемся. Только это не перевод документации. Безусловно она тут присутствует в какой-то степени, было бы странно ее не использовать (добавить небылиц от себя?). В доках вся информация раскидана по куче статей, здесь же пересказана в кратком виде с пояснениями и примерами.

[netslavehq]

Кода-то давно, когда я работал с 1С, была очень хорошая дока с описанием уровней изоляций транзакций, она мне очень нравилась тогда. Я ее себе сохранил.

Ссылка.

Много про сам 1С, но понять смысл можно. Кто бы сделал реферат? Так же есть базовое описание что такое блокировка вообще.

[grihan]

SELECT… FOR UPDATE — блокирует считываемые строки на чтение.

на чтение для обновления? или вообще любое чтение будет заблокировано?

[MastaEx]

Да, блокируется чтение для обновления и для lock in share mode тоже, т.е. любое блокирующее. Простое неблокирующее чтение (то, что из снэпшота) игнорирует блокировки.

[int19h]

Какое счастье, что когда-то кто-то изобрел MVCC…

[GlukKazan]

select… for update как раз позволяет бороться с этим «счастьем», но иногда да, MVCC бывает полезно

[int19h]

Ну, в большинстве случаев стандартное поведение с откатом и повтором вполне устраивает. Что важнее — оно более интуитивно понятно, и тяжелее выстрелить себе в ногу дедлоком.

[GlukKazan]

Ох, я вас уверяю, MVCC в непривычного человека стреляет куда больнее. Deadlock по крайней мере трудно не заметить.

[int19h]

Ну так и конфликт при записи не заметить тоже очень сложно.

Если вы имели в виду что-то другое — поясните, пожалуйста.

[GlukKazan]

Пример хорошо описан у Кайта, повторять не буду. Вкратце: человек переходит (например на Oracle) с блокировочника и «ваяет» систему, по привычке держа в голове, что писатели блокируют читателей и наоборот. Получается нехорошо, лечится for update-ом. С подобной ситуацией я сталкивался лично, когда софт одного крупного мобильного оператора писался людьми, до этого плотно работавшими с MS SQL 2000 и имевшими об Oracle отдаленное представление.

[int19h]

Ну это вами же описан классический случай подхода, «не надо RTFM, разберемся по ходу дела». Но я, опять же, не вижу принципиальной разницы с таким же переходом в обратную сторону, когда человек с MVCC переходит на блокирующий сервер и получает дедлок в той ситуации, которая успешно разруливается MVCC. И в том, и в другом случае опасность в том, что на это можно нарваться не сразу, а погодя (в продакшне, например). Но когда нарываешься, то диагностика в обоих случаях вполне однозначная.

[GlukKazan]

Именно так. MVCC — хороший инструмент (разумеется с собственными накладными расходами), но надо уметь им правильно пользоваться, чтобы не прострелить себе ногу. Высказывания о том какое это «счастье», на мой взгляд, не побуждают к чтению документации и поиску пятен на Солнце. И это плохо.

[int19h]

Мне кажется, вы все-таки смотрите слишком однобоко. Если оставить в стороне ситуации типа кто-то переходит с чего-то, и рассмотреть абстрактного разработчика в вакууме, который осваивает это все с нуля, то объяснить ему поведение снэпшотов в MVCC, и как с ними правильно работать, на мой взгляд, в разы проще («всегда будь готов к тому, что записывающая транзакция при коммите может обломиться, и её надо будет повторить»). А вот с блокировками надо писать статьи, подобные этой, где детально разбирается поведение блокировщика — кто там на что берет какой лок — разные уровни изоляции etc.

При этом снэпшоты обеспечивают поведение от оптимального до «good enough» в большинстве случаев без каких-либо приседаний вообще. С блокировками, опять же, надо очень внимательно продумавать, какие уровни изоляции где использовать, и как все это будет взаимодействовать друг с другом.

[GlukKazan]

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

[GlukKazan]

Ну и по поводу «стреляет больнее». Возможность Deadlock-а можно не заметить, но если он случается достаточно часто (а так оно обычно и бывает), это будет заметно и за это разработчика будут долбать, пока он его не исправит. Ошибки проектирования связанные с непониманием MVCC коварнее. Выявить их можно, как правило, только анализируя некорректное поведение системы. И, как правило, такое некорректное поведение бывает связано с чьими-то денежными средствами.

[MastaEx]

А чего вы человека заминусовали-то? Это ж не сарказм, вроде бы… :-)
Приложения обычно больше читают, нежели пишут. MVCC позволяет читателям не стоять в очереди и не мешать писателям.

[GlukKazan]

Человека я НЕ заминусовал. Минус на комментарий. Почему — объяснил выше.

[renskiy]

Так как блокировок две, то есть теоретический шанс проскочить третьей между ними и вызвать deadlock.

Я так понимаю, что это происходит из-за того, что чтобы поставить эксклюзивную блокировку FOR UPDATE надо сначала снять блокировку от изменения LOCK IN SHARE MODE. В этот промежуток между двумя блокировками и успевает вклиниться другая транзакция, так? Это актуально только для MySQL или все реляционные БД ведут себя подобным образом?

[MastaEx]

Верно. В простейшем виде блокировки, это некая очередь. Если блокировке мешает предыдущая, она не отваливается с ошибкой, она просто встает в очередь. Как только мешающая блокировка будет снята, следующая по очереди вступит в силу. Две блокировки — два действия постановки в эту очередь, соответственно между этими действиями может кто-то еще успеть встать в эту очередь между ними.

В базовом виде, думаю, суть с постановкой в очередь примерно одинакова в различных СУБД, но, полагаю, что у всех есть алгоритмы для предотвращения deadlock'ов, которые работают с этой очередью и могут ее тасовать каким-нибудь хитрым образом. Утверждать не берусь, ибо не знаю.

[AntipodTX]

Господа, а кто-нибудь может привести более-менее внятный реальный пример, когда нужно использовать SERIALIZABLE / SELECT… LOCK IN SHARE MODE? Мне не доводилось сталкиваться с такими случаями и сходу придумать могу. А то в статье рассмотрен пример, когда не надо его использовать, а надо использовать SELECT… FOR UPDATE (с такими случаями я как раз сталкивался).

[grihan]

SERIALIZABLE — это когда сделал на read committed и select for update, протестировал, задеплоил и через год посыпались deadlock-и и срочно стало нужно спасать ситуацию…

[MastaEx]

Представьте дерево или граф, которые пользуются и в хвост и в гриву :-)
Нужно вставить дочерний узел. Перед вставкой нужно убедиться в существовании родителя. Делаем SELECT, убеждаемся, делаем вставку и получаем «foreign key constraint fails» — родителя кто-то успел грохнуть. LOCK IN SHARE MODE в данной ситуации не позволит никому потереть родителя до завершения транзакции, а FOR UPDATE будет излишним, так как не даст другим читать родителя.

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

[AntipodTX]

Спасибо за развёрнутый пример, про удаление связанных данных не подумал… А для SERIALIZEABLE, как я понял, для неаварийной ситуации в продакшене примера нет, т.к. не существует?

[grihan]

как вариант — обработчик биржевых операций. всё строго по порядку. хотя, не лучший пример.