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