trying - mysql check deadlocks

Cómo evitar mysql ''Deadlock encontrado al intentar obtener el bloqueo; intenta reiniciar la transacción ''

Question

Tengo una tabla innoDB que registra los usuarios en línea. Se actualiza en cada actualización de la página por parte de un usuario para realizar un seguimiento de las páginas que están y la última fecha de acceso al sitio. Luego tengo un cron que se ejecuta cada 15 minutos para BORRAR registros antiguos.

Obtuve un ''Punto muerto'' que se encontró al intentar bloquearse; intente reiniciar la transacción ''durante unos 5 minutos la noche anterior y parece ser que al ejecutar INSERTs en esta tabla. ¿Alguien puede sugerir cómo evitar este error?

=== EDIT ===

Aquí están las consultas que se están ejecutando:

Primera visita al sitio:

INSERT INTO onlineusers SET
ip = 123.456.789.123,
datetime = now(),
userid = 321,
page = ''/thispage'',
area = ''thisarea'',
type = 3

En cada página de actualización:

UPDATE onlineusers SET
ips = 123.456.789.123,
datetime = now(),
userid = 321,
page = ''/thispage'',
area = ''thisarea'',
type = 3
WHERE id = 888

Cron cada 15 minutos:

DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND

Luego hace algunos conteos para registrar algunas estadísticas (es decir, miembros en línea, visitantes en línea).

Answer 1

El interbloqueo ocurre cuando dos transacciones se esperan una a la otra para adquirir un bloqueo. Ejemplo:

• Tx 1: bloqueo A, luego B
• Tx 2: bloqueo B, luego A

Hay numerosas preguntas y respuestas sobre los puntos muertos. Cada vez que inserta / actualiza / o elimina una fila, se adquiere un bloqueo. Para evitar el interbloqueo, debe asegurarse de que las transacciones simultáneas no actualicen la fila en un orden que pueda resultar en un interbloqueo. En términos generales, intente adquirir el bloqueo siempre en el mismo orden incluso en transacciones diferentes (por ejemplo, siempre la tabla A primero, luego la tabla B).

Otra razón para el interbloqueo en la base de datos puede ser la falta de índices . Cuando se inserta / actualiza / elimina una fila, la base de datos debe verificar las restricciones relacionales, es decir, asegurarse de que las relaciones sean coherentes. Para hacerlo, la base de datos debe verificar las claves externas en las tablas relacionadas. Puede provocar que se adquiera otro bloqueo que la fila que se modifica. Asegúrese de tener siempre el índice en las claves foráneas (y, por supuesto, las claves primarias), de lo contrario podría resultar en un bloqueo de tabla en lugar de un bloqueo de fila . Si se produce el bloqueo de la tabla, la contención de bloqueo es mayor y aumenta la probabilidad de un interbloqueo.

Answer 2

Es probable que la instrucción de eliminación afecte a una gran fracción del total de filas en la tabla. Eventualmente, esto podría llevar a que se obtenga un bloqueo de tabla al eliminar. Mantener un bloqueo (en este caso, bloqueos de fila o de página) y adquirir más bloqueos siempre es un riesgo de interbloqueo. Sin embargo, no puedo explicar por qué la instrucción de inserción conduce a una escalada de bloqueo, puede que tenga que ver con la división / adición de páginas, pero alguien que conozca mejor MySQL tendrá que rellenar allí.

Para empezar, puede valer la pena intentar explícitamente adquirir un bloqueo de tabla de inmediato para la declaración de eliminación. Ver problemas de bloqueo de tablas y tablas .

Answer 3

Para los programadores de Java que utilizan Spring, he evitado este problema utilizando un aspecto AOP que reintenta automáticamente las transacciones que se ejecutan en interbloqueos transitorios.

Ver @RetryTransaction Javadoc para más información.

Answer 4

Puede intentar que el trabajo de delete funcione primero insertando la clave de cada fila que se eliminará en una tabla temporal como este pseudocódigo

create temporary table deletetemp (userid int);
insert into deletetemp (userid)
select userid from onlineusers where datetime <= now - interval 900 second;
delete from onlineusers where userid in (select userid from deletetemp);

Romperlo de esta manera es menos eficiente, pero evita la necesidad de mantener un bloqueo de rango clave durante la delete .

Además, modifique sus consultas de select para agregar una cláusula where excluyendo filas con más de 900 segundos. Esto evita la dependencia en el trabajo cron y le permite reprogramarlo para que se ejecute con menos frecuencia.

Teoría sobre los puntos muertos: no tengo muchos antecedentes en MySQL, pero aquí va ... La delete va a mantener un bloqueo de rango de teclas para datetime, para evitar que las filas que coinciden con su cláusula where se agreguen en medio de La transacción, y cuando encuentre filas para eliminar, intentará adquirir un bloqueo en cada página que esté modificando. El insert adquirirá un bloqueo en la página en la que se está insertando y luego intentará adquirir el bloqueo de teclas. Normalmente, la insert esperará pacientemente a que se abra el bloqueo de teclas, pero esto se bloqueará si la delete intenta bloquear la misma página que usa la insert porque la delete necesita ese bloqueo de página y la insert necesita ese bloqueo de teclas. Sin embargo, esto no parece correcto para las inserciones, la delete y la insert están utilizando rangos de fecha y hora que no se superponen, por lo que tal vez algo más está sucediendo.

http://dev.mysql.com/doc/refman/5.1/en/innodb-next-key-locking.html

Answer 5

Tengo un método, los internos de los cuales están envueltos en un MySqlTransaction.

El problema del punto muerto se me apareció cuando ejecuté el mismo método en paralelo consigo mismo.

No hubo un problema al ejecutar una sola instancia del método.

Cuando eliminé MySqlTransaction, pude ejecutar el método en paralelo consigo mismo sin problemas.

Solo compartiendo mi experiencia, no estoy abogando por nada.

Answer 6

Un truco fácil que puede ayudar con la mayoría de los interbloqueos es ordenar las operaciones en un orden específico.

Obtiene un interbloqueo cuando dos transacciones intentan bloquear dos bloqueos en órdenes opuestas, es decir:

• conexión 1: llave de cerradura (1), llave de cerradura (2);
• conexión 2: llave de cerradura (2), llave de cerradura (1);

Si ambos se ejecutan al mismo tiempo, la conexión 1 bloqueará la llave (1), la conexión 2 bloqueará la llave (2) y cada conexión esperará a que la otra libere la tecla -> interbloqueo.

Ahora, si cambió sus consultas de modo que las conexiones bloquearían las claves en el mismo orden, es decir:

• conexión 1: llave de cerradura (1), llave de cerradura (2);
• conexión 2: llave de cerradura ( 1 ), llave de cerradura ( 2 );

Será imposible conseguir un punto muerto.

Así que esto es lo que sugiero:

1. Asegúrese de que no tiene otras consultas que bloqueen el acceso a más de una clave a la vez, excepto la instrucción de eliminación. si lo hace (y sospecho que sí), ordene su DÓNDE en (k1, k2, .. kn) en orden ascendente.

2. Arregla tu declaración de borrado para que funcione en orden ascendente:

Cambio

DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND

A

DELETE FROM onlineusers WHERE id IN (SELECT id FROM onlineusers
WHERE datetime <= now() - INTERVAL 900 SECOND order by id) u;

Otra cosa a tener en cuenta es que la documentación de mysql sugiere que, en caso de un interbloqueo, el cliente debe reintentar automáticamente. Puede agregar esta lógica a su código de cliente. (Por ejemplo, 3 reintentos en este error en particular antes de rendirse).