Las transacciones son con frecuencia definida como un conjunto de acciones que tienen éxito o fracasan en su conjunto. Para ser más específicos, las transacciones pueden ofrecer cuatro funciones principales de los procesos de manipulación de datos que el acceso a la base de datos:
- Atomicidad: es la propiedad que asegura que la operación se ha realizado o no, y por lo tanto ante un fallo del sistema no puede quedar a medias.
- Consistencia: Integridad. Es la propiedad que asegura que sólo se empieza aquello que se puede acabar. Por lo tanto se ejecutan aquellas operaciones que no van a romper las reglas y directrices de integridad de la base de datos.
- Aislamiento: es la propiedad que asegura que una operación no puede afectar a otras. Esto asegura que la realización de dos transacciones sobre la misma información sean independientes y no generen ningún tipo de error.
- Durabilidad: es la propiedad que asegura que una vez realizada la operación, ésta persistirá y no se podrá deshacer aunque falle el sistema.
De forma predeterminada en SQL Server, la declaración de cada uno de INSERT, UPDATE, o DELETE es una transacción individual que se comitea de forma automática y no ofrece la funcionalidad de rollback.
Puede activar las transacciones implícitas dentro de la configuración de su conexión de modo que el motor de base de datos se inicia una transacción de forma automática cuando cualquiera de los siguientes comandos se ejecutan:
ALTER TABLE, CREATE, DELETE, DENY, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT,TRUNCATE TABLE, o UPDATE.
La transacción está activa hasta que se emita manualmente una sentencia COMMIT o ROLLBACK. Puede activar las transacciones implícitas mediante el uso de la instrucción SET IMPLICIT_TRANSACTIONS ON, a través de la vinculación e incrustación de objetos de bases de datos (OLEDB) o Open Database Connectivity (ODBC), de interfaces de programación de aplicaciones (API), en la página Opciones de consulta en el SSMS, o puede modificar las propiedades del servidor para cambiar el comportamiento por defecto para permitir las transacciones implícitas en todas las conexiones, a menos que se establezca explícitamente en OFF para una conexión específica.
Definición de Transacciones explícitas
Transacciones explícitas se definen típicamente en procedimientos almacenados.Una transacción explícita se inicia cuando una instrucción BEGIN TRANSACTION se ejecuta. La transacción se ha completado mediante la emisión sea un COMMIT TRANSACTION o ROLLBACK TRANSACTION. Una vez que se confirma una transacción, SQL Server garantiza que los datos se escriben en la base de datos incluso en caso de fallo del servidor. Una instrucción ROLLBACK devuelve los datos a su estado previo al inicio de la transacción.
Nota: A pesar de la sentencia ROLLBACK devuelve los datos a su estado anterior,algunas funcionalidades, como por ejemplo los valores de las columnas de identidad, no se restablecen.
Mientras que una transacción está activa, los bloqueos se mantienen en el acceso a los recursos basados en el nivel de aislamiento de la transacción. Cuando se completa una transacción (a través de COMMIT o ROLLBACK), todos los bloqueos se liberan. La comprensión de cómo estos bloqueos y la función de aislamiento delos niveles es fundamental para comprender y optimizar el código, que incluye las transacciones.
Algunos escenarios de ROLLBACK
Cuando las transacciones se anidan, mediante la emisión de múltiples instrucciones BEGIN TRANSACTION en una reunión, una declaración ROLLBACK deshace la transacción más externa anidada. Esto es cierto incluso si COMMIT se emiten para las transacciones internas antes de que el comando ROLLBACK de la transacción externa. En el siguiente ejemplo, los datos se retrotraen hasta el final de la transacción a partir de la línea 1 y la fila insertada no existe en absoluto en la tabla.
BEGIN TRANSACTION INSERT INTO TestTable VALUES (1, 'a', 'b'); BEGIN TRANSACTION UPDATE TestTable SET Col2 = 'c' WHERE TestID = 1; COMMIT TRANSACTION; ROLLBACK;
Si usted desea recuperar sólo una parte de una transacción, puede definir puntos de rescate mediante el uso de la instrucción SAVE TRANSACTION savepoint_name y luego hacer referencia al nombre del punto de retorno en la instrucción ROLLBACK.De esta manera, le está diciendo el motor de base de datos para revertir los cambios de datos sólo hasta el punto donde se emitió la instrucción SAVE TRANSACTION con el mismo nombre.
Si se define más de un punto con el mismo nombre, la instrucción ROLLBACK retorna los datos de vuelta al punto de retorno más reciente con el nombre especificado en la sentencia ROLLBACK.
Si desea revertir toda la transacción, emita un ROLLBACK TRANSACTION con el nombre de la transacción o sin nombre. Recuerde que la emisión de una instrucción ROLLBACK TRANSACTION sin nombre deshace todas las transacciones anidadas. Usted debe incluir una declaración de COMMIT para la porción guardada de la transacción y el código adicional que puede seguir la instrucción ROLLBACK TRANSACTION savepoint_name. Además, no puede utilizar los puntos de rescate con las transacciones distribuidas.
Recopilar información acerca de las transacciones
Es importante rastrear las transacciones activas, sobre todo cuando se trabaja con transacciones anidadas y puntos de retorno. Cuando se trabaja con procedimientos almacenados, las rutinas de manejo de errores deben comprobar la finalización de la transacción (COMMIT o ROLLBACK) antes de cerrar una conexión.
Puede utilizar la variable @@TRANCOUNT global para ver el número de transacciones abiertas en la sesión actual.
Para un mayor nivel de detalle, puede utilizar los objetos dinámicos siguientes para transacciones específicas de gestión:
- sys.dm_tran_active_snapshot_database_transactions
- n sys.dm_tran_current_snapshot
- n sys.dm_tran_database_transactions
- n sys.dm_tran_session_transactions
- n sys.dm_tran_transactions_snapshot
- n sys.dm_tran_active_transactions
- n sys.dm_tran_current_transaction
- n sys.dm_tran_top_version_generators
- n sys.dm_tran_version_store
- n sys.dm_tran_locks
El objeto sys.dm_tran_active_transactions devuelve información sobre todas las transacciones activas en una instancia. El ID de operación reportada es único en todas las bases de datos, pero no en todas las instancias en un servidor.
Entendiendo los bloqueos (para más información clic AQUI):
Para entender completamente cómo las transacciones interactúan entre sí en un servidor de base de datos, primero hay que entender los niveles de aislamiento y bloqueo. En un entorno de base de datos, hay dos filosofías de control general sobre el bloqueo. El control de aproximación pesimista asume que los usuarios podrían tratar de leer y actualizar los mismos datos al mismo tiempo y se utilizan los bloqueos para evitar los problemas causados por varios usuarios que acceden a los mismos datos al mismo tiempo. El enfoque del control optimista asume que, o bien los usuarios no tendrán acceso a los datos al mismo tiempo, o que un cierto nivel de inconsistencia temporal es aceptable para lecturas simultáneas durante una actualización, y no hay bloqueos de lectura a fin de que una mejor simultaneidad y mayor rendimiento.
Para lograr el enfoque necesario, utilizar una combinación de sugerencias de bloqueo y niveles de aislamiento.
Aunque los niveles de bloqueo y aislamiento están muy interconectados, se discuten los bloqueos y cómo ver información de bloqueo en el primer servidor, seguido por una discusión de los niveles de aislamiento.
Los bloqueos se maneja normalmente de forma dinámica por el administrador de bloqueos, una parte del motor de base de datos, no a través de las aplicaciones.
SQL Server asigna bloqueos en diferentes niveles para optimizar el rendimiento, gestión de recursos, y la concurrencia. Bloqueos pueden ser asignados a los recursos como las filas, las páginas, índices, tablas y bases de datos. Dependiendo de los requerimientos, los bloqueos pueden ser asignados a más de un nivel, creando una jerarquía de bloqueos relacionados.
Modo de bloqueo | Descripción |
Compartido (S) | Se utiliza para operaciones de lectura que no cambian ni actualizan datos, como la instrucción SELECT. |
Actualizar (U) | Se utiliza en recursos que se pueden actualizar. Evita una forma común de interbloqueo que se produce cuando varias sesiones leen, bloquean y actualizan recursos. |
Exclusivo (X) | Se utiliza para operaciones de modificación de datos, como INSERT, UPDATE o DELETE. Garantiza que no puedan realizarse varias actualizaciones simultáneamente en el mismo recurso. |
Intención | Se utiliza para establecer una jerarquía de bloqueos. Los tipos de bloqueo de intención son: intención compartido (IS), intención exclusivo (IX) y compartido con intención exclusivo (SIX). |
Esquema | Se utiliza cuando se ejecuta una operación que depende del esquema de una tabla. Hay dos tipos de bloqueo de esquema: modificación del esquema (Sch-M) y modificación de estabilidad (Sch-S). |
Actualización masiva (BU) | Se utiliza cuando se copian datos de forma masiva en una tabla y se especifica la sugerencia TABLOCK. |
Intervalo de claves | Protege el intervalo de filas que lee una consulta cuando se utiliza el nivel de aislamiento de transacciones serializables. Garantiza que otras transacciones no puedan insertar filas que podrían incluirse como respuesta de las consultas de la transacción serializable si las consultas se volvieran a ejecutar. |
Entendiendo Deadlock y situaciones de bloqueo
Dado que las transacciones determinan en los niveles de aislamiento mantener bloqueos hasta que se complete la transacción, las transacciones pueden bloquearse entre sí y evitar completarse correctamente.
De manera predeterminada, las transacciones en SQL Server 2008 espera una cantidad indefinida de tiempo para que un recurso esté disponible a menos de SQL Server reconoce que una situación de bloqueo se ha producido. En una situación de bloqueo, dos operaciones son “bloquean” los recursos que cada una de las dos transacciones y ambas los requieren antes de su finalización.
Debido a esto, ninguna de transacción es capaz de completar con éxito. Basado en el costo estimado de SQL Server para revertir cada transacción, el administrador de bloqueos selecciona una "víctima" de la situación de punto muerto y se deshace la transacción, haciendo la emisión de un error 1205. Debido a este error no se intenta reiniciar la operación o dar un mensaje informativo a los usuarios, todos los errores de 1205 deben ser capturados y manejados apropiadamente.
Para gestionar los problemas de bloqueo además, los administradores de bases de datos se puede ajustar el tiempo de espera de consulta basado en el análisis de rendimiento. Esto se puede lograr a través de las propiedades avanzadas del servidor.
Para localizar las transacciones que se ven afectados por situaciones de punto muerto, puede utilizar SQL Server Profiler para producir un Extensible Markup Language (XML) la representación de una cadena de bloqueo de eventos, incluyendo el ID del proceso del sistema (SPID) de las transacciones involucradas en la situación de bloqueo.
Reportes para la comprensión sobre la situación de bloqueo
Hay muchas opciones para ver el estado de bloqueo dentro de su equipo con SQL Server.
Puede utilizar SQL para capturar la información de bloqueo y de bloqueo. Puede utilizar el Monitor de sistema que es parte de la consola de rendimiento (perfmon) para capturar las estadísticas sobre los tiempos de espera de bloqueo, bloqueos por segundo, y así sucesivamente. Usted puede utilizar el sys.dm_tran_locks vista de administración dinámica (DMV) para reunir información sobre los bloqueos en manos de las transacciones. Por último, puede utilizar el Monitor de actividad en SSMS para ver información sobre los procesos de bloqueo.
En muchos de estos informes, consulte el método de bloqueo y el recurso que está bloqueado. Es posible que vea bloqueos de los siguientes tipos de recursos:
- Identificador de fila (RID), un identificador de fila se utiliza para definir un bloqueo en una sola fila ubicado en un grupo
- KEY: La gama de llaves en un índice que se utiliza para definir un bloqueo en intervalos de clave
- PAGE: 8 kilobytes (KB) Página de tablas o índices
- EXTENT: Un grupo de ocho páginas contiguas de una tabla o índice
- HoBT: un grupo o un árbol de Índice equilibrado (B-tree)
- TABLE. Una tabla entera, compuesta por los datos y las páginas de índice
- FILE: Un archivo de base de datos
- APPLICATION: Un recurso especificado por la aplicación
- METADATA: Se utiliza para las bloqueos de metadatos
- ALLOCATION_UNIT: Una única unidad de distribución
- DATABASE: Una base de datos completa, incluyendo todos los archivos de datos
SQL Server 2008 incluye SQL Server Extended Events, como FindBlocker y lock_count, que puede ser usado en conjunción con los registros de sucesos de Windows, SQL Profiler o el Monitor de sistema.
Utilizando DBCC LOG:
SQL Server incluye la declaración LOG DBCC, que es una característica no documentada que devuelve información sobre la información contenida en el registro de transacciones actual. La sintaxis es la siguiente:
DBCC LOG (<databasename>, <output identifier>)
El identificador de salida puede ser cualquier de los siguientes niveles:
- Nivel 0: devuelve un mínimo de información, incluyendo el número de registro de secuencia actual (LSN), la operación, el contexto, identificación de la transacción, y la generación de bloqueos | registros
- Nivel 1: devuelve toda la información del nivel anterior, así como las banderas y la información de longitud de registro
- Nivel 2: devuelve toda la información del nivel anterior, así como el nombre del objeto, nombre del índice, identificador de página, y el ID de slot
- Nivel 3: Regresa un conjunto completo de información sobre la operación
- Nivel 4: Regresa un conjunto completo de información sobre la operación, así como un volcado hexadecimal de la fila de registro de transacciones actual
Los niveles de transacción siguientes pueden configurarse mediante el uso de la sintaxis SET TRANSACTION ISOLATION LEVEL:
- READ UNCOMMITTED: Las transacciones que se ejecutan en el nivel READ UNCOMMITTED no emiten bloqueos compartidos para impedir que otras transacciones modifiquen los datos leídos por la transacción actual. Las transacciones READ UNCOMMITTED tampoco se bloquean mediante bloqueos exclusivos que impedirían que la transacción actual leyese las filas modificadas pero no confirmadas por otras transacciones. Cuando se establece esta opción, es posible leer las modificaciones no confirmadas, denominadas lecturas de datos sucios. Los valores de los datos se pueden cambiar, y las filas pueden aparecer o desaparecer en el conjunto de datos antes de que finalice la transacción. Esta opción tiene el mismo efecto que establecer NOLOCK en todas las tablas y en todas las instrucciones SELECT de una transacción. Se trata del nivel de aislamiento menos restrictivo.
- READ COMMITTED: Especifica que las instrucciones no pueden leer datos que hayan sido modificados, pero no confirmados, por otras transacciones. Esto evita las lecturas de datos sucios. Otras transacciones pueden cambiar datos entre cada una de las instrucciones de la transacción actual, dando como resultado lecturas no repetibles o datos ficticios. Esta opción es la predeterminada para SQL Server.
- REPEATABLE READ: Especifica que las instrucciones no pueden leer datos que han sido modificados pero aún no confirmados por otras transacciones y que ninguna otra transacción puede modificar los datos leídos por la transacción actual hasta que ésta finalice. Se aplican bloqueos compartidos a todos los datos leídos por cada instrucción de la transacción, y se mantienen hasta que la transacción finaliza. De esta forma, se evita que otras transacciones modifiquen las filas que han sido leídas por la transacción actual. Otras transacciones pueden insertar filas nuevas que coincidan con las condiciones de búsqueda de las instrucciones emitidas por la transacción actual. Si la transacción actual vuelve a ejecutar la instrucción, recuperará las filas nuevas, dando como resultado lecturas ficticias. Debido a que los bloqueos compartidos se mantienen hasta el final de la transacción en lugar de liberarse al final de cada instrucción, la simultaneidad es inferior que en el nivel de aislamiento predeterminado READ COMMITTED. Utilice esta opción solamente cuando sea necesario.
- SNAPSHOT: a opción de base de datos ALLOW_SNAPSHOT_ISOLATION debe establecerse en ON para poder iniciar una transacción que utilice el nivel de aislamiento SNAPSHOT. Si una transacción que utiliza el nivel de aislamiento SNAPSHOT obtiene acceso a datos de varias bases de datos, será necesario establecer ALLOW_SNAPSHOT_ISOLATION en ON en cada una de ellas.Las transacciones SNAPSHOT no solicitan bloqueos al leer los datos, excepto cuando se recupera una base de datos. Las transacciones SNAPSHOT que leen datos no bloquean la escritura de datos de otras transacciones. Las transacciones que escriben datos no bloquean la lectura de datos de las transacciones SNAPSHOT.
- SERIALIZABLE: Se colocan bloqueos de intervalo en el intervalo de valores de clave que coincidan con las condiciones de búsqueda de cada instrucción ejecutada en una transacción. De esta manera, se impide que otras transacciones actualicen o inserten filas que satisfagan los requisitos de alguna de las instrucciones ejecutadas por la transacción actual. Especifica lo siguiente:
- Las instrucciones no pueden leer datos que hayan sido modificados, pero aún no confirmados, por otras transacciones.
- Ninguna otra transacción puede modificar los datos leídos por la transacción actual hasta que la transacción actual finalice.
- Otras transacciones no pueden insertar filas nuevas con valores de clave que pudieran estar incluidos en el intervalo de claves leído por las instrucciones de la transacción actual hasta que ésta finalice.
Una vez que haya determinado el nivel de aislamiento de transacción correspondiente, la sintaxis del comando es muy sencillo, como se muestra aquí:
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
[ ; ]
Una vez que el SET TRANSACTION ISOLATION LEVEL se ha ejecutado en una sesión, todas las transacciones dentro de este contexto el uso del nivel de aislamiento definido.
Resumen de la lección
- Una transacción es un conjunto de acciones que componen una unidad atómica de trabajo y debe tener éxito o fracasar en su conjunto
- De manera predeterminada, las transacciones implícitas no están habilitadas.Cuando las transacciones implícitas están habilitadas, varias declaraciones automáticamente comienza una transacción.El desarrollador debe ejecutar una sentencia COMMIT o ROLLBACK para completar la transacción.
- Transacciones explícitas comenzar con una instrucción BEGIN TRANSACTION y se han completado ya sea por un ROLLBACK TRANSACTION o COMMIT TRANSACTION.
- La emisión de un comando ROLLBACK cuando las transacciones se anidan deshace todas las transacciones a la declaración BEGIN TRANSACTION más externa, a pesar de las declaraciones emitidas anteriormente de confirmación para transacciones anidadas.
- SQL Server utiliza una variedad de modos de bloqueo, incluida la compartida(S), exclusivo (X), y la intención (IS, IX, SIX)) para gestionar la coherencia de datos, mientras que varias transacciones se procesan simultáneamente.
- SQL Server 2008 es compatible con la lectura no confirmada READ COMMITTED, REPEATABLE READ, Descripción, y los niveles de aislamiento SERIALIZABLE.
No hay comentarios:
Publicar un comentario