mysql - repetible - ¿Cómo producir lecturas fantasmas?

niveles de aislamiento postgresql

Question

Usando la "lectura repetible" debería ser posible producir una lectura fantasma, pero ¿cómo? Lo necesito para un ejemplo enseñando CS-estudiantes.

Creo que debo hacer un "SELECCIONAR ... DONDE x <= 888" en un campo no indexado x, con un límite superior 888 no presente, y luego en otra conexión insertar una nueva fila con un valor justo debajo de 888.

Excepto que no funciona. ¿Necesito una mesa muy grande? ¿O algo mas?

Answer 1

Erik

Vengo de probarlo con un gran número de filas.

Nunca encontrará fantasmas en InnoDB mysql con nivel de aislamiento de lectura comprometida o más restringido. Se explica en la documentación:

LECTURA REPETIBLE: Para lecturas consistentes, hay una diferencia importante con respecto al nivel de aislamiento LEER COMPROMETIDA: todas las lecturas consistentes dentro de la misma transacción leen la instantánea establecida por la primera lectura . Esta convención significa que si emite varias sentencias SELECT simples (sin bloqueo) dentro de la misma transacción, estas sentencias SELECT también son coherentes entre sí. Consulte la Sección 13.6.8.2, “Lecturas no bloqueadas consistentes”.

Pero tampoco puede encontrar fantasmas en el nivel de aislamiento de lectura comprometida: esto es necesario porque las "filas fantasmas" deben estar bloqueadas para que la replicación y recuperación de MySQL funcione.

Información más detallada: http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html

Creo que tendrá que pasar a otra marca de base de datos para mostrar fantasmas a sus estudiantes. Yo uso tanto MSSQLSERVER como Oracle .

Bueno ... es una pena para tu primera pregunta.

Answer 2

InnoDB debería proteger contra las lecturas fantasmas, como han escrito otros.

Pero InnoDB tiene un comportamiento extraño diferente relacionado con el bloqueo. Cuando una consulta adquiere un bloqueo, siempre adquiere el bloqueo en la versión más reciente de la fila. Así que intente lo siguiente

CREATE TABLE foo (i INT PRIMARY KEY, val INT);
INSERT INTO foo (i, val) VALUES (1, 10), (2, 20), (3, 30);

Luego en dos sesiones concurrentes (abrir dos ventanas de terminal):

-- window 1 -- window 2
START TRANSACTION;
START TRANSACTION;
SELECT * FROM foo;
UPDATE foo SET val=35 WHERE i=3;
SELECT * FROM foo;

Esto debería mostrar val = 10, 20, 30 en ambos SELECTs, ya que REPEATABLE-READ significa que la segunda ventana solo ve los datos tal como existían cuando se inició la transacción.

Sin embargo:

 SELECT * FROM foo FOR UPDATE;

La segunda ventana espera para adquirir el bloqueo en la fila 3.

COMMIT;

Ahora finaliza SELECT en la segunda ventana y muestra las filas con val = 10, 20, 35, porque el bloqueo de la fila hace que SELECT vea la versión confirmada más reciente. Las operaciones de bloqueo en InnoDB actúan como si se ejecutaran bajo READ-COMMITTED, independientemente del nivel de aislamiento de la transacción.

Incluso puede cambiar de ida y vuelta:

 SELECT * FROM foo;
SELECT * FROM foo FOR UPDATE;
SELECT * FROM foo;
SELECT * FROM foo FOR UPDATE;

Answer 3

La "lectura fantasma" en MySQL en el nivel de aislamiento de RR está oculta profundamente, pero aún puede reproducirla. Aquí están los pasos:

1. crear tabla ab (una clave primaria int, b int);

2. Tx1:
   empezar;
   seleccione * de ab; // conjunto vacio

3. Tx2:
   empezar;
   insertar en los valores de ab (1,1);
   cometer;
   
4. Tx1:
   seleccione * de ab; // conjunto vacío, lectura fantasma esperada faltante.
   actualizar ab set b = 2 donde a = 1; // 1 fila afectada.
   seleccione * de ab; // 1 fila. fantasma lee aqui !!!!
   cometer;
   

Answer 4

La posibilidad de reproducir lecturas fantasmas para el motor InnoDB para el nivel de aislamiento REPEATABLE READ es cuestionable, porque InnoDB utiliza el control de concurrencia Multiversion : para cada fila, el motor MVCC conoce los números de transacción cuando se insertó y eliminó una fila y puede reproducir el historial de actualizaciones de la fila.

Por lo tanto, todas las sentencias SELECT resultantes mostrarán el estado de la tabla al principio de la transacción, excepto las filas que se insertaron, eliminaron o actualizaron por esta misma transacción. No aparecerán nuevas filas confirmadas por otras transacciones, ya que tendrán números de transacción de inserción mayores que los de esta transacción, y el rango de filas no importa aquí.

Pude reproducir PHANTOM READS para el nivel de aislamiento REPEATABLE READ para la base de datos Apache Derby , ya que no utiliza el control de concurrencia multiversión (versión 10.8.2.2 en el momento de escribir esta respuesta).

Para reproducir, establezca el nivel de transacción adecuado (en ij - Cliente SQL de Derby):

-- Set autocommit off
autocommit off;
-- Set isolation level corresponding to ANSI REPEATABLE READ
set isolation rs;

T1:

SELECT * FROM TableN;

T2:

INSERT INTO TableN VALUES(55, 1);
COMMIT;

T1 de nuevo:

SELECT * FROM TableN;

Ahora T1 debería ver una fila más;

Answer 5

Las lecturas fantasmas pueden ocurrir porque no existen bloqueos de rango, entonces un ejemplo es (pseudocódigo):

Hilo1

Transaction 1 Update TableN set X=2 where X=1 wait(s1) Select TableN where X=1 Commit

hilo 2

Transaction 2: insert into tableN(id, X) values(55,1) commit; notify(s1)

En wikipedia hay otro ejemplo de lecturas fantasma: Lecturas fantasma | wikipedia

Lo importante aquí es la sincronización de transacciones, puede utilizar los puntos de sincronización.

Ejemplo EDIT que usa la función de sueño mysql (no probado):

--on thread 1
Create TableN(id int, x int);
insert into TableN(id, X) values(1,1);
insert into TableN(id, X) values(2,1);
insert into TableN(id, X) values(3,1);

BEGIN TRANSACTION;
Update TableN set X=2 where X=1
SELECT SLEEP(30) FROM DUAL;
select TableN from where X=1;
COMMIT; 

--In other thread, before 20 secs;

BEGIN TRANSACTION; insert into TableN(id, X) values(55,1);

COMMIT;

Answer 6

Para complementar la buena respuesta de Dani, puede usar Microsoft Sql Server para mostrar ese comportamiento a sus estudiantes.

El servidor Sql muestra lecturas fantasma en el nivel de aislamiento de lectura repetible como se afirma en la documentación here .

Postgres se suscribe a la misma noción que InnoDb como se explica here . Con Postgres tampoco, las lecturas fantasma ocurren en lecturas repetibles y, por lo tanto, tampoco son adecuadas para su propósito didáctico.

El servidor Sql ofrece otro nivel de aislamiento, instantánea, que hace lo que MySql InnoDb y Postgres hacen en lectura repetible (que es una implementación de lectura repetible sin bloqueo, sin lectura fantasma, pero no es serializable).

Sql Server Express es gratis, aunque necesita una máquina con Windows. También puede obtener una cuenta de Windows Azure y mostrar ese comportamiento con Sql Azure en línea.