Transacciones en MySQl
ContenidoIntroduccin3Qu es una transaccin?414Propiedades de las
transacciones5Estados de una transaccin6Ejemplo de cdigo para
transacciones en SQL Server2727Control de
concurrencia8Transacciones en MySQL (comandos)8Unejemplo8Cmo
usartransacciones?9Ejemplo de una transaccin desde un lenguaje de
programacin (PHP) usando condiciones para que segn sea la situacin
se ejecute la transaccin o se aborte y la base de datos no sufra
ningn cambio.10MySQL10autocommit10Ejemplo de transaccin en la
consola de Servidor MySQL versin 5.511Arquitectura 4 capas para el
procesamiento de consultas312313Uso de transacciones para realizar
consultas y crear reportes del proyecto de Casa de Materiales
Lomas.14Entrar a la consola de MySQL, listar y seleccionar la base
de datos.14Configurar la tabla requerida para la consulta como
InnoDB15Iniciar transaccin, insertar un registro y consultar la
informacin, abortar la transaccin al notar un error.16Transaccin
sin ejecutar1718Transaccin ejecutada18Bibliografa18
Introduccin
En este documento se presenta informacin 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 bsicos redactados
en palabras comunes para facilitar la comprensin y finalmente el
cdigo con los comandos para configurar MySQL para las transacciones
y control de concurrencia.
Qu es una transaccin?
Una transaccin en base de datos es una operacin de un conjunto,
en donde este conjunto es atomico, osea que se realiza un todo o
nada, esto para evitar conflictos y salvaguardar la informacin si
en el proceso existe un problema como puede ser de hardware,
software o incluso de ataques de terceras personas. Una transaccin
se comienza con una instruccin on begin transaction (no es
necesario en algunos DBMS). La instruccin on commit termina la
transaccin en forma exitosa y hace permanente cualquier cambio
realizado a la BD durante la transaccin Los cambios se hacen
permanentes solo despus de un commit La instruccin rollback aborta
la transaccin y la hace terminar en forma no exitosa, cualquier
cambio que la transaccin pudo hacer a la BD se deshace.En general
se puede hacer rollback para cualquier conjunto de instrucciones no
necesariamente dentro de una transaccin
Transferencia de fondos1:1.begin transaction2. Si A1 no tiene
suficiente dinerorollback3. Se aumenta el saldo de A2 en el monto
especificado.4. Se disminuye el saldo de A1 en el monto
especificado.5. commit1DAZ, P. M. (s.f.). Arquitectura 4 capas . En
P. M. DAZ.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 ejecucin concurrente de dos consultas SQL puede ser
incorrecta si no se toman las precauciones adecuadas.
- Operacin abortada. Por ejemplo, debido a una divisin por cero;
por privilegios de acceso; o para evitar bloqueos
Consistencia La ejecucin aislada de la transaccin 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 despus
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 transaccin el grado
de aceptacin de datos inconsistentes. A mayor grado de aislamiento,
mayor precisin, pero a costa de menor concurrencia. El nivel de
aislamiento para una sesin 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 fsicamente.
Lectura comprometida. Slo se permiten lecturas de datos
comprometidos.
Lectura repetible. Las lecturas repetidas de la misma fila para
la misma transaccin dan los mismos resultados.
Secuenciable. Mayor nivel de aislamiento. Las transacciones se
aslan 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 exista cuando
se inici la transaccin.
Durabilidad El sistema gestor de bases de datos asegura que
perduren los cambios realizados por una transaccin que termina con
xitoEstados de una transaccin
Activa: Cuando se ejecuta la transaccin Parcialmente
comprometida: aun sin completar.
Fallida: no se realiz la transaccion
Abortada: se abort la transaccin por alguna razn y la base de
datos regresa a su estado anterior
Ejecucin. Se puede reiniciar o cancelar
Ejemplo de cdigo 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 instruccin de actualizacin. 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 transaccin 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
2DAZ, P. M. (s.f.). Arquitectura 4 capas . En P. M. DAZ.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 problemticas.
Pesimista: asume que es muy probable que ocurran problemas acta
a la defensiva impidiendo la aparicin de conflictos usando
locks
Un lock es una estructura que solo puede ser adquirida por una
hebra de ejecucin (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.Adems de los locks exclusivos existen locks de solo
lectura o locks compartidos que pueden estar simultneamente siendo
utilizados por distintas ejecuciones.
Transacciones en MySQL (comandos)
UnejemploSupongamos que un sitio web bancario tiene 2 usuarios,
ambos trabajando sobre la mismacuenta.El usuario 1 pide incrementar
su saldo en 10, mientras que el usuario 2 pide disminuirlo (a travs
de un formulario, porejemplo)El programador del sistema no puede
decidir el orden en el que se ejecutarn las consultas, as que bien
podra suceder losiguiente:bal1 := ... SELECT balance FROM cuentas
WHERE cuenta=X -- usuario 1bal2 := ... SELECT balance FROM cuentas
WHERE cuenta=X -- usuario 2En este punto, existen dos copias de la
aplicacin que contienen una variable $balance cada una. Supongamos
que ambas necesitan actualizar el valor en la base dedatos:UPDATE
cuentas SET balance=(bal1+10) WHERE cuenta=X -- usuario 1UPDATE
cuentas SET balance=(bal2-10) WHERE cuenta=X -- usuario 2El
resultado es que ambas copias del programa ejecutaron sus consultas
con la informacin de balance que tenan, 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 informacin
vieja. Al final, en vez de quedar con el mismo saldo, la cuenta
termina perdiendo10.Lo que se necesita para este conjunto de
consultas, es lo que se denomina ACID, un acrnimo ingls que quiere
decir Atomicidad, Consistencia, Aislamiento y Durabilidad.
Recomiendo leer la informacin 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 serrevertidos.Cmo
usartransacciones?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 transaccin con COMMIT, lo
cual provoca que los cambios hechos por las consultas anteriores
sean permanentes. Si las consultas fallan en algn paso, se puede
volver al estado anterior al comienzo de la transaccin ejecutando
ROLLBACKAunque 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 nmero de filas afectadas sigue valiendo usar
mysql_num_rowsMientras la transaccin est ejecutndose, 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 transaccin termine. Esto implica que usar transacciones es
un poco ms lento que no usarlas, pero a la vez implica que los
datos involucrados no pueden ser modificados por otra copia de la
aplicacin, y por lo tanto se evita la situacin planteada al
principio comoejemplo.
Ejemplo de una transaccin desde un lenguaje de programacin (PHP)
usando condiciones para que segn sea la situacin se ejecute la
transaccin o se aborte y la base de datos no sufra ningn cambio.
Dicha situacin, implementada de forma transaccional en PHP,quedara:
1
