update - SQL Server, el engañoso XLOCK y optimizaciones
sql server with tablock (3)
De algunas pruebas y lecturas recientes que he hecho, parece que el nombre "X" (exclusivo) de XLOCK es engañoso. De hecho, no bloquea más que UPDLOCK. Si fuera exclusivo, evitaría SELECT externos, lo que no ocurre.
No puedo ver ni de la lectura ni de las pruebas y la diferencia entre los dos.
La única vez que XLOCK crea un bloqueo exclusivo es cuando se usa con TABLOCK. Mi primera pregunta es "¿por qué solo en esta granularidad?"
Además, me encontré con un blog que dice lo siguiente:
Sin embargo, ten cuidado con la pista XLOCK. ¡SQL Server ignorará efectivamente la pista de XLOCK! Hay una optimización donde SQL Server comprueba si los datos han cambiado desde la transacción abierta más antigua. De lo contrario, se ignora un xlock. Esto hace que las pistas de xlock sean básicamente inútiles y deberían evitarse.
¿Alguien ha encontrado este fenómeno?
Según lo que estoy viendo, parece que esta sugerencia debe ser ignorada.
Exclusividad de cerraduras X frente a cerraduras U
En la matriz de compatibilidad de bloqueo a continuación, se puede ver que el bloqueo X solo es compatible con la estabilidad del esquema y los tipos de bloqueo Insert Range Range-Null. U es compatible con los siguientes tipos de cerradura compartida adicional S / IS / RS-S / RI-S / RX-S
.gif){kind=link}
Granularidad de X cerraduras
Estos se toman bien en todos los niveles. La secuencia de comandos y el seguimiento del generador de perfiles a continuación demuestran que se eliminaron con éxito en el nivel de la fila.
CREATE TABLE test_table (id int identity(1,1) primary key, col char(40))
INSERT INTO test_table
SELECT NEWID() FROM sys.objects
select * from test_table with (rowlock,XLOCK) where id=10
¡Pero todavía se pueden leer las filas!
Resulta que al read committed nivel de aislamiento read committed SQL Server no siempre eliminará bloqueos S , saltará este paso si no hay riesgo de leer datos no confirmados sin ellos . Esto significa que no hay garantía de que se produzca un conflicto de bloqueo.
Sin embargo, si la selección inicial es with (paglock,XLOCK) , esto detendrá la transacción de lectura ya que el bloqueo X en la página bloqueará el bloqueo de la página IS que siempre necesitará el lector. Esto, por supuesto, tendrá un impacto en la concurrencia.
Otras advertencias
Incluso si bloquea la fila / página esto no significa que bloquea todos los accesos a esa fila en la tabla. Un bloqueo en una fila del índice agrupado no evitará que las consultas que lean datos de la fila correspondiente cubran un índice no agrupado.
No es una advertencia, es un malentendido sobre lo que sucede en SELECT.
Un mero SELECT no solicita bloqueos compartidos si las páginas no contienen datos sucios y, por lo tanto, XLOCK no lo bloquea.
Para ser bloqueado por XLOCK, debe ejecutar el nivel de aislamiento REEATABLE READ. Dos cosas pueden desencadenar eso:
- Modificar datos, a través de INSERT / UPDATE / DELETE. La tabla actualizada no tiene que ser en la que está el XLOCK.
- Solicitando explícitamente LECTURA REPETIBLE a través del nivel de aislamiento de transacción o la sugerencia de tabla.
basado en los comentarios en la respuesta de @ Martin , aquí hay un pequeño script (ejecuta las diferentes partes en diferentes ventanas de SSMS para probar el bloqueo, lo que evita un SELECT:
--
--how to lock/block a SELECT as well as UPDATE/DELETE on a particular row
--
--drop table MyTable
--set up table to test with
CREATE TABLE MyTable (RowID int primary key clustered
,RowValue int unique nonclustered not null)
--populate test data
;WITH InsertData AS
(
SELECT 4321 AS Number
UNION ALL
SELECT Number+1
FROM InsertData
WHERE Number<9322
)
INSERT MyTable
(RowID,RowValue)
SELECT
Number, 98765-Number
FROM InsertData
ORDER BY Number
OPTION (MAXRECURSION 5001)
-----------------------------------------------------------------------------
-- #1
--OPEN A NEW SSMS window and run this
--
--create lock to block select/insert/update/delete
DECLARE @ID int
BEGIN TRANSACTION
SELECT @ID=RowID FROM MyTable WITH (ROWLOCK, XLOCK, HOLDLOCK) WHERE RowID=6822
PRINT @ID
--COMMIT --<<<only run the commit when you want to release the lock
--<<<adfter opening the other new windows and running the SQL in them
-----------------------------------------------------------------------------
-- #2
--OPEN A NEW SSMS window and run this
--
--shows how a select must wait for the lock to be released
--I couldn''t get SSMS to output any text while in the trnasaction, even though
--it was completing those commands (possibly buffering them?) so look at the
--time to see that the statements were executing, and the SELECT...WHERE RowID=6822
--was what was where this script is blocked and waiting
SELECT GETDATE() AS [start of run]
SELECT ''1 of 2, will select row'',* FROM MyTable Where RowID=6822
go
DECLARE @SumValue int
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT GETDATE() AS [before transaction, shouldn''t be nuch difference]
BEGIN TRANSACTION
SELECT @SumValue=SUM(RowID) FROM MyTable WHERE ROWID<6000
SELECT GETDATE() AS [in transaction, shouldn''t be much difference]
, @SumValue AS SumValue
--everything to here will run immediately, but the select below will wait for the
-- lock to be removed
SELECT ''2 of 2, will wait for lock'',* FROM MyTable Where RowID=6822
SELECT GETDATE() AS [in transaction after lock was removed, should show a difference]
COMMIT
-----------------------------------------------------------------------------
-- #3
--OPEN A NEW SSMS window and run this
--
--show how an update must wait
UPDATE MyTable SET RowValue=1111 WHERE RowID=5000 --will run immediately
GO
UPDATE MyTable SET RowValue=1111 WHERE RowID=6822 --waits for the lock to be removed
-----------------------------------------------------------------------------
-- #4
--OPEN A NEW SSMS window and run this
--
--show how a delete must wait
DELETE MyTable WHERE RowID=5000 --will run immediately
go
DELETE MyTable WHERE RowID=6822 --waits for the lock to be removed