La gestion de la concurrence des verrous dans SQL Server
19 mars 2019
Par défaut, SQL Server est en mode AUTOCOMMIT. Il n’accepte même pas la concurrence d’un SELECT su une table, alors qu’une transaction explicite marquée par un BEGIN TRANSACTION est censée modifier une seule ligne de ladite table.
Permettre la concurrence du SELECT
Pour pouvoir continuer à faire des SELECT sur table, alors que des transactions INSERT, UPDATE ou DELETE sont en train de verrouiller la table, vous devez changer le niveau d’isolation des transactions effectuées au niveau de la base courante, Northwind, dans l’exemple ci-dessous :
ALTER DATABASE Northwind SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
Verrous sur ligne
Afin de permettre des transactions différentes sur des lignes différentes, il convient de s’assurer que les index utilisés – notamment les clés primaires – acceptent la concurrence en matière de verrous sur ligne. C’est le cas par défaut depuis la version 2008 de SQL Server !
ALTER INDEX pk_categories ON categories SET (ALLOW_PAGE_LOCKS=ON, ALLOW_ROW_LOCKS=ON); ALTER INDEX ix_categroyname ON categories SET (ALLOW_PAGE_LOCKS=ON, ALLOW_ROW_LOCKS=ON);
Pour modifier une ou plusieurs lignes de manière concurrente, vous pouvez passer par la clé primaire utilisée explicitement du fait de la clause WHERE portant sur le champ qui lui est associé :
BEGIN TRAN; UPDATE categories SET categoryname=UPPER(categoryname) WHERE categoryid=1;
Si vous souhaitez passer par un autre index, alors il faudra forcer l’usage de l’index associé au champ utilisé au niveau de la clause WHERE :
BEGIN TRAN; UPDATE c SET categoryname=UPPER(categoryname) FROM categories AS c WITH(INDEX(ix_categoryname) WHERE categoryname='Condiments';
N’oubliez pas, après tout ça de valider ou d’annuler votre transaction par un COMMIT ou un ROLLBACK.