Download Transacciones en Bases de datos - Departamento de Ingeniería de

TRANSACCIONES, AISLAMIENTO Y
CANDADOS
Bases de Datos
Ingeniería de Sistemas
Universidad Nacional de Colombia
2013
Ejemplo de una cuenta corriente
RETIRO
Ingrese retiro
Lea saldo del cliente A
Si saldo >= retiro
saldo = saldo – retiro
grabe saldo
suministre dinero
Fin_SI
CONSIGNACION
Ingrese consignación
Lea saldo del cliente A
saldo = saldo +
consignación
grabe saldo
Operación sin control
Saldo inicial del cliente A = 1000
Retira 800
RETIRO
Ingrese retiro
Lea saldo del cliente A
Si saldo >= retiro
saldo = saldo – retiro
grabe saldo
suministre dinero
Fin_SI
retiro = 800
saldo = 1000
saldo = 200
Operación sin control
RETIRO
Ingrese retiro
Lea saldo del cliente A
Si saldo >= retiro
saldo = saldo – retiro
grabe saldo
suministre dinero
Fin_SI
CONSIGNACION
Ingrese consigna
Lea saldo del cliente A
saldo = saldo + consigna
grabe saldo
Saldo inicial del cliente A = 1000
Retira 800
Retira 700
Retira 100
Consigna 300
retiro = 800
retiro = 700
retiro = 100
consigna = 300
saldo = 1000
saldo = 1000
saldo = 1000
saldo = 1000
saldo = 200
saldo = 300
saldo = 900
saldo = 1300
Operación con control
RETIRO
Ingrese retiro
Lea saldo del cliente A
Si saldo >= retiro
saldo = saldo – retiro
grabe saldo
suministre dinero
Fin_SI
Retira 800
CONSIGNACION
Ingrese consigna
Lea saldo del cliente A
saldo = saldo + consigna
grabe saldo
Saldo inicial del cliente A = 1000
Retira 700
Retira 100
Consigna 300
retiro = 800
retiro = 700
retiro = 100
consigna = 300
saldo = 1000
Espere
Espere
Espere
saldo = 200
Espere
Espere
Espere
saldo = 200
saldo = 200
Espere
saldo = 100
saldo = 100
saldo = 400
Transacciones
Una transacción es una o más sentencias que se toman como
una unidad (todo termina bien o todo se aborta)
Una transacción es una unidad lógica de trabajo
Definida para las reglas del negocio
Típicamente incluye al menos una modificación de datos
Pasa la base de datos de un estado consistente a otro
Una transacción tiene dos posibles salidas:
Committed
Todas las modificaciones quedan en firme
Rolled back
Las modificaciones retornan a su estado inicial
Bases de Datos - 2013
Rol de las transacciones
Proteger los datos de las fallas del software, hardware, y
potencia eléctrica
Permitir el aislamiento de datos de tal forma que varios
usuarios pueden acceder simultáneamente a los datos sin
interferencia
Bases de Datos - 2013
Cuándo usar transacciones?
Cuando un conjunto de sentencias se deben comportar
como una unidad
Bases de Datos - 2013
Sentencias para transacciones
Cuatro sentencias definen la estructura de una
transacción:
begin tran
commit tran
rollback tran
save
Bases de Datos - 2013
begin tran y commit tran
begin tran
Inicia la transacción
commit tran
Finaliza la transacción
Todas las modificaciones quedan en firme
Bases de Datos - 2013
begin tran y commit tran
Sintaxis:
begin { tran | transaction } [ transaction_name ]
commit [ tran | transaction | work ] [ transaction_name
| savepoint_name ]
Bases de Datos - 2013
rollback tran
rollback tran termina una transacción
Deshace las modificaciones que se hayan hecho
La ejecución continua con la instrucción siguiente a
rollback
Bases de Datos - 2013
Sintaxis para rollback tran
Sintaxis:
rollback [ tran [ transaction_name | savepoint_name ] |
transaction [ transaction_name | savepoint_name ] |
work [ transaction_name | savepoint_name ] ]
Bases de Datos - 2013
save
save crea un nombre de un punto de grabación
Es una extensión SQL que permite rollbacks parciales
Bases de Datos - 2013
Sintaxis para save
Sintaxis:
save { transaction | tran } savepoint_name
Bases de Datos - 2013
Transacciones anidadas
Se pueden tener transacciones anidadas:
El begin y commit más externos comienzan y finalizan las transacciones
Las sentencias begin y commit internos solamente guardan un registro del
nivel de anidamiento
Bases de Datos - 2013
Transacciones y el log de transacciones
El registro de transacciones almacena los efectos de cada insert,
update y delete
El sistema utiliza el registro de transacciones para rehacer las
transacciones que se reversaron
Se registra el comienzo de una transacción, los commits y rollbacks
Si un servidor falla durante una transacción, no hay registro de un rollback o
commit
Durante la recuperación (recovery), las modificaciones en transacciones sin un
registro de rollback o commit no tendrán efecto. Si las modificaciones fueron
grabadas en disco, se revertirán.
Bases de Datos - 2013
Modo de transacción
Un modo de transacción especifica cómo el servidor
debe definir las transacciones
Dos modos de transacción
Unchained
Chained
Bases de Datos - 2013
Modo unchained
En modo unchained, se requiere explícitamente de una sentencia
begin tran
También se requiere de commit tran o rollback tran explícitos
Sintaxis:
Set chained off
Bases de Datos - 2013
Modo chained
En modo chained, el servidor ejecuta un begin implícito antes de:
Sentencias DML– insert, update, delete, select
Sentencias de Cursor– open, fetch
Se requiere de commit tran o rollback tran explícitos
Este modo es ANSI compliant
Sintaxis:
Set chained on
Bases de Datos - 2013
Necesidad de aislamiento
En ambientes
multiusuario, las
transacciones acceden
a los datos
simultáneamente
Datos que no estén
aislados pueden estar
errados
Bases de Datos - 2013
Bloqueo (locking)
Mecanismo automático
que aisla los datos para
prevenir conflictos de los
datos que se están
modificando
Bases de Datos - 2013
Estructura interna de una tabla
Bases de Datos - 2013
Alcance de los candados
El alcance de un candado determina cuántos datos se aislan
Tres alcances
Bases de Datos - 2013
Tipos de candados
El tipo de candado determina la extensión del aislamiento de
datos de otras transacciones
Tres tipos de candados
Shared
Exclusive
Update
Bases de Datos - 2013
Candados Shared
Usado por sentencias que leen datos (selects)
Otros procesos pueden leer los datos (coloca candado shared), pero
ningún proceso puede cambiar los datos (coloca candado exclusive)
Bases de Datos - 2013
Candados exclusive
Usado por sentencias que cambian datos (inserts, updates, deletes)
Ningún otro proceso puede leer los datos (coloca candado shared) o
cambiar los datos (coloca candado exclusive sobre la página)
Bases de Datos - 2013
Candados update
Usado por operaciones que pueden o no cambiar los datos (updates,
deletes)
Cuando el proceso primero escanea los datos, le aplica un candado update.
Otros procesos pueden colocar candados shared, pero ningún proceso
puede colocar candados exclusive o update
Bases de Datos - 2013
Resúmen de tipos de candados
Statements
That Impose
This Lock
If Data
Already
Has an S
Lock
Place
another S
lock
Wait for lock
to be
released
If Data
Already
Has an X
Lock
Wait for
lock to be
released
Wait for
lock to be
released
If Data
Already
Has a U
Lock
Place
another S
lock
Wait for
lock to be
released
Scopes for
Which This
Lock
Exists
Row, page,
table
Wait for
lock to be
released
Wait for
lock to be
released
Page
Shared
(S)
select
Exclusive
(X)
insert,
update*,
delete*
Update
(U)
update, delete Place the U
lock
Row, page,
table
*Updates y deletes usan candados exclusive solamente para encontrar
los datos que necesitan modificar
Bases de Datos - 2013
Deadlock
Bases de Datos - 2013
Resolución del deadlock
Bases de Datos - 2013
Esquema de bloqueo
Esquema de bloqueo es un atributo de la tabla que
determina qué datos asociados con la tabla están
bloqueados
Bases de Datos - 2013
bloqueo “allpages”
Se pueden bloquear las páginas de índices
El servidor usa candados de tabla y candados de página, pero no
candados de fila
Bases de Datos - 2013
Bloqueo “datapages”
Las páginas de índices nunca se bloquean
El servidor usa candados de tabla y candados de página, pero no
candados de fila
Bases de Datos - 2013
Bloqueo “datarows”
Las páginas de índices nunca se bloquean
El servidor usa candados de tabla, candados de página y candados
de fila
Bases de Datos - 2013
Fijar el esquema bloqueo
Sintaxis simplificada:
create table table_name (
column_name
datatype [ NULL | NOT NULL | IDENTITY ] ,
...
column_name
datatype [ NULL | NOT NULL | IDENTITY ] )
[ lock { allpages | datapages | datarows } ]
Si no se especifica un esquema de bloqueo, la tabla usa el esquema
default de bloqueo
Bases de Datos - 2013
Cambiar el esquema de bloqueo
Sintaxis simplificada:
alter table table_name
lock { allpages | datapages |
datarows }
Bases de Datos - 2013
Leer datos no aislados
Hay tres tipos de consultas o “reads”, que pueden retornar datos
que son inadecuados para limitar el aislamiento de datos
Las características de cómo se hacen estos “reads” son propios de
cada DBMS
Hay tres tipo de “reads”:
Dirty reads
Nonrepeatable reads
Phantom reads
Bases de Datos - 2013
Lectura sucia
La transacción 1 modifica datos
La transacción 2 lee los datos modificados antes de que la
modificación haya terminado
Esta transacción lee datos “uncommitted” o “dirty”
Bases de Datos - 2013
Lectura no repetible
La transacción 1 lee datos
La transacción 2 modifica esos datos antes de que la primera
transacción haya terminado
La primera lectura es ahora “nonrepeatable”
Bases de Datos - 2013
Lectura fantasma
La transacción 1 lee un conjunto de filas que cumplen una condición
La transacción 2 modifica los datos de algunas columnas que no
cumplían esa condición y ahora la cumplen, o al contrario
Las filas que aparecen y desaparecen se denominan “phantoms”
Bases de Datos - 2013
Nivel de aislamiento
Un nivel de aislamiento es un conjunto de candados que permiten o
no una combinación particular de los tres tipos de lectura: sucia, no
repetible o con fantasmas
ANSI define cuatro niveles de aislamiento, cada uno más restrictivo
que el anterior
Dirty Reads
Nonrepeatable
Reads
Phantom
Reads
Level 0
Allowed
Allowed
Allowed
Level 1
Prevented
Allowed
Allowed
Level 2
Prevented
Prevented
Allowed
Level 3
Prevented
Prevented
Prevented
Bases de Datos - 2013
Nivel 1 de aislamiento
Dirty reads
Nonrepeatable reads
Phantom reads
prevented
allowed
allowed
Nivel 1 - Comportamiento de select:
Se fijan candados Shared hasta que el select termine la lectura
de una fila o página
select espera a que se liberen los candados exclusive
Bases de Datos - 2013
Nivel 2 de aislamiento
Dirty reads
Nonrepeatable reads
Phantom reads
prevented
prevented
allowed
Nivel 2 - Comportamiento de select:
Se fijan candados Shared hasta que termine la transacción
Este comportamiento es diferente al del nivel 1
select espera a que se liberen los candados exclusive
Comportamiento discreto de nivel 2 requiere bloqueo “row-level”
Tables APL (All Pages Lock ) y tables DPL (Data Pages Lock) no tienen bloqueo
“row-level”
Si una consulta con nivel de aislamiento 2 lee una tabla APL o DPL, se forza
comportamiento de aislamiento nivel 3
Bases de Datos - 2013
Nivel 3 de aislamiento
Nivel 3 – El nivel más restrictivo:
Dirty reads
prevented
Nonrepeatable reads
prevented
Phantom reads
prevented
Nivel 3 - Comportamiento de select:
Se fijan candados shared hasta que termine la transacción
Este comportamiento es diferente al del nivel 1
select espera a que se liberen los candados exclusive
Bases de Datos - 2013
Nivel 0 de aislamiento
Nivel 0 – El nivel menos restrictivo:
Dirty reads
Nonrepeatable reads
Phantom reads
allowed
allowed
allowed
Nivel 0 - Comportamiento de select:
Se fijan candados Shared hasta que select termine la lectura
de una fila o página
select ignora los candados exclusive
Este comportamiento es diferente al del nivel 1
Bases de Datos - 2013
Rótulos de modo de transacción
Los procedimientos almacenados se rotulan con el modo de
transacción con el cual fueron creados
No se puede ejecutar una transacción en un modo diferente al del rótulo
Bases de Datos - 2013
sp_procxmode
sp_procxmode permite ver y cambiar el modo de transacción de un
procedimiento
Sintaxis:
sp_procxmode [ procedure_name [ , {chained | unchained | anymode} ] ]
Bases de Datos - 2013
Rollbacks no intencionales
• Un rollback no intencional es un rollback anidado que sin
intención deshace el trabajo en transacciones externas
Proc1(Transacción más externa)
Proc2(Procedimiento anidado)
begin tran
<statements...>
if <error>
begin
rollback tran
return
end
exec proc2
if <error>
begin
rollback tran
return
end
<statements...>
commit tran
return
begin tran
<statements...>
if <error>
begin
rollback tran
return
end
<statements...>
commit tran
return
Bases de Datos - 2013
Savepoints y Rollbacks anidados
• Usar savepoints para evitar rollbacks no deseados
batch (Tran más externa)
begin tran (1)
<statements...>
exec proc1
if <error>
rollback tran (6)
else commit tran
(1) Con begin tran inicia la
transacción. Nivel
anidamiento: 1.
(6) rollback deshace todas
las sentencias en proc1,
proc2, y batch
-ocommit hace commit a todo.
proc 1
proc 2
save tran abc (2)
<statements...>
exec proc2
if <error>
rollback tran abc (5)
else ...
save tran xyz (3)
<statements...>
(2) save tran no incrementa
el nivel de anidamiento.
(3) save tran no incrementa
el nivel de anidamiento.
(5) rollback regresa al punto
(4) rollback regresa al punto
xyz. Entonces se ejecutan
las subsiguientes sentencias
en proc2.
abc. Entonces se ejecutan las
subsiguientes sentencias en
proc1.
if <error>
rollback tran xyz(4)
else ...
Bases de Datos - 2013
Triggers y rollbacks
Tres tipos de rollbacks:
Deshacer el trigger
Deshacer el trigger y la sentencia que lo
disparó
Deshacer toda la transacción
Bases de Datos - 2013
Deshacer un trigger
Para deshacer un trigger, declarar un punto de grabación y luego
hacer el rollback
Un rollback sin punto de grabación deshace toda la transacción
Procedimiento almacenado
Caso A
begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Trigger
save tran s1
....
rollback tran s1
print “tr done”
return
Procedimiento almacenado
Caso B
(este caso
ocasiona un
error)
begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Trigger
begin tran s2
....
rollback tran s2
print “tr done”
return
Bases de Datos - 2013
Deshacer un trigger
rollback trigger deshace el trigger y la sentencia que lo
disparó
Sintaxis:
rollback trigger [with raiserror error_number
[error_statement] ]
Bases de Datos - 2013
Deshacer un trigger
Procedimiento
almacenado
Caso C
begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Trigger
....
....
rollback trigger
print “tr done”
return
Bases de Datos - 2013
Deshacer una transacción
• Para deshacer toda la transacción donde está inmerso el trigger,
ejecutar un rollback sin un punto de grabación
Procedimiento almacenado
Caso D
begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Procedimiento Almacenado
Caso E
begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Trigger
begin tran
...
rollback tran
print "tr done”
return
Trigger
....
....
Rollback tran
print “tr done”
return
Bases de Datos - 2013
Preguntas
Gracias por su Atención
Bases de Datos - 2013