update transaction start for example mysql transactions rowlocking

transaction - select for update mysql



¿Cuántas filas se bloquearán por SELECCIONAR... ORDENAR POR xxx LÍMITE 1 PARA ACTUALIZAR? (4)

Tengo una consulta con la siguiente estructura:

SELECT ..... WHERE status = ''QUEUED'' ORDER BY position ASC LIMIT 1 FOR UPDATE;

Es una instrucción SELECT de una sola tabla en la tabla InnoDB. La position campo (INT NOT NULL) tiene un índice. El estado es ENUM y también está indexado.

SELECT ... FOR UPDATE la página del manual dice que bloquea todas las filas que lee. ¿Entiendo correctamente, que en este caso solo se bloqueará una fila? ¿O más bien bloqueará toda la mesa?

¿Es posible determinar qué filas se bloquearán con la consulta de EXPLAIN ? Si es así, ¿cómo? Explicar para una consulta en la tabla vacía muestra lo siguiente:

1;''SIMPLE'';''job'';''index'';<null>;''index_position'';[34,...];<null>;1;''Using where''

A diferencia de otras bases de datos, en MySQL la consulta bloqueará las posiciones del índice. Esto significa efectivamente que todas las filas que actualmente tienen un status igual a ''QUEUED'' o que desea que haya cambiado a ''QUEUED'' desde otra transacción están bloqueadas. La única solución que he encontrado es seleccionar las filas sin FOR UPDATE , luego seleccionarlas con un filtro basado en ID y volver a verificar la condición una vez que están bloqueadas. No está bien, pero hace el trabajo.

Esta es una gran pregunta. InnoDB es un motor de bloqueo de nivel de fila, pero tiene que establecer bloqueos adicionales para garantizar la seguridad con el registro binario (utilizado para la replicación; recuperación en un punto en el tiempo). Para comenzar a explicarlo, considere el siguiente ejemplo (ingenuo):

session1> START TRANSACTION; session1> DELETE FROM users WHERE is_deleted = 1; # 1 row matches (user_id 10), deleted. session2> START TRANSACTION; session2> UPDATE users SET is_deleted = 1 WHERE user_id = 5; # 1 row matches. session2> COMMIT; session1> COMMIT;

Debido a que las declaraciones solo se escriben en el registro binario una vez confirmadas, en la sesión esclava # 2 se aplicaría primero y produciría un resultado diferente, lo que provocaría la corrupción de los datos .

Entonces, lo que hace InnoDB, es establecer bloqueos adicionales. Si is_deleted está indexado, entonces, antes de la sesión1, ninguna otra persona podrá modificar o insertar en el rango de registros donde is_deleted=1 . Si no hay índices en is_deleted , entonces InnoDB necesita bloquear cada fila de la tabla completa para asegurarse de que la reproducción se realiza en el mismo orden. Puede pensar en esto como cerrar la brecha , que es un concepto diferente para captar directamente el bloqueo a nivel de fila .

En su caso con esa ORDER BY position ASC , InnoDB debe asegurarse de que no se puedan modificar nuevas filas entre el valor de clave más bajo y el valor más bajo posible "especial". Si hiciste algo como ORDER BY position DESC .. bueno, entonces nadie podría insertar en este rango.

Así que aquí viene la solución:

  • El registro binario basado en declaraciones apesta. Realmente espero un futuro en el que todos cambiemos al registro binario basado en filas (disponible desde MySQL 5.1, pero no de forma predeterminada).

  • Con la replicación basada en filas, si cambia el nivel de aislamiento a lectura confirmada, solo se debe bloquear la fila que coincida.

  • Si desea ser un masoquista, también puede activar innodb_locks_unsafe_for_binlog con replicación basada en declaraciones.

Actualización del 22 de abril : para copiar y pegar mi versión mejorada de su testcase (no estaba buscando ''en el espacio''):

session1> CREATE TABLE test (id int not null primary key auto_increment, data1 int, data2 int, INDEX(data1)) engine=innodb; Query OK, 0 rows affected (0.00 sec) session1> INSERT INTO test VALUES (NULL, 1, 2), (NULL, 2, 1), (5, 2, 2), (6, 3, 3), (3, 3, 4), (4, 4, 3); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 session1> start transaction; Query OK, 0 rows affected (0.00 sec) session1> SELECT id FROM test ORDER BY data1 LIMIT 1 FOR UPDATE; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) session2> INSERT INTO test values (NULL, 0, 99); # blocks - 0 is in the gap between the lowest value found (1) and the "special" lowest value. # At the same time, from information_schema: localhost information_schema> select * from innodb_locks/G *************************** 1. row *************************** lock_id: 151A1C:1735:4:2 lock_trx_id: 151A1C lock_mode: X,GAP lock_type: RECORD lock_table: `so5694658`.`test` lock_index: `data1` lock_space: 1735 lock_page: 4 lock_rec: 2 lock_data: 1, 1 *************************** 2. row *************************** lock_id: 151A1A:1735:4:2 lock_trx_id: 151A1A lock_mode: X lock_type: RECORD lock_table: `so5694658`.`test` lock_index: `data1` lock_space: 1735 lock_page: 4 lock_rec: 2 lock_data: 1, 1 2 rows in set (0.00 sec) # Another example: select * from test where id < 1 for update; # blocks

He hecho pruebas. Creó la siguiente tabla:

id data1 data2 1 1 2 2 2 1 5 2 2 6 3 3 3 3 4 4 4 3

Entonces creé la primera conexión con la transacción:

SELECT id FROM test ORDER BY data1 LIMIT 1 FOR UPDATE;

el resultado fue la fila con id = 1;

Luego creé la segunda transacción desde otra conexión sin cometer primero:

SELECT id FROM test WHERE data1=2 FOR UPDATE;

No bloqueó. Y se bloqueó solo cuando intenté seleccionar la misma fila seleccionada por la primera transacción. Intenté lo siguiente con cambiar ORDER BY a DESC one, también funciona.

Conclusión: MySQL solo bloquea las filas que realmente seleccionó al usar las cláusulas ORDER BY y LIMIT. Consulte la respuesta de @Morgan para obtener una explicación sobre el bloqueo de espacios.

Mi versión de MySQL es 5.0.45