SQL Server, La trompeuse xlock & optimisations
D'après des tests récents et des lectures que j'ai faites, il semble que la partie du nom "X" (exclusif) de XLOCK soit trompeuse. En fait, il ne se verrouille pas plus que UPDLOCK. S'il était exclusif, il empêcherait les sélections externes, ce qui n'est pas le cas.
Je ne vois ni d'après la lecture ni d'après les tests et la différence entre les deux.
le seul moment où XLOCK crée une serrure exclusive est lorsqu'il est utilisé avec TABLOCK. Ma première question est: "pourquoi seulement à ce granularité?"
plus loin, je suis tombé sur un blog qui indique ce qui suit:
cependant, attention à xlock hint. SQL Server va effectivement ignorer xlock hint! Il y a une optimisation où SQL Server vérifie si les données ont changé depuis la plus ancienne transaction ouverte. Si non, alors un xlock est ignoré. Cela rend xlock fondamentalement inutile et devrait être évitée.
quelqu'un A couru à travers ce phénomène?
D'après ce que je vois, il semble que cet indice devrait être ignoré.
3 réponses
exclusivité de X serrures vs U serrures
Dans le verrouiller le tableau de compatibilité ci-dessous on peut voir que le X lock n'est compatible qu'avec les types de lock schema stability et Insert Range-Null. U est compatible avec les types de serrures partagées supplémentaires suivants S/IS/RS-S/RI-S/RX-S
.gif){kind=link}
Granularité de X verrous
elles sont prises bien à tous les niveaux. La trace du script et du profileur ci-dessous montre qu'ils ont été retirés avec succès au niveau de la ligne.
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
Mais les lignes peuvent encore être lus!
Il s'avère que read committed niveau d'isolation de SQL Server ne sera pas toujours prendre S serrures, il va sauter cette étape s'il n'y a aucun risque de lire des données non engagées sans eux. Cela signifie qu'il n'y a aucune garantie qu'un conflit de serrures se produise jamais.
cependant si la sélection initiale est with (paglock,XLOCK) puis arrêtez le mouvement de lecture comme le X le verrouillage de la page bloque le IS page lock qui sera toujours nécessaire par le lecteur. Cela aura bien sûr un impact sur la concurrence.
Autres Avertissements
même si vous verrouillez la ligne/page, cela ne signifie pas que vous bloquez tous les accès à cette ligne dans la table. Un verrou sur une ligne de l'index groupé n'empêchera pas les requêtes de lire les données de la ligne correspondante dans un index couvrant non groupé.
ce n'est pas une mise en garde, c'est un malentendu sur ce qui se passe dans SELECT.
un simple SELECT ne demande pas de serrures partagées si les pages ne contiennent pas de données sales, et n'est donc pas bloqué par XLOCK.
pour être bloqué par XLOCK, vous devez exécuter dans le niveau D'isolement de lecture répétable. Deux choses peuvent déclencher que:
- Modifier les données, par insertion / mise à jour / suppression. La table mise à jour ne doit pas nécessairement être celle que XLOCK est sur.
- demander explicitement que le niveau d'isolement de la transaction ou l'indice du tableau soit répétable.
basé sur les commentaires dans la réponse de@Martin, voici un petit script (exécutez les différentes parties dans différentes fenêtres SSMS pour tester la serrure empêchant 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