trying - Solucionando el error de MySQL "Se encontró un punto muerto al tratar de obtener el bloqueo; intente reiniciar la transacción "

mysql check deadlocks

Question

Tengo una tabla MySQL con aproximadamente 5,000,000 filas que se actualizan constantemente en pequeñas formas mediante procesos paralelos de Perl que se conectan a través de DBI. La tabla tiene alrededor de 10 columnas y varios índices.

Una operación bastante común da lugar al siguiente error a veces:

DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at Db.pm line 276.

La instrucción SQL que desencadena el error es algo como esto:

UPDATE file_table SET a_lock = ''process-1234'' WHERE param1 = ''X'' AND param2 = ''Y'' AND param3 = ''Z'' LIMIT 47

El error se dispara solo algunas veces. Calculo en 1% de llamadas o menos. Sin embargo, nunca sucedió con una tabla pequeña y se ha vuelto más común a medida que la base de datos ha crecido.

Tenga en cuenta que estoy usando el campo a_lock en file_table para asegurarme de que los cuatro procesos casi idénticos que estoy ejecutando no intenten trabajar en la misma fila. El límite está diseñado para dividir su trabajo en pequeños trozos.

No he hecho mucho ajuste en MySQL o DBD :: mysql. MySQL es una implementación estándar de Solaris y la conexión de la base de datos se configura de la siguiente manera:

my $dsn = "DBI:mysql:database=" . $DbConfig::database . ";host=${DbConfig::hostname};port=${DbConfig::port}";
my $dbh = DBI->connect($dsn, $DbConfig::username, $DbConfig::password, { RaiseError => 1, AutoCommit => 1 }) or die $DBI::errstr;

He visto en línea que varias otras personas han informado errores similares y que esta puede ser una verdadera situación de estancamiento.

Tengo dos preguntas:

1. ¿Qué exactamente sobre mi situación está causando el error anterior?

2. ¿Hay una manera simple de evitarlo o disminuir su frecuencia? Por ejemplo, ¿cómo hago para "reiniciar la transacción en Db.pm línea 276"?

Gracias por adelantado.

Answer 1

La idea de reintentar la consulta en caso de excepción de punto muerto es buena, pero puede ser terriblemente lenta, ya que la consulta de mysql seguirá esperando que se desbloqueen los bloqueos. Y en caso de estancamiento, mysql está tratando de encontrar si hay un punto muerto, e incluso después de descubrir que hay un punto muerto, espera un momento antes de expulsar un hilo para salir de la situación de estancamiento.

Lo que hice cuando me enfrenté a esta situación es implementar el bloqueo de tu propio código, ya que es el mecanismo de bloqueo de mysql el que falla debido a un error. Así que implementé mi propio bloqueo de nivel de fila en mi código java:

private HashMap<String, Object> rowIdToRowLockMap = new HashMap<String, Object>();
private final Object hashmapLock = new Object();
public void handleShortCode(Integer rowId)
{
    Object lock = null;
    synchronized(hashmapLock)
    {
      lock = rowIdToRowLockMap.get(rowId);
      if (lock == null)
      {
          rowIdToRowLockMap.put(rowId, lock = new Object());
      }
    }
    synchronized (lock)
    {
        // Execute your queries on row by row id
    }
}

Answer 2

La respuesta es correcta, sin embargo, la documentación de Perl sobre cómo manejar los interbloqueos es un poco escaso y tal vez confuso con las opciones PrintError, RaiseError y HandleError. Parece que en lugar de ir con HandleError, utiliza Print and Raise y luego usa algo como Try: Tiny para ajustar tu código y comprobar si hay errores. El siguiente código proporciona un ejemplo en el que el código db se encuentra dentro de un ciclo while que volverá a ejecutar una instrucción sql con error cada 3 segundos. El bloque de captura recibe $ _, que es el mensaje de error específico. Paso esto a una función de controlador "dbi_err_handler" que comprueba $ _ contra una serie de errores y devuelve 1 si el código debe continuar (rompiendo así el ciclo) o 0 si es un punto muerto y debe volverse a intentar ...

$sth = $dbh->prepare($strsql);
my $db_res=0;
while($db_res==0)
{
   $db_res=1;
   try{$sth->execute($param1,$param2);}
   catch
   {
       print "caught $_ in insertion to hd_item_upc for upc $upc/n";
       $db_res=dbi_err_handler($_); 
       if($db_res==0){sleep 3;}
   }
}

dbi_err_handler debe tener al menos lo siguiente:

sub dbi_err_handler
{
    my($message) = @_;
    if($message=~ m/DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction/)
    {
       $caught=1;
       $retval=0; # we''ll check this value and sleep/re-execute if necessary
    }
    return $retval;
}

Debe incluir otros errores que desee controlar y establecer $ retval en función de si desea volver a ejecutar o continuar.

Espero que esto ayude a alguien -

Answer 3

Si está utilizando InnoDB o cualquier RDBMS transaccional a nivel de fila, entonces es posible que cualquier transacción de escritura pueda causar un punto muerto, incluso en situaciones perfectamente normales. Las tablas más grandes, las escrituras más grandes y los bloques de transacción largos a menudo aumentan la probabilidad de que ocurran interbloqueos. En su situación, es probable que sea una combinación de estos.

La única manera de manejar realmente los bloqueos es escribir tu código para esperarlos. Esto generalmente no es muy difícil si el código de su base de datos está bien escrito. A menudo puede simplemente poner a try/catch la lógica de ejecución de la consulta y buscar un punto muerto cuando se producen errores. Si captura uno, lo normal es intentar ejecutar la consulta fallida nuevamente.

Le recomiendo que lea esta página en el manual de MySQL. Tiene una lista de cosas que hacer para ayudar a hacer frente a los bloqueos y reducir su frecuencia.

Answer 4

Tenga en cuenta que si utiliza SELECT FOR UPDATE para realizar una comprobación de exclusividad antes de una inserción, obtendrá un punto muerto para cada condición de carrera a menos que active la opción innodb_locks_unsafe_for_binlog . Un método libre de interbloqueo para comprobar la singularidad consiste en insertar ciegamente una fila en una tabla con un índice único utilizando INSERT IGNORE , luego para verificar el recuento de filas afectadas.

agregue la línea siguiente al archivo my.cnf

innodb_locks_unsafe_for_binlog = 1

#

1 - ENCENDIDO
0 - DESACTIVADO

#