Risoluzione Deadlock su Tabella utilizzata come coda di elaborazione

Esigenza:

Data una struttura di Server costituita da
• una batteria di Web Server che ricevono files di log, li salvano su Disco ed inseriscono un record sulla tabella che viene usata come coda;
• una batteria di Server di Parserizzazione;
• un Database Server.

L’esigenza è quella di caricare su Database il contenuto di una serie di files di Log. I Log vengono caricati tramite Parsers che girano su batterie di servers (da 1 a 16).
Il software di Parserizzazione
• legge la tabella che viene utilizzata come coda;
• prende il nome codificato del file che deve essere elaborato;
• controlla che il file di Log sia formalmente corretto;
• carica il contenuto del File su Database tramite Stored Procedures.

La tabella usata come coda ha la seguenta struttura

CREATE TABLE [dbo].[IncomingFile](
 [IDIncomingFile] [int] IDENTITY(1,1) NOT NULL,
 [IDStatus] [int] NOT NULL,
 [IDPriority] [int] NOT NULL,
 [vcIncomingFileHash] [varchar](40) NOT NULL,
[dtIncomingFileDate] [datetime] NOT NULL CONSTRAINT [DF_IncomingFile_dtIncomingFileDate]  DEFAULT (getdate()),
 [vcIncomingFileSenderIP] [varchar](39) NOT NULL
 CONSTRAINT [PK_IncomingFile] PRIMARY KEY NONCLUSTERED
(
 [IDIncomingFile] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]

CREATE CLUSTERED INDEX [IDX_IncomingFile] ON [dbo].[IncomingFile]
(
 [IDStatus] DESC,
 [IDPriority] DESC,
 [dtIncomingFileDate] ASC
)
WITH
(PAD_INDEX  = OFF,STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON [PRIMARY]

Gli Stati possibili (IDStatus) sono
IDStatus 8 Da Elaborare
IDStatus 7 In Elaborazione
IDStatus 6 Elaborato
IDStatus 5 Errore di Elaborazione

Le priorità possibili (IDPriority) sono
IDPriority 10 Alta
IDPriority 9 Media
IDPriority 8 Bassa

I File devono essere elaborati in base
1. allo stato (nel nostro caso = 8)
2. alla priorità
3. alla data di arrivo al Web Server (dtIncomingFileDate)
Avendo definito il Clustered Index come sopra riportato, ho la certezza che i file con stato alto, con priorità alta e con data meno recente saranno messi al primo posto nella tabella. In questo modo la tabella è fisicamente la mia coda con i suoi criteri di ordine di elaborazione.
Inoltre devo avere la certezza che un File di Log venga elaborato e venga elaborato SOLO una volta. Pertanto devo preoccuparmi che una volta preso in carico un file per elaborarlo, questo file cambi stato istantanemente, andando in IDStatus = 7 (In Elaborazione), per evitare che un altro processo ne tenti l’elaborazione.
La Stored Procedure che dà deadlock è così fatta:

CREATE PROCEDURE [dbo].[prs_IncomingFileGet] @p_vcIncomingFileHash varchar(40)  = NULL output         
AS

DECLARE @ownTransaction int 
DECLARE @et varchar(255)    
DECLARE @ErrorCode  int
SET @ErrorCode = 0
SET @ownTransaction = 0

IF @@TRANCOUNT = 0
 BEGIN
 BEGIN TRANSACTION
 SET @ownTransaction = 1
 END 

(1) UPDATE IncomingFile SET IdStatus = 7, @p_vcIncomingFileHash = vcIncomingFileHash 
WHERE IDIncomingFile = (select top(1) IDIncomingFile FROM IncomingFile I WHERE I.IDStatus = 8 ORDER BY IDStatus DESC, IdPriority DESC, dtIncomingFileDate) SET @ErrorCode = @@ERROR
  
IF @ErrorCode <> 0
   BEGIN
   GOTO ERRTransaction
   END 

— Error Managed
ERRTransaction:
 
 IF @ErrorCode <> 0
  BEGIN 
  IF @ownTransaction = 1
   ROLLBACK TRANSACTION
  END
 ELSE
  BEGIN
   IF @ownTransaction = 1
    COMMIT TRANSACTION
  END

L’istruzione di Update al punto (1) serve a restituire il nome del PRIMO File (in realtà l’Hash del file) da elaborare al Parser, e contestualmente lo toglie (con l’update dell’IDStatus a 7) dall’inizio della coda. Lo statement è velocissimo perchè utilizza il Clustered e il Non-Clustered Index e funziona benissimo con 1 solo Parser che elabora la coda. Ma non appena il numero di Parser aumenta da 2 a 16, il log di SQL inizia a rilevare dei deadlock e non solo uno ogni tanto: tutte le volte che se ne verifica uno,  in cascata vengono rilevati una serie di altri deadlock.
Le condizioni che il file venga elaborato e venga elaborato SOLO una volta rimangono verificate, perchè il deadlock manda in rollback la transazione e quindi l’Update non viene fatto: per questo motivo il record del file da elaborare rimane tale. Tuttavia le condizioni di deadlock non sono mai un evento indolore per SQL Server, il quale recupera la situazione ma con pesanti rallentamenti nel caricamento dei file.
Perchè si verifica il deadlock in una Stored Procedure così semplice?
Essenzialmente per la concorrenza dei Parser che leggono e modificano il contenuto della tabella. Operativamente, leggendo l’Execution Plan, succede che il primo processo (che chiameremo Proc1) che accede alla tabella effettua PRIMA l’operazione 

SELECT TOP(1) IDIncomingFile FROM IncomingFile I WHERE I.IDStatus = 8 ORDER BY IDStatus DESC, IdPriority DESC, dtIncomingFileDate

che fissa un lock di tipo Shared sul record.
Successivamente il parser tenta di fare l’operazione di Update

UPDATE IncomingFile SET IdStatus = 7, @p_vcIncomingFileHash = vcIncomingFileHash WHERE IDIncomingFile =……

Se ci sono altri parser (Proc2, ….., ProcN) che riescono ad accedere al record PRIMA che ne venga fatto l’update, anche questi parser mettono un lock di tipo Shared sul record. Una delle caratteristiche del lock di tipo Shared è quella di rendere NON modificabile il record, fintanto che TUTTI i lock di tipo Shared non vengano rilasciati, il che avviene solo dopo che il record è stato letto oppure quando la transazione viene chiusa.
Non appena il Proc2 legge il record, mette il lock di tipo Shared sullo stesso record. Quando Proc1 tenta l’update, cerca di elevare il suo lock di tipo Shared (“S”) a lock di tipo Esclusivo (“X”), ma non ci riesce perchè si trova il lock di tipo Shared che il Proc2 ha messo sullo stesso record. Quando Proc2 tenta anche lui di fare l’Update, non ci riesce per via del lock di tipo Shared che Proc1 ha ancora sul record. Morale della favola: deadlock. Sql Server decide arbitrariamente di uscire dalla situazione, facendo la Kill di uno dei due processi.
Il problema è stato risolto con l’utilizzo di Hint sulla SELECT

UPDATE IncomingFile SET IdStatus = 7, @p_vcIncomingFileHash = vcIncomingFileHash 
WHERE IDIncomingFile = (SELECT TOP(1) IDIncomingFile FROM IncomingFile I WITH  (UpdLock, ReadPast) WHERE I.IDStatus = 8 ORDER BY IDStatus DESC, IdPriority DESC, dtIncomingFileDate)

I due hint utilizzati,consentono di mettere sùbito un lock di tipo esclusivo al record (UpdLock): in questo modo evito il deadlock. Il problema è che se usassi solo l’Hint Updlock, Proc1 metterebbe un lock esclusivo sul record. Proc2 rimarrebbe in attesa (lock e non in deadlock) finchè Proc1 non finisce la sua elaborazione con tanto di Commit o Rollback Transaction sul record. Utilizzando anche l’Hint ReadPast, dico semplicemente a Proc2 di prendere il record successivo se quello corrente è in lock Esclusivo. In questo modo, in qulsiasi momento io abbia un processo che mi crea un lock su un record, i successivi non si fermano, ma leggono i record successivi fino a quando non trovano un record privo di lock.