Délai de verrou de requête dépassé MS SQL

Question

Bonjour, 

Mon problème est que j'ai cette erreur 1222 : Délai de verrou de requête dépassé.

Alors elle se passe dans deux cas, toujours avec une transaction.

1er cas :
DELETE FROM matable suivit de INSERT INTO matable

2eme cas:
juste au deuxième insert dans une même table

J'ai essayé plusieurs type de level de blocage. mais cela revient sans cesse.

Des idées, c'est assez urgent.

Merci

yann_lo_san · Answer

Salut,

J'ai eu 1 cas similaire avec des transactions "nommées" et des accès concurents.
le fait de revenir à des transactions "anonymes" à partiellement corrigé le problème.
De plus j'ai utilisé l'annulation de transaction automatique et
un try/catch afin de repérer l'erreur de lock et pouvoir réessayer l'instruction SQL plusieurs fois ou jusqu'à ce que l'instruction soit réussie, cela donnait à peu près un truc du genre :

(Ceci ne convient QUE pour les problèmes d'accès concurents/locks,
il se peut que ce ne soit pas la solution à ton problème)

SET XACT_ABORT ON;

DECLARE @maxRetry int ; SET @maxRetry = 5
DECLARE @lastError int

RETRY_COMMAND:
SET @lastError = 0

BEGIN TRY
    BEGIN TRANSACTION
        -- INSERT / UPDATE / DELETE
        -- (...)
    COMMIT TRAN
END TRY
BEGIN CATCH
    print ERREOR_MESSAGE()
    SET @lastError = ERROR_NUMBER()
    IF XACT_STATE() = -1
        ROLLBACK TRAN;
    -- Ici si erreur de lock, alors recommence dans une seconde
    IF @lastError in(1222, 1207) and @maxRetry > 0
    BEGIN
        WAITFOR delay '00:00:01'
        SET @maxRetry = @maxRetry - 1 
        GOTO RETRY_COMMAND;
    END
END CATCH

IF @lastError = 0
    print 'SUCCES'
ELSE
    print 'ECHEC'


Il faut vérifier le numéro d'erreur 1207, je ne me rappelle plus exactement si c'est bien ça.

bye...

Sebcmoi62 · Answer

Bonjour,

Avec des transactions "anonymes", j'ai toujours le problème.
J'avais déjà tenter de ré-exécuter la requête mais avec un temps d'attente du coté applicatif client.

Quelle type de transaction (level) que vous me conseillez ?

Est-ce normal que lorsque l'on fait un "sp_who" pendant l'exécution du programme on voit au moins 3 sessions ?

Merci

yann_lo_san · Answer

Salut,

Je crois que c'est l'isolation par défaut, SERIALIZABLE.

Pour le sp_who, tout dépend de combien d'instance du programme "client" s'exécute en même temps.

Je te conseille de faire : sp_who2 'active' pour n'avoir que les process actifs

Il peut y avoir un autre problème dans ton cas, c'est les transactions systèmes créées par les différents drivers OleDB, ODBC ect...

Que fait exactement ton programme client (quel driver, quel langage ?)

4 suggestions :

1 : Faire des procédures stockées et ne pas utiliser de transaction coté client

2 : les INDEXs
Es-ce que tes tables contiennent bien des index (PK, FK, UK ect...)

3 : Forcer les INDEXs sensibles et mettre des (nolock) dans les selections internes à la transaction
Exemple : select * from TABLE1 with (index= IX_C1,  nolock )

4 : Les INDEX sur les dates :
Il vaut mieux faire 2 champs, 1 pour date dd/MM/yyyy ET 1 pour heure hh:mm:ss 
plutot qu'un seul champ date (si un index est dessus bien sur)

Une méthode existe pour tracer exactement quelle table ou instruction provoque le lock, c'est le trigger "inline"

Exemple : 
imaginons 1 insert sur cette table
TABLE1(t1_1 type, t1_2 type) 

et un delete sur cette table
TABLE2(t2_1 type, t2_2 type)

-- Crée une table TMP sur le modèle de TABLE1
DECLARE @TMP_T1 TABLE(c1 type, c2 type)

-- Crée une table tmp sur le modèle de TABLE2
DECLARE @TMP_T2 TABLE(c1 type, c2 type)

-- insert avec trigger inline
INSERT INTO TABLE1(t1_1, t1_2)
    OUTPUT inserted.t1_1, inserted.t1_2
    INTO @TMP_T1(c1, c2)
SELECT 'toto', 'titi'

-- delete avec trigger inline
DELETE TABLE2
    OUTPUT deleted.t2_1, deleted.t2_2
    INTO @TMP_T2(c1, c2)
WHERE t2_1  = 'toto'

-- il est ainsi facile de retrouver les lignes insérées ou supprimées en selectionnant les tables temporaires en fin de procédure et en analysant quelle ligne à fait le problème (si rowlock) ou quelle table (si tablelock)


En conclusion,
sans connaitre ton programme "client", cela reste de la supposition...

Bonne chance pour la suite,
bye.

Délai de verrou de requête dépassé MS SQL

3 réponses

Votre réponse

sur les mêmes thèmes