SQL Server : comment acquérir un verrou exclusif pour éviter une condition de course ?

Question

J'ai le code T-SQL suivant :

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION T1_Test

    /*This is a dummy table used for "locking" 
    and it doesn't contain any meaningful data.*/        
    UPDATE lockTable 
        SET ID = 1    
        WHERE ID = 1

    DECLARE @Count TINYINT 

    SELECT @Count = COUNT(*)
    FROM debugSP 

    WAITFOR DELAY '00:00:5';

    INSERT INTO debugSP 
        (DateCreated, ClientId, Result)
    SELECT 
        GETDATE(), @@SPID, @Count

COMMIT TRANSACTION T1_Test

J'utilise le hack "locking" marqué d'un commentaire pour acquérir le verrou exclusif.

NOTE : l'utilisation des indices TABLOCKX ou UPDLOCK ne fonctionnera pas car j'ai cassé l'ATOMIC-ité en divisant les déclarations et en ajoutant la commande WAITFOR au milieu à des fins de test. Je ne veux pas quelque chose comme ça :

INSERT INTO debugSP (DateCreated, ClientId, Result)
SELECT GETDATE(), @@SPID, COUNT(*) 
FROM debugSP

Voici le résultat correct après avoir exécuté deux sessions simultanées (avec table de verrouillage)

Id DateCreated           ClientId Result
-- ----------------------- -------- ------
 1 2011-03-17 15:52:12.287       66      0
 2 2011-03-17 15:52:24.534       68      1

et c'est le résultat incorrect de l'exécution du code avec le verrou commenté

Id DateCreated           ClientId Result
-- ----------------------- -------- ------
 1 2011-03-17 15:52:43.128       66      0
 2 2011-03-17 15:52:46.341       68      0

Existe-t-il un meilleur moyen d'obtenir un verrou exclusif à l'échelle de la transaction sans avoir recours à de telles astuces ?

Answer 1

Je ne sais pas exactement ce que vous essayez de faire à partir du code affiché. Je présume que vous essayez simplement de sérialiser l'accès à ce morceau de code ? Si c'est le cas sp_getapplock devrait faire ce dont vous avez besoin au lieu de créer une nouvelle table factice que vous utilisez uniquement pour prendre des verrous.

Détails ici

Answer 2

Vous pouvez utiliser l'indice de verrouillage WITH(XLOCK, ROWLOCK) dans le cadre d'une transaction avec une isolation en lecture répétable. Avec l'isolation Sérialisable, le verrou exclusif est obtenu par défaut sur une opération de lecture, donc si vous avez besoin d'une transaction particulière pour jouer en parallèle, vous pouvez spécifier un niveau de sérialisation accru pour cette transaction lors de sa création (ce que vous faites ; ce n'est pas un hack, juste la façon dont les choses sont faites en fonction de la situation).