trying - Evitar el interbloqueo de MySQL al actualizar compartidos a un bloqueo exclusivo

mysql check deadlocks

Question

Estoy usando MySQL 5.5. He notado que se produce un interbloqueo peculiar en un escenario concurrente, y no creo que este bloqueo se produzca.

Reproduce así, usando dos sesiones de cliente mysql ejecutándose simultáneamente:

Sesión mysql 1 :

create table parent (id int(11) primary key);
insert into parent values (1);
create table child (id int(11) primary key, parent_id int(11), foreign key (parent_id) references parent(id));

begin;
insert into child (id, parent_id) values (10, 1);
-- this will create shared lock on parent(1)

sesión de mysql 2 :

begin;
-- try and get exclusive lock on parent row
select id from parent where id = 1 for update;
-- this will block because of shared lock in session 1

Sesión mysql 1 :

-- try and get exclusive lock on parent row
select id from parent where id = 1 for update;
-- observe that mysql session 2 transaction has been rolled back

sesión de mysql 2 :

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

La información reportada desde el estado de show engine innodb status es esta

------------------------
LATEST DETECTED DEADLOCK
------------------------
161207 10:48:56
*** (1) TRANSACTION:
TRANSACTION 107E67, ACTIVE 43 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 13074, OS thread handle 0x7f68eccfe700, query id 5530424 localhost root statistics
select id from parent where id = 1 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E67 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000107e65; asc     ~e;;
 2: len 7; hex 86000001320110; asc     2  ;;

*** (2) TRANSACTION:
TRANSACTION 107E66, ACTIVE 52 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 12411, OS thread handle 0x7f68ecfac700, query id 5530425 localhost root statistics
select id from parent where id = 1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E66 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000107e65; asc     ~e;;
 2: len 7; hex 86000001320110; asc     2  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E66 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000107e65; asc     ~e;;
 2: len 7; hex 86000001320110; asc     2  ;;

*** WE ROLL BACK TRANSACTION (1)

Puede ver que la transacción (1) no muestra ningún bloqueo S o X ya adquirido; Simplemente está bloqueado tratando de adquirir un bloqueo exclusivo. Como no hay ciclo, no debería haber un punto muerto en esta situación, como lo entiendo.

¿Es este un error conocido de MySQL? ¿Han encontrado otras personas? ¿Qué soluciones se utilizaron?

Estos son los posibles pasos a seguir que podríamos dar:

• Reducir el uso de claves externas (en nuestro escenario de producción, solo eliminamos las filas de la tabla a la que se hace referencia, pero es difícil)
• Adquiera bloqueos exclusivos por adelantado en lugar de bloqueos compartidos implícitos (reducirá nuestro rendimiento concurrente)
• Cambie nuestra lógica para que ya no necesitemos un bloqueo exclusivo en el padre en la misma transacción que agregue una fila hijo (arriesgado y difícil)
• Cambie nuestra versión de MySQL a una que no muestre este comportamiento

¿Hay otras opciones que no estamos considerando?

Answer 1

Este es un error de larga data que puede leer más de: bugs.mysql.com/bug.php?id=48652

Este es un problema en el bloqueo de tablas de nivel MySQL.

Internamente dentro de InnoDB, una comprobación de restricción FOREIGN KEY puede leer (o, con la cláusula ON UPDATE o ON DELETE, escribir) las tablas primarias o secundarias.

Normalmente, el acceso a la tabla se rige por los siguientes bloqueos: 1. Bloqueo de metadatos de MySQL 2. Bloqueo de tabla InnoDB 3. Bloqueos de registro InnoDB

Todos estos bloqueos se mantienen hasta el final de la transacción.

La tabla InnoDB y los bloqueos de registros se omiten en ciertos modos, pero no durante las comprobaciones de clave externa. El interbloqueo se produce porque MySQL adquiere el bloqueo de metadatos solo para las tablas que se mencionan explícitamente en las sentencias de SQL.

Supongo que una solución alternativa podría ser acceder a las tablas secundarias (o principales) al comienzo de la transacción, antes de la operación problemática FOREIGN KEY.

Lee la discusión y es respuesta

Answer 2

La razón para actualizar la fila principal no se dio, pero supongo que esto tiene que ver con alguna normalización, basada en esta secuencia de la pregunta:

-- session 1
begin;
insert into child (id, parent_id) values (10, 1);
...
select id from parent where id = 1 for update;

Por ejemplo, una orden (tabla principal) tiene una cantidad de columna, que se mantiene como la suma de las cantidades de todas las líneas de orden (tabla secundaria).

Parece que la lógica para mantener los datos primarios está codificada en la propia aplicación (con sentencias de actualización explícitas), lo que tiene las siguientes consecuencias:

• Si insert en child se realiza en muchos lugares diferentes, entonces la lógica de la aplicación en el cliente debe actualizarse en todos estos lugares para mantener la integridad. Esto es duplicación de código.

• Incluso si esto se hace en un solo lugar, el hecho de que la tabla principal deba actualizarse cuando se agrega un hijo es imposible para que el servidor lo descubra.

En su lugar, considere la siguiente opción:

Defina los desencadenantes en la tabla secundaria, que actualizan la tabla primaria según sea necesario.

Tiene las siguientes implicaciones:

• Primero, la lógica para mantener la tabla principal ya no está (posiblemente) duplicada, como lo está en el propio activador.

• Segundo, y esta es la parte importante aquí, el servidor MySQL ahora sabe que la tabla principal se actualiza cada vez que se inserta un registro secundario, y debido a esto, se toma el bloqueo adecuado (exclusivo en lugar de compartido).

Probado con 8.0, ver más abajo.

En cuanto a la preocupación sobre el rendimiento de concurrencia,

• diferentes transacciones que operan en diferentes filas principales se ejecutarán en paralelo, ya que los bloqueos exclusivos se toman en las filas principales (diferentes), no en la tabla principal.

• las transacciones que operan simultáneamente en la misma fila principal se serializarán ... lo que en realidad es el resultado esperado, ya que se completan en el mismo registro de todos modos.

Serializar las transacciones que se garantiza que tendrán éxito debería proporcionar un mejor rendimiento (en lo que respecta a la carga de trabajo de la aplicación) que fallar algunas transacciones, solo para reintentarlas.

Obviamente, los activadores de actualización y eliminación también deberían ser necesarios para actualizar el principal, dependiendo de la lógica de la aplicación.

Preparar

create table parent (
  id int(11) primary key,
  number_of_children int(11));

create table child (
  id int(11) primary key,
  parent_id int(11),
  foreign key (parent_id) references parent(id));

delimiter $$;
create trigger bi_child before insert on child
for each row
begin
  update parent
    set number_of_children = number_of_children + 1
    where id = NEW.parent_id;
end
$$
delimiter ;$$

begin;
insert into parent values (1, 0);
insert into parent values (2, 0);
commit;

Sesión 1

begin;
insert into child values (10, 1);

Sesion 2

begin;
insert into child values (20, 2);

No bloqueado, ya que se utiliza un padre diferente.

Sesion 3

begin;
-- this now blocks, waiting for an X lock on parent row 1.
insert into child values (11, 1);

Sesión 1

-- unlocks session 3
commit;

Sesion 3

cometer;

Sesion 2

cometer;

Resultados

select * from parent;
id      number_of_children
1       2
2       1