218 votos

Cómo evitar mysql 'Deadlock found when trying to get lock; try restarting transaction'

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

Anoche recibí el mensaje 'Deadlock found when trying to get lock; try restarting transaction' durante unos 5 minutos y parece que es al ejecutar INSERTs en esta tabla. ¿Puede alguien 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 actualización de la página:

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

A continuación, realiza algunos recuentos para registrar algunas estadísticas (es decir, miembros en línea, visitantes en línea).

226voto

Omry Yadan Puntos 7523

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

Se produce un bloqueo cuando dos transacciones intentan bloquear dos cerraduras en órdenes opuestas, es decir:

  • conexión 1: cierra la llave(1), cierra la llave(2);
  • conexión 2: cierra la llave(2), cierra la llave(1);

Si ambas 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 llave -> bloqueo.

Ahora bien, si cambias tus consultas de manera que las conexiones bloqueen las llaves en el mismo orden, es decir

  • conexión 1: cierra la llave(1), cierra la llave(2);
  • conexión 2: bloquea la llave( 1 ), bloquea la llave ( 2 );

será imposible llegar a 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 sentencia de eliminación. si lo hace (y sospecho que lo hace), ordene sus WHERE en (k1,k2,..kn) en orden ascendente.

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

Cambiar

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 bloqueo el cliente debe reintentar automáticamente. puedes añadir esta lógica a tu código de cliente. (Digamos, 3 reintentos en este error particular antes de rendirse).

55voto

ewernli Puntos 23180

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

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

Existen numerosas preguntas y respuestas sobre los bloqueos. Cada vez que se inserta/actualiza/borra una fila, se adquiere un bloqueo. Para evitar el bloqueo, debes asegurarte de que las transacciones concurrentes no actualicen las filas en un orden que pueda provocar un bloqueo. En términos generales, intentar adquirir el bloqueo siempre en el mismo orden incluso en diferentes transacciones (por ejemplo, siempre primero la tabla A y luego la tabla B).

Otro motivo de bloqueo en la base de datos puede ser índices que faltan . Cuando se inserta/actualiza/borra una fila, la base de datos necesita comprobar las restricciones relacionales, es decir, asegurarse de que las relaciones son coherentes. Para ello, la base de datos debe comprobar las claves externas de las tablas relacionadas. Para ello, puede dan lugar a que se adquiera otro bloqueo que el de la fila que se modifica. Asegúrese entonces de tener siempre índices en las claves foráneas (y por supuesto en las claves primarias), de lo contrario podría resultar en un bloqueo de la mesa en lugar de un bloqueo de filas . Si se produce un bloqueo de tabla, la contención de bloqueos es mayor y la probabilidad de bloqueo aumenta.

7voto

Anders Abel Puntos 36203

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

Para empezar, puede valer la pena intentar adquirir explícitamente un bloqueo de tabla de inmediato para la sentencia delete. Véase BLOQUEAR MESAS y Problemas de bloqueo de tablas .

5voto

Brian Sandlin Puntos 91

Podrías intentar tener eso delete operan insertando primero la clave de cada fila a 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);

Dividirlo así es menos eficiente, pero evita la necesidad de mantener un cierre de rango de llave durante el delete .

Además, modifique su select consultas para añadir un where excluyendo las filas de más de 900 segundos. Esto evita la dependencia de la tarea cron y permite reprogramar su ejecución con menor frecuencia.

Teoría sobre los bloqueos: No tengo mucha experiencia en MySQL pero aquí va... El delete va a mantener un bloqueo de rango de claves para datetime, para evitar que las filas que coincidan con su where para evitar que se añada la cláusula en medio de la transacción, y a medida que encuentre filas para eliminar intentará adquirir un bloqueo en cada página que esté modificando. La página insert va a adquirir un bloqueo en la página en la que se está insertando, y entonces intento de adquirir la cerradura de la llave. Normalmente el insert esperará pacientemente a que se abra la cerradura de la llave, pero ésta se bloqueará si el delete intenta bloquear la misma página que el insert está utilizando porque el delete necesita ese bloqueo de página y el insert necesita esa cerradura con llave. Sin embargo, esto no parece adecuado para los insertos, el delete y insert están utilizando rangos de fechas que no se solapan, así que quizá esté pasando algo más.

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

1voto

Archie Puntos 2742

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

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

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X