Bloqueo de MySQL causado por INSERT y SELECT concurrentes

deadlock

Question

• Versión de MySQL: 5.6
• Motor de almacenamiento: InnoDB

El interbloqueo se produjo cuando dos tareas intentaron select y luego insert la misma tabla. El procedimiento se parece a:

 Task_1 Task_2
------ ------
Phase 1 | SELECT SELECT
Phase 2 | INSERT INSERT
SELECT count(id) from mytbl where name = ''someValue'' and timestampdiff(hour, ts, now()) < 1;
INSERT mytbl (id, name, ts) values (''newId'', ''anotherValue'', now());

El registro de interbloqueo es el siguiente (con algunos detalles truncados):

------------------------
LATEST DETECTED DEADLOCK
------------------------
151225 8:22:17
*** (1) TRANSACTION:
TRANSACTION 0 746402, ACTIVE 0 sec, process no 4690, OS thread id 140411390486272 inserting
mysql tables in use 1, locked 1
LOCK WAIT 1172 lock struct(s), heap size 112624, 32914 row lock(s)
MySQL thread id 3909, query id 31751474 10.20.36.38 mydb update
INSERT INTO mytbl -- truncated
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`mytbl` trx id 0 746402 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 0 746449, ACTIVE 0 sec, process no 4690, OS thread id 140411389953792 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1172 lock struct(s), heap size 112624, 32914 row lock(s)
MySQL thread id 3906, query id 31751477 10.20.36.38 mydb update
INSERT INTO mytbl -- truncated
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`MYTBL` trx id 0 746449 lock mode S
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 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`MYTBL` trx id 0 746449 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)

Preguntas

1. De acuerdo con el manual de MySQL, la simple instrucción SELECT utiliza la lectura de instantáneas que no requiere S lock . La INSERT requiere el bloqueo X en la fila única para insertarse. Entonces, ¿ Task_2 qué Task_2 mantuvo un bloqueo S y resultó en un interbloqueo?

Editar

El resultado de SHOW CREATE TABLE es el siguiente:

| task_content | CREATE TABLE `mytbl` (
`id` bigint(20) NOT NULL,
`ts` timestamp NULL DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Answer 1

donde name = ''someValue'' y timestampdiff (hour, ts, now ()) <1;

Eso es bastante ineficiente. Vamos a limpiar eso para acelerar las cosas, para disminuir la posibilidad de un punto muerto.

timestampdiff(hour, ts, now()) < 1 oculta cualquier índice con ts ; vamos a reescribirlo para

ts < NOW() - INTERVAL 1 HOUR

Tuyo truncado de maneras inesperadas; el mío dice "hace más de 1 hora", que sospecho que querías.

Ahora podemos indexar ts con buenos resultados. Pero llevémoslo más lejos usando un índice "compuesto":

INDEX(name, ts)

Esto utilizará de manera eficiente ambas partes de la cláusula WHERE para ubicar la (s) fila (s).

COUNT(id) : esto implica que debe evitar NULLs en id . Quizás eso no sea una preocupación, y podría decir simplemente COUNT(*) .

Esos deberían hacer el SELECT más rápido. Ahora averigüemos por qué SELECT e INSERT tienen algo que ver entre sí. ¿Están en la misma transacción? ¿O tiene la confirmación automática desactivada, pero se olvidó de decir COMMIT ? Por favor, muéstrenos la transacción completa, más SHOW CREATE TABLE .

Answer 2

El artículo here ofrece una explicación exhaustiva sobre los bloqueos y los niveles de aislamiento.

Gracias a @newtover por darnos una pista sobre el nivel de aislamiento. Mi resumen del artículo y la respuesta a mi propia pregunta es el siguiente:

El nivel de aislamiento predeterminado en InnoDB es Lectura repetible , que bloquearía el índice (sin bloquear la tabla de datos) hasta el final de la transacción .

En mi caso, el único índice es PRIMARY , que fue inútil en mi consulta SELECT (se puede verificar mediante la explain select... ). Como resultado, todas las entradas en el índice PRIMARY se bloquearon. Cuando TXN_2 esperaba un bloqueo X en una determinada entrada, la entrada estaba bloqueada por un bloqueo S retenido por TXN_1 . De manera similar, TXN_1 esperó un bloqueo X en otra entrada, pero la entrada también fue bloqueada por el bloqueo S retenido por sí mismo. Se produjo un interbloqueo "one S two X" .

Por el contrario, después de crear un name índice en el name de la columna, el name del índice se usaría en la instrucción SELECT (se puede verificar mediante la explain select ... ), por lo que los bloqueos se emitirán en el name del índice en lugar de en PRIMARY . Más importante aún, la instrucción SELECT solo emitiría un bloqueo S en la entrada igual a someValue lugar de todas las entradas del name del índice. Además, el bloqueo IX y el bloqueo X requeridos por INSERT se emitirán en el índice PRIMARY . Conflictos entre el bloqueo S y el bloqueo IX , el bloqueo X se resolvería.

El índice en el name columna no solo aceleró la consulta sino que, lo que es más importante, impidió el bloqueo de todas las entradas del índice.

Answer 3

Escriba todas sus consultas en la transacción BEGIN y END . Espero que no suceda.

Más: here

Answer 4

La parte escrita de la consulta parece ser correcta y definitivamente NO es la fuente de su problema. Supongo que sus tareas se están ejecutando entrelazadas y, al principio, cada tarea inicia una transacción. ¿No dijo nada sobre cómo ejecutar estas tareas y cuál es el valor de la clave principal cuando ejecuta cada una de ellas? Es posible que desee cambiar el campo de la clave principal a AUTO_INCREMENT o asegurarse de que la clave principal que utilizan sus tareas es realmente única.
Si no funcionara, una solución alternativa (pero no sugerida) sería proteger su código de llamada de procedimiento en el nivel superior mediante un mutext.

Answer 5

Si su nivel de aislamiento actual es de repeatable read o más fuerte, para poder repetir el mismo resultado para el select count(id) ... dentro de una transacción, MySQL debe bloquear toda la clave principal (o una parte de otra clave utilizada por la condición WHERE . ). Luego modifica la clave insertando un nuevo valor. Pero las transacciones concurrentes modifican el estado de la clave, que ya se ha visto. Ambos pueden comenzar con el mismo estado de la clave, y luego esperar hasta que la otra finalice sin cambios, para que aplique sus propios cambios.