1. TRANSACCIONES, AISLAMIENTO Y CANDADOS Bases de Datos Ingeniería de Sistemas Universidad Nacional de Colombia 2013

2. 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

3. Operación sin control Saldo inicial del cliente A = 1000 RETIRO Ingrese retiro Lea saldo del cliente A Si saldo >= retiro saldo = saldo – retiro grabe saldo suministre dinero Fin_SI

4. 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

5. Operación con 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

6. 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

7. 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

8. Cuándo usar transacciones? • Cuando un conjunto de sentencias se deben comportar como una unidad

9. Sentencias para transacciones • Cuatro sentencias definen la estructura de una transacción: • begin tran • commit tran • rollback tran • save

10. begin tran y commit tran • begin tran • Inicia la transacción • commit tran • Finaliza la transacción • Todas las modificaciones quedan en firme

11. begin tran y commit tran • Sintaxis: begin { tran | transaction} [ transaction_name ] commit [ tran | transaction | work ] [ transaction_name | savepoint_name]

12. 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

13. Sintaxis para rollback tran • Sintaxis: rollback [ tran [ transaction_name | savepoint_name ] | transaction [ transaction_name | savepoint_name ] | work [ transaction_name | savepoint_name ] ]

14. save • save crea un nombre de un punto de grabación • Es una extensión SQL que permite rollbacks parciales

15. Sintaxis para save • Sintaxis: save { transaction | tran } savepoint_name

16. 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

17. 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.

18. 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

19. Modo unchained • En modo unchained, se requiereexplícitamente de unasentenciabegin tran • Tambiénse requiere de commit trano rollback tranexplícitos • Sintaxis: Set chained off

20. Modo chained • En modo chained, el servidorejecuta un beginimplícito antes de: • Sentencias DML– insert, update, delete, select • Sentencias de Cursor– open, fetch • Se requiere de commit trano rollback tranexplícitos • Este modoes ANSI compliant • Sintaxis: Set chained on

21. Necesidad de aislamiento • En ambientes multiusuario, las transacciones acceden a los datos simultáneamente • Datos que no estén aislados pueden estar errados

22. Bloqueo (locking) • Mecanismo automático que aisla los datos para prevenir conflictos de los datos que se están modificando

23. Estructura interna de una tabla

24. Alcance de los candados • El alcance de un candado determina cuántos datos se aislan • Tres alcances

25. 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

26. 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)

27. 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)

28. 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

29. Resúmen de tipos de candados *Updates y deletes usan candados exclusive solamente para encontrar los datos que necesitan modificar

30. Deadlock

31. Resolución del deadlock

32. Esquema de bloqueo • Esquema de bloqueo es un atributo de la tabla que determina qué datos asociados con la tabla están bloqueados

33. 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

34. 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

35. Bloqueo “datarows” • Las páginas de índices nunca se bloquean • El servidor usa candados de tabla, candados de página y candados de fila

36. Fijar el esquema bloqueo • Sintaxissimplificada: create table table_name ( column_namedatatype [ NULL | NOT NULL | IDENTITY ] , ... column_namedatatype [ NULL | NOT NULL | IDENTITY ] ) [ lock { allpages | datapages | datarows } ] • Si no se especifica un esquema de bloqueo, la tablausa el esquema default de bloqueo

37. Cambiar el esquema de bloqueo • Sintaxissimplificada: alter tabletable_name lock { allpages | datapages | datarows }

38. Ver esquema de bloqueo • Ver el default del esquema de bloqueo actual: sp_configure "lock scheme" • Ver el esquema de bloqueode unatabla: exec sp_helpnombre_tabla

39. 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

40. 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”

41. 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”

42. 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”

43. 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

44. 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

45. 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

46. 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

47. 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

48. 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

49. sp_procxmode • sp_procxmode permite ver y cambiar el modo de transacción de un procedimiento • Sintaxis: sp_procxmode [ procedure_name [ , {chained | unchained | anymode} ] ]

50. Proc2(Procedimientoanidado) 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 Rollbacks no intencionales 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