Revisamos el concepto de transacción, los tipos de bloqueos y su compatibilidad entre ellos para entrar en detalle con los modelos de aislamiento en SQL Server.
El objetivo del post que se desarrolla a continuación, es cubrir las dudas que surgen en cursos y seminarios que venimos impartiendo en los últimos años:
- ¿A qué viene inventar el concepto de aislamiento?
- ¿Qué modelo de aislamiento he de utilizar?
- ¿Qué diferencia existen entre los modelos existentes?
- ¿Qué implicaciones tiene los efectos colaterales de los modelos de aislamiento?
Cada modelo de negocio tiene necesidades distintas, pero te ayudamos a determinar qué modelo de aislamiento es necesario en cada caso y qué implicaciones tiene utilizar unos y otros.
Antes de entrar en materia hemos de fijar algunos conceptos que nos ayudarán a entender los modelos de aislamiento:
- Concepto de transacción y las propiedades ACID (Atomicidad, Consistencia, Aislamiento y durabilidad). El artículo se enfoca el aislamiento como herramienta vehicular para asegurar las propiedades ACID en una transacción.
- Modelos de bloqueos en SQL Server y algunas herramientas para detectarlos y operar con ellos.
- Compatibilidad entre los distintos tipos de bloqueos. En este punto es importante clarificar qué tipo de bloqueos se pueden establecer cuando ya tenemos establecido un bloqueo previo.
En el artículo empezamos por revisar el concepto de transacción y sus propiedades, revisión de los tipos de bloqueos y la compatibilidad entre ellos y, a partir de ahí, entrar en detalle con los modelos de aislamiento y las diferencias que podemos encontrar en ellos.
Gestión de Transacciones
Una transacción es la unidad lógica de trabajo en un motor relacional. Se asemeja a acciones cotidianas como, por ejemplo, la acción de comprar: cuando pagas dinero por algo pero no recibes objeto, la transacción se detiene hasta obtener el bien que se ha comprado. Por otro lado, la transacción puede verse interrumpida (bloqueada), por parte del vendedor, a la espera del pago. En escenarios de este tipo, todas las acciones han de completarse o la transacción no llega a completarse.
En el ámbito relacional, pasa algo parecido y se identifican una serie de requisitos que se han de cumplir para completar la transacción. A estas propiedades nos referiremos en el artículo, con el acrónimo ACID:
Las propiedades ACID son:
- Atomicidad (Atomicity). Asumamos que cada transacción es una unidad atómica de trabajo. Con esto, el éxito de las acciones de una transacción está condicionada al éxito de todas y cada una de las acciones relacionadas con dicha transacción.
Para mantener la atomicidad, SQL Server trata cada transacción individualmente y no permitirá la ejecución parcial. Por ejemplo, un UPDATE que actualiza 500 filas, no dará la transacción por terminada hasta que se actualicen las 500 filas. Si algo lo impide, SQL Server abortará el comando y revertirá la transacción.
- Consistencia (Consistency). Cada transacción, ya sea exitosa o no, deja la base de datos en un estado coherente en base a las restricciones definidas en el entorno, ya sean contraints de foreign keys, unicidad de constraints unique o cualquier restricción definida en la lógica transaccional de la base de datos.
- Aislamiento (Isolation). Cada transacción parece que ocurre aisladamente de otras transacciones con respecto a los cambios en la base de datos. El grado de aislamiento puede variar según el nivel aislamiento que se define en la transacción, y es en este aspecto donde centramos el alcance de este artículo.
SQL Server implementa el aislamiento transaccional mediante el bloqueo y el control de versiones de filas, evitando la interferencia con otras transacciones.
- Durabilidad (Durability). Cada transacción perdura a través de una interrupción del servicio. Cuando se restaura el servicio, todas las transacciones comprometidas se confirman y las transacciones no confirmadas se revierten.
¿Necesitas sacar más partido a tus datos para impulsar tu proyecto?
Acelera tus procesos de Business Analytics. Toma mejores decisiones, optimiza tu Datawarehouse y el proceso de generación de informes en tus proyectos de analítica con nuestra consultoría en Business Intelligence.
Bloqueo
Concepto de Bloqueo
Para preservar el aislamiento de las transacciones, SQL Server implementa un conjunto de protocolos de bloqueo. A el nivel básico, hay dos modos generales de bloqueo:
- Bloqueos compartidos: Se utilizan para sesiones que leen datos, es decir, para lectores
- Bloqueos exclusivos: Se utilizan para cambios en los datos, es decir, los escritores.
Cuando una sesión se propone cambiar datos, SQL Server intentará asegurar un bloqueo exclusivo en los objetos en cuestión. Estos bloqueos exclusivos siempre ocurren en el contexto de una transacción, incluso si solo está en el modo de confirmación automática y la sesión no inicia una transacción explícita.
Cuando una sesión tiene un bloqueo exclusivo en un objeto, ninguna otra transacción puede cambiar esos datos hasta que esta transacción sea confirmada (commit o rollback). Excepto en niveles especiales de aislamiento, otras sesiones ni siquiera pueden leer objetos con bloqueos exclusivos.
Compatibilidad de Bloqueo
Cuando una sesión está leyendo datos, de forma predeterminada SQL Server emitirá bloqueos compartidos. Dos o más sesiones pueden leer los mismos objetos, sin embargo, cuando una sesión bloquea de forma exclusiva un recurso (operaciones de modificación), esta preserva el aislamiento, evitando los accesos a dicho recurso. A el nivel básico, hay dos modos generales de bloqueo:
- Bloqueos compartidos. Se utilizan para sesiones que leen datos, es decir, para lectores.
- Bloqueos exclusivos. Se utilizan para cambios en los datos, es decir, los escritores.
Cuando una sesión se propone cambiar datos, SQL Server intentará asegurar un bloqueo exclusivo en los objetos en cuestión evitando que otra transacción, pueda cambiar esos datos hasta que esta transacción se confirme.
Herramientas para Seguimiento y Detección de Bloqueos
En el desarrollo de este post, se hará uso de dos herramientas que nos permitirán detectar e identificar bloqueos en la ejecución de transacciones.
Script
Para visualizar el bloqueo hacemos uso del script que se muestra a continuación:
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ‘ ‘), CHAR (13), ‘ ‘ ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID, BLOCKED, CAST (REPLICATE (‘0’, 4–LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL, BATCH
FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID) AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID, R.BLOCKED, CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL, R.BATCH
FROM #T AS R INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID
WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N‘ ‘ + REPLICATE (N‘| ‘, LEN (LEVEL)/4 – 1) + CASE WHEN (LEN(LEVEL)/4 – 1) = 0 THEN ‘HEAD – ‘ ELSE ‘|—— ‘ END
+ CAST (SPID AS NVARCHAR (10)) + N‘ ‘ + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO
Este script permitirá identificar los procesos bloqueados, pudiendo determinar qué sesiones provocan dichos bloqueos y qué sesiones están bloqueadas, mostrándolo en una estructura jerárquica como la que se muestra en la siguiente ilustración:
Sp_whoisactive es una herramienta pública y gratuita que nos permite consultar qué tipo de bloqueos se están produciendo y sobre qué objetos, entre otras capacidades.
Niveles de Aislamiento
Para asegurar el aislamiento de transacciones, el motor relacional dispone de las siguientes opciones (niveles de aislamiento):
Read Commited
Es el nivel de aislamiento predeterminado en SQL Server (Onprem) y consiste en establecer mecanismos para asegurar que las operaciones de lectura de datos, solo puedan obtener valores que se hayan confirmado (commit o rollback tran). Para ello, las operaciones SELECT intentarán adquirir bloqueos compartidos (S) e impedirán (bloqueo) el acceso a objetos con bloqueos exclusivos relacionados con modificación de datos.
En el siguiente ejemplo, se aprecia el comportamiento del tipo de aislamiento READ COMMITED:
Read Commited Snapshot Isolation (RCSI)
El nivel de aislamiento READ COMMITTED SNAPSHOT es, en realidad, una modificación de los mecanismos para implementar los requerimientos de READ COMMITTED (posibilitar solo lecturas confirmadas).
Este nivel de aislamiento tiene las siguientes características:
– Utiliza tempdb para almacenar versiones originales de datos. Estas versiones solo se almacenan mientras sean necesarias para permitir a los lectores (es decir, sentencias SELECT) leer los datos en su estado original. Como resultado, Las sentencias SELECT ya no necesitan bloqueos compartidos en el recurso subyacente mientras solo leyendo (originalmente) datos comprometidos.
– Es una opción que se establece a nivel de base de datos (ALTER DATABASE TSQLV5 SET READ_COMMITTED_SNAPSHOT ON;)
– Es el nivel de aislamiento predeterminado para Windows Azure SQL Database.
– A diferencia que READ COMMITED, no se establece bloqueo compartido y, por tanto, se posibilita la modificación de los datos que se están consultando.
A continuación se muestra un ejemplo del comportamiento del nivel de aislamiento RCSI.
A nivel de base de datos, hemos de ejecutar la siguiente instrucción para posibilitar este tipo de aislamiento que se hace a nivel de base de datos:
ALTER DATABASE TSQLV5 SET READ_COMMITTED_SNAPSHOT ON;
Read Uncommited
A diferencia de READ COMMITED, READ UNCOMMITED no establece un bloqueo compartido para operaciones de lectura de datos, por tanto, permite a los lectores leer a pesar de la existencia de bloqueos sobre los datos a consultar. Sin embargo, los resultados de una sentencia SELECT podrían leer datos no confirmados que se cambiaron durante una transacción y luego se volvió a su estado inicial. A esto se le llama lectura sucia.
A continuación, se muestra un ejemplo con READ UNCOMMITED e invitamos al lector que haga comparaciones con el comportamiento de READ COMMITED en el ejemplo mostrado en el apartado anterior.
Lectura sucia
Una lectura sucia ocurre cuando una transacción puede leer datos de una fila que ha sido modificada por otra transacción en ejecución y que aún no ha sido confirmada (Commit o rollback).
Repeatable Read
Con este tipo de aislamiento, se establecen los mecanismos para asegurar que la totalidad de los datos consultados en una transacción, no se vean afectados por modificaciones de transacciones externas, impidiendo la lectura de datos que están siendo modificados (y no conformados) por otras transacciones.
Para ello, se establecen 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, evitando que otras transacciones modifiquen las filas que han sido leídas por la transacción actual.
A diferencia que ocurría con el modelo de aislamiento READ COMMITED, los bloqueos compartidos se mantienen hasta el final de la transacción en lugar de liberarse al final de cada instrucción.
A continuación se muestra el comportamiento con el modelo de aislamiento REPEATABLE READ.
En la siguiente tabla, identificamos las filas sobre las que vamos a actuar:
SELECT productid, productname,unitprice
FROM Production.Products
WHERE unitprice = 18.00;
Concepto de Lectura Repetible
En el siguiente ejemplo, vemos de forma clara el concepto de lectura repetible en una transacción con ISOLATION LEVEL REPEATABLE. Mientras no se confirme la transacción de la conexión 1, vemos los mismos valores para productid = 2, pese a que se han modificado en el transcurso de dicha transacción.
Filas de Fantasmas con Nivel de Aislamiento READ REPEATABLE
Concepto de lectura fantasma
Una lectura fantasma ocurre cuando, en el curso de una transacción, el número de filas devuelto en dos lecturas consecutivas, es distinto.
Como veremos en los siguientes apartados, las lecturas fantasmas, son evitadas por el nivel de aislamiento READ SERIALIZABLE, estableciendo bloqueos tipo KEY-RANGE.
El nivel de aislamiento READ REPEATABLE permite la lectura de filas fantasma, debido a que, en dicho nivel de aislamiento, otras transacciones pueden insertar filas nuevas que coincidan con las condiciones de búsqueda de la transacción actual.
En el siguiente ejemplo se muestra la posibilidad de insertar nuevas filas en la tupla que se consulta.
Serializable Read
Este nivel de aislamiento es el nivel más estricto ya que, todas las lecturas son repetibles y no se permiten nuevas filas en las tablas subyacentes que satisfaga las condiciones de búsqueda de las declaraciones SELECT en la transacción.
Como se analiza en el siguiente ejemplo, para evitar la inserción en datos que se están leyendo en la transacción actual, se establecen bloqueos tipo KEY-RANGE.
Concepto de bloqueo KEY RANGE
Protege el rango de filas leídas por una consulta cuando se usa el nivel de aislamiento serializable, evitando que otras transacciones inserten filas en las tuplas recuperadas por la transacción actual.
Snapshot
Al igual que en READ COMMITTED SNAPSHOT, este nivel de aislamiento usa el control de versiones de filas en TEMPDB, pero en este caso, dicho control de versiones, se mantiene en el trascurso de la transacción completa.
A continuación, se muestra un ejemplo del comportamiento de transacciones concurrentes con el nivel de aislamiento SNAPSHOT.
ALTER DATABASE TSQLV5 SET ALLOW_SNAPSHOT_ISOLATION ON;
Conflictos en ISOLATION LEVEL SNAPSHOT
Uno de los aspectos a tener en cuenta en este nivel de aislamiento, es la imposibilidad de ejecutar, de forma concurrente, operaciones de modificación en los datos sobre los que se está actuando en la transacción.
A continuación, se muestra un ejemplo del comportamiento de ISOLATION LEVEL SNAPSHOT en este tipo de escenarios.
Conclusiones
En este artículo se analizan las opciones de aislamiento disponibles en SQL Server y los conceptos relacionados:
- Concepto de transacción.
- Concepto de bloqueo.
- Conceptos relacionados con los efectos de la aplicación de los distintos niveles de aislamiento: lecturas sucias, lecturas repetibles y filas fantasmas.
- Niveles de aislamiento: Read committed, read committed snapshot, read uncommitted, read repeatable, read serializable y snapshot.
A continuación, se muestra a modo de resumen una comparativa entre las distintas opciones de aislamiento en SQL Server:
¡Has llegado al final! Parece que te ha gustado nuestro post sobre SQL Server
Recuerda, si tienes dudas o algún problema en concreto que te impide avanzar en la optimización de tus bases de datos y tu proyecto de SQL, no dudes en contactar con nosotros. No esperes más para acelerar tus procesos de Business Analytics y tomar mejores decisiones.
