semana - optimizar consultas lentas mysql
Cuándo usar SELECCIONAR... PARA ACTUALIZAR? (2)
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 lasrooms
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
enMySQL
(y varios otros sistemas antiguos) bloquea la tabla completa mientras dure una consulta.En
SQL Server
, las consultasSELECT
colocan bloqueos compartidos en los registros / páginas / tablas que han examinado, mientras que las consultasDML
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 consultasSELECT
oDELETE
se bloquearán hasta que se confirme otra sesión.En las bases de datos que usan
MVCC
(comoOracle
,PostgreSQL
,MySQL
conInnoDB
), una consultaDML
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, unSELECT FOR UPDATE
sería útil: bloquearía la consultaSELECT
oDELETE
hasta que otra sesión se comprometa, tal como lo haceSQL Server
.
¿Cuándo debería uno usar el aislamiento de transacción
REPEATABLE_READ
versusREAD_COMMITTED
conSELECT ... 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 dePostgreSQL
,REPEATABLE READ
es en realidad un sinónimo deSERIALIZABLE
. 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 deThread 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 conSELECT FOR UPDATE
En
InnoDB
,REPEATABLE READ
ySERIALIZABLE
son cosas diferentes: los lectores en modoSERIALIZABLE
establecen los bloqueos de la próxima clave en los registros que evalúan, lo que previene eficazmente elDML
simultáneo en ellos. Por lo tanto, no necesitaSELECT FOR UPDATE
en modo serializable, pero sí las necesita enREPEATABLE READ
oREAD 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".
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]
- habitaciones contiene
- 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é.
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.