[T-SQL]verrou sur une ligne avant un select
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 ;-)
