Enfoque pesimista de gestión de transacciones en SQL Server

Sergio Alcalde | 05 de febrero de 2009 | Añadir comentario

Más sobre: Documentación, SQL Server | Tags: bloqueos, concurrencia, SQL, T-SQL, transacciones

Diseñar nuestra aplicación, que va a soportar el acceso concurrente a datos, basándonos en un enfoque optimista de gestión de transacciones implica suponer que SQL Server va a administrar a la perfección las contiendas de bloqueos que se produzcan cuando nuestra aplicación esté en marcha.

Por un lado, resulta poco realista suponer que un programador o un usuario no pueda hacer nada que provoque explícitamente un bloqueo y, por el otro, tenemos aplicaciones cuyas exigencias nos obligan a determinar de forma explícita con qué lógica deben ser manejadas las acciones que realizamos sobre los datos… realmente existen muchas situaciones que dan lugar a que se produzcan un gran número de bloqueos en el servidor, lo que puede llegar a inutilizar nuestra aplicación si no ponemos cuidado a la hora de diseñar el acceso a la base de datos.

Un enfoque pesimista de gestión de transacciones consiste en diseñar de qué forma vamos a condicionar a nuestro sistema gestor de bases de datos SQL Server para que gestione la competición por el acceso a datos o contienda de bloqueo.

En SQL Server tenemos dos posibilidades, no excluyentes, de tomar el control, o al menos de condicionar el comportamiento, sobre cómo se va a manejar la contienda de bloqueo:

utilizando los niveles de aislamiento de transacciones que implementa SQL Server
utilizando sugerencias para nivel de aislamiento y granularidad de bloqueo

Utilización de los niveles de aislamiento de las transacciones

Los niveles de aislamiento determinan el grado en que los datos incoherentes de una transacción están protegidos frente a otras transacciones. En teoría, toda transacción debe estar completamente aislada de otras transacciones, pero en la realidad, por razones prácticas, esto puede no ser cierto siempre. El uso de un nivel u otro estará condicionado por la naturaleza de los procesos de tratamiento de datos de cada aplicación en particular.

Para establecer el grado de aislamiento de las transacciones durante toda una conexión, en SQL Server se utiliza la sentencia “set transaction isolation level”.

En un entorno multiusuario, pueden darse cuatro situaciones: actualización perdida, lecturas sucias, lecturas no repetibles, lecturas fantasma.

Actualización perdida: No proporciona ningún aislamiento. La última transacción en modificar el conjunto de datos prevalecerá y los cambios realizados por otras transacciones se perderán.
Lecturas sucias: Una transacción puede leer datos que están siendo modificados por otras transacciones. Estos datos son incoherentes porque las otras transacciones pueden decidir cancelar los cambios.
Lecturas no repetibles: En esta situación una transacción lee los mismos datos dos veces, pero entre una lectura y otra, otra transacción ha modificado los datos. Así, los datos obtenidos en la primera lectura serán distintos a los obtenidos en la segunda.
Lecturas fantasma: En esta situación, la transacción 1 lee los datos en un rango de claves del 1 al 8, existiendo las filas 1, 3 y 5. Una segunda transacción inserta en la misma tabla una fila con la clave 2. Cuando la transacción 1 realice una nueva consulta sobre dicho rango de claves se encontrará con una fila que antes no existía.

Idealmente, un SGBD debe proporcionar niveles de aislamiento para evitar todas estas situaciones. A veces, por razones de rendimiento y siempre condicionado por la naturaleza de los procesos que manejan la información, pueden permitirse algunas de estas situaciones.

ANSI define los siguientes cuatro estándares para el aislamiento de transacciones: lectura de datos no confirmados (N0), lectura de datos confirmados (N1), lectura repetible (N2) y serializable (N4). SQL Server proporciona soporte para todos estos niveles.

Lectura de datos no confirmados

Si se selecciona el modo de lecturas no confirmadas no se proporciona ningún aislamiento a las transacciones.

Una transacción que se ejecute con este nivel de aislamiento no será inmune a las lecturas sucias, lecturas no repetibles ni lecturas fantasma.

La instrucción T-SQL que selecciona este nivel de aislamiento es:

set transaction isolation level read uncommitted

Lectura de datos confirmados

Es el comportamiento predeterminado de SQL Server.

En este modo no se ejecutan inicios de transacciones implícitas. Durante la ejecución de una sentencia se establece un bloqueo de actualización sobre el recurso a modificar. En cuanto termine esta sentencia, se liberará el bloqueo.

En este nivel de aislamiento se protege a la transacción contra lecturas sucias, pero las transacciones no son inmunes a la pérdida de actualizaciones, las lecturas no repetibles ni a las lecturas fantasma.

set transaction isolation level read committed

Lectura repetible

SQL Server da inicio a una transacción de manera implícita al ejecutar una sentencia de modificación de datos, por lo que será necesaria una sentencia “commit” o “rollback” para terminarla. Este modo permite a las transacciones establecer bloqueos compartidos y exclusivos sobre los recursos a modificar.

Bajo este nivel de aislamiento, las transacciones están protegidas contra pérdida de atualizaciones, las lectruras sucias y las lecturas no repetibles. Sin embargo, dichas transacciones sí pueden sufrir problemas de lecturas fantasma.

set transaction isolation level repeatable read

Serializable. Serialización y bloqueo de rango de clave

Este modo es el más estricto, ofreciéndose aislamiento completo a las transacciones.

SQL Server da comienzo de manera implícita a una transacción, utilizando un bloqueo de rango de clave sobre las páginas índice, para asegurar aislamiento frente lecturas fantasma.

Bajo este nivel de aislamiento las transacciones utilizan bloqueos compartidos, exclusivos y de rango de clave para asegurar el aislamiento total frente a otras transacciones.

Si la operación es una búsqueda de rango, se establece un bloqueo de intención compartido sobre el rango de claves especificado en la cláusula “where”. Otras transacciones podrán leer ese rango, pero no podrán insertar ni borrar registros dentro del mismo.

Si la operación es de búsqueda o borrado de una fila no existente, SQL Server establece un bloqueo de intención compartido sobre la página de índice para el rango de claves comprendido en la sentencia de la operación.

Si la operación es de inserción, SQL Server establece un bloqueo de intención de inserción sobre la página de índice, sólo para la clave que está siendo insertada. Lo mismo ocurre si la operación es de borrado.

Dado que pueden llegar a bloquearse un elevado número de recursos, pueden existir problemas de baja concurrencia en las aplicaciones que establezcan este nivel de aislamiento.

set transaction isolation level serializable

Utilización de sugerencias para niveles de aislamiento y granularidad de bloqueos

Como se ha comentado en el apartado anterior, se puede establecer el nivel de aislamiento de las transacciones que se ejecuten en una conexión con la sentencia “set transaction isolation level”.

Esta sentencia T-SQL crea un nivel de global de aislamiento para la conexión, lo que proporciona un nivel de aislamiento homogéneo para la aplicación. Sin embargo, a veces es recomendable utilizar distintos niveles de aislamiento dependiendo de la naturaleza del proceso de tratamiento de los datos en cuestión, etc.

SQL Server permite especificar sugerencias de tablas en las cláusulas “select”, “delete”, “update” e “insert”, lo que permite sustituir el nivel de aislamiento definido para la conexión. Esta sustitución del nivel de aislamiento estará vigente mientras dure la transacción.

Aunque suele ser buena idea que sea el optimizador el que tome las decisiones de qué granularidad de bloqueo y nivel de aislamiento se debe utilizar, a veces resulta útil sugerir al gestor a utilizar una granularidad y niveles de aislamiento determinados.

Sugerencias de nivel de aislamiento de una transacción

“holdlock”: Dentro de una transacción, los bloqueos compartidos (los que se establecen al seleccionar una fila, por ejemplo) se liberan en cuanto la sentencia que mantiene el bloqueo se termina. Para mantener el bloqueo compartido durante toda la transacción se utiliza la cláusula “holdlock”. Esta cláusula es útil, por ejemplo, para actualizar el valor de un contador no “identity”.
```
declare @SEQNO int
begin transaction
select @SEQNO = isnull(SEQ#,0)+1
from SEQUENCE with holdlock
/*
En ausencia de holdlock el bloqueo compartido sería liberado
por lo que si otra transacción concurrente ejecutara la
misma sentencia obtendría el mismo número de secuencia
*/
update SEQUENCE
set SEQ# = @SEQNO
/*
Ahora puede hacerse lo que se desee con este número
de secuencia
*/
…
commit transaction
```
Hay que resaltar una cuestión que puede llevar a engaño. La ejecución del conjunto de sentencias anterior, impide que dos procesos ejecutándose concurrentemente acaben obteniendo el mismo número de secuencia. Sin embargo, no evita que se produzca una situación de interbloqueo con la consecuente terminación del proceso que lo ha producido. Esta situación se daría en el caso de que los dos procesos ejecutaran el “select” y luego ambos quisieran ejecutar el “update”. Sólo uno de ellos lo conseguiría y SQL Server terminaría el proceso que ha provocado el interbloqueo. Reescribiendo el código de la siguiente manera se evitaría esta situación:
```
declare @SEQNO int
begin transaction
update SEQUENCE
set @SEQNO = isnull(SEQ#,0)+1
from SEQUENCE
select @SEQNO
…
commit transaction
```
De esta forma se obtiene el bloqueo de actualización directamente y se evita la posibilidad de interbloqueo.
“nolock”: Puede usarse esta sugerencia para especificar que no se establezca ningún bloqueo compartido sobre el recurso y que se denieguen las solicitudes de bloqueos de actualización o exclusivos. Esta opción permitirá las lecturas sucias a otras transacciones. Es útil en los entornos de generación de informes donde los datos aproximados resulten aceptables.
“readcommitted”: Es equivalente a utilizar “set transaction isolation level” salvo, por supuesto, que sólo tiene efecto hasta que termine la transacción.
“readpast”: Esta sugerencia se aplica sólo a las sentencias “select” y permite que la consulta se salte filas sobre las que existan bloqueos. Dadas las restricciones a las que está sometida el uso de esta sugerencia, es prácticamente imposible implementarla en entornos OLTP.
“readuncommitted”: Equivalente a especificarlo con “set transaction isolation level”, salvo que sólo tiene efecto hasta que termine la transacción, y es igual a la sugerencia “nolock”.
“repeatableread”: Equivalente a usar “set transaction isolation level”, salvo que sólo tiene efecto hasta el fin de la transacción.
“serializable”: Equivalente a usar “set transaction isolation level”, salvo que sólo tiene efecto hasta que termine la transacción.

Sugerencias de granularidad de bloqueo

Quiero remarcar que no uso la palabra “sugerencia” por gusto: en SQL Server se definen así, como sugerencias, y es que el optimizador de consultas del gestor de base de datos a tenderá a nuestras sugerencias siempre y cuando no considere que perjudican al rendimiento del servidor.

“rowlock”: Fuerza al gestor de bloqueos a establecer un bloqueo a nivel de fila.
“paglock”: Fuerza al gestor a establecer un bloqueo a nivel de página.
“tablock”: Se usa para establecer un bloqueo a nivel de tabla.
“tablockx”: Especifica un bloqueo exclusivo sobre una tabla. Sólo es necesario si se pretenden realizar operaciones de mantenimiento de la tabla.
“updlock”: Esta opción es similar a “holdlock”. La diferencia es que “holdlock” establece un bloqueo compartido y “updlock” establece un bloqueo de actualización sobre los recursos mientras dure la transacción.

Si quieres leer más sobre gestión de bloqueos: “Gestión de bloqueos en SQL Server“.

Leer más sobre:

Gestión de transacciones en SQL Server (1/7)
Gestión de bloqueos en SQL Server (2/7)
Enfoques de control de transacciones en SQL Server (3/7)
Enfoque optimista de gestión de transacciones en SQL Server (4/7)
Enfoque pesimista de gestión de transacciones en SQL Server (5/7)
Enfoque mixto de gestión de transacciones en SQL Server (6/7)
Interbloqueos en SQL Server (7/7)

Más sobre: Documentación, SQL Server | Tags: bloqueos, concurrencia, SQL, T-SQL, transacciones