MYSQL

transacciones y control de concurrencia

MY SQL

Cruz Delgado Adriel Joaquin

Transacción

• Si ejecuta una consulta sin mencionar la palabra clave BEGIN TRAN, se consideraría una transición implícita .
• Si ejecuta una consulta que comienza con BEGIN TRAN y termina con COMMIT o ROLLBACK, se consideraría una transacción explícita .

"Toda consulta que se ejecuta en un servidor SQL está en una transacción", lo que significa que cualquier consulta que ejecute en un servidor SQL se considera parte de una transacción. Podría ser una simple consulta SELECT o cualquier consulta UPDATE o ALTER.

Propiedades de las Transacciones

CONSISTENCY (consistencia/integridad): una transacción no puede dejar la base de datos en un estado inconsistente. Es decir, debe asegurar que no se rompe ninguna regla de integridad llevando a la bbdd de un estado válido a otro.

ATOMICITY (atomicidad): todo en una transacción tiene lugar o esta es revertida. O dicho de otro modo, o tiene lugar todo o nada.

DURABILITY (persistencia): una transacción completada debe persistir aún cuando el servidor se reinicie. Es decir, el resultado de la transacción debe almacenarse en algún tipo de almacenamiento persistente.

ISOLATION (aislamiento): una transacción no puede afectar o interferir con otras. Esto asegura que cuando dos transacciones tenga lugar sobre la misma información estas sean independientes.

Control de transacciones

ROLLBACK : para revertir los cambios

COMMIT − para guardar los cambios.

SET TRANSACTION : coloca un nombre en una transacción

SAVEPOINT : crea puntos dentro de los grupos de transacciones en los que ROLLBACK

COMMIT

El comando COMMIT es el comando transaccional que se utiliza para guardar los cambios invocados por una transacción en la base de datos. El comando COMMIT es el comando transaccional que se utiliza para guardar los cambios invocados por una transacción en la base de datos. El comando COMMIT guarda todas las transacciones en la base de datos desde el último comando COMMIT o ROLLBACK. La sintaxis del comando COMMIT es la siguiente. COMMIT;

Por lo tanto, se eliminarían dos filas de la tabla y la instrucción SELECT produciría el siguiente resultado.

Ejemplo Considere la tabla CLIENTES que tiene los siguientes registros:

El siguiente es un ejemplo que eliminaría los registros de la tabla que tienen edad = 25 y luego COMMIT los cambios en la base de datos.

ROLLBACK

El comando ROLLBACK es el comando transaccional que se utiliza para deshacer transacciones que aún no se han guardado en la base de datos. Este comando solo se puede usar para deshacer transacciones desde que se emitió el último comando COMMIT o ROLLBACK. La sintaxis para un comando ROLLBACK es la siguiente: ROLLBACK; Ejemplo Considere la tabla CLIENTES que tiene los siguientes registros:

Por lo tanto, la operación de eliminación no afectaría a la tabla y la instrucción SELECT produciría el siguiente resultado.

El siguiente es un ejemplo, que eliminaría los registros de la tabla que tienen la edad = 25 y luego ROLLBACK los cambios en la base de datos.

SAVEPOINT

Un SAVEPOINT es un punto en una transacción en el que puede revertir la transacción hasta cierto punto sin revertir toda la transacción.La sintaxis para un comando SAVEPOINT es como se muestra a continuación. SAVEPOINT SAVEPOINT_NAME; Este comando sirve solo en la creación de un SAVEPOINT entre todas las declaraciones transaccionales. El comando ROLLBACK se utiliza para deshacer un grupo de transacciones. La sintaxis para retroceder a un SAVEPOINT es la que se muestra a continuación. ROLLBACK TO SAVEPOINT_NAME; El siguiente es un ejemplo en el que planea eliminar los tres registros diferentes de la tabla CLIENTES. Desea crear un SAVEPOINT antes de cada eliminación, de modo que pueda ROLLBACK a cualquier SAVEPOINT en cualquier momento para devolver los datos apropiados a su estado original. Ejemplo Considere la tabla CLIENTES que tiene los siguientes registros.

El siguiente bloque de código contiene la serie de operaciones.

Ahora que se han realizado las tres eliminaciones, supongamos que cambió de opinión y decidió VOLVER al SAVEPOINT que identificó como SP2. Debido a que SP2 se creó después de la primera eliminación, las dos últimas eliminaciones se deshacen:

Tenga en cuenta que solo se realizó la primera eliminación desde que revirtió a SP2.

SET TRANSACTION

El comando SET TRANSACTION se puede utilizar para iniciar una transacción de base de datos. Este comando se utiliza para especificar las características de la transacción que sigue. Por ejemplo, puede especificar que una transacción sea de solo lectura o de lectura y escritura. La sintaxis para un comando SET TRANSACTION es la siguiente. SET TRANSACTION [ READ WRITE | READ ONLY ];

Concurrencia

PROBLEMAS

La concurrencia es una situación que surge en una base de datos debido al proceso de transacción. La concurrencia ocurre cuando dos o más de dos usuarios intentan acceder a los mismos datos o información. La concurrencia de un sistema de gestión de base de datos se considera un problema porque el acceso simultáneo a los datos por parte de dos usuarios diferentes puede generar resultados inconsistentes o un comportamiento no válido.

El problema de concurrencia surge principalmente cuando ambos usuarios intentan escribir los mismos datos, o cuando uno escribe y el otro lee. Además de esta lógica, existen algunos tipos comunes de problemas de concurrencia: Lecturas sucias Actualizaciones perdidas Lecturas no repetibles Lecturas fantasma

Actualización perdida y escritura sucia

Este fenómeno ocurre cuando dos transacciones acceden al mismo registro y ambas actualizan este registro. La siguiente figura resume lo que podría suceder en un ejemplo simple. En este ejemplo, tenemos 2 transacciones concurrentes que acceden a un registro con un valor modificable (60) . Este registro se identifica por su ID de fila o por una columna de clave principal que no se presentará aquí por simplicidad. La primera transacción lee este registro, realiza algún procesamiento, luego actualiza este registro y finalmente confirma su trabajo. La segunda transacción lee el registro, luego lo actualiza inmediatamente y lo confirma. Ambas transacciones no actualizan este registro al mismo valor. Esto conduce a una pérdida de la instrucción de actualización realizada por la segunda transacción.Como la Transacción 1 sobrescribe un valor que la Transacción 2 ya modificó. Podríamos haber dicho que la Transacción 1 hizo una "escritura sucia" si la Transacción 2 no comprometió su trabajo.

lectura sucia

Una lectura sucia ocurre cuando una transacción accede a datos que han sido modificados por otra transacción, pero este cambio aún no se ha confirmado ni revertido. La siguiente figura muestra un caso de lectura sucia. En este ejemplo, el registro tiene dos columnas con un valor inicial de (60,40). En este contexto, digamos que tenemos una restricción aplicativa que dice que la suma de esos valores siempre debe ser 100. .

Lectura no repetible o lectura difusa

La situación de lectura no repetible es casi la misma que la lectura sucia, excepto que se modifican ambos valores. Como en las subsecciones anteriores, revisaremos una representación gráfica de una situación de lectura no repetible. Para ello, mantendremos dos transacciones concurrentes accediendo a dos columnas del mismo registro. Uno de ellos lee y modifica cada valor, uno a la vez, luego confirma mientras que el otro lee el primer valor, realiza algún procesamiento, lee el segundo valor y luego confirma. Manteniendo nuestra restricción del ejemplo anterior (la suma de ambos valores es igual a 100), la situación presentada lleva a la segunda transacción a manipular datos inconsistentes y tal vez a presentarlos a un usuario final. .

lecturas fantasma

Las lecturas fantasma son una variación de las lecturas no repetibles en el contexto de los conjuntos de filas. Aquí hay un ejemplo que ilustra esto: Digamos que tenemos una transacción Transacción 1 que realiza dos veces una consulta SELECT en una tabla T , una al principio y otra justo antes de su final. Supongamos que otra transacción, la Transacción 2, comienza después de la primera, inserta una nueva fila en la tabla T y se confirma antes de la segunda vez que la Transacción 1 ejecutará su consulta SELECT . Los conjuntos de resultados que devolverán las dos instancias de la consulta SELECT serán diferentes. .

Resolver problemas de concurrencia

Cuando nos conectamos a una base de datos del servidor SQL, la aplicación puede enviar consultas a la base de datos con uno de los cinco niveles de aislamiento diferentes. Estos niveles son: Leer sin confirmar Lectura comprometida Lectura repetible Serializable Instantánea De estos cinco niveles de aislamiento, Lectura no confirmada, Lectura confirmada, Lectura repetible y Serializable pertenecen al modelo de concurrencia pesimista . Snapshot viene bajo el modelo de concurrencia optimista . Estos niveles están ordenados en función de la separación del trabajo por dos procesos diferentes, desde la separación mínima hasta la máxima.

SQL Server proporciona 5 niveles diferentes de aislamiento de transacciones para superar estos problemas de concurrencia. Estos 5 niveles de aislamiento funcionan en dos modelos principales de concurrencia: Modelo pesimista: en el modelo pesimista de gestión del acceso a datos simultáneos, los lectores pueden bloquear a los escritores y los escritores pueden bloquear a los lectores. Modelo optimista: en el modelo optimista de gestión del acceso a datos simultáneos, los lectores no pueden bloquear a los escritores y los escritores no pueden bloquear a los lectores, pero el escritor puede bloquear a otro escritor. Tenga en cuenta que los lectores son usuarios que realizan las operaciones SELECT. Los escritores son usuarios que realizan operaciones INSERT, ALTER, UPDATE, SET.

LEER SIN CORFIRMAR Este es el primer nivel de aislamiento y se encuentra bajo el modelo pesimista de concurrencia. En Read Uncommitted, se permite que una transacción lea los datos que están a punto de ser modificados por la confirmación de otro proceso. Read Uncommitted permite el problema de lectura sucia.

LECTURA COMPROMETIDA Este es el segundo nivel de aislamiento y también cae bajo el modelo pesimista de concurrencia. En el nivel de aislamiento de lectura confirmada, solo se nos permite leer datos confirmados, lo que significa que este nivel elimina el problema de lectura sucia. En este nivel, si está leyendo datos, las transacciones concurrentes que pueden eliminar o escribir datos, algunos trabajos se bloquean hasta que otros trabajos se completan.

LECTURA REPETIBLE El nivel de aislamiento de lectura repetible es similar al nivel de lectura confirmada y elimina el problema de lectura no repetible. En este nivel, la transacción tiene que esperar hasta que se complete la consulta de actualización o lectura de otra transacción. Pero si hay una transacción de inserción, no espera a nadie. Esto puede conducir al problema de Phantom Read.

SERIALIZABLEEste es el nivel más alto de aislamiento en el modelo pesimista. Al implementar este nivel de aislamiento, podemos prevenir el problema de Phantom Read. En este nivel de aislamiento, podemos pedirle a cualquier transacción que espere hasta que se complete la transacción actual.

INSTANTÁNEALa instantánea sigue el modelo optimista de concurrencia, y este nivel de aislamiento toma una instantánea de los datos actuales y la usa como una copia para las diferentes transacciones. Aquí cada transacción tiene su copia de datos, por lo que si un usuario intenta realizar una transacción como una actualización o inserción, le pide que vuelva a verificar toda la operación antes de que el proceso comience a ejecutarse.