Транзакции и блокировки в mysql. Теория и практика
TRANSCRIPT
![Page 1: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/1.jpg)
Транзакциии блокировки
в MySQL
Теория и практика
![Page 2: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/2.jpg)
Table of Contents
1. Теория. Что такое транзакции в СУБД2. Требования ACID3. Проблемы параллельного выполнения транзакций4. Уровни изоляции транзакций5. Механизмы реализации изоляции транзакций6. InnoDB
a. Виды блокировокb. Многоверсионостьc. Пользовательские блокировкиd. Предотвращение фантомовe. Причины возникновения взаимоблокировокf. Предотвращение взаимоблокировок
![Page 3: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/3.jpg)
Транзакция
Группа последовательных операций с базой данных, которая представляет собой логическую единицу работы с данными.
![Page 4: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/4.jpg)
Пример транзакции: заказ товара на сайте
{BEGIN}1. Найти товар, проверить его наличие и получить цену2. Получить данные о покупателе и его баланс3. Добавить товар в список оплаченных покупок4. Уменьшить баланс пользователя5. Уменьшить количество товара на складе
{COMMIT}
![Page 5: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/5.jpg)
Требования ACID
1. Атомарность (Atomicity) - все входящие в транзакцию операции выполняются нераздельно, т.е. будут либо выполнены все операции, либо не выполнено ни одной. “Все или ничего”.
![Page 6: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/6.jpg)
Требования ACID
1. Атомарность (Atomicity) - все входящие в транзакцию операции выполняются нераздельно, т.е. будут либо выполнены все операции, либо не выполнено ни одной. “Все или ничего”.
2. Согласованность (Consistency) - транзакция, фиксирующая результаты, должна сохранять согласованность данных в базе.
![Page 7: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/7.jpg)
Требования ACID
1. Атомарность (Atomicity) - все входящие в транзакцию операции выполняются нераздельно, т.е. будут либо выполнены все операции, либо не выполнено ни одной. “Все или ничего”.
2. Согласованность (Consistency) - транзакция, фиксирующая результаты, должна сохранять согласованность данных в базе.
3. Изоляция (Isolation) - во время выполнения транзакции параллельные транзакции не должны оказывать влияние на её результат. Другие процессы не должны видеть данные в промежуточном состоянии.
![Page 8: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/8.jpg)
Требования ACID
1. Атомарность (Atomicity) - все входящие в транзакцию операции выполняются нераздельно, т.е. будут либо выполнены все операции, либо не выполнено ни одной. “Все или ничего”.
2. Согласованность (Consistency) - транзакция, фиксирующая результаты, должна сохранять согласованность данных в базе.
3. Изоляция (Isolation) - во время выполнения транзакции параллельные транзакции не должны оказывать влияние на её результат. Другие процессы не должны видеть данные в промежуточном состоянии.
4. Надежность (Durability) - после фиксации изменений, сделанных успешной транзакцией, никакое внешнее событие не должно привести к потере этих изменений.
![Page 9: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/9.jpg)
Как обеспечить требования ACID в реальных СУБД?
![Page 10: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/10.jpg)
Как обеспечить требования ACID в реальных СУБД?
![Page 11: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/11.jpg)
Проблемы параллельного выполнения транзакций
Потерянное обновление (lost update) - при одновременном изменении одного блока данных разными транзакциями, одно из изменений теряется.
balance = 100
balance = 80 или 90
Транзакция 1 Транзакция 2
UPDATE user SET balance=balance-20 WHERE
id=1;
UPDATE user SET balance=balance-10 WHERE
id=1;
![Page 12: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/12.jpg)
Проблемы параллельного выполнения транзакций
Грязное чтение (dirty read) - чтение данных, добавленных или изменённых незафиксированной транзакцией, которая впоследствии откатится.
balance = 100
balance = 125 в транзакции 2
Транзакция 1 Транзакция 2
SELECT balance FROM user WHERE id=1;
UPDATE users SET balance=balance+25 WHERE id=1;
ROLLBACK;
SELECT balance FROM users WHERE id=1;
![Page 13: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/13.jpg)
Проблемы параллельного выполнения транзакций
Неповторяющееся чтение (non-repeatable read) - при повторном чтении в рамках одной транзакции, ранее прочитанные данные оказываются изменёнными.balance = 100
balance = 100, затем 125 в одной и той же транзакции
Транзакция 1 Транзакция 2
SELECT balance FROM users WHERE id=1;
UPDATE users SET balance=balance+25 WHERE id=1;
COMMIT;
SELECT balance FROM users WHERE id=1;
SELECT balance FROM users WHERE id=1;
![Page 14: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/14.jpg)
Проблемы параллельного выполнения транзакций
Фантомное чтение (phantom reads)
f2 = 10, затем 35 в одной и той же транзакции
Транзакция 1 Транзакция 2
SELECT SUM(balance) FROM users WHERE age BETWEEN 18 AND 25;
SELECT SUM(balance) FROM users WHERE age BETWEEN 18 AND 25;
INSERT INTO users (age, balance) VALUES (20, 100);COMMIT;
![Page 15: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/15.jpg)
Проблемы параллельного выполнения транзакций
● Потерянное обновление (lost update) - при одновременном изменении одного блока данных разными транзакциями, одно из изменений теряется
![Page 16: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/16.jpg)
Проблемы параллельного выполнения транзакций
● Потерянное обновление (lost update) - при одновременном изменении одного блока данных разными транзакциями, одно из изменений теряется
● Грязное чтение (dirty read) - чтение данных, добавленных или изменённых транзакцией, которая впоследствии откатится
![Page 17: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/17.jpg)
Проблемы параллельного выполнения транзакций
● Потерянное обновление (lost update) - при одновременном изменении одного блока данных разными транзакциями, одно из изменений теряется
● Грязное чтение (dirty read) - чтение данных, добавленных или изменённых незафиксированной транзакцией
● Неповторяющееся чтение (non-repeatable read) - при повторном чтении в рамках одной транзакции, ранее прочитанные данные оказываются изменёнными
![Page 18: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/18.jpg)
Проблемы параллельного выполнения транзакций
● Потерянное обновление (lost update) - при одновременном изменении одного блока данных разными транзакциями, одно из изменений теряется
● Грязное чтение (dirty read) - чтение данных, добавленных или изменённых незафиксированной транзакцией
● Неповторяющееся чтение (non-repeatable read) - при повторном чтении в рамках одной транзакции, ранее прочитанные данные оказываются изменёнными
● Фантомное чтение (phantom reads) - Одна транзакция в ходе своего выполнения несколько раз выбирает множество строк по одним и тем же критериям. Другая транзакция в интервалах между этими выборками добавляет или удаляет строки, попадающие в критерии выборки первой транзакции, и успешно заканчивается. В результате одни и те же выборки в первой транзакции дают разные множества строк.
![Page 19: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/19.jpg)
Уровни изоляции транзакций (ANSI SQL92 )
Уровень изоляции Потерянное обновление
Грязное чтение
Неповторяющееся чтение
Фантомное чтение
READ UNCOMMITTED
+ - - -
READ COMMITTED + + - -
REPEATABLE READ + + + -
SERIALIZABLE + + + +
![Page 20: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/20.jpg)
Механизмы для реализации требований ACIDБлокировки (locks) - механизм синхронизации, позволяющий ограничить доступ к разделяемому ресурсу между несколькими параллельными процессами
X - exclusive lock
S- shared lock
![Page 21: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/21.jpg)
Механизмы для реализации требований ACIDMVCC (Multiversion concurrency control) - многоверсионный контроль конкурентных транзакций
Механизм обеспечения одновременного конкурентного доступа к БД, заключающийся в предоставлении каждому пользователю «снимка» БД, обладающего тем свойством, что вносимые пользователем изменения в БД невидимы другим пользователям до момента фиксации транзакции
![Page 22: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/22.jpg)
InnoDB
● InnoDB - транзакционное хранилище данных (storage engine) для MySQL.
● Появилось в версии 3.23, с версии 5.5 - хранилище по умолчанию● Percona, MariaDB - форки● Поддерживает все 4 уровня изоляции транзакций, уровень изоляции
по умолчанию - REPEATABLE READ● Реализация MVCC - rollback segment, DB_TRX_ID, DB_ROLL_PTR● Блокировки - shared, exclusive, next-key lock, gap lock, deadlock
detection
![Page 23: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/23.jpg)
Реализация ACID1. Atomicity
a. transactionsb. commitc. rollback
2. Consistencya. redo logb. doublewrite buffer
3. Isolationa. lockingb. mvcc
4. Durabilitya. doublewrite bufferb. sync_binlogc. innodb_flush_log_at_trx_commit
![Page 24: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/24.jpg)
Механизм MVCC
журнал транзакций
Rollback SegmentDB_TRX_IDDB_ROLL_PTR
![Page 25: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/25.jpg)
Блокировки в InnoDBExclusive (X) - эксклюзивные (монопольные, блокировки записи)Shared (S) - совместные (разделяемые, блокировки чтения)
2-phase locking (фаза установки блокировок, фаза снятия)
Intention locks - блокировки намерения
X IX S IS
X Conflict Conflict Conflict Conflict
IX ConflictCompatibl
eConflict
Compatibl
e
S Conflict ConflictCompatibl
e
Compatibl
e
IS ConflictCompatibl
e
Compatibl
e
Compatibl
e
Совместимость блокировок
![Page 26: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/26.jpg)
Пользовательские блокировки
Установка S блокировки:SELECT … LOCK IN SHARE MODE;
SELECT * FROM user WHERE id=1 LOCK IN SHARE MODE;INSERT INTO order (user_id, product_id) VALUES (...);
![Page 27: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/27.jpg)
Пользовательские блокировки
Установка S блокировки:SELECT … LOCK IN SHARE MODE;
SELECT * FROM user WHERE id=1 LOCK IN SHARE MODE;INSERT INTO order (user_id, product_id) VALUES (...);
Альтернатива: constraints on users and orders
![Page 28: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/28.jpg)
Пользовательские блокировки
Установка X блокировки:SELECT … FOR UPDATE;
SELECT balance FROM user WHERE id=1 FOR UPDATE;если balance > priceUPDATE user SET balance=balance - price WHERE id=1;
![Page 29: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/29.jpg)
Пользовательские блокировки
Установка X блокировки:SELECT … FOR UPDATE;
SELECT balance FROM user WHERE id=1 FOR UPDATE;если balance > priceUPDATE user SET balance=balance - price WHERE id=1;
Альтернатива:UPDATE user SET balance=balance - price WHERE id=1 AND balance > price;
![Page 30: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/30.jpg)
Блокировка следующего ключа и предотвращение фантомов
Record lock - блокировка индексных записей Gap lock - блокировка интервала между записями, перед первой записью или после последнейNext-key lock - комбинация блокировки записи и интервала
SELECT * FROM userWHERE id > 5 FOR UPDATE;
SELECT * FROM userWHERE name = 'nikolay' FOR UPDATE;
![Page 31: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/31.jpg)
Блокировки в различных операциях
● SELECT ... FROM ...: производится из образа базы данных без блокировки
● SELECT ... FROM ... LOCK IN SHARE MODE: устанавливает S блокировку следующего ключа
● SELECT ... FROM ... FOR UPDATE: устанавливает X блокировку следующего ключа
● INSERT INTO ... VALUES (...): устанавливает X блокировку на вставленную строку.
● UPDATE ... SET ... WHERE ...: устанавливает X блокировку следующего ключа для каждой записи
● DELETE FROM ... WHERE ...: устанавливает X блокировку следующего ключа для каждой записи
![Page 32: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/32.jpg)
Взаимоблокировки (deadlocks)
Транзакция 1 Транзакция 2
UPDATE user set balance=100 WHERE id=1;
UPDATE user set balance=100 WHERE id=2;COMMIT;
UPDATE user set balance=500 WHERE id=2;
UPDATE user set balance=500 WHERE id=1;COMMIT;
DEADLOCK
![Page 33: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/33.jpg)
Как избежать взаимоблокировок?
● SHOW ENGINE INNODB STATUS● Короткие транзакции● Механизм для перезапуска транзакции● Планировать порядок выполнения операций в транзакции● Хорошие индексы для таблиц
![Page 34: Транзакции и блокировки в MySql. Теория и практика](https://reader035.vdocuments.us/reader035/viewer/2022081505/556c7c8ad8b42ac71e8b4b83/html5/thumbnails/34.jpg)
Литература
The InnoDB Transaction Model and Lockinghttp://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-model.html
Критика уровней изолированности в стандарте ANSI SQL
http://citforum.ru/database/classics/SQL_critiques/
Понимание SQLМартин Грабер
MySQL. Оптимизация производительности
High Perfomance MySQL
Авторы: Бэрон Шварц, Петр Зайцев, Вадим Ткаченко, Джереми Д. Зооднай, Дерек Дж. Баллинг, Арьен Ленц