Control de transacciones y bloqueos en SQL Server

Agradezco a Sergio Alcalde su labor en una serie de Blogs que me han permitido aprender sobre este espinoso tema.

 

Gestión de transacciones en SQL Server

Conocer la gestión de transacciones que realiza SQL Server, qué son y cómo funcionan, es la clave para desarrollar aplicaciones eficaces y eficientes que se conectan a un gestor de bases de datos (SGBD) para consultar y modificar datos, con usuarios accediendo concurrentemente para realizar acciones de distinta naturaleza sobre los mismos (no es lo mismo consultar información que actualizarla) y teniendo que gestionar acciones de actualización que afectan a varios datos a la vez.

Procesamiento de transacciones

Una transacción es una unidad lógica de trabajo que tiene ciertas propiedades conocidas como propiedades ACID: atomicidad, coherencia, aislamiento y permanencia.

Es responsabilidad del sistema gestor de bases de datos el asegurar que se cumplan estas propiedades. Sin embargo, el programador dispone de un cierto grado de control sobre la forma en que SQL Server gestiona algunas de estas propiedades.

Existen tres formas de llevar a cabo este procesamiento: autoconfirmación, explícita e implícita (que no voy a abordar en este post pero que os animo a comentar si lo creéis interesante).

Transacciones y bloqueos

SQL Server realiza y mantiene una serie de bloqueos durante la transacción. Existen dos formas de modificar el tratamiento de los bloqueos.

La primera de ellas consiste en introducir cláusulas en las sentencias “select”. Estas cláusulas modificarán el tratamiento de los bloqueos de las unidades afectadas (filas, tablas, páginas; la granularidad también es seleccionable y se tratará más adelante) por la sentencia y se mantendrán hasta la finalización de la transacción.

La segunda, se mantiene durante toda la sesión. Para modificar la forma en que una sentencia “select” gestiona una transacción y sus bloqueos se utiliza la sentencia “set transaction isolation level” que tiene las siguientes opciones o niveles de aislamiento, mutuamente excluyentes:

  • “read committed”: Es el comportamiento predeterminado. La transacción mantiene bloqueos compartidos durante el tiempo que dure la lectura. La transacción puede encontrarse con lecturas no repetibles o datos fantasma dado que el bloqueo no impide a otras transacciones modificar los datos que se han leído.
  • “read uncommitted”: Con este nivel de aislamiento una transacción puede leer datos sucios de otras transacciones. Es el nivel de aislamiento menos restrictivo. Esta opción tiene el mismo efecto que usar la opción “nolock” en una sentencia “select”.
  • “repeatable read”: A medida que se leen datos se establecen bloqueos de forma que otra transacción no pueda modificar datos leídos. Este nivel de aislamiento es más restrictivo y puede hacer que otras transacciones se bloqueen. No evita que se puedan añadir nuevas filas.
  • “snapshot”: Con este nivel de aislamiento las consultas de la transacción en curso sólo leerán los datos confirmados antes del comienzo de la misma. Dado que la transacción en curso trabaja con una instantánea de los datos, capturada en el momento de inicio de la transacción, las lecturas de datos de esta transacción no solicitan bloqueos y otras transacciones que realicen operaciones de escritura no bloquearán a ésta.
  • “serializable”: Nivel de aislamiento más restrictivo ya que aplica un bloqueo al rango de datos leídos. Proporciona, por tanto, protección ante lectura de filas fantasma.

Supervisión de transacciones

La supervisión de transacciones se apoya en el registro de transacciones. Para llevar la cuenta del número de transacciones abiertas, SQL Server utiliza una variable global de sesión denominada “@@trancount”.

Codificación de transacciones eficientes

Las transacciones tienen un efecto negativo en la concurrencia de acceso a datos. Para reducir la contienda de bloqueo sobre los recursos, las transacciones deben ser tan cortas y eficientes como sea posible. A continuación se enumeran algunas reglas que se deben observar al programar transacciones:

  • No devolver conjuntos de resultados dentro de una transacción. Dicha acción prolonga la transacción innecesariamente. Se debe realizar la recuperación y análisis de los datos fuera de la transacción.
  • Se debe evitar, en la medida de lo posible, pedir al usuario una respuesta cuando se haya iniciado una transacción. En caso de error se debe cerrar primero la transacción y después mostrar el mensaje de error.
  • La transacción debe ser lo más corta posible. Se debe abrir justo en el momento en el que se deseen realizar las modificaciones y cerrarla justo después de haberlas realizado.
  • Iniciar y terminar transacciones es un proceso que necesita una gran cantidad de trabajo por parte del servidor, por ello se deben abrir transacciones únicamente cuando sea necesario.

Conexiones acopladas

Como se ha comentado anteriormente, las transacciones tienen un efecto negativo en la concurrencia al acceso de datos.

Si es necesario implementar una transacción de larga duración manteniendo, por tanto, bloqueos exclusivos, y que continúe siendo posible algún tipo de procesamiento operativo, las conexiones acopladas o enlazadas pueden ser una solución. Existen dos tipos de conexiones acopladas: locales y distribuidas:

Las conexiones acopladas locales son conexiones dentro de un único servidor que comparten un espacio de transacciones.

Las conexiones acopladas distribuidas hacen uso del coordinador de transacciones distribuidas para que se comparta el mismo espacio de transacciones entre conexiones procedentes de más de un servidor.

Las conexiones enlazadas son útiles al desarrollar procedimientos almacenados extendidos que deben ejecutar instrucciones de Transact-SQL en nombre del proceso que las llama. Pasar el proceso que realiza la llamada en un testigo enlazado como un parámetro del procedimiento almacenado extendido permite al procedimiento combinar el espacio de transacciones del proceso que realiza la llamada y, por ello, integrar el procedimiento almacenado extendido con el proceso que realiza la llamada.

Se puede utilizar conexiones enlazadas para desarrollar aplicaciones en tres estratos en las que la lógica comercial se representa en programas independientes que funcionan en colaboración en una sola transacción comercial.

El ejemplo siguiente de conexiones enlazadas ilustra cómo pueden tener acceso dos conexiones a la misma transacción: Un cliente decide adquirir un producto a unos grandes almacenes locales. El vendedor obtiene acceso al sistema de transacciones de ventas que inserta una fila en la tabla de transacciones de ventas e incluye un número de autorización de tarjeta de crédito. Se realizan dos conexiones con el mismo servidor, la conexión C1 y la conexión C2. C1 comienza una transacción que agrega una fila de venta de productos a la tabla de ventas. Se debe agregar un número de autorización de tarjeta de crédito a la nueva fila de transacciones de ventas. Durante el proceso de autorización de la tarjeta de crédito, el procedimiento almacenado extendido crea la conexión C2 para conectar mediante la línea telefónica con la compañía de tarjetas de crédito y modifica la fila de transacciones de ventas con el número de autorización de la tarjeta de crédito. Solamente mediante la utilización de conexiones enlazadas, ambas conexiones pueden tener acceso a la misma fila sin que se produzcan conflictos de bloqueo.

Existe una importante desventaja de las transacciones acopladas: el procesamiento secuenciado. Sólo una de entre todas las conexiones acopladas puede estar realizando una operación. Cualquier intento de efectuar una tarea mientras que la anterior operación está teniendo lugar dará como resultado un error, que debe detectarse para su posterior tratamiento.

Transacciones distribuidas

Dada la creciente utilización de los sistemas distribuidos, existe la necesidad de acceder y modificar datos distribuidos.

Es necesaria una forma de asegurar que la transacción distribuida opere de la misma forma que lo haría una transacción local y que respete las propiedades ACID de cualquier transacción.

SQL Server proporciona las sentencias “begin distributed transaction” para comenzar una transacción distribuida.

El manejo de estas transacciones se realiza mediante el protocolo de confirmación en dos fases.

 

Gestión de bloqueos en SQL Server

Hemos comentado el funcionamiento de las transacciones en este gestor de base de datos (SGBD). Sin embargo, para acabar de comprender su funcionamiento debemos conocer cómo se manejan los bloqueos en SQL Server. El correcto funcionamiento y la eficiencia de nuestra aplicación que maneja usuarios concurrentes accediendo a nuestra base de datos dependen de la configuración de SQL Server y de la programación de la aplicación, así que, tanto para una cosa como para la otra, debemos comprender y manejar estos conceptos.

Bloqueos y rendimiento

Los bloqueos son los encargados de hacer que las transacciones tengan la propiedad de aislamiento. La realización de bloqueos en un entorno concurrente proporciona aislamiento en los siguientes casos:

  • Múltiples transacciones intentan modificar la misma fila.
  • Datos no confirmados por una transacción son leídos por otras transacciones. Esto se conoce como lecturas sucias.
  • Los datos leídos con la misma cláusula “select” durante varias etapas de la transacción dan lugar a valores diferentes con el mismo conjunto de datos. Conocido como lecturas no repetibles.
  • Los datos leídos con la misma cláusula “select” durante varias etapas de la transacción dan lugar a diferentes conjuntos de datos. Es lo que se conoce como “lecturas fantasma”.

El establecimiento de los bloqueos es automático en SQL Server, no teniéndose que preocupar, por tanto, el programador directamente por ellos, aunque debe tener en cuenta qué tipo de bloqueos establece cada operación atendiendo a las cláusulas incluidas, etc., para minimizar los interbloqueos.

Recursos de los bloqueos. Granularidad del bloqueo

El establecimiento de bloqueos es automático en SQL Server. El gestor de bloqueos es el encargado de decidir el tipo y la granularidad del bloqueo.

Los bloqueos se pueden producir tanto a nivel de página de datos como a nivel de página de índice. El gestor de bloqueo puede decidir bloquear los recursos: tabla, extensión, página, fila y rango de clave.

La granularidad del bloqueo incide de forma directa sobre la concurrencia y el rendimiento del sistema. El optimizador de consultas decide la granularidad del bloqueo automáticamente. No obstante, SQL Server proporciona extensiones de T-SQL que proporcionan un mecanismo para controlar la granuralidad de los bloqueos.

Tipos de bloqueo en SQL Server

El procedimiento almacenado “sp_lock” proporciona los bloqueos activos en el sistema gestor de bases de datos. También ofrecen información sobre bloqueos el administrador corporativo, el monitor de rendimiento, el administrador de memoria y el objeto de SQL Server bloqueos.

Dependiendo del tipo de transacción, el gestor de bloqueos establece distintos tipos de bloqueos.

Bloqueos compartidos

Se establecen para operaciones de sólo lectura, por ejemplo “select”.

Varias transacciones pueden mantener varios bloqueos compartidos simultáneamente sobre el mismo recurso ya que dichas transacciones no interfieren entre sí.

Si una transacción intenta modificar un dato de un recurso sobre el que se ha establecido un bloqueo compartido, se bloqueará hasta que todos los bloqueos compartidos se liberen.

Un bloqueo compartido se libera en cuanto se finaliza la operación de lectura de datos, es decir, no permanece a lo largo de la transacción. Existen cláusulas de las que se hablará más adelante para modificar este comportamiento.

Bloqueos de actualización

Los bloqueos de actualización se usan para instrucciones que modifican datos.

Cuando una transacción intenta actualizar una fila primero ha de leerla para asegurarse de que la fila en cuestión es realmente la que se quiere modificar. En este momento establece un bloqueo compartido. Una vez se ha asegurado que es la fila correcta procederá a modificarla, para lo que necesita establecer un bloqueo exclusivo que será liberado al terminar la transacción. Los bloqueos de actualización se emplean como bloqueo intermedio entre los dos anteriores para evitar interbloqueos.

Bloqueos exclusivos

Se concede un bloqueo de este tipo a una transacción cuando ésta está lista para modificar los datos. Un bloqueo exclusivo sobre un recurso asegura que ninguna otra transacción pueda interferir las acciones llevadas a cabo por la primera transacción sobre los recursos bloqueados.

SQL Server libera el bloqueo al finalizar la transacción.

Bloqueos de intención

El bloqueo de intención es un mecanismo que utilizan las transacciones para declarar su intención de obtener un bloqueo compartido, de actualización o exclusivo sobre un recurso.

No establece ningún bloqueo en si, excepto por el hecho de que otra transacción no podrá adquirir un bloqueo de actualización sobre un recurso sobre el que se haya establecido un bloqueo de intención.

Bloqueos de esquema

Se utilizan para mantener la integridad estructural de las tablas SQL Server. A diferencia de otros bloqueos, que proporcionan aislamiento para los datos, los bloqueos de esquema proporcionan aislamiento para el esquema de objetos de la base de datos.

Compatibilidad de bloqueos

En la siguiente lista se muestra la compatibilidad entre los distintos tipos de bloqueo. Para cada bloqueo que puede llegar a estar activo sobre una conexión al sistema gestor de bases de datos SQL Server se lista qué tipos de bloqueo pueden solicitarse concurrentemente (por ejemplo, si existe un bloqueo de modificación de esquema no puede solicitarse ningún otro tipo de bloqueo, tal y como muestra el último elemento de esta lista).

  • De intención compartido: de intención compartido, compartido, de actualización, de intención exclusivo, compartido de intención exclusivo y de estabilidad de esquema.
  • Compartido: de intención compartido, compartido, de actualización y de estabilidad de esquema.
  • De actualización: de intención compartido, compartido y de estabilidad de esquema.
  • De intención exclusivo: de intención compartido, de intención exclusivo y de estabilidad de esquema.
  • Compartido de actualización exclusivo: de intención compartido y de estabilidad de esquema.
  • Exclusivo: estabilidad de esquema.
  • Estabilidad de esquema: de intención compartido, compartido, de actualización, de intención exclusivo, compartido de intención exclusivo, exclusivo y de estabilidad de esquema.
  • Modificación de esquema: ningún bloqueo es compatible con el bloque de modificación de esquema.

Bloqueos de índices

De forma similar a los bloqueos de páginas de datos, SQL Server gestiona los bloqueos de las páginas de índices internamente.

Con el comando “sp_indexoption” se puede modificar el gestor permitiendo o no bloqueos a nivel de filas.

Se recomienda no realizar modificaciones sobre el comportamiento por defecto del gestor de bloqueos en lo referente a las páginas de índices ya que suele hacer buenas elecciones.

Enfoque optimista de gestión de transacciones en SQL Server

La eficiencia de una aplicación multiusuario (usuarios concurrentes de acceso a bases de datos, en nuestro caso) que trabaje contra SQL Server se ve condicionada por ciertos factores entre los que se encuentran el enfoque de control de transacciones que adoptemos.

Como se introducía en el post anterior de esta saga “Enfoques de control de transacciones en SQL Server“,existen dos enfoques básicos: enfoque optimista y enfoque pesimista. Del enfoque que seleccionemos y de lo hábiles que seamos observando ciertas sencillas reglas en cada uno de ellos dependerá la eficiencia de nuestra aplicación.

Un bloqueo optimista supone que no se va a hacer nada en el código de la aplicación que imponga explícitamente bloqueos de los recursos cuando se esté trabajando con ellos. En lugar de hacer esto, se confía en que el gestor de bases de datos (Microsoft SQL Server, por ejemplo) se encargue de hacerlo mientras el programador únicamente se debe centrar en la lógica de la aplicación.

Una vez hemos decidio que vamos a dejar que SQL Server gestione las transacciones por nosotros, y pese a que hemos descargado sobre el gestor de bases de datos en gran medida la responsaibilidad de hacer que nuestra aplicación sea eficiente, aún hay ciertas cosas que como buenos programadores debemos tener en cuenta.

Para implementar un bloqueo optimista en una aplicación sin que ésta se detenga bruscamente cuando exista un número excesivo de bloqueos en el servidor es preciso observar ciertas reglas sencillas:

  • Se debe minimizar el tiempo de duración de una transacción.
  • El código de la aplicación debe asegurar que las actualizaciones se realicen sobre un registro concreto, en lugar de mantener el bloqueo mientras el usuario está examinado los datos.
  • Asegurar que todos los códigos de la aplicación actualizan y seleccionan tablas en el mismo orden. Esto evitará que se produzcan los bloqueos permanentes.

Bloqueo optimista utilizando “timestamp”

SQL Server proporciona un tipo especial de datos denominado “timestamp”. El valor de una columna de este tipo es generado automáticamente cada vez que se almacena una fila con “insert” o “update”. Es simplemente un contador que cambia de manera monótona con cada actualización o inserción.
El propósito, para nuestro caso en particular, del tipo de datos “timestamp” consiste en servir como número de versión para los esquemas de bloqueo optimista.
Para poder emplear un bloqueo optimista basado en este tipo de datos, deben cumplirse las siguientes dos condiciones:

  • La tabla debe tener una clave primaria.
  • La tabla debe tener una columna de tipo “timestamp”.

El cliente lee la fila con el valor actual de la columna de marca temporal, pero no mantiene ningún bloqueo. En algún momento posterior, cuando el cliente quiere actualizar la fila, debe asegurarse de que ningún otro cliente haya cambiado la misma fila mientras tanto (puesto que no hay bloqueos, es responsabilidad del cliente el asegurarse de que los cambios hechos por otros clientes sean preservados). El cliente prepara de una forma especial el “update”, utilizando una columna “timestamp” como marcador de versión:

update TABLA

set COLUMNA_CAMBIADA = NUEVO_VALOR

where COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA and

timestamp = NUM_VERSION_OLD

Puesto que la cláusula “where” incluye la clave primaria, sólo se verá afectada una fila como máximo. Si algún otro cliente ha cambiado la fila, la segunda parte del “where” fallará, dándose como resultado que el “update” ha actualizado cero filas, como indicación de fallo de bloqueo. El cliente puede entonces elegir entre volver a leer los datos o efectuar cualquier otro procedimiento de recuperación que se considere oportuno.

Existen varias ineficiencias asociadas a esta forma de trabajo que se expondrán en el siguiente apartado. Por ello, si se va a utilizar un enfoque optimista basado en una marca de tiempo, se recomienda que dicha marca esté basada en un tipo de datos “int”.

Bloqueo optimista utilizando “int”

La única diferencia con respecto al método anterior es que, puesto que el servidor no va a actualizar de forma automática el valor de esta columna, será el cliente el encargado de hacerlo.

La sentencia “update” tendrá la forma:

update TABLA

set COLUMNA_CAMBIADA = NUEVO_VALOR,

NUM_VERSION = NUM_VERSION_OLD+1

where COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA and

NUM_VERSION = NUM_VERSION_OLD

Las diferencias con respecto al método anterior son las siguientes:

  • El tipo de datos “int” ocupa sólo cuatro bytes mientras que el tipo de datos “timestamp” ocupa ocho.
  • Al utilizar el tipo de datos “int” el cliente debe añadir la actualización de la marca en las sentencias “insert” y “update”, además de incluir el tratamiento en la cláusula “where”.
  • Si se usa el tipo de datos “timestamp”, después de hacer un “update” será necesario que el cliente realice un nuevo “select” para obtener la nueva marca de tiempo, si es que quiere continuar trabajando con los mismos datos.

update TABLA

set COLUMNA_CAMBIADA = NUEVO_VALOR

where COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA and

timestamp = NUM_VERSION_OLD

 

/*

Si se quiere seguir trabajando con la misma fila, será necesario

conocer el nuevo valor de la columna timestamp ya que ha

sido asignado automáticamente por el servidor

*/

 

select timestamp

from TABLA

where COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA

En el caso de usar una marca basada en un tipo de datos “int”, el cliente sabe después del “update” el nuevo valor de la marca, por lo que no tendrá que realizar una consulta para seguir trabajando con los mismos datos.

update TABLA

set COLUMNA_CAMBIADA = NUEVO_VALOR,

NUM_VERSION = NUM_VERSION_OLD+1

where COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA and

NUM_VERSION = NUM_VERSION_OLD

 

/*

Aquí el cliente sabe cuál es el nuevo valor de la columna

NUM_VERSION, que es el que tenía almacenado + 1

*/

En la mayoría de los casos, la elección de este enfoque de control de transacciones es acertada. Sin embargo, existen ciertas situaciones donde el número de usuarios que acceden concurrentemente a la base de datos es elevado, las transacciones son largas y un largo etcétera en los que será conveniente evaluar la idoneidad de apostar por un enfoque pesimista de gestión de transacciones.

 

 

 

Enfoque pesimista de gestión de transacciones en SQL Server

Diseñar nuestra aplicación, que va a soportar el acceso concurrente a datos, basándonos en un enfoque optimista de gestión de transaccionesimplica 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.

 

Enfoque mixto de gestión de transacciones en SQL Server

Cuando los requisitos de eficiencia de una aplicación en la que se produce acceso concurrente a datos sobre SQL Server son muy estrictos, no podemos basar el diseño de nuestra aplicación ni en un enfoque optimista de gestión de transacciones ni en un enfoque pesimista de gestión de transacciones ya que corremos el riesgo de no satisfacer dichos requisitos.

Además, aunque en apariencia estos dos enfoques de control de transacciones expuestos con anterioridad cubren las necesidades de cualquier aplicación, esto no es así dado que las técnicas empleadas para implementar uno y otro enfoque presentan ciertas carencias que obligan a diseñar otras formas de trabajo.

En cuanto al enfoque optimista, esas carencias se resumen en el siguiente punto:

  • La carencia principal reside en el hecho de que puede darse que dos usuarios seleccionen los mismos datos para su posterior modificación. Ambos usuarios podrán realizar todas las modificaciones que quieran, pero sólo uno de ellos conseguirá completar la operación con éxito, dado que el mecanismo implementado por estos enfoques optimistas impedirá que se pierdan modificaciones. Si el mantenimiento afectaba a un elevado volumen da datos, el usuario que no consiga completar la actualización de los datos habrá perdido mucho tiempo de trabajo.
  • Existe la necesidad de implementar la gestión de tratamiento de carreras de actualización de datos.

Las técnicas de enfoque optimista funcionan bien si el número de tablas a manejar es pequeño. Sin embargo, esta técnica complica el desarrollo de aquellos mantenimientos que manejen un elevado número de tablas. En estos casos debe estudiarse la posibilidad de utilizar un enfoque pesimista del control de transacciones, siempre y cuando esta decisión no genere nuevos problemas.

En cuanto al enfoque pesimista, las carencias se resumen en los siguientes puntos:

  • La elección en cada caso del nivel de aislamiento de una transacción o una conexión y de la granularidad del bloqueo es una tarea extremadamente complicada dado el elevado número de factores y combinaciones que pueden darse. Una elección no adecuada puede hacer que la eficiencia del servidor, y por tanto de la aplicación, se vea seriamente afectada.
  • Existe la necesidad de implementar el tratamiento de interbloqueos.
  • Por otro lado, existe el mismo problema que había con el enfoque optimista referente a la actualización simultánea de los mismos datos por procesos distintos, con la particularidad de que ahora el tratamiento de interbloqueos será diferente.
  • Finalmente, existe un problema relacionado con el carácter indefinido que tienen los bloqueos sobre los datos. Con un enfoque pesimista podría darse la situación de que un usuario bloqueara los datos para su posterior modificación y que, en el caso extremo, nunca liberara los recursos bloqueados. Esta situación que parece imposible es realmente usual.

Los siguientes esquemas de trabajo propuestos, basados en que cada una de las copias de nuestra aplicación respete una serie de reglas a la hora de acceder a los datos, tratan de cubrir estas carencias. Cabe resaltar que no son excluyentes con respecto a los métodos expuestos anteriormente, necesitándose en algunos casos dichos métodos para asegurar el correcto funcionamiento y mejorar la eficiencia de estos últimos.

Los dos métodos están basados en añadir una columna a las tablas que indique si la fila en cuestión está bloqueada o no. La elección de qué tablas son las que deben incluir esta columna depende de cada modelo de datos y de las tablas que se vean involucradas en cada mantenimiento.

Bloqueo pesimista utilizando “flag + plan de contingencia”

En las tablas en las que se quiera controlar el acceso concurrente se introducirá una columna “IT_EN_USO”. En una fila que haya sido seleccionada por un proceso de modificación, el valor de dicha columna será 1, y valdrá 0 en cualquier otro caso.

Un proceso que quiera modificar los datos de una fila, sólo podrá hacerlo si “IT_EN_USO” vale 0. Para ocupar el recurso, pondrá el valor 1 en dicha columna.

Si un mismo proceso necesita modificar varias filas, de la misma tabla o de varias, deberá obtener todos los recursos a modificar o no ocupar ninguno. En caso de no poder ocupar todos los recursos deberá liberar aquellos que ya haya ocupado y se mostrará un mensaje al usuario que le invite a intentarlo más tarde.

Tras finalizar la modificación de los datos, el proceso que los ha modificado será el encargado de liberar los recursos poniendo el valor 0 en la columna “IT_EN_USO” en aquellas filas que corresponda.

Dentro de la utilización de este enfoque, pueden darse mantenimientos que necesiten la interacción con el usuario (para la recogida de los nuevos datos) y mantenimientos que no la necesiten (actualizaciones automáticas, por ejemplo).

Para mantenimientos en los que es necesaria la intervención del usuario, el esquema de trabajo es el siguiente:

(*

* —————————————————————-

* function OcuparTodosRecursos (Cod) : boolean;

* Cometido..: Ocupa todos los recursos que se requieren para

*             realizar las posteriores modificaciones.

*             Enfoque optimista.

* —————————————————————-

*)

function OcuparTodosRecursos (Cod) : boolean;

begin

TodosLibres:=true;

strSQL:=’select CD, IT_EN_USO ‘+

‘from TABLA ‘+

‘where CD = ‘+Cod;

EjecutarSQL(strSQL);

while not eof and TodosLibres do begin

if FieldByName(‘IT_EN_USO’).AsString = ‘1’ then

TodosLibres:=false

else begin

strSQL:=’update TABLA ‘+

‘set IT_EN_USO = 1 ‘+

‘where CD = ‘+Cod+’ and ‘+

‘IT_EN_USO = 0’;

NumRegsAfectados:=EjecutarSQL(strSQL);

if NumRegsAfectados < 1 then

TodosLibres:=false;

end;

Next;

end;

OcuparTodosRecursos:=TodosLibres;

end; // OcuparTodosRecursos

 

(*

* —————————————————————-

* procedure LiberarTodosRecursos (Cod);

* Cometido..: Libera todos los recursos que se ocuparon para la

*             modificación.

* —————————————————————-

*)

procedure LiberarTodosRecursos (Cod);

begin

strSQL:=’select CD ‘+

‘from TABLA ‘+

‘where CD = ‘+Cod;

EjecutarSQL(strSQL);

while not eof do begin

strSQL:=’update TABLA ‘+

‘set IT_EN_USO = 0 ‘+

‘where CD = ‘+Cod;

EjecutarSQL(strSQL);

Next;

end;

end; // LiberarTodosRecursos

 

(*

* —————————————————————-

* procedure VolcarDatosABD (Cod, DatosModif);

* Cometido..: Vuelca los datos modificados por el usuario a la BD.

* —————————————————————-

*)

procedure VolcarDatosABD (Cod, DatosModif);

begin

strSQL:=’select CD ‘+

‘from TABLA ‘+

‘where CD = ‘+Cod;

EjecutarSQL(strSQL);

while not eof do begin

strSQL:=’update TABLA ‘+

‘set CAMPOS_MODIF = ‘+DatosModif+’ ‘+

‘where CD = ‘+Cod;

EjecutarSQL(strSQL);

Next;

end;

end; // VolcarDatosABD

 

(*

* —————————————————————-

* rutina Principal;

* Cometido..: Esqueleto de la aplicación que maneje esta forma de

*             trabajo.

* —————————————————————-

*)

rutina Principal;

begin

StartTransaction;

if OcuparTodosRecursos(Cod) then begin

CommitTransaction;

CargarDatosModificables(Cod);

DatosModificados:=RecogerDatosAModificar(DatosModif);

if DatosModificados = OK then begin

StartTransaction;

VolcarDatosABD(Cod,DatosModif);

LiberarTodosRecursos(Cod);

CommitTransaction;

end;

if DatosModificados = CANCEL then begin

StartTransaction;

LiberarTodosRecursos(Cod);

CommitTransaction;

end;

end

else begin

RollBackTransaction;

MostrarError(‘Los datos están siendo modificados’+

‘ por otro usuario.’+#13+

‘Por favor, inténtelo más tarde.’);

end;

end; // Principal

Como puede apreciarse, en el proceso existen dos transacciones. En la primera de ellas se ocupan todos los recursos que sean objeto de la modificación. Una vez ocupados todos los recursos objeto de modificación, se cierra la primera transacción. De esta forma se liberan los bloqueos establecidos por SQL Server y se potencia así la concurrencia en el acceso a los datos.

Cuando se han recogido todos los datos que el usuario quiere modificar, si éste acepta los cambios, se inicia una nueva transacción en la que, tras volcar la nueva información a la base de datos, se liberan todos los recursos que se habían ocupado. Si el usuario ha cancelado los cambios, se inicia una nueva transacción con el fin de liberar todos los recursos ocupados. Después se cierra la transacción.

Si entre la primera y la segunda transacción se produce un corte en el suministro eléctrico, el ordenador se bloquea, etc., los recursos ocupados permanecerán constantemente en este estado, ya que se cerró la transacción en la que se ocuparon. En estos casos, poco habituales por otro lado, será necesario aplicar el plan de contingencia.

El esquema del plan de contingencia sería:

(*

* —————————————————————-

* procedure PlanContingencia;

* Cometido..: Liberar todos los recursos de la BD.

* —————————————————————-

*)

procedure PlanContingencia;

begin

StartTransaction;

for TodosLosRecursosControlados do

LiberarTodosRecursos(Cod);

CommitTransaction;

end; // PlanContingencia

Por otro lado, en aquellos mantenimientos en los que no se requiere interactuación con el usuario, el esquema de trabajo varía en algunos aspectos. El pseudocódigo en este caso sería:

(*

* —————————————————————-

* function ModificarDatos (Cod,DatosModif) : boolean;

* Cometido..: Modificación de datos de la BD.

* —————————————————————-

*)

function ModificarDatos (Cod,DatosModif) : boolean;

begin

StartTransaction;

if OcuparTodosRecursos(Cod) then begin

VolcarDatosABD(Cod,DatosModif);

LiberarTodosRecursos(Cod);

CommitTransaction;

ModificarDatos:=true;

end

else begin

RollBackTransaction;

ModificarDatos:=false;

end;

end; // ModificarDatos

 

(*

* —————————————————————-

* rutina Principal;

* Cometido..: Esqueleto de la aplicación que maneje esta forma de

*             trabajo.

* —————————————————————-

*)

rutina Principal;

begin

if ModificarDatos(Cod,DatosModif) then

Mensaje(‘Datos modificados’)

else

MostrarError(‘Imposible modificar datos’);

end; // Principal

Como puede apreciarse, en este caso, la ocupación y liberación de los recursos se produce dentro de la misma rutina y en la misma transacción, dado que ya se tienen los nuevos datos a introducir en la base de datos. Este tipo de mantenimientos no provoca problemas que hagan necesaria la ejecución del plan de contingencia, ya que todas las modificaciones sobre la base de datos se realizan dentro de la misma transacción.

Los procesos que sólo visualicen datos sin llegar a modificarlos, no tendrán en cuenta el valor de la columna IT_EN_USO, no estando para estos procesos bloqueada la fila en cuestión.

Esta forma de trabajo evita que dos procesos seleccionen a la vez los mismos datos para su posterior modificación, por lo que ningún usuario perderá tiempo modificando datos que finalmente no pueda salvar. Sin embargo, no impide que un usuario bloquee indefinidamente datos.

La pega de este esquema reside en el plan de contingencia. Para poder aplicar el proceso que liberará todas las filas bloqueadas permanentemente por un fallo en el suministro eléctrico, etc., será necesario que no haya ninguna aplicación activa excepto la que está ejecutando el administrador. Es, por tanto, aplicable en aplicaciones con pocos usuarios y en oficinas en las que se pueda controlar la actividad de todos los operarios a la vez para evitar situaciones de pérdida de datos e inconsistencias que podrían llegar a derivarse de la aplicación del plan de contingencia estando alguna aplicación activa.

Una forma de evitar el plan de contingencia manteniendo este esquema de trabajo es bajar el nivel de aislamiento de las transacciones para poder leer datos de los que todavía no se ha hecho el commit (permitiendo lecturas sucias). En este caso, no se cerraría la transacción tras ocupar los recursos, sólo se cerraría tras liberarlos, por lo que si algo falla entre el proceso de ocupar los recursos y después liberarlos, sería el propio SQL Server el encargado de deshacer los cambios, dejando de nuevo los recursos libres.

El flag “IT_EN_USO” asegura que dos usuarios no modifiquen a la vez los mismos datos. Bajar el nivel de aislamiento permite una mayor concurrencia en el acceso a los datos.

El esquema de trabajo para mantenimientos que necesiten la intervención del usuario será en este caso:

(*

* —————————————————————-

* rutina Principal;

* Cometido..: Esqueleto de la aplicación que maneje esta forma de

*             trabajo.

* —————————————————————-

*)

rutina Principal;

begin

EjecutarSQL(‘set transaction isolation level readuncommitted’);

StartTransaction;

if OcuparTodosRecursos(Cod) then begin

CargarDatosModificables(Cod);

DatosModificados:=RecogerDatosAModificar(DatosModif);

if DatosModificados = OK then begin

VolcarDatosABD(Cod,DatosModif);

LiberarTodosRecursos(Cod);

CommitTransaction;

end;

if DatosModificados = CANCEL then begin

RollBackTransaction;

end;

end

else

RollBackTransaction;

MostrarError(‘Los datos están siendo modificados’+

‘ por otro usuario.’+#13+

‘Por favor, inténtelo más tarde.’);

EjecutarSQL(‘set transaction isolation level readcommitted’);

end; // Principal

El esquema de trabajo para mantenimientos que no necesiten la intervención del usuario no variará con respecto al esquema que no baja el nivel de aislamiento excepto en el hecho de que no necesita el plan de contingencia.

En las consultas que trabajen sobre las tablas manejadas con estos esquemas de bloqueo también se debe bajar el nivel de aislamiento ya que, al no cerrar la transacción hasta el final, SQL Server mantendrá bloqueos sobre dichas tablas.

Esta opción puede emplearse en contados casos ya que bajar el nivel de aislamiento de las transacciones puede ocasionar graves problemas. Un caso en el que sería medianamente fácil usar esta optimización sería en aquellos mantenimientos que sólo afectan a datos de una tabla. Si el mantenimiento afecta a tablas de tipo maestro – detalle, la programación se complica ya que las consultas que se realicen podrán leer datos sucios, en los que todavía no existan todos los detalles de un maestro, etc., pudiendo ocasionar resultados no deseados.

Supongamos ahora que mezclamos este esquema de trabajo con un enfoque pesimista. La función “OcuparTodosRecursos” podría haber sido escrita:

(*

* —————————————————————-

* function OcuparTodosRecursosPesimista (Cod) : boolean;

* Cometido..: Ocupa todos los recursos que se requieren para

*             realizar las posteriores modificaciones.

*             Enfoque pesimista.

* —————————————————————-

*)

function OcuparTodosRecursosPesimista (Cod) : boolean;

begin

TodosLibres:=true;

strSQL:=’select CD, IT_EN_USO ‘+

‘from TABLA with (repeatableread) ‘+

‘where CD = ‘+Cod;

EjecutarSQL(strSQL);

while not eof and TodosLibres do begin

if FieldByName(‘IT_EN_USO’).AsString = ‘1’ then

TodosLibres:=false

else begin

strSQL:=’update TABLA ‘+

‘set IT_EN_USO = 1 ‘+

‘where CD = ‘+Cod;

EjecutarSQL(strSQL);

end;

next;

end;

OcuparTodosRecursosPesimista:=TodosLibres;

end; // OcuparTodosRecursosPesimista

Podría ocurrir que dos usuarios intentaran modificar a la vez los mismos datos. En este caso, podría producirse un interbloqueo. SQL Server mataría a la transacción que ha provocado la cadena circular de bloqueo y sería necesario un tratamiento de errores especial. Con la primera de las versiones para ocupar recursos, no se producirá nunca un interbloqueo, descargando así al servidor de la tarea de tener que resolver este tipo de conflictos y evitando tener que programar un tratamiento de errores adicional en la aplicación.

Existe aún otra manera de codificar el procedimiento “OcuparTodosRecursos”.

Esta forma evita el interbloqueo, ya que lo primero que se hace es el update, con lo que se obtienen directamente los bloqueos de escritura. Además, como consecuencia de hacer un único update no se sobrecarga la red, aunque sí el servidor, por lo que es adecuada para arquitecturas en la que la red es lenta y existe un servidor potente, frente a la solución presentada en la primera solución que sería más adecuada si el servidor se encuentra ya sobrecargado.

(*

* —————————————————————-

* function OcuparTodosRecursosEficiente (Cod) : boolean;

* Cometido..: Ocupa todos los recursos que se requieren para

*             realizar las posteriores modificaciones.

*             Enfoque optimista.

* —————————————————————-

*)

function OcuparTodosRecursosEficiente (Cod) : boolean;

begin

strSQL:=’update TABLA ‘+

‘set IT_EN_USO = 1 ‘+

‘where IT_EN_USO = 0 and ‘+

‘CD = ‘+Cod;

NumRegsAfectadosUpdate:=EjecutarSQL(strSQL);

strSQL:=’select count(*) as NumRegsSe ‘+

‘from TABLA ‘+

‘where CD = ‘+Cod;

EjecutarSQL(strSQL);

NumRegsAfectadosSelect:=FieldByName(‘NumRegsSe’).AsInteger;

if NumRegsAfectadosUpdate = NumRegsAfectadosSelect then

OcuparTodosRecursosEficiente:=true

else

OcuparTodosRecursosEficiente:=false;

end; // OcuparTodosRecursosEficiente

Esta forma de ocupar los recursos, con un servidor poco cargado, es mucho más eficiente siendo este aumento de eficiencia más patente cuanto más recursos sea necesario ocupar.

Esta forma de codificación es válida también para la operación de liberar recursos.

Aunque esta codificación es más eficiente, en los ejemplos de código se seguirá manteniendo la codificación más natural del mismo para que resulte más clara su comprensión. Sin embargo, hay que tener en cuenta siempre en cuenta que esta forma de trabajo es más eficiente y ocasiona una menor carga en el servidor por resolución de contiendas de bloqueo.

El hecho de que la ocupación y liberación de recursos no sea transaccional y se necesite un plan de contingencia para los casos de bloqueo permanente hace de esta solución una primera aproximación de la solución que se expone a continuación.

Bloqueo pesimista utilizando “caducidad” de bloqueo

Partiendo del bloqueo optimista utilizando un flag para bloquear los recursos y basándose en la filosofía de trabajo de los enfoques optimistas simples, se puede plantear otra solución que tiene ciertas características que la hacen interesante.

En este caso, en aquellas tablas en las que se quiera controlar el acceso concurrente se introducirá una columna “FC_OCUPACION” de tipo datetime que, en el caso de SQL Server, ofrece una precisión de 1 milisegundo.
Se establece un tiempo de caducidad, que indicará el tiempo máximo que un proceso de modificación puede tener ocupado un recurso.

Un proceso que quiera ocupar recursos, sólo podrá hacerlo si el campo “FC_OCUPACION” del recurso a ocupar contiene una fecha caducada, es decir, si la fecha actual del servidor menos el valor de “FC_OCUPACION” supera el tiempo de caducidad. Para ocupar un recurso, el proceso introducirá la fecha actual del servidor en el campo “FC_OCUPACION” del recurso a ocupar. Esta será su marca de ocupación.

Si un mismo proceso quiere modificar varias filas, de la misma tabla o de varias, deberá obtener todos los recursos a modificar o no ocupar ninguno. En caso de no poder ocupar todos los recursos deberá liberar aquellos que ya haya ocupado y se mostrará un mensaje al usuario que le invite a intentarlo más tarde. Todos los recursos que tenga que ocupar el proceso se han de ocupar con la misma marca de ocupación.

Un proceso que haya conseguido completar con éxito todo el proceso de actualización de los datos será el encargado de liberar los mismos para que otros procesos puedan ocuparlos. Para liberar un recurso, ha de introducirse una fecha caducada en el campo “FC_OCUPACION” de cada recurso que se ha de liberar.

Llegado este punto, hay que hacer una puntualización: Una fecha no caducada indica que el recurso en cuestión está en uso. Una fecha caducada indica que el recurso no está en uso, o que el proceso que lo estaba usando ha excedido el tiempo que se le ha dado para modificar el recurso que ocupó y que, por tanto, se ha liberado el recurso (pero no lo ha liberado el proceso que lo ocupó) o, mejor dicho, que el recurso se puede ocupar.

De esta forma, un proceso que haya ocupado todos los recursos que necesite, a la hora de volcar la actualización a la base de datos, debe comprobar que la marca de ocupación sigue siendo la misma con la que ocupó los recursos. Si la marca sigue siendo la suya, podrá volcar la información, incluso aunque la marca haya caducado ya que si sigue siendo la suya significa que no hay otro proceso que haya ocupado los recursos. Por el contrario, si la marca es distinta significa que el tiempo de modificación fue excedido y que otro proceso a ocupado el recurso. En este caso, el proceso al que le ha caducado la marca no puede realizar la modificación. Este proceso debe liberar los recursos que sigan teniendo su marca de ocupación y mostrar un mensaje al usuario que le indique que no se puede realizar la actualización de los datos porque se excedió el tiempo de modificación.

Para mantenimientos en los que es necesaria la intervención del usuario, el esquema de trabajo es el siguiente:

const

MARCA_NO_OCUPADO = ’01/01/1900 00:00:00.000′;

T_CADUCIDAD = XX; // Tiempo de caducidad

(*

* —————————————————————-

* function ObtenerMarcaTemp : DateTime;

* Cometido..: Obtiene la fecha del servidor para establecer la

*             marca de ocupación.

* —————————————————————-

*)

function ObtenerMarcaTemp : DateTime;

begin

strSQL:=’select getdate() as MarcaTemp’;

EjecutarSQL(strSQL);

ObtenerMarcaTemp:=FieldByName(‘MarcaTemp’).AsDateTime;

end; // ObtenerMarcaTemp

 

(*

* —————————————————————-

* function MarcaCaducada (MarcaOld, MarcaTemp): boolean;

* Cometido..: Comprueba si la marca de modificación ha caducado

*             o no.

* —————————————————————-

*)

function MarcaCaducada : boolean;

begin

if (MarcaTemp – MarcaOld) > T_CADUCIDAD then

MarcaCaducada:=true

else

MarcaCaducada:=false;

end; // MarcaCaducada

 

(*

* —————————————————————-

* function OcuparTodosRecursos (Cod, MarcaTemp) : boolean;

* Cometido..: Ocupa todos los recursos que se requieren para

*             realizar las posteriores modificaciones.

* —————————————————————-

*)

function OcuparTodosRecursos (Cod, MarcaTemp) : boolean;

begin

TodosLibres:=true;

strSQL:=’select CD, FC_OCUPACION ‘+

‘from TABLA ‘+

‘where CD = ‘+Cod;

EjecutarSQL(strSQL);

while not eof and TodosLibres do begin

MarcaOld:= FieldByName(‘FC_OCUPACION’).AsString;

if not MarcaCaducada(MarcaOld,MarcaTemp) then

TodosLibres:=false

else begin

strSQL:=’update TABLA ‘+

‘set FC_OCUPACION = ‘+MarcaTemp+’ ‘+

‘where CD = ‘+Cod+’ and ‘+

‘FC_OCUPACION = ‘MarcaOld+;

NumRegsAfectados:=EjecutarSQL(strSQL);

if NumRegsAfectados < 1 then

TodosLibres:=false;

end;

Next;

end;

OcuparTodosRecursos:=TodosLibres;

end; // OcuparTodosRecursos

 

(*

* —————————————————————-

* procedure LiberarTodosRecursos (Cod, MarcaTemp);

* Cometido..: Libera todos los recursos que se ocuparon para la

*             modificación.

* —————————————————————-

*)

procedure LiberarTodosRecursos (Cod, MarcaTemp);

begin

TodosOcupados:=true;

strSQL:=’select CD ‘+

‘from TABLA ‘+

‘where CD = ‘+Cod;

EjecutarSQL(strSQL);

while not eof and TodosOcupados do begin

strSQL:=’update TABLA ‘+

‘set FC_OCUPACION = ‘+MARCA_NO_OCUPADO+’ ‘+

‘where CD = ‘+Cod+’ and ‘

‘FC_OCUPACION = ‘+MarcaTemp;

NumRegsAfectados:=EjecutarSQL(strSQL);

if NumRegsAfectados < 1 then

TodosOcupados:=false;

Next;

end;

end; // LiberarTodosRecursos

 

(*

* —————————————————————-

* function VolcarDatosABD (Cod, MarcaTemp, DatosModif) : boolean;

* Cometido..: Vuelca los datos modificados por el usuario a la BD.

* —————————————————————-

*)

function VolcarDatosABD (Cod, MarcaTemp, DatosModif) : boolean;

begin

TodosOcupados:=true;

strSQL:=’select CD, FC_OCUPACION ‘+

‘from TABLA ‘+

‘where CD = ‘+Cod;

EjecutarSQL(strSQL);

while not eof and TodosOcupados do begin

if MarcaCaducada(

FieldByName(‘FC_OCUPACION’).AsDateTime,MarcaTemp) then

TodosOcupados:=false;

if TodosOcupados then begin

strSQL:=’update TABLA ‘+

‘set CAMPOS_MODIF = ‘+DatosModif+’ ‘+

‘where CD = ‘+Cod+’ and ‘

‘FC_OCUPACION = ‘MarcaTemp;

NumRegsAfectados:=EjecutarSQL(strSQL);

if NumRegsAfectados < 1 then

TodosOcupados:=false;

end;

Next;

end;

VolcarDatosABD:=TodosOcupados;

end; // VolcarDatosABD

 

(*

* —————————————————————-

* rutina Principal;

* Cometido..: Esqueleto de la aplicación que maneje esta forma de

*             trabajo.

* —————————————————————-

*)

rutina Principal;

begin

StartTransaction;

MarcaTempOc:=ObtenerMarcaTemp;

if OcuparTodosRecursos(Cod,MarcaTempOc) then begin

CommitTransaction;

CargarDatosModificables(Cod);

DatosModificados:=RecogerDatosAModificar(DatosModif);

if DatosModificados = OK then begin

StartTransaction;

if VolcarDatosABD(Cod,MarcaTempOc,DatosModif) then begin

LiberarTodosRecursos(Cod,MarcaTempOc);

CommitTransaction;

else begin

RollBackTransaction;

MostrarError(‘Los datos en memoria han caducado.’+

‘Para modificar estos datos vuelva’+

‘ a filtrar’);

end;

end;

if DatosModificados = CANCEL then begin

MarcaTempLib:=ObtenerMarcaTemp;

if not MarcaCaducada(MarcaTempOc,MarcaTempLib) then begin

StartTransaction;

LiberarTodosRecursos(Cod,MarcaTempOc);

CommitTransaction;

end;

end;

end

else begin

RollBackTransaction;

MostrarError(‘Los datos están siendo modificados’+

‘ por otro usuario.’+#13+

‘Por favor, inténtelo más tarde.’);

end;

end; // Principal

En este caso, por poner un ejemplo, la ocupación eficiente de los recursos tendría la forma:

(*

* —————————————————————-

* function OcuparTodosRecursosEficiente (Cod,MarcaTemp) : boolean;

* Cometido..: Ocupa todos los recursos que se requieren para

*             realizar las posteriores modificaciones con el

*             esquema de trabajo más eficiente en servidores poco

*             cargados.

* —————————————————————-

*)

function OcuparTodosRecursosEficiente (Cod, MarcaTemp) : boolean;

begin

strSQL:=’update TABLA ‘+

‘set FC_OCUPACION = ‘+MarcaTemp+’ ‘+

‘where ‘+MarcaTemp+’-FC_OCUPACION > ‘+T_CADUCIDAD+

‘ and ‘+

‘CD = ‘+Cod;

NumRegsAfectadosUpdate:=Ejecutar(strSQL);

strSQL:=’select count(*) as NumRegsSe ‘+

‘from TABLA ‘+

‘where CD = ‘+Cod;

EjecutarSQL(strSQL);

NumRegsAfectadosSelect:=FieldByName(‘NumRegsSe’).AsInteger;

if NumRegsAfectadosUpdate = NumRegsAfectadosSelect then

OcuparTodosRecursosEficiente:=true

else

OcuparTodosRecursosEficiente:=false;

end; // OcuparTodosRecursosEficiente

Como puede apreciarse, en el proceso existen dos transacciones. En la primera de ellas se ocupan todos los recursos que sean objeto de la modificación. Una vez ocupados todos los recursos objeto de modificación, se cierra la primera transacción. De esta forma se liberan los bloqueos establecidos por SQL Server y se potencia así la concurrencia en el acceso a los datos.

Cuando se han recogido todos los datos que el usuario quiere modificar, si éste acepta los cambios, se inicia una nueva transacción en la que, tras volcar la nueva información a la base de datos, se liberan todos los recursos que se habían ocupado. Si el usuario ha cancelado los cambios, se inicia una nueva transacción con el fin de liberar todos los recursos ocupados. Después se cierra la transacción.

Si entre la primera y la segunda transacción se produce un corte en el suministro eléctrico, el ordenador se bloquea, etc., los recursos ocupados permanecerán en este estado, hasta que su marca de ocupación caduque, por lo que en este caso no es necesario un plan de contingencia que libere los recursos, aunque es cierto que puede implementarse esta opción por si se necesitara la liberación inmediata de los recursos.

Por otro lado, en aquellos mantenimientos en los que no se requiere interactuación con el usuario, el esquema de trabajo varía en algunos aspectos. El pseudocódigo en este caso sería:

(*

* —————————————————————-

* function ModificarDatos (Cod,DatosModif) : boolean;

* Cometido..: Modificación de datos de la BD.

* —————————————————————-

*)

function ModificarDatos (Cod,DatosModif) : boolean;

begin

StartTransaction;

MarcaTemp:=ObtenerMarcaTemp;

if OcuparTodosRecursos(Cod,MarcaTemp) then begin

VolcarDatosABD(Cod,MarcaTemp,DatosModif);

LiberarTodosRecursos(Cod,MarcaTemp);

CommitTransaction;

ModificarDatos:=true;

end

else begin

RollBackTransaction;

ModificarDatos:=false;

end;

end; // ModificarDatos

 

(*

* —————————————————————-

* rutina Principal;

* Cometido..: Esqueleto de la aplicación que maneje esta forma de

*             trabajo.

* —————————————————————-

*)

rutina Principal;

begin

if ModificarDatos(Cod,DatosModif) then

Mensaje(‘Datos modificados’)

else

MostrarError(‘Imposible modificar datos’);

end; // Principal

Como puede apreciarse, en este caso, la ocupación y liberación de los recursos se produce dentro de la misma transacción, dado que ya se tienen los nuevos datos a introducir en la base de datos. En este tipo de mantenimientos, el proceso que ocupa los registros siempre podrá terminar con éxito el proceso, ya que SQL Server mantiene los bloqueos que estableció sobre las filas cuando el proceso de modificación ocupó los recursos. Los procesos de este tipo deben tener la transacción abierta el menor tiempo posible para evitar reducir el nivel de concurrencia de acceso a los datos.

Los procesos que sólo visualicen datos sin llegar a modificarlos no tendrán en cuenta el valor de la columna “FC_OCUPACION”, no estando para estos procesos bloqueada la fila en cuestión.

Esta forma de trabajo impide que un usuario bloquee indefinidamente un recurso, sin embargo, puede ocasionar pérdida de tiempo de trabajo si tras introducir un gran volumen de datos no puede llevarse a cabo la modificación porque la ocupación haya caducado y otro proceso haya ocupado alguno de los recursos que necesitaba el primer proceso. Si la elección del tiempo de caducidad se ha hecho adecuadamente, esta situación no debería darse nunca, a no ser que un usuario la forzara de forma intencionada.

“Caducidad” de bloqueo sin pérdidas de actualización

En procesos muy delicados realizados por usuarios altamente cualificados que no provoquen un bloqueo innecesario de recursos y en los que exista la necesidad de interactuación con el usuario, existen dos esquemas que evitan que se pierdan datos de actualización.

El primero de estos esquemas consiste en mezclar la forma de trabajo en mantenimientos que necesitan interactuación con el usuario con la de los que no necesitan dicha interactuación, de forma que las ocupaciones no caduquen. En este caso el pseudocódigo sería:

const

DATOS_MODIF    = 1;

DATOS_CANCEL   = 2;

DATOS_OCUPADOS = 3;

 

(*

* —————————————————————-

* function ModificarDatos (Cod) : integer;

* Cometido..: Modificación de datos de la BD.

* —————————————————————-

*)

function ModificarDatos (Cod) : integer;

begin

StartTransaction;

MarcaTempOc:=ObtenerMarcaTemp;

if OcuparTodosRecursos(Cod,MarcaTempOc) then begin

CargarDatosModificables(Cod);

DatosModificados:=RecogerDatosAModificar(DatosModif);

if DatosModificados = OK then begin

VolcarDatosABD(Cod,MarcaTempOc,DatosModif);

LiberarTodosRecursos(Cod,MarcaTempOc);

CommitTransaction;

ModificarDatos:=DATOS_MODIF;

end;

if DatosModificados = CANCEL then begin

RollBackTransaction;

ModificarDatos:=DATOS_CANCEL;

end;

end

else begin

RollBackTransaction;

ModificarDatos:=DATOS_OCUPADOS;

end;

end; // ModificarDatos

 

(*

* —————————————————————-

* rutina Principal;

* Cometido..: Esqueleto de la aplicación que maneje esta forma de

*             trabajo.

* —————————————————————-

*)

rutina Principal;

begin

Resultado:=ModificarDatos(Cod);

if Resultado = DATOS_MODIF then

Mensaje(‘Datos modificados’);

if Resultado = DATOS_CANCEL then

Mensaje(‘Modificación cancelada’);

if Resultado = DATOS_OCUPADOS then

MostrarError(‘Los datos están siendo modificados’+

‘ por otro usuario.’+#13+

‘Por favor, inténtelo más tarde.’);

end; // Principal

Como puede apreciarse, todo el proceso se realiza dentro de la misma transacción por lo que SQL Server mantiene los bloqueos establecidos sobre los recursos que bloqueó el proceso hasta el final de la misma. De esta forma, ningún otro proceso podrá bloquear los recursos, aunque la marca haya caducado. De hecho, otro proceso que intentara bloquear los recursos mediante el enfoque optimista se quedaría bloqueado hasta que se terminara la transacción.

Este esquema de trabajo sólo debe implementarse en aquellos procesos extremadamente delicados y cortos en duración, realizados por personal altamente cualificado que no provoque bloqueos innecesarios de los recursos dado que, al mantener los bloqueos establecidos por SQL Server hasta el final de la transacción, ningún otro proceso podrá bloquear dichos recursos hasta la finalización de la misma, llegando incluso a quedarse bloqueados hasta ese momento, reduciéndose de esta forma el nivel de concurrencia de acceso a los datos.

El segundo de estos esquemas presenta la ventaja de que no provocará que ningún otro proceso quede bloqueado por SQL Server, aunque representa una mayor carga para el gestor de bases de datos.

Consiste en asociar un temporizador al formulario en el que se están recogiendo los datos a actualizar de forma que, cuando se dispare éste, se actualice el campo en el que se almacena la caducidad del bloqueo, antes de que se produzca dicha caducidad. De esta forma, otros procesos podrán visualizar datos sin llegar a bloquearse y se asegura que el proceso que ocupó los recursos finalice exitosamente la actualización de los datos.

Interbloqueos en SQL Server

Por si fuera poco lo que hemos visto hasta ahora, aún tenemos pendiente revisar la gestión de interbloqueos que realiza SQL Server. Este punto es de vital importancia en nuestras aplicaciones que acceden concurrentemente a los datos en nuestra base de datos.

Un interbloqueo es una situación en la que dos transacciones están esperando para utilizar un recurso bloqueado.

SQL Server detecta automáticamente una situación de interbloqueo mediante un método denominado detección de cadenas circulares de bloqueos. Cuando un interbloqueo ocurre, SQL Server termina el proceso que haya generado la cadena circular de bloqueos.

Los interbloqueos no se producen únicamente a nivel de página de datos, de hecho, muchos interbloqueos se producen a nivel de página de índice.

Minimización de la contienda de bloqueo

Para minimizar el número de interbloqueos se deben considerar las siguientes recomendaciones:

  • Las transacciones deben ser cortas y afectar al menor número de datos posibles.
  • Se debe dar preferencia a la utilización de alguna de las técnicas del enfoque optimista.
  • Puede resultar conveniente establecer una prioridad baja (“set deadlockpriority low”) para las consultas DSS y una alta (“set deadlockpriority normal”) para las consultas OLTP.
  • El orden de acceso a las tablas debe ser siempre el mismo en las distintas partes del código.
  • Se debe minimizar el uso de “holdlock”. Por ejemplo, el código:

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 el mismo

comando 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

podría reescribirse como:

declare @SEQNO int

 

begin transaction

update SEQUENCE

set @SEQNO = isnull(SEQ#,0)+1

from SEQUENCE

select @SEQNO

commit transaction

De esta forma se obtiene directamente el bloqueo de actualización eliminándose la posibilidad de que se produzca un interbloqueo.

  • SQL Server proporciona un soporte completo para establecer la granularidad del bloqueo a nivel de fila. Una granularidad del bloqueo más fina proporciona una mejor concurrencia, minimizando el número de interbloqueos, aunque aumenta el trabajo del servidor para gestionar los bloqueos.
  • Se debe evitar las interactuaciones con el usuario dentro de una transacción con el fin de minimizar el tiempo de duración de la misma.
  • Se debe seleccionar cuidadosamente el nivel de aislamiento de una transacción, ya que el nivel de aislamiento determina el tipo de bloqueo. Es posible reducir el número de interbloqueos bajando el nivel de aislamiento.

Gestión de interbloqueos

SQL Server devuelve el error 1205 al cliente cuando termina un proceso como resultado de un interbloqueo.

Si algo sucedió con nuestra salud, creemos que hay una solución a cualquier enfermedad en una píldora. Hoy en día, alrededor del 35% de los estadounidenses compran medicamentos en línea. A veces los servicios ofrecen a sus clientes Kamagra. Debajo están las decisiones importantes que usted tienes razón que hacer sobre “http://zithromax.me/es/azitromicina.html“. ¿De qué hablan los proveedores de atención médica “Azitromicina“? No se olvide, considere “a href=”http://zithromax.me/es/azitromicina.html”>Azithromycin“. Después de todo, hay algunas explicaciones y los farmacéuticos son generalmente capaces de identificar su problema a través de pruebas psicológicas. Antes de comprar Kamagra o cualquier otro medicamento, describa a su farmacéutico su condición médica. El médico forense puede ordenar algunas pruebas para descartar cualquier problema médico de cabecera que pueda estar contribuyendo a la enfermedades respiratorias. No tome medicamentos genéricos no deseados. Llévelos a su farmacia local que dispondrá de ellos para usted.


Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Límite de tiempo se agote. Por favor, recargar el CAPTCHA por favor.