semana - optimizar consultas lentas mysql

Cuándo usar SELECCIONAR... PARA ACTUALIZAR?

Question

Por favor, ayúdame a entender el caso de uso detrás de SELECT ... FOR UPDATE .

Pregunta 1 : ¿Es el siguiente un buen ejemplo de cuándo se debe usar SELECT ... FOR UPDATE ?

Dado:

• habitaciones [id]
• etiquetas [id, nombre]
• room_tags [room_id, tag_id]
  • room_id y tag_id son claves foráneas

La aplicación desea listar todas las salas y sus etiquetas, pero necesita diferenciar entre habitaciones sin etiquetas y habitaciones que se han eliminado. Si SELECCIONAR ... PARA ACTUALIZAR no se usa, lo que podría suceder es:

• Inicialmente:
  • habitaciones contiene [id = 1]
  • etiquetas contiene [id = 1, name = ''cats'']
  • room_tags contiene [room_id = 1, tag_id = 1]
• Tema 1: SELECT id FROM rooms;
  • returns [id = 1]
• Subproceso 2: DELETE FROM room_tags WHERE room_id = 1;
• Subproceso 2: DELETE FROM rooms WHERE id = 1;
• Tema 2: [confirma la transacción]
• Subproceso 1: SELECT tags.name FROM room_tags, tags WHERE room_tags.tag_id = 1 AND tags.id = room_tags.tag_id;
  • devuelve una lista vacía

Ahora, el Tema 1 piensa que la habitación 1 no tiene etiquetas, pero en realidad la sala se ha eliminado. Para resolver este problema, el subproceso 1 debe SELECT id FROM rooms FOR UPDATE , evitando así que el Tema 2 elimine de las rooms hasta que finalice el Tema 1. ¿Es eso correcto?

Pregunta 2 : ¿Cuándo se debe usar el aislamiento de transacciones READ_COMMITTED versus READ_COMMITTED con SELECT ... FOR UPDATE ?

Se espera que las respuestas sean portátiles (no específicas de la base de datos). Si eso no es posible, explica por qué.

Answer 1

La única forma portátil de lograr consistencia entre salas y etiquetas y asegurarse de que las salas nunca se devuelvan después de haber sido eliminadas las bloquea con SELECT FOR UPDATE .

Sin embargo, en algunos sistemas, el bloqueo es un efecto secundario del control de concurrencia y se obtienen los mismos resultados sin especificar FOR UPDATE explícitamente.

Para resolver este problema, el subproceso 1 debe SELECT id FROM rooms FOR UPDATE , evitando así que el Tema 2 elimine de las rooms hasta que finalice el Tema 1. ¿Es eso correcto?

Esto depende del control de concurrencia que esté usando su sistema de base de datos.

• MyISAM en MySQL (y varios otros sistemas antiguos) bloquea la tabla completa mientras dure una consulta.

• En SQL Server , las consultas SELECT colocan bloqueos compartidos en los registros / páginas / tablas que han examinado, mientras que las consultas DML colocan bloqueos de actualización (que más tarde serán promovidos a exclusivos o degradados a bloqueos compartidos). Los bloqueos exclusivos son incompatibles con los bloqueos compartidos, por lo que las consultas SELECT o DELETE se bloquearán hasta que se confirme otra sesión.

• En las bases de datos que usan MVCC (como Oracle , PostgreSQL , MySQL con InnoDB ), una consulta DML crea una copia del registro (de una u otra manera) y generalmente los lectores no bloquean a los escritores y viceversa. Para estas bases de datos, un SELECT FOR UPDATE sería útil: bloquearía la consulta SELECT o DELETE hasta que otra sesión se comprometa, tal como lo hace SQL Server .

¿Cuándo debería uno usar el aislamiento de transacción REPEATABLE_READ versus READ_COMMITTED con SELECT ... FOR UPDATE ?

Generalmente, REPEATABLE READ no prohíbe filas fantasmas (filas que aparecieron o desaparecieron en otra transacción, en lugar de modificarse)

• En Oracle y versiones anteriores de PostgreSQL , REPEATABLE READ es en realidad un sinónimo de SERIALIZABLE . Básicamente, esto significa que la transacción no ve los cambios realizados después de que haya comenzado. Por lo tanto, en esta configuración, la última consulta de Thread 1 devolverá la sala como si nunca se hubiera eliminado (que puede ser o no lo que usted quería). Si no desea mostrar las habitaciones después de que se hayan eliminado, debe bloquear las filas con SELECT FOR UPDATE

• En InnoDB , REPEATABLE READ y SERIALIZABLE son cosas diferentes: los lectores en modo SERIALIZABLE establecen los bloqueos de la próxima clave en los registros que evalúan, lo que previene eficazmente el DML simultáneo en ellos. Por lo tanto, no necesita SELECT FOR UPDATE en modo serializable, pero sí las necesita en REPEATABLE READ o READ COMMITED .

Tenga en cuenta que el estándar en los modos de aislamiento prescribe que no verá ciertas peculiaridades en sus consultas, pero no define cómo (con el bloqueo o con MVCC u otros).

Cuando digo "no es necesario SELECT FOR UPDATE " realmente debería haber agregado "debido a los efectos secundarios de la implementación de ciertos motores de base de datos".

Answer 2

Respuestas cortas:

Q1: Sí.

P2: no importa cuál use.

Respuesta larga:

Una select ... for update (como implica) seleccionará ciertas filas pero también las bloqueará como si ya las hubiera actualizado la transacción actual (o como si la actualización de identidad se hubiera realizado). Esto le permite actualizarlos de nuevo en la transacción actual y luego confirmar, sin otra transacción, poder modificar estas filas de ninguna manera.

Otra forma de verlo, es como si las dos afirmaciones siguientes se ejecutaran atómicamente:

select * from my_table where my_condition;
update my_table set my_column = my_column where my_condition;

Como las filas afectadas por my_condition están bloqueadas, ninguna otra transacción puede modificarlas de ninguna manera, y por lo tanto, el nivel de aislamiento de transacción no hace diferencia aquí.

Tenga en cuenta también que el nivel de aislamiento de la transacción es independiente del bloqueo: establecer un nivel de aislamiento diferente no le permite moverse y actualizar filas en una transacción diferente que está bloqueada por su transacción.

Lo que sí garantizan los niveles de aislamiento de las transacciones (a diferentes niveles) es la coherencia de los datos mientras las transacciones están en progreso.