transaction et isolation serialized

Version imprimable

Bonjour,

J'ai une question concernant les transactions serialized. J'ai mis en place une gestion de clefs comme expliqué par SQLpro dans son article : http://sqlpro.developpez.com/cours/clefs/

Voici un extrait du code de la procédure stockée de calcul de la prochaine clef :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SET_KEY ... -- calcule de la nouvelle clef SELECT @maxcle = MSK_LAST_VALUE +1 FROM SYS_DB_MASTERKEY WHERE MSK_TABLE_NAME = @NOM_TABLE -- mise à jour de la nouvelle clef dans la table des clefs UPDATE SYS_DB_MASTERKEY SET MSK_LAST_VALUE = @maxcle WHERE MSK_TABLE_NAME = @NOM_TABLE -- renvoi de la valeur de la clef SELECT @maxcle SET NOCOUNT OFF COMMIT TRANSACTION SET_KEY
Dans une transaction SERIALIZABLE Il est dit que :

- Les instructions ne peuvent pas lire des données qui ont été modifiées mais pas encore validées par d'autres transactions.
- Aucune autre transaction ne peut modifier des données qui ont été lues par la transaction active tant que celle-ci n'est pas terminée.
- Les autres transactions ne peuvent pas insérer de nouvelles lignes avec des valeurs de clés comprises dans le groupe de clés lues par des instructions de la transaction active, tant que celle-ci n'est pas terminée.

La question est la suivante :
Une autre transaction peut elle lire des données lues par une transaction serializable ?

Que se passe t'il si cette procédure stockée est appelée par 2 applications en même temps et demande une clef sur une même table ?
Le
SELECT @maxcle = MSK_LAST_VALUE +1
FROM SYS_DB_MASTERKEY
WHERE MSK_TABLE_NAME = @NOM_TABLE

va t'il mettre un verrou sur la table ou la ligne ?

Ne faudrait il pas rajouter un indicateur de table UPDLOCK sur le select ?

Exemple de déroulement :
Code:

1 2 3 4 5 6 7 8 9 T1 T2 TRANS select TRANS select update COMMIT update <- Cet update la est il correct COMMIT

14/12/2009, 14h57
elsuket

Bonjour,

Citation:

Une autre transaction peut elle lire des données lues par une transaction serializable ?

A quel niveau d'isolation sont les transactions T1 et T2 ?
Si elles sont toutes deux en SERIALIZABLE, l'UPDATE est bon, sous réserve que l'UPDATE de T1 se termine avant que l'UPDATE de T2 ne commence.

le hint UPDLOCK est de toute façon nécessaire pour un UPDATE, donc l'optimiseur les sautera.

@++ ;)

Ce sont 2 transactions SERIALIZABLE. Je complète l'exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
Requete R1 : SELECT @maxcle = MSK_LAST_VALUE +1
FROM   SYS_DB_MASTERKEY
WHERE  MSK_TABLE_NAME = 'PERSONNE'
 
Requete R2 : UPDATE SYS_DB_MASTERKEY
SET    MSK_LAST_VALUE = @maxcle
WHERE  MSK_TABLE_NAME = 'PERSONNE'
 
 
 
T1                                        T2
TRANS SERIALIZABLE
 
Requete 1
                                            TRANS SERIALIZABLE
                                            Requete 1           
Requete 2
COMMIT
                                            Requete 2
                                            COMMIT

T1.requete_1.@maxcle aura t'il la meme valeur que T2.requete_1.@maxcle ?
T2.requete_1 sera t'il bloqué jusqu'au commit de T1 ? Sinon je risque d'updater la même valeur, donc de renvoyer par la suite le même numéro de clef pour des valeurs qui devraient être différentes.

Après tests, vous risquez des problèmes de deadlocks:

Soit une table de test toute simple:
Code:

1 2 3 4 5 6 7 8 9 10 create table test ( id int ) insert into test select 1 --drop table test
Ensuite des transactions:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 set transaction isolation level serializable begin tran declare @id int select @id = id + 1 from test print @id waitfor delay '00:00:02' update test set id = @id commit
Ouvrez 2 fenetres queries dans ssms et copier cette transaction dans chacune. Vous pouvez regler le waitfor delay, qui permet de simuler ce que vous souhaitez mettre en évidence.

Lancer les queries l'une après l'autre.

Chez moi, voici le résultat:
Msg 1205, Level 13, State 56, Line 11
Transaction (Process ID 151) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Bon test!

Meme résultat avec ce jeu de test, plus ressemblant à votre problématique:

La table:
Code:

1 2 3 4 5 6 7 8 9 10 11 create table test ( id int, val varchar(50) ) insert into test select 1, 'test' --drop table test
La transaction:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 set transaction isolation level serializable begin tran declare @id int select @id = id + 1 from test where val = 'test' print @id waitfor delay '00:00:05' update test set id = @id where val = 'test' commit
Le résultat:
1 des 2 transactions s'effectue -> par run de 2 transactions concurrantes, 1 se termine -> incrémentation de 1 de la clef.
Pour l'autre:
Msg 1205, Level 13, State 56, Line 12
Transaction (Process ID 151) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

J'ai une solution qui fonctionne mais qui par contre bloque toute la table.
En rajoutant le hint with(UPDLOCK) sur le select :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 SET transaction isolation level READ COMMITTED begin tran declare @id int SELECT @id = id + 1 FROM test with(UPDLOCK) WHERE val = 'test' print @id waitfor delay '00:00:05' UPDATE test SET id = @id WHERE val = 'test' commit
Ici toutes les transactions s'executent et retournent le bon résultat par contre si j'ai une autre transaction qui elle fait son select avec une clause
WHERE val = 'autre' cette dernière va attendre que toutes les autres soient terminées.
J'aurais préféré trouver une solution avec un blocage au niveau de la ligne et pas au niveau de la table !

16/12/2009, 10h40
SQLpro

Rajoutez ROWLOCK dans le WITH
16/12/2009, 11h26
mikedavem
En utilisant ROWLOCK comme le conseille SQL Pro vous limiterez effectivement l'étendue de verrouillage.

Cependant une question : Avez vous un index sur votre colonne val ?

Si cela est possible dans votre cas vous pourriez éviter de mettre un indicateur de requête ROWLOCK car le moteur verrouillerait automatique au niveau de la clé (verrouillage fin).

Si vous n'avez pas d'index et que vous utilisez ROWLOCK rien ne garantit que vous allez pouvoir sélectionner d'autres valeurs dans votre table test en fonction du nombre de pages que SQL Server aura verrouillé (Vous pouvez le voir avec la DMV sys.dm_tran_locks)
Code:

1 2 3 4 5 6 7 8 9 10 SET transaction isolation level READ COMMITTED begin tran declare @id int SELECT @id = id + 1 FROM test WITH(UPDLOCK) WHERE val = 'test' print @id -- rollback
suivi de :
Code:

1 2 3 4 5 6 7 8 9 10 SET transaction isolation level READ COMMITTED begin tran declare @id int SELECT @id = id + 1 FROM test WITH(UPDLOCK) WHERE val = 'test_2' print @id -- rollback
Dans certains cas il faudra attendre que la 1ère transaction soit terminée pour que la 2ème puisse s'exécuter normalement

++

Effectivement je n'avais pas mis d'index sur cette table de test :oops:
Avec l'index en place le verrouillage se fait bien uniquement sur la ligne et non pas sur toute la table. Exactement ce que je voulais.

Par contre il faut bien indiquer WITH(UPDLOCK) sur le select. Le ROWLOCK n'a pas l'air de maintenir le blocage.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 CREATE TABLE test ( id int, val varchar(50) not null ) alter table test ADD CONSTRAINT PK_test PRIMARY KEY CLUSTERED ( val ASC ) INSERT INTO test SELECT 1, 'test' INSERT INTO test SELECT 2, 'velo' INSERT INTO test SELECT 200, 'voiture'
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 SET transaction isolation level READ COMMITTED begin tran declare @id int SELECT @id = id + 1 FROM test with (UPDLOCK) WHERE val = 'test' print @id waitfor delay '00:00:05' UPDATE test SET id = @id WHERE val = 'test' commit select * from test
En tout cas, Merci à tous pour votre aide...