Hola a todos. Hoy queremos hablaros del uso del procedimiento almacenado de sistema sp_getAppLock, que hemos descubierto por una petición de un cliente. Es un procedimiento que se utiliza para conseguir un bloqueo a nivel de la transacción que se esté ejecutando. También se puede adquirir el bloqueo a nivel de sesión.
En este caso, el problema surge a nivel de transacción, ya que en el momento de crear valores para utilizarlos como Primary Key, partiendo de una tabla donde se almacena el último valor generado. Se ejecuta una consulta para recuperar el último valor generado, para posteriormente modificarlo y utilizarlo en la tabla como valor de la Primary Key.
Al ejecutar una SELECT, no hay ningún tipo de bloqueo, ya que una SELECT no obtiene bloqueo por defecto de los datos que lee. Entonces puede haber una acumulación de lecturas por parte de varias llamadas que recuperan siempre el mismo valor, si se hacen antes de que se ejecute la instrucción UPDATE que realiza el cambio en la columna de la tabla.
Por lo tanto, el error ocurre cuando todas las llamadas que han recuperado el mismo valor, intentan crear una nueva fila con el mismo procedimiento. Entonces se va a utilizar este valor, ya que se generan valores duplicados para la Primary Key, y por eso se devuelve el error.
Ejemplo
Esto lo podemos ilustrar con un ejemplo para entenderlo mejor:
Hay una tabla Alumnos, donde la Primary Key es el código de alumno. El código del último alumno dado de alta se guarda en una tabla cod_Alumno, en la columna identificador. En un momento dado, por ejemplo cuando se abren los plazos de matrícula, puede haber varias sesiones al mismo tiempo ejecutando un procedimiento para dar de alta usuarios que recuperen el último código del alumno.
Si hay varias sesiones que recuperan el código antes de que este cambie, el procedimiento realiza el mismo proceso para todos ellos, y al dar de alta los alumnos, puede haber errores de Primary Key porque varios intentan guardarse en la tabla con el mismo código.
Para ilustrar el ejemplo, se hace una prueba, utilizando sqlcmd. Si tenéis dudas de como conectar a la BBDD utilizando sqlcmd, podéis ver el post que pusimos sobre ello:
https://www.gpsos.es/2017/05/linea-comandos-sql-server/
Sesión 1
Se pone un Delay de 10 segundos para comprobar lo que ocurre, porque si no, es un proceso muy rápido y no se puede comprobar.
1> BEGIN 2> select * from dbo.cod_Alumno 3> WAITFOR DELAY '00:00:10' 4> update cod_Alumno set identificador=identificador+1 5> END 6> GO identificador ------------- 34962 (1 filas afectadas) (1 filas afectadas)
Sesión 2
Se ejecuta dentro de esos 10 segundos
1> select * from cod_Alumno 2> GO identificador ------------- 34962 (1 filas afectadas) Tiempos de ejecución de SQL Server: Tiempo de CPU = 0 ms, tiempo transcurrido = 70 ms.
Sesión 3
Se ejecuta dentro de esos 10 segundos
1> select * from cod_Alumno 2> GO identificador ------------- 34962 (1 filas afectadas) Tiempos de ejecución de SQL Server: Tiempo de CPU = 0 ms, tiempo transcurrido = 80 ms.
Sesión 4
Se ejecuta después de 10 segundos
Ya ha dado tiempo a ejecutar el UPDATE
1> select * from cod_Alumno 2> GO identificador ------------- 34963 (1 filas afectadas) Tiempos de ejecución de SQL Server: Tiempo de CPU = 0 ms, tiempo transcurrido = 70 ms.
En las 3 primeras sesiones, el procedimiento de alta de nuevos alumnos habría recuperado la columna identificador con valor 34962, con lo que al intentar hacer una inserción de nuevos alumnos, habría un error de clave duplicada en la Primary Key.
Para evitar esto, existe un procedimiento de sistema que se usa para obtener el bloqueo. Se trata de sp_getAppLock.
El procedimiento devuelve un valor para comprobar su correcto funcionamiento. Estos valores se pueden encontrar en la documentación oficial de Microsoft, en el siguiente link:
Sintaxis
La sintaxis general de la llamada al procedimiento es:
A continuación se detalla para qué se usa cada parámetro de sp_getAppLock:
sp_getapplock [ @Resource = ] 'resource_name' , -- Nombre del bloqueo [ @LockMode = ] 'lock_mode' -- Tipo de bloqueo: Shared, Update, IntentShared, IntentExclusive, o Exclusive. [ , [ @LockOwner = ] 'lock_owner' ] -- A nivel de transacción o a nivel de sesión [ , [ @LockTimeout = ] 'value' ] -- Tiempo que dura el bloqueo si no termina la transacción o la sesión (en milisegundos) [ , [ @DbPrincipal = ] 'database_principal' ] -- Usuario que ejecuta el bloqueo [ ; ]
Solución. Añadiendo sp_getAppLock
Si se ejecuta el procedimiento en el ejemplo anterior, se observa como ya no se devuelven los valores duplicados, y las sesiones tienen que esperar a que se libere el bloqueo, que es cuando se confirma el UPDATE, y adquieren el valor siguiente.
Sesión 1
1> BEGIN 2> select * from dbo.cod_Alumno 3> Begin Tran 4> EXEC sp_getapplock @Resource = 'Block_alumnos' , 5> @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = '100000', @DbPrincipal = 'public' 6> ; 7> update cod_Alumno set identificador=identificador+1 8> WAITFOR DELAY '00:00:10' 9> Commit 10> END 11> GO Tiempo de análisis y compilación de SQL Server: Tiempo de CPU = 0 ms, tiempo transcurrido = 683 ms. identificador ------------- 34962 (1 filas afectadas) Tabla 'cod_Alumno'. Recuento de exámenes 1, lecturas lógicas 1, lecturas físicas 1, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0. Tiempos de ejecución de SQL Server: Tiempo de CPU = 0 ms, tiempo transcurrido = 130 ms. Tiempo de análisis y compilación de SQL Server: Tiempo de CPU = 0 ms, tiempo transcurrido = 1 ms. Tabla ' cod_Alumno '. Recuento de exámenes 1, lecturas lógicas 1, lecturas físicas 1, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0. Tiempos de ejecución de SQL Server: Tiempo de CPU = 15 ms, tiempo transcurrido = 811 ms. (1 filas afectadas) Tiempos de ejecución de SQL Server: Tiempo de CPU = 0 ms, tiempo transcurrido = 10001 ms.
Sesión 2
Se ejecuta dentro de esos 10 segundos
1> select * from dbo.cod_Alumno 2> go Tiempo de análisis y compilación de SQL Server: Tiempo de CPU = 0 ms, tiempo transcurrido = 156 ms. identificador ------------- 34963 (1 filas afectadas) Tabla 'cod_Alumno'. Recuento de exámenes 1, lecturas lógicas 1, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0. Tiempos de ejecución de SQL Server: Tiempo de CPU = 0 ms, tiempo transcurrido = 7788 ms.
Esta segunda sesión tarda en ejecutarse casi 8 segundos, mientras que en el primer ejemplo tarda 1 décima de segundo. Esto es debido a que tiene que esperar a que termine el bloqueo. Después de terminar, el procedimiento devuelve el valor siguiente, por lo tanto ya no habrá conflicto con la Primary Key y no se devolverá el error.
Si no quieres perderte artículos como éste, del mundo de las bases de datos, Open Source y Linux, regístrate a nuestra newsletter. Con un solo email al mes, estarás al día de nuestras publicaciones.
Esperamos que os sea de utilidad. Nos vemos en próximas entradas.
Un saludo,
Equipo de Base de datos
