linuXCeros

Transacciones con MySQL 5.0.51b

Septiembre 4th, 2009. Publicado bajo Bases de Datos, MySQL. 4 Comments.

Escrito por Teb@n, 23 artículos en el Blog

El concepto de “bases de datos transaccionales” es un sinónimo de fiabilidad superior a las bases de datos comunes. Existen situaciones en las que necesitamos hacer más de una consulta al mismo tiempo, y todas tienen que ser correctas para que los datos sean consistentes y tengan sentido, de otro modo tendríamos información “corrupta”, a este grupo de sentencias se les llama transacciones.

Una transacción en un sistema de gestión de bases de datos (SGBD), es un conjunto de órdenes que se ejecutan formando una unidad de trabajo, es decir, en forma indivisible o atómica. Un SGBD se dice transaccional si es capaz de mantener la integridad de los datos, haciendo que estas transacciones no puedan finalizar en un estado intermedio. Cuando por alguna causa el sistema debe cancelar la transacción, empieza a deshacer las órdenes ejecutadas hasta dejar la base de datos en su estado inicial (llamado punto de integridad), como si la orden de la transacción nunca se hubiese realizado.

Para esto, el lenguaje de consulta de datos SQL (Structured Query Language), provee los mecanismos para especificar que un conjunto de acciones deben constituir una transacción.

• BEGIN TRAN: Especifica que va a empezar una transacción.

• COMMIT TRAN: Le indica al motor que puede considerar la transacción completada con éxito.

• ROLLBACK TRAN: Indica que se ha alcanzado un fallo y que debe restablecer la base al punto de integridad.

En un principio MySQL no las soportaba, esto cambió a partir de la versión 4, y con el uso de tablas InnoDB. Cabe mencionar que el motor por defecto para las tablas en MySQL hasta las versiones 5, era MyISAM, que no soporta transacciones. Las transacciones son ampliamente utilizadas en sistemas bancarios por mencionar solo un ejemplo.

A grandes rasgos, la idea es que al momento de que estamos ejecutando por ejemplo una sentencia UPDATE, esta sentencia no tenga ningún efecto hasta que nosotros lo indiquemos con COMMIT.

Por ejemplo en un sistema bancario, la base de datos, con una tabla cuentas para almacenar los datos de todas las cuentas de los clientes (el número de cuenta, el saldo, etc.). Cada vez que un cliente hace una transacción, el sistema transfiere determinada cantidad de dinero de una cuenta X a una cuenta Y, de esta manera:

• Hace un UPDATE de la cuenta X restándole el dinero a transferir

• Hace un UPDATE de la cuenta Y sumándole el dinero transferido

A simple vista parece simplemente perfecto, pero ¿qué pasaría si justo después del primer UPDATE ocurriera algún fallo y se detuviera la ejecución? Entonces habríamos restado el dinero de la cuenta X sin habérselo sumado a la cuenta Y. ¿cómo lo solucionamos? necesitamos usar transacciones.

Una transacción funciona de la siguiente manera, ejecuta las sentencias (siguiendo con el ejemplo anterior los UPDATE para las cuentas X y Y) y si llegara a haber un error, por medio de journaling restablece la base de datos tal como estaba antes de iniciar la transacción, de esta manera se asegura que las sentencias se ejecuten todas o no se ejecute ninguna.

¿Qué es journaling?

Es un mecanismo por el cual por cada sentencia que se ejecute, se crea una sentencia para restablecer los cambios realizados por esa sentencia. Por ejemplo, si ejecutáramos un INSERT entonces se crearía una sentencia DELETE que eliminaría el registro recién creado en caso de que la transacción falle, y de esa manera restablecer todo como estaba al inicio de la transacción. En este articulo vamos a explicar y demostrar el funcionamiento de las transacciones con ejemplos sencillos y prácticos.

¿Cómo utilizar transacciones en InnoDB con distintas APIs?

En forma predeterminada, cada cliente se que conecta al servidor MySQL comienza con el modo de AUTOCOMMIT habilitado, lo cual automáticamente confirma (COMMIT) cada sentencia SQL ejecutada. Para utilizar transacciones de múltiples sentencias se puede deshabilitar el modo AUTOCOMMIT con la sentencia SQL SET AUTOCOMMIT = 0 y emplear COMMIT y ROLLBACK para confirmar o cancelar la transacción. Si se desea dejar activado AUTOCOMMIT, se pueden encerrar las transacciones entre las sentencias START TRANSACTION y COMMIT o ROLLBACK.

En APIs como PHP, Perl DBI/DBD, JDBC, ODBC, o la interfase de llamadas C estándar de MySQL, se pueden enviar sentencias de control de transacciones como COMMIT al servidor MySQL en forma de cadenas, igual que otras sentencias SQL como SELECT o INSERT. Algunas APIs también ofrecen funciones o métodos especiales para confirmación y cancelación de transacciones.

EJEMPLOS DE TRANSACCIONES Y LECTURAS CONSISTENTES.

Tenemos que montar un escenario donde explicaremos el manejo de transacciones así como el bloqueo de lectura y escritura. Haremos uso de MySQL 5.0.51b, en el cual vamos a crear dos conexiones cada una con un usuario distinto para hacer mas transparente la explicación. Se asume que estos dos usuarios disponen de todos los privilegios para poder realizar todas las operaciones citadas en este articulo.

Para crear los usuarios y asignarles permisos, usted tiene que escribir lo siguiente:

Usuario CELSO:

• CREATE USER CELSO@’LOCALHOST’ IDENTIFIED BY ‘CELSO’;

• GRANT ALL PRIVILEGES ON *. * TO ‘CELSO’@'LOCALHOST’ WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

Usuario ALEX:

• CREATE USER ALEX@’LOCALHOST’ IDENTIFIED BY ‘ALEX’;

• GRANT ALL PRIVILEGES ON *. * TO ‘ALEX’@'LOCALHOST’ WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

Esto debe de hacerse entrando con el usuario ROOT. A continuación se muestra la imagen de cómo quedan los usuarios.

Ahora bien procedemos a crear las dos conexiones, para ello vamos a la línea de comandos de Windows, accedemos al directorio donde se encuentra MySQL y entramos con el usuario correspondiente.

Conexión 1: > mysql -u CELSO -p “CELSO”

Conexión 2: > mysql -u ALEX -p “ALEX”

Para este caso vamos a usar las tablas InnoDB, debido a que estas tablas son transaccionales, para comprobar que MySQL tiene soporte para este tipo de tablas y como consecuencia para las transacciones, podemos escribir lo siguiente:

SHOW VARIABLES LIKE ‘%innodb%’;

Como podemos ver en la imagen anterior nos muestra todas las variables que tienen que ver con las tablas InnoDB, la que mas nos interesa es HAVE_INNODB=YES, por lo tanto decimos que MySQL si soporta las transacciones. Para comprender como trabajan las transacciones empezaremos por la creación de una tabla de tipo InnoDB, tenga en cuenta que necesitara crear una base de datos donde va a crear esa tabla, en este caso la base de datos se llamara “PRACTRAN”, para crear la tabla escriba lo siguiente:

CREATE TABLE DATOS (CLAVE INT(10) NOT NULL PRIMARY KEY) TYPE = InnoDB;

Es importante mencionar que esta operación la podemos realizar en cualquiera de las dos conexiones que tenemos, en la figura de arriba se puede apreciar que la tabla se creo correctamente, también se puede observar la descripción de su estructura.

Otro factor que es importante señalar es la definición del tipo de tabla, que se le especifico, de no ser así, MySQL pondrá por defecto una tabla de tipo MyISAM. Después de crear la tabla, procedemos a introducir algunos registros en la tabla con las instrucciones siguientes:

INSERT INTO DATOS VALUES (1000);

INSERT INTO DATOS VALUES (1001);

INSERT INTO DATOS VALUES (1002);

Hasta este punto todavía no hemos explicado nada acerca de cómo funcionan las transacciones, a partir de aquí hemos pues de describir claramente el funcionamiento de las transacciones en MySQL. Para ello empezaremos hacer uso de las Conexiones que fueron creadas, primero en la Conexión 1, debemos de empezar una transacción, para ello debemos de escribir lo siguiente:

BEGIN; ó START TRANSACTION;

No importa cual escriba, con esto estará declarando el inicio de una transacción, posteriormente escriba cualquier consulta, en este caso vamos a empezar por insertar un registro, así como la actualización de otro registro y la eliminación de otro.

INSERT INTO DATOS VALUES (1003);

UPDATE DATOS SET CLAVE=3333 WHERE CLAVE=1003;

DELETE FROM DATOS WHERE CLAVE=1001;

En la imagen de arriba se puede observar el comportamiento de la transacción, cabe señalar que se hicieron tres operaciones (insertar, actualizar y eliminar) y como resultado hacemos una selección la cual nos muestra los cambios hechos en dicha tabla.

Lo interesante aquí es que podemos deshacer los cambios aplicando un ROLLBACK o confirmar dichos cambios con la sentencia COMMIT, para este ejemplo vamos a deshacer los cambios, para ello escribimos ROLLBACK en la línea de comandos:

Al deshacer los cambios, en la tabla no se guardaron los cambios y regreso a su estado original. Ahora que pasa si al estar ejecutando una transacción perdemos la conexión del servidor, para ello vamos ejecutar otra consulta, y después de ello, vamos a cerrar la conexión lo que provocará que la transacción no finalice con un COMMIT.

Al cerrarse la conexión e iniciar otra nueva conexión vamos a verificar los registros de la tabla, obteniendo un resultado como el que se muestra a continuación:

Debido a que no se realizo un COMMIT, la transacción no finalizo, por lo cual los cambios no fueron aplicados, resultando los registros originales. Ahora vamos a repetir la transacción, pero esta vez, antes de perder la conexión vamos hacer un COMMIT, para que se guarden los cambios.

Ahora iniciamos la conexión nuevamente y comprobamos que los cambios si fueron hechos a comparación con el caso anterior.

Cabe señalar que este resultado fue gracias a que antes de perder la conexión se realizó un COMMIT, por lo que al perder la conexión los cambios ya habían sido aplicados, y es por eso que los resultados no fueron alterados debido a la perdida de la conexión.

Lecturas consistentes:

Por defecto, las tablas InnoDB ejecutan una lectura consistente (consistent read). Esto significa que cuando una sentencia SELECT es ejecutada, MySQL regresa los valores presentes en la base de datos hasta la transacción más reciente que ha sido completada. Si alguna transacción está en progreso, los cambios hechos por alguna sentencia INSERT o UPDATE no serán reflejados.

Sin embargo, existe una excepción: las transacciones abiertas si pueden ver sus propios cambios. Para demostrar esto, vamos hacer uso de las conexiones que se plantearon al inicio de este articulo.

Conexión 1:

En esta conexión vamos a empezar una transacción y a ingresar un registro, ó cualquier otra operación que afecte a los registros.

Conexión 2:

Ahora en la otra conexión vamos a ejecutar una consulta para verificar los cambios que fueron hechos en la conexión 1.

Como podemos observar, el registro que hemos insertado desde la conexión 1 no es regresado puesto que forma parte de una transacción que no ha sido completada. Ahora, desde la conexión 1 tecleamos COMMIT.

Ahora vamos a la conexión 2 y volvemos a ejecutar la consulta, lo cual nos arroja lo siguiente:

Este ejemplo fue hecho basándose en la sentencia INSERT, pero también funciona con las sentencias UPDATE, DELETE, etc.

Espero que les sea útil esta información…

Esperen el próximo post: Niveles de aislamiento, lectura y bloqueo.

Saludos!

No de olviden de comentar!