transactions - tipos - ¿MySQL/InnoDB implementa un verdadero aislamiento serializable?
tipos de aislamiento mysql (5)
No queda completamente claro a partir de la documentación de MySQL si el motor InnoDB implementa verdadero aislamiento serializable 1 o aislamiento de instantáneas , que a menudo también se conoce como "serializable". ¿Cuál es?
Si MySQL InnoDB no lo hace, ¿hay algún RDBMS completamente gratuito y de calidad de producción que lo haga?
1 donde "verdadero aislamiento serializable" significa la ausencia no solo de anomalías de lectura según el estándar SQL, sino también de la anomalía de inclinación de escritura, que se explica con más detalle here .
¿Hay algún RDBMS de calidad de producción completamente gratuito que haga?
Postgres admite el verdadero aislamiento serializable a partir de la versión 9.1 . Sin duda, se califica como "completamente gratis" y "de calidad de producción".
¿Está seguro de que está utilizando transacciones "serializables"? Para estar seguro, debe usar "CONFIGURAR sesión SISTEMA DE AISLAMIENTO DE TRANSACCIÓN SERIALIZABLE;" para que toda la sesión sea serializable y no solo la siguiente transacción.
Estoy probando con 5.5.29 en OSX
y cuando intento insertar (3,30) en T1, después de la creación del índice en clase, la transacción espera y se cancela después del tiempo de espera de espera de bloqueo. (T2 aún está en progreso)
Al leer más en el enlace que proporcionó , dice que el uso del modo "lectura repetible" (el valor predeterminado de innodb) elimina las anomalías de lectura, como mencionó como uno de sus requisitos. Además, al leer su segundo enlace, parece que el manejo de anomalías de escritura se desplaza al usuario final. En el artículo mencionan Select for Update de Oracle, que MySQL también admite . No estoy seguro de si esto cumple con sus requisitos, pero debería ayudarlo un poco.
No creo que MySQL implemente aislamiento serializable, lo que, según tengo entendido, requeriría la capacidad de revertir, lo que definitivamente no es compatible. Para más información, lea here .
ACTUALIZAR:
Vea los comentarios, esto parece haberse solucionado en MySQL 5.5 , con estos ejemplos todavía tenemos un bloqueo de tabla y el bloqueo de la próxima clave del índice no se puede engañar, AFAIK.
Original:
Encontré su pregunta ayer y me preguntaba sobre el modelo de seriedad MVCC de InnoDb también.
Así que hice algunas pruebas . MySQL 5.1.37. Una buena prueba para el problema de la serializabilidad es la que se proporciona en la documentación postgrESQL 9.0 MVCC , en este capítulo Aislamiento serializable versus verdadera serialización podemos ver el límite del modelo MVCC en la serializabilidad si no se realiza un bloqueo de predicado.
Así que vamos a probarlo en MySQL:
CREATE TABLE t1 (
class integer,
value integer
) ENGINE=InnoDB;
INSERT INTO t1 (`class`,`value`) VALUES
(1,10),
(1,20),
(2,100),
(2,200);
Ahora abriremos dos conexiones diferentes para tener dos transacciones paralelas (T1 y T2):
T1:
SET TRANSACTIOn ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT SUM(value) FROM t1 WHERE class = 1;
El resultado es 30.
T2:
SET TRANSACTIOn ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT SUM(value) FROM t1 WHERE class = 2;
El resultado es 300.
Ahora viene el problema de la serializabilidad. Si T1 inserta una fila, la selección de T2 no es válida (aquí T2 hace lo mismo).
T1:
INSERT INTO t1 (`class`,`value`) VALUES (2,30);
==> esperando (hay un candado en su lugar)
T2:
INSERT INTO t1 (`class`,`value`) VALUES (1,300);
==> ERROR 1213 (40001): Se encontró un interbloqueo al intentar bloquearse; intente reiniciar la transacción
T1 ahora tiene éxito en su inserción, t2 tuvo un ROLLBACK, buena serializabilidad .
Esto fallaría en PostgreSQL 9.0 (las cosas están cambiando en 9.1, pero es otro problema). De hecho, solo una de las transacciones puede realizar una inserción en la tabla. Incluso si tratamos de insertar en la class=3 con.
INSERT INTO t1 (`class`,`value`) VALUES (3,30);
Veríamos un bloqueo de espera y puntos muertos en caso de problemas. Parece que tenemos un bloqueo de predicado en MySQL ... Pero, de hecho, es una implementación de bloqueo de próxima clave en InnoDB.
Innodb realiza bloqueos de filas con algunos huecos bloqueados también en los índices. Aquí no tenemos índices en la tabla, parece que MySQL decidió bloquear la tabla.
Así que intentemos probar el siguiente bloqueo de clave para ver si esto obliga a la serialización. Primero deshacer la transacción en ejecución (T1). Luego crea un índice.
CREATE index t1class ON t1 (class);
Ahora rehacer la prueba. Éxito , la serialización todavía se aplica. Buenas noticias.
Pero con el índice en su lugar, creo que el siguiente bloqueo de clave y los bloqueos de fila se hacen en el índice. Esto significa que deberíamos poder realizar una inserción si no afecta a una transacción paralela ... y aquí viene el gran problema .
T1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT SUM(value) FROM t1 WHERE class = 1;
El resultado es 30.
T2:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT SUM(value) FROM t1 WHERE class = 2;
El resultado es 300.
Aquí haremos un inserto no relacionado en T1, ahora que tenemos un índice, esto tendrá éxito:
T1:
INSERT INTO t1 (`class`,`value`) VALUES (3,30);
Ambos pueden realizar el inserto (aquí solo hice uno), eso es normal. El bloqueo predictivo no se aplica, no se han realizado consultas SELECT en la class=3 . Parece que el siguiente bloqueo de clave funciona mejor si le damos buenos índices (no hay bloqueo de tabla en las inserciones).
Ahora intentamos insertar en el siguiente bloqueo de tecla, en una fila que coincide con la selección de T2 (clase = 2):
T1:
INSERT INTO t1 (`class`,`value`) VALUES (2,30);
Ay. ¡Tiene éxito !
T2:
INSERT INTO t1 (`class`,`value`) VALUES (1,300);
==> esperando. Todavía hay un bloqueo allí. Ojalá.
T1:
COMMIT;
T2: (donde el bloqueo ha pasado, se hace el inserto)
SELECT SUM(value) FROM t1 WHERE class = 2;
COMMIT;
Todavía tengo 300 aquí. Parece que la serializabilidad se ha ido.
select * from t1;
+-------+-------+
| class | value |
+-------+-------+
| 1 | 10 |
| 1 | 20 |
| 2 | 100 |
| 2 | 200 |
| 3 | 30 | <-- test
| 2 | 30 | <-- from trans1
| 1 | 300 | <-- from trans2 ERROR!
+-------+-------+
Resultado: Al insertar una nueva fila no relacionada antes de insertar una fila que afecta a una consulta de transacción paralela, hemos falsificado el mecanismo de bloqueo de la siguiente clave. O al menos esto es lo que entiendo de mis pruebas. Así que diría, no confíe en el motor para una verdadera serialización . Cuando tiene funciones agregadas en una transacción, lo mejor es bloquear manualmente la tabla , transformar su problema de serialización en una situación real de un solo hombre, ¡sin sorpresas! Otros problemas de serialización en los ejemplos son las verificaciones de restricciones (verifique que la cantidad aún sea positiva después de su operación), también tiene bloqueos en estos casos.