Principios de las Transacciones SQL

1. www.dbtechnet.org Principios de las Transacciones SQL “Big Picture” paracomprender COMMIT y ROLLBACK de transacciones SQL Fileros, Buffers, Listener, Hebras de Servicio, y Transacciones Martti Laiho 2012-04-02

2. Transacción SQL (Plana) [BEGIN TRANSACTION] [SET TRANSACTION ISOLATION .. ] SELECT … INSERT … UPDATE … DELETE … COMMITo ROLLBACK Base de Datos Log de Transacciones

3. Visión de Instancia de Servidor de Base de Datos Conexiones (sesiones) transacciones - Comandos y resultados SQL red Memoria principal listener &hebras de servicio bufferpool(s) Procesos de instancia De SGBD (hebras) x startup log cache(s) disco Ficheros control instancia Software SGBD Ficheros control BD Ficheros de alertas y trazas Ficheros espaciotablas Logs transacciones Ficheros de base de datos

4. Arquitecturas de bases de datos Instancia DB2 Instancia Oracle Ficheros control directorios Ficheros control directorios bufferpools bufferpools bufferpools Para cada base de datos los logs de transacciones activas forman una cadena circular Oracle llama a las cadenas de logs de Transacciones activas logs de reconstrucción Instancias SQL Server BD BD BD bufferpool BDs sistema Una instacia de servidor puede incluir una o varias Bases de datos dependiendo del producto SGBD. En adelante nos centraremos en un caso simple de Una única base de datos BD master BD Los logs de transacciones activas son alojadas en ficheros De log virtuales en un único fichero que forma una cadena circular Martti Laiho 2012-04-09

5. Ficheros y Cachés Listener Aplicaciones (clientes) Esperando peticiones De conexión de clientes . . . Hebras de servicio (agentes) Caché de Control Control e transacción, etc Caché de Datos (Bufferpool) Cache de páginas de datos Para el procesamiento rápido De datos minimizando Operaciones de E/S de disco Protocolo LRU para liberar páginas Caché de Log Punto de Control Cache de imágenes Previas y posteriores De filas de Transacciones activas Ficheros de Datos en discosconstituyen el espacio de tablas Para tablas e índices Páginas de datos para filas WAL protocolo write-ahead-logging Archivo de Log (historial) Cadena circular de log de transacciones Martti Laiho 2012-04-02

6. Estructuras de Páginas de Datos Datos de control incluyendo punteros, dirty bit, LSN, etc fila Resto de la fila Cabecera Página Cabecera Página PCTFREE Porcentaje de espacio libre originalmente en páginas Registro de la fila Registro Dirección de una fila es RID (ROWID) File# : Page# : Slot# Una fila puede continuar en otras páginas o el registro original puede contener sólo un enlace a una nuevadirección, preservando el valor RID original de la dirección de la fila. Slotdirectory Slotdirectory Cada fichero de datos tiene un file# en la BD y el espacio de trabla se gestiona como una secuancia de Páginas. Page# es el número ordinal de la página en el fichero. Un tamaño de página típico hoy día es 4, 8, 16 o 32 KB. Martti Laiho 2012-04-06

7. Sesión SQL: Un clienteiniciasuconexión SQL .. connect? Listener . . . Hebras de Servicio (agentes) Caché de Control Caché de Datos(Bufferpool) Caché de Log Ficheros de Datos Archivo de Log Logs de Transacciones Martti Laiho 2012-04-02

8. .. Obtieneunahebra de servicio connect? Listener . . . Hebras de Servicio (agentes) Caché de Control Caché de Datos(Bufferpool) Caché de Log Ficheros de Datos Archivo de Log Logs de Transacciones Martti Laiho 2012-04-02

9. .. Clienteusa un comando SQL paraactualizarunafila (aún en disco) El primer comando SQL inicia Una nueva trasacción update T set .. where id=.. Listener . . . Hebras de Servicio (agentes) Caché de Control Transaction #tr Caché de Datos(Bufferpool) Caché de Log Ficheros de Datos # begin La transacción obtienen un ID (#) Y un registro begin_transaction se escribe en la Caché de Logs Archivo de Log Logs de Transacciones Martti Laiho 2012-04-02

10. SGBD leé la página de la fila en bufferpool update T set .. where id=.. Listener . . . Hebras de Servicio (agentes) Caché de Control Transaction #tr Caché de Datos(Bufferpool) . . . Caché de Log Ficheros de Datos # . begin . . . Archivo de Log Logs de Transacciones Martti Laiho 2012-04-02

11. La filaesactualizada y la páginaactualizada se marca con Dirty Bit update T set .. where id=.. Listener . . . Hebras de Servicio (agentes) Caché de Control Transaction #tr dirtybit Caché de Datos(Bufferpool) xxx Caché de Log Ficheros de Datos # begin Archivo de Log Logs de Transacciones Martti Laiho 2012-04-02

12. Un registro de log de la imagenprevia y posterior de la fila se escribe en la cahñe de logs update T set .. where id=.. Listener . . . Hebras de Servicio (agentes) Caché de Control Transaction #tr dirtybit Caché de Datos(Bufferpool) xxx xxx Caché de Log #tr & before image & after image Ficheros de Datos # # begin Archivo de Log Logs de Transacciones Martti Laiho 2012-04-02

13. Con commit los registros de log de la transacción se escriben en el log de la transacción COMMIT Listener . . . Hebras de Servicio (agentes) Caché de Control Transaction #tr dirtybit Caché de Datos(Bufferpool) xxx xxx xxx Caché de Log Ficheros de Datos # # # # begin begin Archivo de Log # commit Logs de Transacciones Martti Laiho 2012-04-02

14. .. Pero en caso de ROLLBACK lasimágenesprevias son devueltas a sus direccionesoriginales ROLLBACK Listener . . . Hebras de Servicio (agentes) Caché de Control Transaction #tr dirtybit Caché de Datos(Bufferpool) xxx xxx Caché de Log Ficheros de Datos # # # # begin begin Archivo de Log # rollback Logs de Transacciones Martti Laiho 2012-04-03

15. .. Finalmente el clientecierra la conexión SQL disconnect Listener . . . Hebras de Servicio (agentes) Caché de Control dirtybit Caché de Datos(Bufferpool) xxx xxx Caché de Log Ficheros de Datos # # begin Archivo de Log # commit Logs de Transacciones Martti Laiho 2012-04-02

16. .. Algunaspáginaspuedenseractualizadasporsesiones SQL de clientes Listener . . . Hebras de Servicio (agentes) Caché de Control #tr(s) Caché de Datos(Bufferpool) . . . xxx X xx xxx Caché de Log # X xx Ficheros de Datos # # # begin Archivo de Log … # commit Logs de Transacciones Martti Laiho 2012-04-02

17. De vez en cuando un punto de control para los servicios .. Listener . . . Hebras de Servicio (agentes) Caché de Control Checkpoint #tr(s) Caché de Datos(Bufferpool) . . . xxx xxx xxx Caché de Log # X xx Ficheros de Datos # # # begin 1. write-ahead-logging (WAL) Archivo de Log … # commit Logs de Transacciones Martti Laiho 2012-04-07

18. Y todaslaspa´ginas “sucias” se escriben en los ficheros de datoslimpiando los dirty bits Listener . . . Hebras de Servicio (agentes) Caché de Control Checkpoint #tr(s) Caché de Datos(Bufferpool) . . . xxx xxx xxx xxx LogCache Caché de Log 2. Ficheros de Datos # # begin xxx Archivo de Log # # … Xxx # commit Logs de Transacciones Martti Laiho 2012-04-07

19. Y escribe el registro de punto de control Listener . . . Hebras de Servicio (agentes) Caché de Control Checkpoint #tr(s) Caché de Datos(Bufferpool) . . . xxx xxx xxx Caché de Log Ficheros de Datos # # begin Archivo de Log Checkpointrecord # # … Xxx # commit 3. CBg ### CBg ### CEn CEn - Chk Begin - #trlist - ChkEnd Logs de Transacciones Martti Laiho 2012-04-08

20. Después del punto de control los clientespuednecontinuar Listener . . . Hebras de Servicio (agentes) Caché de Control #tr(s) Caché de Datos(Bufferpool) . . . xxx xxx xxx xxx Caché de Log Ficheros de Datos # # begin xxx Archivo de Log # # … Xxx # commit CBg ### CEn Logs de Transacciones Martti Laiho 2012-04-02

21. Los contenidos de los ficheros de log de transacciones se copian en el archivo .. Listener . . . Hebras de Servicio (agentes) Caché de Control #tr(s) Caché de Datos(Bufferpool) . . . xxx xxx xxx xxx xxx Caché de Log Ficheros de Datos # # # # begin begin xxx Archivo de Log # # … Xxx # # commit commit CBg ### CEn … Logs de Transacciones Martti Laiho 2012-04-02

22. .. Y el espacio de los ficheros de log copiadospuedeserreutilizado Listener . . . #tr(s) . . . xxx xxx xxx xxx # # begin xxx # # Xxx # commit CBg ### CEn … Martti Laiho 2012-04-02

23. Una pruebade log usando SQL Server 2012 Basado en el ejercicio original de Kari Silpiö 21 10 11 Cabecera Página 22 10 21 11 23 21 Slots . . 3 2 1 0 Page ID 1:77

24. Algunas Notas .. • Least Recently Used (LRU) es necesario cuando el SGBD necesita recuperar espacio libre en el/los bufferpool(s). Las páginas que no han sido usadas por mayor tiempo y tienen reiniciado el dirty bit, serán sustituidas por páginas nuevas para leerse de ficheros de datos. • Los ficheros circulares de log de transacciones activas son los ficheros más importantes de una base de datos ya que contienen datos de las transacciones más recientes confirmadasDual logging (replicado) se recomienda para estos ficheros en discos dedicados. • Basado en el último registro de punto de control y el log de transacciones circular un SGBD puede recuperar una base de datos después de un soft crash automaticamente mediante roll-back recovery de fallos y roll-forward recovery de transacciones confirmadas al nivel de la última trnsacción confirmada antes del soft crash. • En caso de hardware crash el contenido de la base de datos tiene que se restaurado desde el backup de la base de datos y la aplicaciónand de roll-forward recovery del historial de transacciones desde el archivo y los últimos logs circulares, y finalmente rollback recovery de las últimas transacciones fallidas. • Estos temas están cubiertos en tutoriales distintos.