Bloqueo de recursos en SQL Server

En los últimos años hemos sido testigos de un crecimiento exponencial de aplicaciones distribuidas, con infinidad de componentes concurrentes de propósito específico interactuando unos con otros en tiempo real y a grandes velocidades. Estos nuevos enfoques de arquitecturas asíncronas han traído consigo antiguos problemas que nosotros, los desarrolladores, debemos resolver en nuestro día a día; uno de ellos, el mítico bloqueo mutuo.

Escenarios tales como accesos a interfaces de hardware, aplicaciones IOT o hasta la simple generación de números de secuencia de tickets son casos donde el interbloqueo o bloqueo mutuo es un problema que de no ser resuelto, podría comprometer el éxito de un proyecto de software. Si bien es cierto, existen soluciones que van desde complejas arquitecturas distribuidas hasta increíbles artimañanas inventadas por algún programador adicto al café, en este artículo nos centraremos en una propuesta que manejará el interbloqueo desde base de datos haciendo uso de un procedimiento almacenado de sistema, el sp-getapplock.

El problema se planteará de la siguiente manera: "Debemos desarrollar un procedimiento almacenado que garantice la inserción de registros en una tabla determinada, cada uno de los cuales estará identificado por un id que deberá generarse de manera secuencial sin dejar ningún vacío, esto quiere decir que de presentarse algún error, se deberá ejecutar una instrucción de rollback y la siguiente inserción deberá hacer uso de esta secuencia".

El primer paso será crear nuestra tabla, para lo cual haremos ejecutaremos la siguiente consulta en SQL Server.

CREATE TABLE dbo.Registros  
   (Registro_Seq_ID int PRIMARY KEY NOT NULL,  
    Registro_Descripcion varchar(25) NOT NULL)  
GO 

El siguiente paso será crear un procedimiento almacenado que se encargue de la inserción, esto con el fin de tener un punto de entrada dónde poder aplicar nuestro control contra los interbloqueos.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE spInsertarRegistro
 @Descripcion VARCHAR(25),
 @RegistroSeq INT OUT
AS
BEGIN
 SET NOCOUNT ON;
 SELECT @RegistroSeq = ISNULL(MAX(Registro_Seq_ID), 0) + 1  from Registros;
 INSERT INTO Registros(Registro_Seq_ID, Registro_Descripcion) VALUES (@RegistroSeq, @Descripcion);
END
GO

Y ahora utilizaremos Java para probar nuestro procedimiento almacenado.

package com.rolandopalermo.sql;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class App {
    
 public String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
 public void insertRegistro(String descripcion) throws SQLException,
 ClassNotFoundException {
  String connectionUrl = "jdbc:sqlserver://localhost:55418;databaseName=BD_TEST;user=user_test_app;password=12345678";
  try (
  Connection connection = DriverManager.getConnection(connectionUrl);
  CallableStatement cstmt = connection.prepareCall("{call spInsertarRegistro(?,?)}")) {
   Class.forName(driver);
   cstmt.setString(1, descripcion);
   cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
   cstmt.execute();
   System.out.println("Se creó el registro [" + cstmt.getInt(2) + "]");
  }
 }
 
 public static void main(String[] args) throws Exception {
  App app = new App();
  app.insertRegistro("Registro sin interbloqueo");
 }
 
}

Al ejecutar dicha clase, veremos que nuestro registro se insertó correctamente, sin embargo ahora realizaremos una pequeña mofidicación al método main para que inserte registros de manera continua, estableciendo una pausa de 200 milisegundos entre cada inserción. Al ejecutar un sólo proceso, el resultado será el siguiente:

Sin embargo, al ejecutar tres procesos, la historia cambia:

Es aquí donde sp-getapplock viene a salvarnos el día. Nuestro procedimiento sufrirá una ligera modifación, la cual evitará que más de una petición intente ejecutarlo de manera concurrente. El recurso de bloqueo creado por sp_getapplock se crea en la base de datos actual para la sesión.

SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 ALTER PROCEDURE spInsertarRegistro2  
      @Descripcion VARCHAR(25),  
      @RegistroSeq INT OUT  
 AS  
 DECLARE  
      @rc int = 0,  
      @msg varchar(2000)  
 SET @msg = CONVERT(varchar, GETDATE(), 114) + ' Se incia el segmento a sincronizar'  
 RAISERROR (@msg, 0, 1) WITH NOWAIT  
 BEGIN TRY  
      BEGIN TRAN  
           SET @msg = CONVERT(varchar, GETDATE(), 114) + ' Tratando de obtener el candado'  
           RAISERROR (@msg, 0, 1) WITH NOWAIT  
           -- Se solicita el candado  
           EXEC @rc = sp_getapplock   
                @Resource = 'spInsertarRegistro2', -- Nombre del recurso a bloquear  
                @LockMode = 'Exclusive', -- Tipo de candado  
                @LockOwner = 'Transaction', -- Transaction or Session  
                @LockTimeout = 15000 -- Tiempo de espera máximo, 15 seconds  
           -- Se verifica si se pudo obtener el candado  
           SET @msg =   
                CONVERT(varchar, GETDATE(), 114) +   
                ' sp_getapplock retornó ' +   
                CONVERT(varchar(30), @rc) +   
                ' -- ' +   
                CASE  
                     WHEN @rc < 0 THEN 'No se pudo obtener el candado'  
                     ELSE 'Candado obtenido'  
                END  
           RAISERROR (@msg, 0, 1) WITH NOWAIT  
           IF @rc >= 0  
                BEGIN  
                     SET @msg = CONVERT(varchar, GETDATE(), 114) + ' Se iniciará con la sección crítica '  
                     RAISERROR (@msg, 0, 1) WITH NOWAIT  
                     -- Inserciones protegias por código  
                     SET NOCOUNT ON;  
                     SELECT @RegistroSeq = ISNULL(MAX(Registro_Seq_ID), 0) + 1 from Registros;  
                     INSERT INTO Registros(Registro_Seq_ID, Registro_Descripcion) VALUES (@RegistroSeq, @Descripcion);  
                     --   
                     COMMIT TRAN -- Se libera el candado  
                     SET @msg = CONVERT(varchar, GETDATE(), 114) + ' Trabajo completado, se libera el candado'  
                     RAISERROR (@msg, 0, 1) WITH NOWAIT  
                END  
           ELSE  
                BEGIN  
                ROLLBACK TRAN  
                SET @rc = 50000  
           END  
 END TRY  
 BEGIN CATCH  
      set @msg = 'ERROR: ' + ERROR_MESSAGE() + ' en '   
       + coalesce(ERROR_PROCEDURE(), '')  
       + coalesce (' la línea:' + convert(varchar(30), ERROR_LINE()), '')  
      IF @@Trancount > 1  
           ROLLBACK TRAN;  
      RAISERROR (@msg, 0, 1)  
 END CATCH  

Ahora realizaremos una modificación a la clase Java para que inserte de manera concurrente registros sin ninguna pausa. Al ser la columna Registro_Seq_ID una clave primaria, si en algún momento llegase a duplicarse, se deberá lanzar un error. El siguiente video evidencia que, a pesar de la concurrencia, todos los registros se están insertando de manera correcta.

Espero que este post les sea de utilidad y cualquier consulta adicional, no duden en comentarla.