sábado, 12 de noviembre de 2016

SQL Server. El registro de transacciones (Transaction Log)

El  registro de transacciones (transaction log) el transaction Iog  es una entidad de datos utilizada por SQL Server para almacenar la historia de las transacciones por las que ha pasado una base de datos.

SQL Server. El registro de transacciones (Transaction Log)



Cuando se crea una base de datos, SQL Server genera automáticamente un transaction log asociado. Las modificaciones que sufre la base de datos se realizan con sentencias INSERT, UPDATE, DELETE o a través de sentencias SQL de definición de datos. Estos cambios se escriben en el transaction log antes de ser ejecutados. Este proceso se llama write-ahead y trata de mantener la integridad de los datos cuando se produzcan pérdidas por causas ajenas al funcionamiento normal de la base de datos. Si el servidor se apaga o se bloquea por cualquier causa, durante una actualización de datos, el transaction log permite preservar la integridad de la base de datos completando o descartando las transacciones dependiendo del estado en el que se encontrasen en el momento de la caída. 

Proceso de escritura de datos en disco

Proceso de escritura write-ahead

Como se ha dicho anteriormente SQL Server lleva a cabo las modificaciones de datos siguiendo un esquema write-ahead. En esta modo de escritura, las modificaciones no son escritas directamente sobre la base de datos, se escriben en primer lugar sobre el transaction log, y no se ejecutan sobre la base de datos real hasta que no se ejecuta una instrucción commit transaction.
Estas operaciones se almacenan temporalmente en una estructura de datos intermedia, que minimiza el número de escrituras en disco, facilitando que el rendimiento de la transacción sea mayor. 

Secuencia de operaciones del transaction log

Esta es la secuencia de operaciones que se realiza sobre la caché cuando se está validando una transacción. (Ojo, no confundir la caché de la máquina con un espacio de memoria RAM reservado por SQL Server para estas operaciones, que es el que aquí llamamos caché)
BEGIN TRANSACTION sobre el transaction log.
Se escriben las sentencias que integran la transacción sobre el transaction log.
Se escribe la sentencia COMMIT TRANSACTION en el transaction log.
La ejecución de la sentencia COMMIT TRANSACTION  realiza las operaciones escritas en el trasaction  log sobre la base de datos.
Cuando una transacción se confirma, las instrucciones escritas en el transaction log  se ejecutan sobre la base de datos disco.

Información que se escribe en el transaction log

SQL Server anota en el transaction log toda la información necesaria para que la transacción confirmada sea re-ejecutada si los datos se perdieron. Esta información puede ser muy diferente en función de tipo de la transacción. 
Por ejemplo, una actualización de un registro de una tabla escribirá en el transaction log:
-Todos los datos del registro antes de la actualización. Y los marcará como registro a borrar.
-Los nuevos datos a insertar en el registro, marcados como información a insertarse en el registro.
-La información eliminada de las páginas de índice que se hallan modificado, en tantos apuntes de borrado de índice como sean necesarios.
-Información análoga a la anterior para las actualizaciones realizadas en los índices en apuntes de inserción de índice. 
-Apuntes de ubicación de página para las páginas que haya sido necesario añadir.
-Apuntes de liberación de página para las páginas sobrantes. 

El mecanismo de checkpointing y la recuperación automática

Puntos de actualización (Checkpoints)

Las transacciones sobre las bases de datos no se escriben directamente sobre la ubicación física de los mismos a medida se van produciendo, sino que se utiliza lo que se llama write ahead caché, consiste en un mecanismo que  provoca que la base de datos deba ser actualizada en algún momento. Cuando esto sucede, procede a escribir las páginas de la base de datos que hayan sido modificadas desde la última actualización. El mecanismo que provoca la actualización automática es precisamente el establecimiento de puntos de actualización o checkpoints. Cada vez que se produce un checkpoint, SQL Server escribe todas las dirty pages (porción modificada del transaction log)  de la caché a la base de datos física.
Existe dos tipos de puntos de actualización, (checkpoints) automáticos y explícitos: 
Los automáticos son llevados a cabo por SQL Server sin que se produzcan actuaciones por parte del administrador del sistema. Se producen a intervalos prefijados, con una periodicidad es calculada automáticamente por SQL Server, basándose en el valor máximo del tiempo aceptable para la recuperación
Existe un procedimiento almacenado llamado sp_configure que se puede utilizar para modificar algunos parámetros de la tabla de sistema sys.syscurconfigs que contiene entre otros el intervalo de recuperación del log expresado en minutos. 
Un punto de actualización explícito puede ser provocado por un propietario de la base de datos o el administrador utilizando el comando CHECKPOINT  Sólo los propietarios de la base de datos pueden ejecutar este comando  y sus permisos de ejecución no pueden ser transferidos. 
Cuando se produce un truncamiento de un transaction log, el sistema si no se le solicita explícitamente que no lo haga, provoca un punto de actualización automático, de manera que la base de datos se actualice, antes de  eliminar el transaction log. 
Al no estar construido el transaction log con páginas de datos convencionales, no compite con los datos por la ocupación de la memoria caché, Con la consiguiente mejora de rendimiento. Además, se facilitan y automatizan las operaciones de copia de backup, ya que el fichero físico de log es divisible en varios ficheros virtuales, puede crecer automáticamente y permite un truncado del log más rápido y más independiente de los datos. 

Truncado del transaction log

A medida que la base de datos va cambiando, el transaction log crece anotando dichas modificaciones. Este crecimiento se producirá hasta que se proceda a truncarlo. La razón más importante para esta operación es mantener su tamaño limitado, pues si el transaction log agota el espacio de almacenamiento que se  tiene asignado la base de datos no podría soportar ninguna transacción. 

Porción activa del transaction log

Entre el mecanismo write-ahead de escritura de datos en disco, y la anotación de las modificaciones mediante puntos de actualización, se deduce que el transaction log se compone de una porción que contiene operaciones que ya se han escrito en disco, y de otra que incluye sentencias cuya ejecución aún no se ha asentado. Esta última porción es la denominada activa, mientras que el conjunto de operaciones del log que ya ha sido escrito en disco es la parte inactiva del log. La porción inactiva del log, por tanto, contiene operaciones perfectamente estables susceptibles de ser archivadas en copias pero que carecen de utilidad como integrantes del transaction log.
Para limitar el tamaño del log deberemos proceder periódicamente a eliminar esta porción inactiva. Este proceso se denomina truncado. 

Ficheros virtuales de log

El transaction log está compuesto por un conjunto de ficheros lógicos que contienen páginas especiales, que se denominan registros, pero que por consideraremos páginas de log. Cada uno de estos ficheros lógicos es denominado fichero virtual de log (virtual log files). 
El transaction log deberá contener, al menos, 2 ficheros virtuales, y cada uno de ellos debe tener un tamaño mínimo de 512 KB. El tamaño y número de ficheros virtuales del log va evolucionando a medida que el log va modificando su tamaño. Estos ficheros virtuales representan la unidad de truncado del transaction log. En particular, cada proceso de truncado del log puede eliminar los ficheros virtuales de log que no contengan ninguna operación de  la porción activa del log. En la figura se muestra un fichero físico de log con sus ficheros virtuales inmersos, coloreados según si contienen o no la porción activa del transaction log.

fichero físico del transaction log

Redimensionamiento automático del transaction log

También es posible configurar el transaction log para su redimensionamiento automático. Este proceso se hace en los ficheros virtuales de log. Cuando el transaction log debe crecer se añade un conjunto de ficheros virtuales de log completos, mientras que cuando es precisa una reducción, él o los ficheros físicos asociados al log también se reducirán siempre con la eliminación de ficheros virtuales completos. 
Para configurar este comportamiento en el Administrador Corporativo, seleccionamos la base de datos y con el botón derecho del ratón se muestra un menú del cual elegimos propiedades. Aparece la página de propiedades en la pestaña Archivos podremos configurar si desearnos que el log se redimensione automáticamente y la dinámica de este redimensionainiento. 

propiedades y redimensionado automático del transaction log


Transacciones

Para SQL Server cada uno de los procesos de modificación de datos tiene un punto de inicio y un punto final. Las operaciones que se realicen entre ambos puntos serán consideradas una unidad de trabajo. Las unidades de trabajo, o transacciones, serán llevadas a cabo de manera completa, o descartadas si la completitud de la misma no es posible. Una transacción debe considerarse como si se tratase de una sola operación. Esto es crítico por ejemplo para una transferencia bancaria ya que las operaciones deben completarse para que la base de datos no contenga datos incoherentes. De este modo, si no se alcanza el punto final de la modificación de datos SQL Server deshará automáticamente todos los cambios realizados, asegurando así la coherencia e integridad de los datos.
Para que esto sea posible, debe existir una relación de las operaciones que se han llevado a cabo, para que sea posible deshacerlas. Esta relación de transacciones es la que se almacena, en el transaction log. 

Inicio y final de una transacción

Cada una de las sentencias SQL es, en sí misma, una transacción. Sin embargo, es posible y frecuente definir las transacciones explícitamente como un conjunto de sentencias con el comando BEGIN TRANSACTION en el inicio de una transacción Una vez que se han terminado las sentencias de la unidad de trabajo podemos validarla o descartarla. La validación supone que se acepten las modificaciones realizadas en el marco de la misma, y se realiza mediante la sentencia COMMIT TRANSACTION. Para descartar las operaciones deshaciendo los cambios que se hayan llevado a cabo utilizaremos la sentencia ROLLBACK TRANSACTION. 

Ubicación y tamaño del transaction log

El transaction log, ocupa un espacio, por lo que será necesario proveer de un dispositivo de almacenamiento que lo albergue. Es recomendable almacenarlo siempre en un dispositivo diferente de los que contienen la base de datos. Las razones para esta recomendación son estas:
Posibilidad de independizar las copias de seguridad del transaction log de las de la base de datos. 
Aumento de la seguridad, pues es más difícil la pérdida simultánea de los datos y de la relación de transacciones.
Mejora de rendimiento si se almacena el log en un dispositivo físico diferente al que se utiliza para la base de datos, pues las escrituras al transaction log no compiten por el recurso con el resto de actividades sobre la base de datos. 
El tamaño del transaction log depende de la actividad de modificación de la base de datos a la que se asocia. En general, podríamos destinar un 10 % del tamaño de la base de datos. En situaciones especiales puede revelarse insuficiente. Esto será así, si la dinámica de operaciones de nuestra base de datos hace que las transacciones sean muy largas.


No hay comentarios:

Publicar un comentario