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.