10. Transacciones y Concurrencia
10.1 Introducción
Como se mencionó anteriormente una de las grandes ventajas de emplear bases de datos y más especificamente de emplear un dbms es que se procure un buen manejo de la información, garantizando que todas las operaciones sean "ácidas", es decir, que cumplan con
- Atomicity: que las transacciones sean atómicas, se vean como una sola unidad
- Consistency: que los datos realmente estén bien relacionados y que no existan problemas de falta de información o falta de confiabilidad en los datos
- Isolation: aislamiento, separar las operaciones de distintas conexiones/usuarios unos de otros
- Durability: que se garantice la persistencia de los datos.
Durante esta sección se tratan de abordar los conceptos de atomicity, consistency e isolation.
10.2 Transacciones
10.2.1 Definición
Una transacción es una unidad de programa que accesa y posiblemente actualiza varios elementos de datos.
10.2.2 Estados de transacción
- Active, el estado inicial y permanece durante toda la ejecución
- Partially committed, después de que se ha ejecutado el último statement
- Failed, después de algun error, no se puede continuar
- Aborted, se hace un "rollback" hacia un estado anterior consistente
- Committed, después del éxito
10.2.3 Programación
|
Crear Conexión Abrir Conexión Iniciar Transacción Queries... -Error Procesar resultados Asegurar Transacción Cerrar Conexión
|
Notas:
- Por default toda transacción tiene autocommit = 1
- El rollback generalmente se usa en el catch de los programas, aunque puede ir en cualquier parte.
- Una transacción que no llega al commit automáticamente hace el rollback
10.3 Control de concurrencia
10.3.1 Introducción
El control de accesos concurrentes y específicamente de transacciones concurrentes es manejado por un módulo del dbms llamado "scheduler".
Es importante recordar que muchos de los datos de la base no se encuentran nada más en disco, sino tambien en los buffers de memoria, de ahí que el scheduler interactúa con ellos y en su defecto solicita la lectura de los datos del disco.
 |
Scheduler del DBMS
El calendarizador crea agendas, secuencias ordenadas de las acciones tomadas por una o más transacciones.
El siguiente ejemplo muestra 2 transacciones cuya única característica de consistencia es que A=B ya que ambas operaciones son iguales para ambos elementos. Por otro lado sabemos que si las transacciones son ejecutadas aisladamente la consistencia se preservará.
 |
2 transacciones
10.3.2 Serial Schedules
Un schedule se considera "serial" si sus acciones consisten de todas las acciones de una transacción, seguidas de todas las de otra transacción y así sucesivamente sin mezclas de ninpún tipo.
 |
Serial schedule, T1 precede a T2
 |
Serial schedule, T2 precede a T1
Se puede observar que el resultado final no es el mismo, pero esto no es algo central ya que lo que realmente importa es la preservación de consistencia. En general no se espera que el estado final de una base de datos sea independiente del orden de las transacciones.
10.3.3 Serializable Schedules
Existe otra manera de mantener la consistencia, un schedule puede ser serializable siempre y cuando se mantenga el mismo estado que si se tratase de un serial.
 |
Serializable, pero no serial schedule
 |
Nonserializable schedule
 |
Serializable por accidente
10.3.4 Conflict-Serializability
Tratando de plantear una condición para asegurar que un schedule es serializable, se debe entender aquellos casos que representan conflictos, es decir, aquellos pares de acciones cuyo orden no puede ser intercambiado.
Veamos primero aquellas acciones que no representan conflictos.
Cuando Ti y Tj son transacciones diferentes:
-
ri(X); rj(Y) : nunca es conflicto
-
ri(X); wj(Y) : no es un conflicto, X != Y
-
wi(X); rj(Y) : no es un conflicto, por lo mismo que la anterior
-
wi(X); wj(Y) : no es un conflicto, por lo mismo que la anterior
Por otro lado existen 3 situaciones en donde NO se pueden intercambiar el orden de las acciones.
-
2 acciones de la misma transacción, ej ri(X); wi(Y)
-
2 escrituras del mismo elemento por diferentes transacciones, wi(X); wj(X).
-
Una lectura y escritura del mismo elemento por diferentes transacciones, ri(X); wj(X) y wi(X); rj(X).
De manera que 2 acciones de diferentes transacciones pueden ser intercambiadas, a menos que:
-
Involucren al mismo elemento y
-
Al menos 1 es una escritura
 |
Conversión de un conflict-serializable schedule a un serial schedule
10.3.5 Procurando serializability con Locks
10.3.5.1 Locks
Para garantizar que no haya problemas entre transacciones el scheduler emplea una tabla de lock para bloquear ciertas acciones de manera que es seguro ejecutar toda transacción.
 |
Schedules con Lock table
La consistencia de transacciones se basa en que:
-
Una transacción solo puede leer y escribir un elemento si se solicitó un bloqueo y éste no se ha liberado.
-
Si una transacción bloquea un elemento, debe liberarlo posteriormente.
Si un schedule cumple con las condiciones anteriores se dice que el schedule es "legal"
 |
Legal schedule, pero desafortunadamente no serializable
 |
Locking retrasando una petición para evitar un illegal schedule
10.3.5.2 Two-phase locking (2PL)
"En toda transacción, todos los locks preceden a todos los unlocks".
Para los últimos 2 diagramas de la sección 10.3.5.1, el primero no cumple con 2PL y el segundo cumple 2PL.
El problema de 2PL, caer en un deadlock
 |
Deadlock
10.3.5.3 Shared y Exclusive Locks
Un shared lock permite a otras transacciones leer la misma información pero no escribir
Un exclusive lock evita que otros puedan leer y mucho menos escribir
 |
Shared y Exclusive locks
10.3.6 Control de concurrencia por Timestamps
10.3.6.1 Timestamps
Se asigna una fecha o un contador a cada transacción, para cada elemento afectado por la transacción se le asigna ese timestamp, de manera que se asegura que toda acción pertenece a dicha transacción.
 |
T read too late
 |
T write too late
10.3.6.2 Dirty data
 |
T con dirty read
 |
Write cancelled
10.3.7 Control de concurrencia por Validation
10.3.7.1 Validation
Muy similar al timestamp la diferencia radica en que el scheduler mantiene un registro de lo que están haciendo las transacciones mas que tener los tiempos de cada r-w por cada elemento.
El proceso de toda transacción se divide en 3 fases: read, validate y write
 |
T no puede validarse por la escritura de U
 |
T no puede validarse si puede adelantarse a otra transacción
10.4 Control de concurrencia en el DBMS
10.4.1 Manejo de transacciones en SQL
SET AUTOCOMMIT = {0 | 1}
Si el modo de autocommit está en apagadado SET AUTOCOMMIT = 0, entonces se asume que la misma transacción continua hasta que se realice un COMMIT o un ROLLBACK. Donde un commit actualizará Por default el modo de autocommit está encendido, de manera que cada operación es considerada una transacción y todo cambio se va reflejando automáticamente en la base de datos.
10.4.2 Niveles de aislamiento en SQL
SQL estándar define 4 niveles de aislamiento en términos de 3 fenómenos que deben ser prevenidos entre transacción concurrentes. Estos son:
- dirty read: Una transacción lee datos escritos por una transacción concurrente que no ha hecho "commit"
- nonrepeatable read: Una transacción re-lee datos que leyó previamente y encuentra que han sido modificados por otra transacción (que hizo commit en el inter).
- phantom read:
Una transacción re-ejecuta un query regresando un conjunto de tuplas que satisfacen una condición de búsqueda y encuentra que el resultado ha cambiado debido a otra transacción que hizo "commit" recientemente.
SQL Transaction Isolation Levels
| Isolation Level | Dirty Read | Nonrepeatable Read | Phantom Read |
|---|---|---|---|
| Read uncommitted | Possible | Possible | Possible |
| Read committed | Not possible | Possible | Possible |
| Repeatable read | Not possible | Not possible | Possible |
| Serializable | Not possible | Not possible | Not possible |
*PostgreSQL ofrece Read Committed (default) y Serializable
*La mayoría de los dbms ofrecen los 4 niveles
READ UNCOMMITTED- Tambíen llamado "dirty read": los selects que no requieren bloqueos son realizados de manera que no es posible ver una versión más reciente del registro, entonces no hay lecturas consistentes bajo este nivel. El opuesto es el
READ COMMITTED.
READ COMMITTED- Parecido al nivel de aislamiento en Oracle. Todos los queries del tipo
SELECT ... FOR UPDATEandSELECT ... LOCK IN SHARE MODEúnicamente bloquean los índices de los registros, no los huecos (gaps) anteriores a ellos y así permite insertar libremente nuevos registros después de los registros bloqueados.UPDATEyDELETEque usan un índice único con una única condición de búsqueda, solo bloquean el índice del registro encontrado, no los huecos entre ellos. Pero en selecciones de "rangos" paraUPDATE y DELETEen Innodb se debe aplicar un bloqueo de "next-key o gap" y bloquear las inserciones de otros usuarios en los huecos cubiertos en ese rango. Esto es necesario porque tuplas fantasmas (phantom rows) tienen que ser bloqueadas para replicación y recuperación. Lecturas consistentes llegan a ser como en Oracle: cada lectura consistente, aun dentro de la misma transacción, lee y actualiza su propia copia.
REPEATABLE READ
- El nivel por default en InnoDB.
SELECT ... FOR UPDATE,SELECT ... LOCK IN SHARE MODE,UPDATE, andDELETE, los cuales usan índices únicos con una única condición de búsqueda, sólo bloquean el índice del registro encontrado, no los huecos anteriores a él. Con otras opciones de búsqueda, estas operaciones emplean bloqueos "next-key", bloqueando el rango de índices obtenido a partir del bloqueo "next-key" o "gap" y bloquea nuevas inserciones por parte de otros usuarios. En lecturas consistentes hay una diferencia muy importante con el nivel "read commited": en este nivel todas las lecturas consistentes dentro de la misma transacción leen la misma copia de información establecida por la primer lectura. Esta convención significa que si se realizan varios selects simples dentro de la misma transacción estos selects son consistentes entre si.
SERIALIZABLE
- Este nivel es como el anterior, pero todos los selects simples son implícitamente convertidos a
SELECT ... LOCK IN SHARE MODE.
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
10.4.3 Consistent read
Es el uso de multiversiones para presentar a un query la información contenida en la base de datos en un punto de tiempo dado. El query verá los cambios hechos exactamente por aquellas transacciones que hicieron un "commit" antes de ese punto y no cambios hechos después ni mucho menos por transacciones "uncommited". La excepción a esta regla es que el query ve los cambios hechos por la misma transacción que hizo el query.
Si se está empleando el nivel repeatable read, entonces todas las lectuas consistentes dentro de la misma transacción leen la "copia" establecida por la primer lectura realizada en esa transacción. Para obtener copias más actualizadas se debe hacer un "commit" primero.
Lectura consistente es el modo por defecto en el cual InnoDB procesa "selects" en los niveles READ COMMITTED y REPEATABLE READ. Una lectura consistente no aplica ningún bloqueo en las tablas que accesa y sin embargo otros usuarios tienen la libertad de modificar esas tablas al mismo tiempo que una lectura consistente está siendo realizada en la tabla.
| User A | User B |
|---|---|
mysql> set autocommit=0;
mysql> SELECT * FROM t;
Empty set (0.40 sec)
mysql> SELECT * FROM t;
Empty set (0.40 sec)
mysql> SELECT * FROM t;
Empty set (0.40 sec)
mysql> COMMIT;
mysql> SELECT * FROM t;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.11 sec)
| mysql> set autocommit=0;
mysql> INSERT INTO t VALUES (1, 2);
mysql> COMMIT;
|
10.4.4 SELECT ... FOR UPDATE y SELECT ... LOCK IN SHARE MODE
Una lectura consistente no es conveniente en algunas circunstancias.
Supongamos que se desea agregar una nueva tupla en una tabla "child", y asegurarse que el hijo tiene un padre en la tabla "parent".
| id_parent | name |
Child
| id_child | name | id_parent |
Supongamos que se usa una lectura consistente para leer la tabla "parent" y en efecto se verifica que existe el padre del hijo en la tabla.
Se podría agregar de manera segura la tupla en la tabla "child" ?.
No, porque puede suceder que mientras, otro usuario borre la tupla del padre en la tabla "parent" y no se recibiría una notificación al respecto.
La solución es realizar un "select" en modo de bloqueo, LOCK IN SHARE MODE.
SELECT * FROM PARENT WHERE NAME = 'Jones' LOCK IN SHARE MODE;
Realizando la lectura en modo compartido significa que se lee la última versión de los datos y se aplica un bloqueo en modo compartido en las tuplas leídas. Si la última versión pertenece a una transacción "uncommitted" de otro usuario, se debe esperar a que la transacción termine. Un bloqueo en modo compartido nos previene de que otros puedan actualizar o borrar la tupla que hemos leído.
Este ejemplo muestra cómo implementar integridad referencial en una aplicación.
Veamos otro ejemplo:
Tenemos una columna que se emplea como contador en una tabla "child_codes" la cual se emplea para asignar ids a cada hijo que se agrega a la tabla "child".
Child_codes
| code |
Obviamente usar una lectura consistente para leer el valor actual no es una buena idea, ya que otros usuarios pueden leer la misma información y en consecuencia generarán un error de llave duplicada cuando se pretendan agregar a dichos hijos. Usar lock in share mode para la lectura no es una buena solución tampoco porque si 2 usuarios leen el contador al mismo tiempo, entonces al menos uno de ellos terminará con un "deadlock" cuando trate de actualizar el contador.
En este caso existen 2 maneras de implementar la lectura e incrementar el contador:
- Actualizar el contador primero, incrementándolo en 1 y sólo entonces leerlo.
- Leer el contador primero con un bloqueo "for update" e incrementarlo despúes.
SELECT COUNTER_FIELD FROM CHILD_CODES FOR UPDATE;
UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1;
Un SELECT ... FOR UPDATE leerá la última versión del dato, aplicando un bloqueo exclusivo para cada tupla que se lee. Se aplica el mismo bloqueo que se realiza para un update, recordemos que un update lleva también una condición de búsqueda.
10.4.5 Next-key locking: evitando el problema del fantasma
En bloqueos por renglón Innodb emplea un algoritmo llamado "next-key locking". Innodb hace el bloqueo de manera que busca o escanea el índice de una tabla, aplica bloqueos exclusivos o compartidos en los registros índice encontrados. De ahi que el bloque por renglón usualmente se conoce como bloqueos a los registros del índice.
Los bloqueos que aplica InnoDB a los registros del índice probablemente afecten también los huecos anteriores al registro índice. Si un usuario tiene un bloqueo exclusivo o compartido en un registro R en un índice, entonces otro usuario NO puede insertar un nuevo índice inmediatamente antes que R en el orden del índice. Este bloqueo de huecos esta hecho para prevenir el problema del "fantasma". Supongamos que se desea leer y bloquear todos los hijos con un id mayor que 100 en la tabla "child" y actualizar algún campo en las tuplas seleccionadas.
SELECT * FROM CHILD WHERE ID_CHILD > 100 FOR UPDATE;
Supongamos que hay un índice en la columna "id_child" de la tabla "child". Nuestro query escaneará ese índice desde el primer registro que es mayor que 100. Ahora, si el bloqueo aplica en el índice no bloquearía las inserciones en los huecos, un nuevo hijo podría agregarse mientras tanto a la tabla. Si nuevamente se ejecuta:
SELECT * FROM CHILD WHERE ID_CHILD > 100 FOR UPDATE;
se verá un nuevo hijo en el conjunto de resultados del query. Esto está en contra del principio de ailamiento de transacciones: una transacción debe ser capaz de correr de manera que los datos que ha leído no cambia durante la transacción. Si consideramos un conjunto de tuplas como un "item" entonces el nuevo hijo "fantasma" rompería el principio de aislamiento.
Cuando Innodb escanea un índice, también bloquea los huecos despues del último registro en el índice. De manera que en el ejemplo anterior, el bloqueo también previene alguna inserción a la tabla para algún id mayor que 100. De manera que el bloqueo "next-key" permite bloquear aquellos datos aún no existentes en una tabla.
10.4.6 Locks en los diferentes enunciados SQL
Un bloqueo de lectura, un update o un delete generalmente aplica bloqueos en cada registro índice escaneado en el procesamiento del query. No importa si existen condiciones where en el query lo cual excluiría a la tupla del conjunto de resultados. InnoDB no recuerda la condición "where" exacta, solo conoce cuáles rangos de índices fueron escaneados. Los bloqueos de registros son normalmente "next-key" los cuales tambien bloquean inserciones a los huevos inmediatamente antes del registro.
Si el bloqueo es exclusivo, entonces InnoDB siempre recupera en índice agrupado y lo bloquea.
Si no se tienen buenos índices para un query y MySQL tiene que escanear la tabla completa para procesar el query, entonces cada tupla de la tabla es bloqueada, lo cual obviamente hace que se bloqueen todas las inserciones de otros usuarios. Es importante crear buenos índices para no tener que escanear toda la tabla.
SELECT ... FROM ...- esta es una lectura consistente, leyendo una copia de la base de datos y no aplicando bloqueos, a menos que el nivel de aislamiento sea
SERIALIZABLE, en cuyo caso se aplican bloqueos next-key compartidos en los índices de registros encontrados.
SELECT ... FROM ... LOCK IN SHARE MODE- aplica bloqueo compartido en todos los registros índice encontrados.
SELECT ... FROM ... FOR UPDATE- aplica bloqueo exclusivo en todos los registros índice encontrados.
INSERT INTO ... VALUES (...)- aplica un bloqueo exclusivo en la tupla insertada.
- initializing the counter for an
AUTO_INCREMENTcolumn - aplica un bloqueo exclusivo al contador
INSERT INTO T SELECT ... FROM S WHERE ...- aplica un bloqueo exclusivo (non-next-key) en cada tupla insertada en T. Normalmente hace una búsqueda en S como lectura consistente, pero aplica bloqueos compartidos en S así se especifica binlogging. InnoDB tiene que aplicar bloqueos de esta manera para recuperar un backup exactamente en la misma manera que fue realizado originalmente.
CREATE TABLE ... SELECT ...- realiza el select como una lectura consistente o con bloqueos compartidos (como el anterior)
REPLACE- es realizado como un insert si es que no hay una colisión en una llave única. De otra manera un bloqueo exclusivo es aplicado en la tupla que será actualizada.
UPDATE ... SET ... WHERE ...- aplica un bloqueo exclusivo en cada registro encontrado.
DELETE FROM ... WHERE ...- aplica un bloqueo exclusivo en cada registro encontrado.
FOREIGN KEYconstraints- si una condición de llave foránea es definida en una tabla, cualquier insert, update o delete que requiera revisar la condición aplica un bloqueo compartido en las tuplas. Aún en las tuplas que no cumplan con la condición.
LOCK TABLES ...- aplica el bloqueo a una tabla.
10.4.7 Deadlock detection y rollback
Por lo general el dbms detecta aquellas transacciones que caen en un deadlock y lo que hacer es solucionarlo haciendo rollback considerando primero aquellas transacciones en las que tenga que "deshacer menos".
10.4.8 Locking and Indexes
Generalmente los bloqueos se hacen en los índices, de manera que hay ciertas variantes entre los distintos tipos.
B-tree indexes
- Bloqueos exclusivos o compartidos para R/W a nivel de página
- Los bloqueos son liberados inmediatamente después de que cada tupla es recuperada o insertada
- Proveen la más alta concurrencia sin condiciones de "deadlock"
GiST and R-tree indexes
- Bloqueos exclusivos o compartidos para R/W a nivel de índice
- Los bloqueos son liberados inmediatamente después de que cada comando es procesado
Hash indexes
- Bloqueos exclusivos o compartidos para R/W a nivel de página
- Los bloqueos son liberados inmediatamente después de que cada página es procesada
- El bloqueo por página es mejor (hablando de concurrencia) que aquellos por nivel de índice pero son más factibles para "deadlocks"
B-Tree índices ofrecen el mejor rendimiento para aplicaciones concurrentes, además de tener más ventajas que los índices hash. Son recomendados para aplicaciones que requieren indexar datos "escalares" en otro caso hay que esta concientes de las limitantes que presentan los otros esquemas.
*Un dato escalar es cualquier número o cadena de caracteres.
10.5 Ejemplos de Transacciones y Concurrencia
Partiendo del esquema siguiente se presentan las acciones de 2 transacciones concurrentes.
mysql> desc bank;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | | PRI | 0 | |
| debit | float | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.08 sec) T1 |
T2 |
mysql> set autocommit=0; mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 999 |
| 64 | 7865 |
+------+-------+
2 rows in set (0.00 sec)
mysql> insert into bank values(66,3453);
Query OK, 1 row affected (0.09 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 999 |
| 64 | 7865 |
| 66 | 3453 |
+------+-------+
3 rows in set (0.00 sec)
mysql> delete from bank where id=64;
Query OK, 1 row affected (0.12 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 999 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.07 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 999 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec) |
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 999 |
| 64 | 7865 |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 999 |
| 64 | 7865 |
+------+-------+
2 rows in set (0.15 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 999 |
| 64 | 7865 |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 999 |
| 64 | 7865 |
+------+-------+
2 rows in set (0.00 sec)
mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 999 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
|
mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from bank where id=32 for update;
+------+-------+
| id | debit |
+------+-------+
| 32 | 999 |
+------+-------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 999 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 999 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 666 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.01 sec)
|
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update bank set debit=666 where id=32;
.
.
.
.
.
.
.Wait unlock
.
.
.
.
mysql> update bank set debit=666 where id=32;
Query OK, 1 row affected (13.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 666 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 666 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.01 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 666 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.01 sec)
|
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank where id=32 lock in share mode;
+------+-------+
| id | debit |
+------+-------+
| 32 | 666 |
+------+-------+
1 row in set (0.00 sec)
mysql> update bank set debit=888 where id=32;
ERROR 1213 (40001): Deadlock found when trying to get lock;
Try restarting transaction
>T1 tiene bloqueado a 32, T2 espera la liberacion
>al hacer el update en T1 ahora T1 espera a T2
>por lo tanto se produce un deadlock
>el dbms hace rollback en T1 (ultimo en espera) y procesa T2
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 666 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 666 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 777 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.01 sec)
|
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update bank set debit=777 where id=32;
.
.
.
.>Wait unlock
.
.
.
.
.
.
.
.
.
.
.
.
.
.
mysql> update bank set debit=777 where id=32;
Query OK, 1 row affected (23.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 777 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 777 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
|
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank where id=32 for update;
+------+-------+
| id | debit |
+------+-------+
| 32 | 777 |
+------+-------+
1 row in set (0.00 sec)
mysql> update bank set debit=111 where id=32;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 111 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update bank set debit=000 where id=32;
Query OK, 1 row affected (6.88 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 0 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 0 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
|
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank where id=32 for update;
.
.
.
.
.
.
.> Wait unlock
.
.
.
.
.
mysql> select * from bank where id=32 for update;
+------+-------+
| id | debit |
+------+-------+
| 32 | 111 |
+------+-------+
1 row in set (32.45 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 111 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 0 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
|
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update bank set debit=333 where id=32;
.
.
.
.
.
.> Wait unlock
.
.
.
.
.
mysql> update bank set debit=333 where id=32;
Query OK, 1 row affected (3.20 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 333 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 333 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
|
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank where id=32 for update;
+------+-------+
| id | debit |
+------+-------+
| 32 | 0 |
+------+-------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 0 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 0 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 333 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
|
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank where id=32 lock in share mode;
+------+-------+
| id | debit |
+------+-------+
| 32 | 333 |
+------+-------+
1 row in set (0.00 sec)
mysql> update bank set debit=444 where id=32;
.
.
.
.
.> Wait unlock
.
.
.
.
.
.
.
mysql> update bank set debit=444 where id=32;
Query OK, 1 row affected (30.83 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 444 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 444 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
|
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank where id=32 lock in share mode;
+------+-------+
| id | debit |
+------+-------+
| 32 | 333 |
+------+-------+
1 row in set (0.00 sec)
mysql> update bank set debit=777 where id=32;
ERROR 1213 (40001): Deadlock found when trying to get lock;
Try restarting transaction
>T2 tiene bloqueado a 32, T1 espera la liberacion
>al hacer el update en T2 ahora T2 espera a T1
>por lo tanto se produce un deadlock
>el dbms hace rollback en T2 (ultimo en espera) y procesa T1
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 333 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+------+-------+
| id | debit |
+------+-------+
| 32 | 444 |
| 66 | 3453 |
+------+-------+
2 rows in set (0.00 sec) |
mysql> set autocommit=0; mysql> select * from bank where id=32 lock in share mode;
+----+-------+
| id | debit |
+----+-------+
| 32 | 444 |
+----+-------+
1 row in set (0.00 sec)
>
> Nadie puede modificar la tupla 32
>
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+----+-------+
| id | debit |
+----+-------+
| 32 | 444 |
| 66 | 3453 |
+----+-------+
2 rows in set (1.02 sec)
mysql> select * from bank;
+----+-------+
| id | debit |
+----+-------+
| 32 | 555 |
| 66 | 3453 |
+----+-------+
2 rows in set (0.01 sec)
|
mysql> set autocommit=0; Query OK, 0 rows affected (0.01 sec)
mysql> update bank set debit=555 where id=32;
.
.
.
.
.> Wait unlock
.
.
.
.
.
.
mysql> update bank set debit=555 where id=32;
Query OK, 1 row affected (13.21 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; Query OK, 0 rows affected (0.14 sec) mysql> select * from bank;
+----+-------+
| id | debit |
+----+-------+
| 32 | 555 |
| 66 | 3453 |
+----+-------+
2 rows in set (0.00 sec)
|