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.
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 ?
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...
