Confuso su UPDLOCK, HOLDLOCK

Durante la ricerca sull’uso dei Table Hints , mi sono imbattuto in queste due domande:

Le risposte a entrambe le domande dicono che quando si utilizza (UPDLOCK, HOLDLOCK) , altri processi non saranno in grado di leggere i dati su quella tabella, ma non l’ho visto. Per testare, ho creato una tabella e avviato due windows SSMS. Dalla prima finestra, ho eseguito una transazione che è stata selezionata dalla tabella utilizzando vari suggerimenti sulla tabella. Mentre la transazione era in esecuzione, dalla seconda finestra ho eseguito varie istruzioni per vedere quali sarebbero state bloccate.

La tabella di prova:

 CREATE TABLE [dbo].[Test]( [ID] [int] IDENTITY(1,1) NOT NULL, [Value] [nvarchar](50) NULL, CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] 

Dalla finestra 1 di SSMS:

 BEGIN TRANSACTION SELECT * FROM dbo.Test WITH (UPDLOCK, HOLDLOCK) WAITFOR DELAY '00:00:10' COMMIT TRANSACTION 

Dalla finestra SSMS 2 (è stato eseguito uno dei seguenti):

 SELECT * FROM dbo.Test INSERT dbo.Test(Value) VALUES ('bar') UPDATE dbo.Test SET Value = 'baz' WHERE Value = 'bar' DELETE dbo.Test WHERE Value= 'baz' 

Effetto di diversi suggerimenti tabella su istruzioni eseguite in Finestra 2:

  (UPDLOCK) (HOLDLOCK) (UPDLOCK, HOLDLOCK) (TABLOCKX) --------------------------------------------------------------------------- SELECT not blocked not blocked not blocked blocked INSERT not blocked blocked blocked blocked UPDATE blocked blocked blocked blocked DELETE blocked blocked blocked blocked 

Ho frainteso le risposte fornite in quelle domande, o ho commesso un errore nei miei test? In caso contrario, perché dovresti usare (UPDLOCK, HOLDLOCK) contro (HOLDLOCK) da solo?


Ulteriore spiegazione di ciò che sto cercando di realizzare:

Vorrei selezionare le righe da una tabella e impedire che i dati in quella tabella vengano modificati mentre li sto elaborando. Non sto modificando quei dati e vorrei consentire l’esecuzione delle letture.

Questa risposta dice chiaramente che (UPDLOCK, HOLDLOCK) bloccherà le letture (non quello che voglio). I commenti su questa risposta implicano che è HOLDLOCK che impedisce le letture. Per cercare di capire meglio gli effetti dei suggerimenti della tabella e vedere se UPDLOCK da solo farebbe ciò che volevo, ho fatto l’esperimento di cui sopra e ho ottenuto risultati che contraddicono quelle risposte.

Attualmente, credo che (HOLDLOCK) sia quello che dovrei usare, ma (HOLDLOCK) aver commesso un errore o di aver trascurato qualcosa che tornerà a mordermi in futuro, quindi questa domanda.

Perché scegliere il blocco UPDLOCK? La Lock Compatibility Matrix mostra chiaramente N per il conflitto S / U e U / S, come in No Conflict .

Per quanto riguarda il suggerimento HOLDLOCK, la documentazione indica:

HOLDLOCK: equivale a SERIALIZABLE. Per ulteriori informazioni, vedere SERIALIZABLE più avanti in questo argomento.

SERIALIZZABILE: … La scansione viene eseguita con la stessa semantica di una transazione in esecuzione al livello di isolamento SERIALIZABLE …

e l’argomento Livello di isolamento della transazione spiega che cosa significa SERIALIZABLE:

Nessuna altra transazione può modificare i dati letti dalla transazione corrente fino al completamento della transazione corrente.

Altre transazioni non possono inserire nuove righe con valori chiave che rientrerebbero nell’intervallo di chiavi lette da qualsiasi istruzione nella transazione corrente fino al completamento della transazione corrente.

Pertanto il comportamento che vedi è perfettamente spiegato dalla documentazione del prodotto:

  • UPDLOCK non blocca SELECT simultaneo né INSERT, ma blocca qualsiasi UPDATE o DELETE delle righe selezionate da T1
  • HOLDLOCK significa SERALIZABLE e quindi consente SELEZIONA, ma blocca UPDATE e DELETES delle righe selezionate da T1, così come qualsiasi INSERT nell’intervallo selezionato da T1 (che è l’intera tabella, quindi qualsiasi inserto).
  • (UPDLOCK, HOLDLOCK): il tuo esperimento non mostra cosa bloccherebbe oltre al caso precedente, vale a dire un’altra transazione con UPDLOCK in T2 :
    SELECT * FROM dbo.Test WITH (UPDLOCK) WHERE ...
  • TABLOCKX non ha bisogno di spiegazioni

La vera domanda è cosa stai cercando di ottenere ? Giocare con i suggerimenti di blocco senza una comprensione completa del 110% della semantica di blocco è un accenno ai problemi …

Dopo la modifica OP:

Vorrei selezionare le righe da una tabella e impedire che i dati in quella tabella vengano modificati mentre li sto elaborando.

Dovresti utilizzare uno dei livelli di isolamento della transazione più elevati. REPEATABLE READ impedirà la modifica dei dati che hai letto. SERIALIZABLE impedirà la modifica dei dati da leggere e l’inserimento di nuovi dati. L’utilizzo dei livelli di isolamento delle transazioni è l’approccio giusto, anziché utilizzare i suggerimenti per le query. Kendra Little ha un bel poster che espone i livelli di isolamento .

UPDLOCK viene utilizzato quando si desidera bloccare una riga o righe durante un’istruzione select per una futura dichiarazione di aggiornamento. L’aggiornamento futuro potrebbe essere la prossima dichiarazione nella transazione.

Altre sessioni possono ancora vedere i dati. Semplicemente non possono ottenere serrature che non sono compatibili con UPDLOCK e / o HOLDLOCK.

Si utilizza UPDLOCK quando si desidera mantenere altre sessioni dalla modifica delle righe bloccate. Limita la loro capacità di aggiornare o eliminare le righe bloccate.

Si utilizza HOLDLOCK quando si desidera impedire alle altre sessioni di modificare i dati che si stanno guardando. Limita la loro capacità di inserire, aggiornare o eliminare le righe bloccate. Ciò consente di eseguire nuovamente la query e vedere gli stessi risultati.