• Author / Uploaded
• Freddo Rasmussen

• Categories
• Ingeniería Informática
• Bases de datos
• Informática
• Tecnología
• Gestión de datos

Citation preview

Ing. Alfredo Monterrubio Bastida

Transacciones en MySQl Orientado a la arquitectura 4 capas.

Contenido

Introducción En este documento se presenta información recabada de distintas fuentes para ayudar al lector a comprender y utilizar las transacciones y el control ce concurrencia en el gestor de base de datos de MySQL. En un principio con los conceptos básicos redactados en palabras comunes para facilitar la comprensión y finalmente el código con los comandos para configurar MySQL para las transacciones y control de concurrencia.

¿Qué es una transacción? Una transacción en base de datos es una operación de un conjunto, en donde este conjunto es atomico, osea que se realiza un todo o nada, esto para evitar conflictos y salvaguardar la información si en el proceso existe un problema como puede ser de hardware, software o incluso de ataques de terceras personas. Una transacción se comienza con una instrucción on begin transaction (no es necesario en algunos DBMS). La instrucción on commit termina la transacción en forma exitosa y hace permanente cualquier cambio realizado a la BD durante la transacción Los cambios se hacen permanentes solo después de un commit La instrucción rollback aborta la transacción y la hace terminar en forma no exitosa, cualquier cambio que la transacción pudo hacer a la BD se deshace. En general se puede hacer rollback para cualquier conjunto de instrucciones no necesariamente dentro de una transacción

Transferencia de fondos1:

1.begin transaction 2. Si A1 no tiene suficiente dinero⇒rollback 3. Se aumenta el saldo de A2 en el monto especificado. 4. Se disminuye el saldo de A1 en el monto especificado. 5. commit 1 DÍAZ, P. M. (s.f.). Arquitectura 4 capas . En P. M. DÍAZ. Rojas, J. P. (s.f.). Campus curico.utalca.cl. Recuperado el 08 de agosto de 2013, de http://campuscurico.utalca.cl/~jperez/bd/documentos/transacciones.pdf

Propiedades de las transacciones Atomicidad Es la propiedad de las transacciones donde se observan como un todo y se ejecutan de la misma manera o todo o nada Casos a considerar: - Consultas unitarias. Incluso para consultas unitarias hay que preservar la atomicidad: en un Sistema operativo de tiempo compartido, la ejecución concurrente de dos consultas SQL puede ser incorrecta si no se toman las precauciones adecuadas. - Operación abortada. Por ejemplo, debido a una división por cero; por privilegios de acceso; o para evitar bloqueos

Consistencia La ejecución aislada de la transacción conserva la consistencia de la base de datos.

Aislamiento Para cada par de transacciones que puedan ejecutarse concurrentemente Ti y Tj, se cumple que

para los efectos de Ti: - Tj ha terminado antes de que comience Ti - Tj ha comenzado después de que termine Ti Las transacciones son independientes entre sí

Niveles de aislamiento Se puede ajustar el nivel de aislamiento entre las transacciones y determinar para una transacción el grado de aceptación de datos inconsistentes. A mayor grado de aislamiento, mayor precisión, pero a costa de menor concurrencia. El nivel de aislamiento para una sesión SQL establece el comportamiento de los bloqueos para las instrucciones SQL. Niveles de aislamiento: •

Lectura no comprometida. Menor nivel. Asegura que no se lean datos corruptos físicamente.

•

Lectura comprometida. Sólo se permiten lecturas de datos comprometidos.

•

Lectura repetible. Las lecturas repetidas de la misma fila para la misma transacción dan los mismos resultados.

•

Secuenciable. Mayor nivel de aislamiento. Las transacciones se aíslan completamente. Comportamiento concurrente de las transacciones.

•

Lectura sucia. Lectura de datos no comprometidos. (Retrocesos)

•

Lectura no repetible. Se obtienen resultados inconsistentes en lecturas repetidas.

•

Lectura fantasma. Una lectura de una fila que no existía cuando se inició la transacción.

Durabilidad El sistema gestor de bases de datos asegura que perduren los cambios realizados por una transacción que termina con éxito

Estados de una transacción Activa: Cuando se ejecuta la transacción Parcialmente comprometida: aun sin completar.

Fallida: no se realizó la transaccion Abortada: se abortó la transacción por alguna razón y la base de datos regresa a su estado anterior Ejecución. Se puede reiniciar o cancelar

Ejemplo de código para transacciones en SQL Server2 (Rojas): Incremento de un 1% de las comisiones 15% y 16% de la tabla de comisiones roysched . Si no existen estos porcentajes entonces no se ejecutará la instrucción de actualización. En este ejemplo se deben incrementar ambos; si uno de ellos no existe, se debe dejar sin modificar. BEGIN TRAN actualiza_comisiones -- Inicio de la transacción USE pubs IF EXISTS (SELECT titles.title, roysched.royalty FROM titles, roysched WHERE titles.title_id=roysched.title_id AND roysched.royalty=16) UPDATE roysched SET royalty=17 WHERE royalty=16 ELSE ROLLBACK TRAN actualiza_comisiones IF EXISTS (SELECT titles.title, roysched.royalty FROM titles, roysched WHERE titles.title_id=roysched.title_id AND roysched.royalty=15) BEGIN

UPDATE roysched SET royalty=16 WHERE royalty=15 COMMIT TRAN actualiza_comisiones END ELSE ROLLBACK TRAN actualiza_comisiones

2 DÍAZ, P. M. (s.f.). Arquitectura 4 capas . En P. M. DÍAZ. Rojas, J. P. (s.f.). Campus curico.utalca.cl. Recuperado el 08 de agosto de 2013, de http://campuscurico.utalca.cl/~jperez/bd/documentos/transacciones.pdf

Control de concurrencia Forma en que el DBMS maneja las ejecuciones paralelas en la BD Principalmente dos enfoques: •

Optimista: supone que los conflictos son escaso ⇒permitir acceso concurrente y deshacer las acciones problemáticas.

•

Pesimista: asume que es muy probable que ocurran problemas ⇒ actúa a la defensiva impidiendo la aparición de conflictos usando locks

Un lock es una estructura que solo puede ser adquirida por una hebra de ejecución (thread) a la vez. Si dos ejecuciones tratan de obtener un lock para actualizar una tabla, la primera que trate de obtenerlo tendrá acceso exclusivo a la tabla, la segunda debe esperar a que la primera lo suelte para obtener el acceso. Los locks pueden tener distintas granularidades: Base de Datos, Tabla, Tupla, Atributo. Además de los locks exclusivos existen locks de solo lectura o locks compartidos que pueden estar simultáneamente siendo utilizados por distintas ejecuciones.

Transacciones en MySQL (comandos) Un ejemplo Supongamos que un sitio web bancario tiene 2 usuarios, ambos trabajando sobre la misma cuenta. El usuario 1 pide incrementar su saldo en 10, mientras que el usuario 2 pide disminuirlo (a través de un formulario, por ejemplo) El programador del sistema no puede decidir el orden en el que se ejecutarán las consultas, así que bien podría suceder lo siguiente: bal1 := ... SELECT balance FROM cuentas WHERE cuenta=X -- usuario 1 bal2 := ... SELECT balance FROM cuentas WHERE cuenta=X -- usuario 2

En este punto, existen dos copias de la aplicación que contienen una variable $balance cada una. Supongamos que ambas necesitan actualizar el valor en la base de datos: UPDATE cuentas SET balance=(bal1+10) WHERE cuenta=X -- usuario 1 UPDATE cuentas SET balance=(bal2-10) WHERE cuenta=X -- usuario 2

El resultado es que ambas copias del programa ejecutaron sus consultas con la información de balance que tenían, por lo que el resultado final es como si la consulta del usuario 1 no se hubiera ejecutado nunca, ya que el usuario 2 actualiza el registro con información vieja. Al final, en vez de quedar con el mismo saldo, la cuenta termina perdiendo 10. Lo que se necesita para este conjunto de consultas, es lo que se denomina ACID, un acrónimo inglés que quiere decir Atomicidad, Consistencia, Aislamiento y Durabilidad. Recomiendo leer la información de Wikipedia para entender de qué se trata esto, pero lo importante es lo siguiente: las transacciones son un conjunto de consultas que se ejecutan como si fuesen una. Y por esto, permiten asegurar la consistencia de los datos, ya que si en mitad del proceso una consulta falla, todos los cambios producidos por consultas anteriores pueden ser revertidos.

¿Cómo usar transacciones? Usar transacciones es muy simple: antes de ejecutar la primer consulta, se ejecuta una que solamente contiene BEGIN. Luego se ejecutan las consultas que deban ejecutarse. Si éstas resultan exitosas, se termina la transacción con COMMIT, lo cual provoca que los cambios hechos por las consultas anteriores sean permanentes. Si las consultas fallan en algún paso, se puede volver al estado anterior al comienzo de la transacción ejecutando ROLLBACK Aunque los datos no sean realmente escritos a la o las tablas involucradas hasta ejecutar el COMMIT, las consultas devuelven lo mismo que si lo fueran, es decir, para saber si una consulta falló basta con ver el valor de retorno de mysql_query y para ver el número de filas afectadas sigue valiendo usar mysql_num_rows Mientras la transacción está ejecutándose, los datos (en el caso de InnoDB las filas y en el caso de MyISAM las tablas) afectados quedan bloqueados, nadie puede acceder a ellos. Cualquier consulta que tenga que ver con los mismos datos será demorada hasta que la transacción termine. Esto implica que usar transacciones es un poco más lento que no usarlas, pero a la vez implica que los datos involucrados no pueden ser modificados por otra copia de la aplicación, y por lo tanto se evita la situación planteada al principio como ejemplo.

Ejemplo de una transacción desde un lenguaje de programación (PHP) usando condiciones para que según sea la situación se ejecute la transacción o se aborte y la base de datos no sufra ningún cambio. Dicha situación, implementada de forma “transaccional” en PHP, quedaría: 1