try - Reparando "Excedió el tiempo de espera de espera de bloqueo; intente reiniciar la transacción "para una tabla ''atrapada'' Mysql?

transacciones en mysql workbench (12)

Desde un script envié una consulta como esta miles de veces a mi base de datos local:

update some_table set some_column = some_value

Olvidé agregar la parte donde, por lo que la misma columna se estableció en el mismo valor para todas las filas de la tabla y esto se hizo miles de veces y la columna se indexó, por lo que el índice correspondiente probablemente también se actualizó muchas veces .

Noté que algo andaba mal, porque tardó demasiado, así que maté el guión. Incluso reinicié mi computadora desde entonces, pero algo se quedó en la mesa, porque las consultas simples tardan mucho tiempo en ejecutarse y cuando intento eliminar el índice relevante, falla con este mensaje:

Lock wait timeout exceeded; try restarting transaction

Es una tabla innodb, por lo que la transacción está probablemente implícita. ¿Cómo puedo arreglar esta tabla y eliminar la transacción atascada de ella?

Cuando establece una conexión para una transacción, adquiere un bloqueo antes de realizar la transacción. Si no puede adquirir el candado, intente por algún tiempo. Si aún no se puede obtener el bloqueo, el tiempo de espera de bloqueo excedido será arrojado. Por qué no podrá adquirir un candado es porque no está cerrando la conexión. Por lo tanto, cuando intente obtener un bloqueo por segunda vez, no podrá adquirir el bloqueo ya que su conexión anterior aún no está cerrada y sosteniendo el bloqueo.

Solución: cierre la conexión o configure AutoCommit (verdadero) [de acuerdo con su diseño] para liberar el bloqueo.

Esto comenzó a sucederme cuando el tamaño de mi base de datos creció y estaba haciendo muchas transacciones en él.

La verdad es que probablemente haya alguna manera de optimizar sus consultas o su base de datos, pero intente estas 2 consultas para solucionar el problema.

Ejecuta esto:

SET GLOBAL innodb_lock_wait_timeout = 5000;

Y luego esto:

SET innodb_lock_wait_timeout = 5000;

Ir a procesos en mysql.

Entonces puedo ver que la tarea todavía está funcionando.

Elimine el proceso particular o espere hasta que el proceso se complete.

Me encontré con el mismo problema con una declaración de "actualización". Mi solución fue simplemente ejecutar las operaciones disponibles en phpMyAdmin para la tabla. Optimicé, ruboricé y desfragmenté la tabla (no en ese orden). No es necesario dejar caer la tabla y restaurarla desde la copia de seguridad para mí. :)

Puede verificar las transacciones que se están ejecutando actualmente con

SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`

Su transacción debe ser una de las primeras, porque es la más antigua de la lista. Ahora solo tome el valor de trx_mysql_thread_id y envíele el comando KILL :

KILL 1234;

Si no está seguro de qué transacción es suya, repita la primera consulta muy a menudo y vea qué transacciones persisten.

Reinicie MySQL, funciona bien.

PERO ten en cuenta que si tal consulta se bloquea, hay un problema en alguna parte:

en su consulta (charla perdida, producto cartesiano, ...)
muy numerosos registros para editar
combinaciones complejas o pruebas (MD5, subcadenas, LIKE %...% , etc.)
problema de estructura de datos
modelo de llave extranjera (bloqueo de cadena / lazo)
datos desindexados

Como dijo @syedrakib, funciona, pero esta no es una solución duradera para la producción.

Tenga cuidado: hacer el reinicio puede afectar sus datos con un estado incoherente.

Además, puede verificar cómo maneja MySQL su consulta con la palabra clave EXPLAIN y ver si hay algo allí para acelerar la consulta (índices, pruebas complejas, ...).

Resolví el problema dejando caer la tabla y restaurándola de la copia de seguridad.

Se corrigió, asegúrese de que no haya insertado el tipo de datos no coincidentes en la consulta. Tuve un problema en el que estaba intentando "datos del agente del navegador del usuario" en "VARCHAR (255)" y tuve problemas con este bloqueo; sin embargo, cuando lo cambié a "TEXTO (255)" lo arregló. Así que lo más probable es que no coincida con el tipo de datos

Tuve el mismo problema. Creo que fue un problema de punto muerto con SQL. Simplemente puede forzar el cierre del proceso de SQL desde el Administrador de tareas. Si eso no lo solucionó, simplemente reinicie su computadora. No es necesario que suelte la tabla y vuelva a cargar los datos.

Tuve este problema al intentar eliminar un determinado grupo de registros (utilizando MS Access 2007 con una conexión ODBC a MySQL en un servidor web). En general, eliminaría ciertos registros de MySQL y luego los reemplazaría con registros actualizados (la cascada elimina varios registros relacionados, esto agiliza la eliminación de todos los registros relacionados para la eliminación de un solo registro).

Traté de ejecutar las operaciones disponibles en phpMyAdmin para la tabla (optimizar, nivelar, etc.), pero estaba obteniendo un permiso de necesidad para recargar el error cuando traté de descargar. Como mi base de datos está en un servidor web, no pude reiniciar la base de datos. Restaurar desde una copia de seguridad no era una opción.

Intenté ejecutar la consulta de eliminación para este grupo de registros en el acceso cPanel mySQL en la web. Tengo el mismo mensaje de error.

Mi solución: utilicé el MySQL Query Browser gratuito de Sun (Oracle) (que instalé previamente en mi computadora) y ejecuté la consulta de eliminación allí. Funcionó de inmediato, Problema resuelto. Pude volver a realizar la función utilizando la secuencia de comandos de Access mediante el acceso ODBC a la conexión MySQL.

Tuve un problema similar y lo resolví comprobando los hilos que se están ejecutando. Para ver los hilos en ejecución use el siguiente comando en la interfaz de línea de comandos de mysql:

SHOW PROCESSLIST;

También se puede enviar desde phpMyAdmin si no tiene acceso a la interfaz de línea de comandos de mysql.
Esto mostrará una lista de subprocesos con los identificadores correspondientes y el tiempo de ejecución, por lo que puede MATAR los hilos que tardan demasiado tiempo en ejecutarse. En phpMyAdmin tendrá un botón para detener hilos utilizando KILL, si está utilizando la interfaz de línea de comandos solo use el comando KILL seguido de la identificación del hilo, como en el siguiente ejemplo:

KILL 115;

Esto terminará la conexión para el hilo correspondiente.

Comprobar el estado de InnoDB para bloqueos

SHOW ENGINE InnoDB STATUS;

Compruebe las tablas abiertas de MySQL

SHOW OPEN TABLES WHERE In_use > 0;

Verifique las transacciones InnoDB pendientes

SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;

Verifique la dependencia de bloqueo: qué bloquea qué

SELECT * FROM `information_schema`.`innodb_locks`;

Después de investigar los resultados anteriores, debería poder ver qué está bloqueando qué.

La causa raíz del problema también podría estar en su código: compruebe las funciones relacionadas especialmente para las anotaciones si usa JPA como Hibernate.

Por ejemplo, como se describe here , el mal uso de la siguiente anotación puede causar bloqueos en la base de datos:

@Transactional(propagation = Propagation.REQUIRES_NEW)