transacciones, aislamiento y candadosdis.unal.edu.co/~icasta/consejero/sql_transacciones_v5.pdf ·...
Post on 25-Sep-2018
241 Views
Preview:
TRANSCRIPT
TRANSACCIONES, AISLAMIENTO Y CANDADOS
Bases de Datos
Ingeniería de Sistemas Universidad Nacional de Colombia
2013
Ejemplo de una cuenta corriente
CONSIGNACION
Ingrese consignación
Lea saldo del cliente A
saldo = saldo +
consignación
grabe saldo
RETIRO
Ingrese retiro
Lea saldo del cliente A
Si saldo >= retiro
saldo = saldo – retiro
grabe saldo
suministre dinero
Fin_SI
Operación sin control
RETIRO
Ingrese retiro
Lea saldo del cliente A
Si saldo >= retiro
saldo = saldo – retiro
grabe saldo
suministre dinero
Fin_SI
Retira 800
Saldo inicial del cliente A = 1000
retiro = 800
saldo = 1000
saldo = 200
Operación sin control
CONSIGNACION
Ingrese consigna
Lea saldo del cliente A
saldo = saldo + consigna
grabe saldo
RETIRO
Ingrese retiro
Lea saldo del cliente A
Si saldo >= retiro
saldo = saldo – retiro
grabe saldo
suministre dinero
Fin_SI
Retira 800 Retira 700 Retira 100 Consigna 300
retiro = 800 retiro = 700 retiro = 100 consigna = 300
Saldo inicial del cliente A = 1000
saldo = 1000 saldo = 1000 saldo = 1000 saldo = 1000
saldo = 200 saldo = 300 saldo = 900 saldo = 1300
Operación con control
CONSIGNACION
Ingrese consigna
Lea saldo del cliente A
saldo = saldo + consigna
grabe saldo
RETIRO
Ingrese retiro
Lea saldo del cliente A
Si saldo >= retiro
saldo = saldo – retiro
grabe saldo
suministre dinero
Fin_SI
Retira 800 Retira 700 Retira 100 Consigna 300
retiro = 800 retiro = 700 retiro = 100 consigna = 300
Saldo inicial del cliente A = 1000
saldo = 1000 Espere Espere Espere
saldo = 200 Espere Espere Espere
saldo = 200 saldo = 200 Espere
saldo = 100 saldo = 100
saldo = 400
Bases de Datos - 2013
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
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
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
Modo chained
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
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
If Data
Already
Has an X
Lock
If Data
Already
Has a U
Lock
Scopes for
Which This
Lock
Exists
Shared
(S)
select Place
another S
lock
Wait for
lock to be
released
Place
another S
lock
Row, page,
table
Exclusive
(X)
insert,
update*,
delete*
Wait for lock
to be
released
Wait for
lock to be
released
Wait for
lock to be
released
Row, page,
table
Update
(U)
update, delete Place the U
lock
Wait for
lock to be
released
Wait for
lock to be
released
Page
*Updates y deletes usan candados exclusive solamente para encontrar los datos que necesitan modificar
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
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
Dirty reads prevented
Nonrepeatable reads allowed
Phantom reads allowed
Bases de Datos - 2013
Nivel 2 de aislamiento
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
Dirty reads prevented
Nonrepeatable reads prevented
Phantom reads allowed
Bases de Datos - 2013
Nivel 3 de aislamiento
Nivel 3 – El nivel más restrictivo:
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
Dirty reads prevented
Nonrepeatable reads prevented
Phantom reads prevented
Bases de Datos - 2013
Nivel 0 de aislamiento
Nivel 0 – El nivel menos restrictivo:
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
Dirty reads allowed
Nonrepeatable reads allowed
Phantom reads allowed
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
Proc2(Procedimiento anidado)
begin tran <statements...> if <error> begin
rollback tran return
end <statements...> commit tran return
• Un rollback no intencional es un rollback anidado que sin
intención deshace el trabajo en transacciones externas
Proc1(Transacción más externa)
begin tran <statements...> if <error> begin
rollback tran return
end exec proc2 if <error> begin
rollback tran return
end <statements...> commit tran return
Bases de Datos - 2013
Savepoints y Rollbacks anidados
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
-o-
commit hace commit a todo.
• Usar savepoints para evitar rollbacks no deseados
proc 1
save tran abc (2)
<statements...>
exec proc2
if <error>
rollback tran abc (5)
else ...
(2) save tran no incrementa el nivel de anidamiento. (5) rollback regresa al punto
abc. Entonces se ejecutan las subsiguientes sentencias en proc1.
proc 2
save tran xyz (3)
<statements...>
if <error>
rollback tran xyz(4)
else ...
(3) save tran no incrementa el nivel de anidamiento. (4) rollback regresa al punto xyz. Entonces se ejecutan las subsiguientes sentencias en proc2.
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 begin tran ... (este caso insert ... ocasiona un print "in sp" error) ... commit tran print "sp done"
Trigger
begin tran s2
....
rollback tran s2
print “tr done”
return
Bases de Datos - 2013
rollback trigger deshace el trigger y la sentencia que lo disparó
Sintaxis: rollback trigger [with raiserror error_number
[error_statement] ]
Deshacer un trigger
Bases de Datos - 2013
Procedimiento
almacenado
Caso C begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Trigger
....
....
rollback trigger
print “tr done”
return
Deshacer un trigger
Bases de Datos - 2013
Procedimiento almacenado Caso D begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Trigger
begin tran
...
rollback tran
print "tr done”
return
• Para deshacer toda la transacción donde está inmerso el trigger, ejecutar un rollback sin un punto de grabación
Procedimiento Almacenado Caso E begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Trigger
....
....
Rollback tran
print “tr done”
return
Deshacer una transacción
top related