Voici le problème que je vais traiter : je veux pouvoir m’assurer dans une transaction qu’un jeu d’enregistrements ne sera pas touché pendant que j’opère une série de modifications. Par exemple, je ne veux pas qu’on puisse modifier une série d’adresses pendant que je mets à jour le contact qui les possède. Pour être plus concret, je veux bloquer les adresses 31 et 52 pendant que je modifie le contact John Smith. Par exemple :
BEGIN TRAN
SELECT * INTO #TempTable FROM Person.Address Where AddressID in (31,52)
UPDATE Person.Person Set Title = 'Lord' Where LastName = 'Smith' and FirstName = 'John'
UPDATE Person.PersonPhone Set [...]
Par défaut, le niveau d’isolation de transaction de SQL Server est READ COMMITED. En d’autres termes, cela signifie que l’on ne peut lire ou modifier que des données qui ont validées (comitées pour reprendre le barbarisme franglais). Selon le moteur de transactions, les adresses peuvent être modifiées pendant que ma transaction a lieu.
Techniquement, dans le moteur SQL, cela se traduit par des locks partagés (Shared) sur la table Address qui durent le temps du premier SELECT et des locks exclusifs (eXclusive) sur les tables modifiées. Une fois le SELECT passé, les locks Shared sont libérés, laissant la place pour des modifications. En revanche les locks exclusifs sont maintenus jusqu’au COMMIT (ou ROLLBACK), empêchant les modifications. On peut le vérifier en regardant les verrous sur les tables en question :
SET TRANSACTION ISOLATION LEVEL READ COMMITTED --mode par défaut
BEGIN TRAN
SELECT *
FROM Person.Address
WHERE AddressID IN (31,52)
select resource_type, resource_description, request_mode, request_type
from sys.dm_tran_locks
ROLLBACK
Lors de la consultation des locks, il n’y en a aucun donc une instruction parallèle peut modifier les adresses. Nous allons donc utiliser un autre niveau de transaction pour changer le comportement du moteur.
En choisissant le niveau juste au dessus, REPEATABLE READ, le moteur va poser des locks Shared et les maintenir le temps de la transaction. C’est ce que l’on appelle des lectures répétables. Pour modifier un enregistrement, le moteur doit poser un lock exclusif et donc doit attendre de n’avoir plus de locks Shared. Voyons le comportement des verrous, pour cela, il suffit de changer le niveau d’isolation dans la première ligne :
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT *
FROM Person.Address
WHERE AddressID IN (31,52)
select resource_type, resource_description, request_mode, request_type
from sys.dm_tran_locks
ROLLBACK
On voit bien que des locks Shared (S et IS) sont maintenus après le SELECT. Ceci garantie que ces enregistrements ne seront pas modifiés le temps de ma transaction. De plus, du fait que ce soit des locks Shared, une autre transaction peut lire ces enregistrements.
Bien évidemment, la gestion des locks est très fine dans SQL Server et se complexifie avec l’ensemble des requêtes à un instant T. SQL Server va tenter de verrouiller le niveau le plus fin et le moins contraignant pour respecter vos niveaux d’isolation et garantir la transactionalité. Et tout ceci se combine avec les différents objets liés à la table (index, allocations, etc.).
Vous aurez noté au passage l’utilisation de la vue système sys.dm_tran_locks qui remplace depuis 2005 l’ancien sp_lock et qui vous aidera à comprendre le fonctionnement du moteur transactionnel et à minimiser la contention sur vos bases.
Bonne optimisation…
Business Geek 