mysql - comandos - clusvcadm

Solución para Insertar Bloqueos de Intención en MySQL

Question

Tengo una mesa muy simple:

CREATE TABLE `d` (
`id` int(11) DEFAULT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

sin registros:

select * from d;
Empty set (0,01 sec)

Luego trato de abrir dos transacciones en diferentes sesiones:

Sesión 1:

begin;
Query OK, 0 rows affected (0,00 sec)
select * from d where id = 100 for update;
Empty set (0,00 sec)

Sesión # 2:

begin;
Query OK, 0 rows affected (0,00 sec)
select * from d where id = 700 for update;
Empty set (0,00 sec)

Ahora trato de insertar un nuevo registro en la sesión n. ° 2 y la sesión se "congela":

insert into d values (700);

Y cuando intento hacer lo mismo (con otro campo de Id.) En la sesión n. ° 1 , falla:

insert into d values (100); --> ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction in Session #1
insert into d values (700); --> Query OK, 1 row affected (4,08 sec) in Session #2

¿Cómo puedo arreglar el punto muerto? El estado de InnoDB es:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-07-06 15:59:25 0x70000350d000
*** (1) TRANSACTION:
TRANSACTION 43567, ACTIVE 15 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 123145358217216, query id 89 localhost root update
insert into d values (700)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43567 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 43568, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 123145357938688, query id 90 localhost root update
insert into d values (100)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43568 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43568 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)

Answer 1

Sospecho que el punto muerto ocurre porque InnoDB es conservador al tratar con "brechas". Tenga en cuenta que 100 y 700 están ambos en la misma área nebulosa de tierra virgen. InnoDB no puede (o al menos no lo hace) lidiar con el hecho de que "100" y "700" son diferentes. InnoDB desea etiquetar filas individuales, pero ya no hay filas en la tabla con esos identificadores.

La transacción 2 probablemente iba a innodb_lock_wait_timeout (ver innodb_lock_wait_timeout ). Cuando pulsó Transaction 1 por segunda vez y # 2 aún quería un candado, InnoDB punteó y se dio por vencido.

Conclusión: vive con deadlocks. Cuando sucedan, comience de nuevo en BEGIN . Has encontrado aún otro caso oscuro donde ocurre un punto muerto innecesario.

Además, sospecho que corregir el código de este caso ralentizaría la mayoría de los demás casos y provocaría una serie de errores que tardarían varios lanzamientos en descubrir y corregir.

Answer 2

Tenga en cuenta que a partir de MySQL 8.0.1, el esquema de rendimiento expone innodb bloqueos de datos.

Ver https://dev.mysql.com/doc/refman/8.0/en/data-locks-table.html

Ver https://dev.mysql.com/doc/refman/8.0/en/data-lock-waits-table.html

En este ejemplo, después de la primera selección en la sesión 1 solo, los bloqueos son:

mysql> select * from performance_schema.data_locks /G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 1808:76
ENGINE_TRANSACTION_ID: 1808
THREAD_ID: 35
EVENT_ID: 13081
OBJECT_SCHEMA: test
OBJECT_NAME: d
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139756088373592
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 1808:2:5:1
ENGINE_TRANSACTION_ID: 1808
THREAD_ID: 35
EVENT_ID: 13111
OBJECT_SCHEMA: test
OBJECT_NAME: d
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: id
OBJECT_INSTANCE_BEGIN: 139756088370552
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record <--- HERE
2 rows in set (0.00 sec)

Esta no es una solución al punto muerto en sí mismo, pero tener visibilidad en los bloqueos lleva a un largo camino para entender el problema.

Aquí, ambos SELECT FOR UPDATE bloquean el registro "suprenum", porque id 100 y 700 son mayores que el ID más grande en la tabla (está vacío).

Una vez que haya más registros (digamos en ID = 500), ambas consultas se ejecutarán al mismo tiempo, ya que se bloqueará un espacio diferente en los ID.