**Пример получения взаимной блокировки **
Предположим, что есть таблица t с двумя полями i и j и несколькими строками:
mysql>
CREATE TABLE t (i int, j int);
INSERT INTO t VALUES (1, 1);
INSERT INTO t VALUES (2, 2);
INSERT INTO t VALUES (3, 3);
Предположим есть два клиента А и В. Клиент А в своей транзакции получает блокировку чтения на строку с i = 2.
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE i = 2 LOCK IN SHARE MODE;
+------+------+
| i | j |
+------+------+
| 2 | 2 |
+------+------+
1 row in set (0.00 sec)
Далее клиент В в своей транзакции пытается обновить строку с i = 2.
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set j = 4 where i = 2;
Операция обновления требует блокировки записи на данную строку, но так как другой клиент удерживает блокировку чтения на эту же строку, то клиент В уходит в очередь блокировок для этой строки.
В завершение клиент А решает также обновить строку с i = 2.
mysql> update t set j = 3 where i = 2;
Query OK, 1 row affected (32.46 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Клиенту А также необходима блокировка записи на эту строку и он становится в очередь блокировок, потому что не может получить ее сразу же на основе удерживаемой блокировки чтения. Но в очереди блокировок уже находится клиент В, ожидающий от клиента А освобождения блокировки чтения. Происходит взаимная блокировка (deadlock). В результате InnoDB для одного из клиентов генерирует ошибку и отпускает его блокировки. В данном примере генерируется ошибка для клиента В:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Соответственно клиент А получает блокировку записи и запрос на обновление от него завершается успешно.
Замечание. Обычный селект не дает блокировки чтения, что не приводит к взаимной блокировке при обновлении одной и той же строки, один из клиентов просто ждет коммита транзакции от другого клиента. Так же ведет себя InnoDB и при одном запросе UPDATE (без SELECT ... LOCK IN SHARE MODE). Если же перед обновлением использовать SELECT ... FOR UPDATE, то за счет получения сразу блокировки записи, другой клиент просто дожидается коммита транзакции.
Когда нужен SELECT ... LOCK IN SHARE MODE. Предположим, что для чтения записи из таблицы PARENT был использован обычный SELECT, который вернул некоторую родительскую строку. Можно ли теперь безопасно добавить дочернюю строку в таблицу CHILD? Нет, потому что в это время другой пользователь мог удалить родительскую строку из таблицы PARENT.
В данной ситуации необходимо выполнить операцию SELECT в режиме блокировки, LOCK IN SHARE MODE.
SELECT * FROM PARENT WHERE NAME = 'Jones' LOCK IN SHARE MODE;
Выполнение чтения в режиме совместного использования (share mode) позволяет заблокировать прочитанные данные, при этом другием пользователи смогут лишь прочесть данные, но не обновить или удалить. После того, как указанный выше запрос вернет родительскую строку 'Jones', мы можем безопасно добавить дочернюю строку в таблицу CHILD и зафиксировать транзакцию.