[T-SQL]verrou sur une ligne avant un select [Résolu]

[dinobogan]

Bonjour à tous,

je fouine partout depuis 2 jours dans les forums et les doc mais sans succès.
Voici le code (fortement simplifié) de la procédure stockée qui permet de faire des débits sur un compte (je ne met pas la gestion des erreurs par exemple) :

Code :

1
2
3
4
5
6
7
 
SELECT @NOUVEAU_SOLDE=SOLDE FROM COMPTE WHERE ID = @ID
begin transaction trans
-- pleins de tests pour modifier @NOUVEAU_SOLDE et faire un débit
UPDATE COMPTE SET SOLDE = @NOUVEAU_SOLDE WHERE ID = @ID
-- pleins d'autres insert, notemment dans des historiques
commit ou rollback selon divers tests

Il y a un problème d'exclusion mutuelle au niveau du SELECT :
Thread_1 recupere NOUVEAU_SOLDE_1
Thread_2 recupere NOUVEAU_SOLDE_1
Thread_1 modifie sa valeur NOUVEAU_SOLDE_1 en NOUVEAU_SOLDE_2
Thread_2 modifie sa valeur NOUVEAU_SOLDE_1 en NOUVEAU_SOLDE_3
Thread_1 update SOLDE avec NOUVEAU_SOLDE_2, puis commit
Thread_2 update SOLDE avec NOUVEAU_SOLDE_3, puis commit

le calcul de la nouvelle valeur NOUVEAU_SOLDE_2 est perdue et écrasée par le Thread_2. Donc le débit fait par Thread_1 n'est jamais répercuté sur le compte client :-(

Il aurait fallut que Thread_2 patiente jusqu'au commit de Thread_1 pour qu'il utilise NOUVEAU_SOLDE_2 au lieu de NOUVEAU_SOLDE_1.

J'ai trouvée une solution que je trouve très moche. La voici :

Code :

1
2
3
4
5
6
7
8
 
begin transaction trans
UPDATE COMPTE SET SOLDE = ( SELECT SOLDE FROM COMPTE WHERE ID = @ID ) WHERE ID = @ID
SELECT @NOUVEAU_SOLDE=SOLDE FROM COMPTE WHERE ID = @ID
-- pleins de tests pour modifier @NOUVEAU_SOLDE
UPDATE COMPTE SET SOLDE = @NOUVEAU_SOLDE WHERE ID = @ID
-- pleins d'autres insert
commit ou rollback selon divers tests

Le premier update sert uniquement à mettre un verrou sur la ligne que je vais modifier. Ca fonctionne très bien. Le second thread attend que la transaction se termine avant d'entrer dans le premier update.
Mais y-a-t-il une solution propre et standard ?

Dans mes recherches, j'ai donc exploré pleins de techniques, trouvées sur les forums et autres doc :
- débuter la transaction avant le select, en mettant un "at isolation" sur le select, mais j'ai un deadlock
- mettre un holdlock sur le select, mais ça ne change rien

Il y a aussi la solution du curseur sur le SELECT, avec un "FOR UPDATE". Mais ce select renvoit soit une ligne, soit aucune ligne. Donc faire un curseur me parait un peu lourd. De plus j'ai peur que ça utilise trop de ressources. En effet, cette procédure est appelée sur la journée en moyenne 900 fois par secondes, et atteint en pic deux fois par jour 1400 fois par secondes. Et pour la table COMPTE, la voici :

Code :

1
2
3
4
5
6
 
1> sp_spaceused COMPTE
2> go
 name                 rowtotal    reserved        DATA            index_size      unused
 -------------------- ----------- --------------- --------------- --------------- ---------------
 COMPTE           46223277    7002578 KB      4954592 KB      1989784 KB      58202 KB

la solution envisagée pour le moment (et utilisée dans une autre procédure stockée qui gère d'autres types de comptes) est de placer les calculs erronés dans une table via un algo légèrement tarabiscoté, et de traiter cette table la nuit à l'aide d'un batch lorsque le traffic est le plus bas. Cette solution est pourrie et laisse passer quelques "débit gratuit". Il me faut donc une solution qui règle le problème d'accès concurrent "en live".

PS : merci d'avoir été jusqu'au bout de mon explication ;-)

[mpeppler]

Envoyé par dinobogan

J'ai trouvée une solution que je trouve très moche. La voici :

Code :

Le premier update sert uniquement à mettre un verrou sur la ligne que je vais modifier.

Cette solution me semble tout à fait raisonnable.

Une alternative consiste à avoir une table de type sémaphore pour faire un lock "logique". Le problème est que cela risque de créer un "hot point", avec les problèmes de perfs associées.

Le holdlock (ou isolation 3) aurait à priori dû marcher, pour autant que la transaction est démarrée avant le select:

Code :

1
2
3
4
5
6
 
begin tran
SELECT ... FROM ... WHERE ... at isolation 3
-- on fait les modifs, etc.
UPDATE ...
commit

Dans tous les cas j'éviterais l'utilisation d'un curseur pour ce genre de chose, surtout au vu du nombre d'exécutions par seconde que tu annonces.

Michael

[mpeppler]

Après un petit test je vois que le "select ... at isolation 3" pose un verrou de type shared, ce qui ne sert évidemment à rien.

Donc ta première solution semble la plus adaptée.

Michael

[dinobogan]

Merci beaucoup !
Je garde donc ma solution pas super propre avec l'update