В MySQL, почему установка переменной из select получает блокировку при использовании read uncommitted?

У нас есть таблица в MySQL, использующая InnoDB, и мы используем уровень изоляции транзакции для чтения незафиксированным. Почему настройка @x как показано приобрести замок?

mysql> set @x = (select userID from users limit 1);
Query OK, 0 rows affected (0.02 sec)

mysql>

Попытка обновить эту таблицу из другого приглашения приводит к ошибке времени ожидания:

mysql> update users set userID = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2 ответа

Для чего это стоит, эта блокировка не ограничивается READ-UNCOMMITTED:

mysql1> show variables like '%isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
mysql1> BEGIN;
mysql1> SET @x := (SELECT x FROM foo LIMIT 1);

mysql2> UPDATE foo SET x = x+1;
[gets a lock wait]

mysql3> SHOW ENGINE INNODB STATUS;
...
---TRANSACTION 228746, ACTIVE 22 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 58, OS thread handle 0x7fc262a1c700, query id 8163
  192.168.56.1 root cleaning up
TABLE LOCK table `test`.`foo` trx id 228746 lock mode IS
RECORD LOCKS space id 801 page no 3 n bits 80 index `PRIMARY` 
  of table `test`.`foo` trx id 228746 lock mode S
...

Как уже упоминалось в сообщении об ошибке, ошибка #67452 При установке переменной из выборки блокируется при использовании чтения без передачи, такое поведение, вероятно, является конструктивным. Кажется, попадают в ту же категорию, что и SELECT операторы, результаты которых используются для изменения данных, как описано в следующих случаях:

http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

Когда SELECT используется в конструкциях REPLACE INTO t SELECT ... FROM s WHERE ... или же UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB устанавливает общие блокировки следующего ключа для строк из таблицы s,

Причиной блокировок следующего ключа является SELECT результаты более стабильные. То есть мы не хотим, чтобы строки соответствовали SELECT изменить, пока они используются для UPDATE или другой оператор изменения данных.

Даже когда tx_isolation REPEATABLE-READэто важно, потому что InnoDB не поддерживает REPEATABLE-READ за SELECT заявления, когда они выполняются как часть любого типа UPDATE,


Re ваш комментарий:

Независимо от документации, вот что происходит:

Когда вы делаете простой SELECT Заявление, InnoDB не блокирует ничего, в любой транзакции изоляции, кроме SERIALIZABLE,

Если вы делаете SELECT ... LOCK IN SHARE MODE или же SELECT ... FOR UPDATE замки конечно.

Но когда вы делаете SELECT как часть заявления о модификации данных, как INSERT INTO...SELECT или в подзапросе UPDATE или как вы нашли в SET @variable := (SELECT...) он использует общую блокировку, чтобы убедиться, что данные не изменяются во время обновления.

Документация может быть неполной. Лучше проверить.

Ваш первый оператор выполняет SELECT для таблицы, поэтому транзакция получает блокировку чтения для одной строки.

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

Вам необходимо оформить COMMIT (или же ROLLBACK) команда после SET @x = (...), чтобы освободить блокировку чтения.

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

Другие вопросы по тегам