PDF de programación - Parte III. Transacciones - Lenguaje SQL con MySQL avanzado

Lenguaje Sql con MySQL avanzado



Parte III. Transacciones

PARTE III.

TRANSACCIONES



Tema 3. Transacciones en MySQL


3.1 Introducción...........................................................................................2
3.2 Niveles de aislamiento...........................................................................3
3.3 Instrucciones de manejo de transacciones.............................................9
3.4 Transacciones y bloqueos....................................................................13
3.4.1 Deadlock. Cursores de actualización..............................15
3.5 Estrategias de bloqueo.........................................................................19
3.6 Aspectos a tener en cuenta a la hora de utilizar transacciones ............21


Anexo. Manejo de transacciones desde otras aplicaciones



1. Desde PHP.............................................................................................21
2. Desde Visual Basic Express 2005 .........................................................22

Página 1

Lenguaje Sql con MySQL avanzado



Parte III. Transacciones

TEMA 3.
Transacciones en MySQL


Alberto Carrera Martín

3.1 Introducción

TRANSACCIÓN en SQL: Conjunto de instrucciones SQL, agrupadas lógicamente,
que o bien se ejecutan todas sobre la base de datos o bien no se ejecuta ninguna.
Una transferencia bancaria entre dos cuentas supone un ejemplo claro para ilustrar el
concepto de
transferencia se compone de dos operaciones
(instrucciones):

transacción. La

1) Descontar de la libreta origen a transferir la cantidad fijada.
2) Aumentar el saldo de la libreta destino con el importe de la cantidad transferida

de la cuenta origen.


Está claro que la transacción bancaria anterior no se puede quedar “a medias”. O bien se
aplican las dos operaciones lógicas que componen la transacción o bien no se realiza
ninguna por mantener una consistencia contable. Si sólo llegara a realizarse una
operación de las dos anteriores, esta se desharía.

Las transacciones sobre la base de datos deben ajustarse al principio ACID (ATOMIC +
CONSISTENT + ISOLATED + DURABLE), lo que implica que una transacción debe
ser:


- ATÓMICA (ATOMIC): Todas las operaciones que componen la transacción
deben aplicarse sobre la base de datos o no aplicarse ninguna. Una transacción
es indivisible.


- CONSISTENTE (CONSISTENT): Se mantiene la consistencia de los datos

antes y después de ejecutar la transacción.



- AISLADA (ISOLATED): Cuando muchas transacciones se pueden llevar a
cabo simultáneamente por uno o varios usuarios, la realización de una no debe
afectar jamás a las otras y por tanto no llevar a una situación de error.

- PERMANENTE (DURABLE): Una vez que la transacción ha sido confirmada
(COMMIT) y por tanto los cambios de la base de datos guardados, éstos deben
perdurar en el tiempo aun cuando el gestor de base de datos o el propio equipo
fallen.


La capacidad transaccional de MySQL depende del sistema de almacenamiento
empleado. Los dos más importantes son el MyISAM y el InnoDB.

MyISAM: No admite transacciones. Utilizada para aplicaciones que en su mayoría son
de sólo lectura. Si las aplicaciones casi no necesitan el empleo de las transacciones, es
la forma de almacenamiento a utilizar pues el rendimiento es óptimo. Si el número de

Página 2

Lenguaje Sql con MySQL avanzado



Parte III. Transacciones

transacciones y accesos concurrentes para modificar los datos de la base de datos es
importante, entonces es mejor utilizar el siguiente modelo de almacenamiento.

InnoDB: El modelo de almacenamiento de transacción segura de MySQL más popular.
Soporta transacciones ACID así como bloqueos a nivel de fila y concurrencia (a costa
de sacrificar un poco la velocidad de proceso). Es el tipo predeterminado de modelo de
almacenamiento si se ha instalado el gestor de bases de datos utilizando el asistente y
por tanto no se ha especificado a la hora de crear las tablas en la sentencia CREATE
TABLE. Puedes comprobar dicho modelo de almacenamiento con la instrucción SHOW
CREATE TABLE alumnos y ver la columna CREATE TABLE:



Ilustración 1. Almacenamiento InnoDB


3.2 Niveles de aislamiento

Una sesión de base de datos es una conexión única a la base de datos que comienza
cuando se entra en el sistema (login) y termina cuando se desconecta de ella.

Cada vez que abrimos el programa cliente MySQL Query Browser estamos abriendo
una sesión (podemos tener varias instancias de este programa abiertas varias
sesiones). Para averiguar el identificador de conexión que nos asigna el servidor,
consultaremos la función que nos lo proporciona:


Página 3

Lenguaje Sql con MySQL avanzado



Parte III. Transacciones

Ilustración 2. Identificador de conexión I



La sesión termina cuando cerramos el programa MySQL Query Browser.

También en alguna ocasión hemos creado alguna sesión para trabajar con la base de
datos desde la ventana de línea de comandos de Windows:



Ilustración 3 Identificador de conexión II


En el momento de conectarnos de esta manera a MySQL se visualiza el identificador de
conexión (4ª línea en la ilustración 3) que éste nos asigna. La conexión dura hasta que la
abandonemos (comando EXIT).

Cada sesión trabaja en su propia zona de memoria pudiendo llegar incluso a bloquear
los datos de la base de datos con los que trabaja.
Los niveles de aislamiento determinan la manera en que las transacciones de una sesión
pueden afectar a los datos recuperados o accedidos por otra sesión. Hay por tanto dos
conceptos interrelacionados: por una lado la concurrencia (varios sesiones realizando
transacciones al mismo tiempo) y por otro el grado de consistencia de los datos.
Determinan también el grado en que las transacciones se ajustan al principio ACID.
Cuanto mayor es el grado de aislamiento, menor es el número de transacciones que se
pueden realizar concurrentemente pero también es menor la posibilidad de que
interfieran las transacciones. Por otro lado, cuanto menor es el grado de aislamiento,
mayor es el número de transacciones que se pueden realizar concurrentemente pero el
riesgo de conflicto entre transacciones es elevado.


Página 4

Lenguaje Sql con MySQL avanzado



Parte III. Transacciones

El estándar ANSI define 4 modelos de aislamiento, soportados todos ellos por el
sistema de almacenamiento InnoDB de MySQL:


- Lectura no confirmada (también conocido como lectura sucia): Es el nivel más
bajo. Permite que una transacción pueda leer filas que todavía no han sido
confirmadas (COMMIT). El hecho de que utilizando este nivel se aumente el
rendimiento del proceso no justifica que pueda permitirse que un usuario lea
datos modificados por otro usuario que todavía puede deshacer dichas
modificaciones.


- Lectura confirmada. Sólo se permite lectura de datos que han sido
confirmados. Si un usuario está ejecutando un procedimiento que recorre las
filas recuperadas de una tabla mediante una sentencia SELECT, si otro usuario
en ese momento realiza una modificación de la tabla, el primero no verá las
modificaciones realizadas por este último.



- Lectura repetible. Es el nivel por defecto. Las lecturas repetidas de la misma
fila por la misma transacción dan los mismos resultados. Ningún cambio hecho
en la base de datos por otros usuarios será visto por la transacción lanzada hasta
que esta se confirme o deshaga, es decir, si se repite dentro de una transacción
una instrucción SELECT, esta devolverá siempre los mismos resultados
(excepto cuando dentro de la misma transacción pudieran realizarse cambios en
esa filas recuperadas por la SELECT).


- Secuenciable. Mayor nivel de aislamiento. Las transacciones se aíslan
completamente dando la impresión de que se ejecutan secuencialmente, una
detrás de otra. Para conseguir esto, los sistemas gestores de bases de datos
bloquean cada fila leída para que otras sesiones no puedan modificar estos datos
hasta que la transacción finalice. El bloqueo dura hasta que la transacción es
confirmada o deshecha. Este nivel disminuye mucho el rendimiento del sistema
y puede provocar situaciones de “abrazo mortal” como se verá más adelante.

El nivel de aislamiento por defecto se podría cambiar (no aconsejable):

SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ
COMMITTED | REPEATABLE READ | SERIALIZABLE}

Los siguientes ejemplos nos ayudarán a comprender el manejo de transacciones:

Nota: Recordar que se mantiene siempre la consistencia en lectura Los datos que ven
las transacciones son los últimos que se validaron.

Antes de comenzar estos ejemplos ejecuta el procedimiento1 ya
visto en temas anteriores y que crea la tabla alumnos con 5 alumnos:

Abre dos sesiones nuevas de cliente de línea de comandos de
MySQL (lo puedes hacer mediante la opción de menú Tools /
MySQL Command Line Client).

Ilustración 4. Tabla alumnos

Página 5

Lenguaje Sql con MySQL avanzado



Parte III. Transacciones

En este momento tendrás 3 sesiones abiertas, una del programa MySQL Query Browser
y dos clientes de línea de comando de MySQL (ilustración 5). Puedes comprobar el
identificador de cada sesión con
(SELECT
CONNECTION_ID();):


función vista anteriormente

la



Ilustración 5. Sesiones abiertas en la barra de tareas

Utilizaremos para la práctica las dos sesiones de clientes de línea de comando MySQL.

Como los números de identificación de conexión serán distintos