Requête déverrouillable

Récemment, alors que j'étais confronté à un problème de verrouillage, j'ai découvert une chose plutôt déprimante. L'utilisation du verrou X n'a pas réussi à verrouiller la requête. simuler ce problème. Vous pouvez utiliser le script T-SQL suivant pour établir un environnement de test.

USE master;
GO
IF @@TRANCOUNT > 0
ROLLBACK TRAN;
GO
-- =======================================
-- 建立测试数据库
-- a. 删除测试库, 如果已经存在的话
IF DB_ID(N'db_xlock_test') IS NOT NULL
BEGIN;
ALTER DATABASE db_xlock_test
SET SINGLE_USER
WITH
ROLLBACK AFTER 0;
DROP DATABASE db_xlock_test;
END;
-- b. 建立测试数据库
CREATE DATABASE db_xlock_test;
-- c. 关闭READ_COMMITTED_SNAPSHOT 以保持SELECT 的默认加锁模式
ALTER DATABASE db_xlock_test
SET READ_COMMITTED_SNAPSHOT OFF;
GO
-- =======================================
-- 建立测试表
USE db_xlock_test;
GO
CREATE TABLE dbo.tb(
id int IDENTITY
PRIMARY KEY,
name sysname
);
INSERT dbo.tb
SELECT TOP(50000)
O1.name + N'.' + O2.name + N'.' + O3.name
FROM sys.objects O1 WITH(NOLOCK),
sys.objects O2 WITH(NOLOCK),
sys.objects O3 WITH(NOLOCK);
GO

Ensuite, Établissez une connexion et exécutez le script suivant pour implémenter le verrouillage.

-- =======================================
-- 测试连接1 - 加锁
BEGIN TRAN
--测试的初衷是通过SELECT加锁，结果发现UPDATE也锁不住
UPDATE dbo.tb SET name = name
--SELECT COUNT(*) FROM dbo.tb WITH(XLOCK)
WHERE id <= 2;
SELECT
spid = @@SPID,
tran_count = @@TRANCOUNT,
database_name = DB_NAME(),
object_id = OBJECT_ID(N&#39;dbo.tb&#39;, N&#39;Table&#39;);
-- 显示锁
EXEC sp_lock@@SPID;

Grâce aux résultats de l'exécution, vous pouvez voir l'état de l'objet verrouillé : tableau niveau et Le niveau de la page est IX verrouillé, et l'enregistrement est X verrouillé.

spid	tran_count		database_name			object_id
51	1		db_xlock_test			21575115
spid	dbid	ObjId		IndId	Type		Ressource		Mode	Statut
51	7	0		0	DB				S	subvention
51	7	21575115		1	PAG		0.095138889		IX	subvention
51	7	21575115		0	TAB				IX	SUBVENTION
51	1	1131151075		0	TAB				IS	SUBVENTION
51	7	21575115		1	CLE		(020068e8b274)		X	SUBVENTION
51	7	21575115		1	CLÉ		-10086470766		X	subvention

然后新建一个连接，执行下面的T-SQL查询，看看会否被连接1锁住

-- =======================================
-- 测试连接2 - 被阻塞(在测试连接1 执行后执行)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM dbo.tb
WHERE id <= 2;

上述查询会很快返回结果，并不会被查询1阻塞住。

按照我们的了解（联机帮助上也有说明），在READ COMMITTED事务隔离级别下，查询使用共享锁（S），而根据锁的兼容级别，S锁是与X锁冲突的，所以正常情况下，连接2的查询需要等待连接1执行完成。可是测试的结果去违反了这一原则。

Afin de comprendre pourquoi la connexion 2 n'est pas bloquée, faites un 2 🎜>Trace, a trouvé un problème plus déprimant, les résultats de Trace sont les suivants :

EventClass	TextData	ObjectID	Type	Mode
Verrouillage : Acquis		21575115	5 - OBJET	6 - EST
Verrouillage : Acquis	1:77	0	6 - PAGE	6 - EST
Verrouillage : Acquis	[PLANGUIDE]	0	2 - BASE DE DONNÉES	3 - S
Verrouillage : Acquis		21575115	5 - OBJET	6 - EST
Verrouillage : Acquis	1:77	0	6 - PAGE	6 - EST
Verrouillage : Acquis	1:80	0	6 - PAGE	6 - EST
Verrouillage : Acquis	1:89	0	6 - PAGE	6 - EST

Les deux premières lignes de

Trace sont les résultats de Trace reliant 2 , d'après les résultats, la connexion 2 n'utilise que le verrou partagé d'intention (IS), et ce n'est qu'au niveau table et niveau page, selon les principes de compatibilité Lock, IS et IX (connexion 1 dans le tableau Le niveau et le niveau de la page utilisent uniquement le IX lock) et il n'y a pas de conflit, donc la requête reliant 2 ne sera pas bloqué. Après avoir augmenté la quantité de données de requête, les résultats Trace montrent que la requête utilise toujours uniquement le verrou IS au niveau de la table et de la page. niveau ( TraceLes dernières 4 lignes du résultat).

Pour ce problème, la solution est bien sûr d'augmenter la granularité de la connexion 1 verrouiller et d'utiliser PAGLOCKTable invite à augmenter la granularité du verrouillage au niveau de la page, de sorte que si IS entre en conflit avec X, la connexion peut être bloqué avec succès2.

Mais la question est de savoir pourquoi la requête a uniquement l'intention de partager le verrou au niveau de la table et au niveau de la page (IS) au lieu de partager les verrous au niveau de la ligne (X), cela semble différent des instructions de l'aide en ligne (il s'agit quand même d'un écart de compréhension).

Pièce jointe : Instructions sur le mode verrouillage dans l'aide en ligne

Verrouillage partagé

Les verrous partagés (S verrous) permettent à des transactions simultanées de lire sous contrôle de concurrence fermé (SÉLECTIONNER) Ressources.

Mettre à jour le verrouillage

Le verrouillage de mise à jour (U verrouillage) peut empêcher les blocages courants. Dans une transaction de lecture répétable ou sérialisable, cette transaction lit des données [Acquiert un verrou partagé (S ( X Verrouillage) ]. Si deux transactions acquièrent un verrou en mode partagé sur une ressource puis tentent de mettre à jour les données simultanément, une transaction tente de convertir le verrou en verrou exclusif (X verrouillage ). La conversion du mode partagé en verrouillage exclusif doit attendre un certain temps car le verrouillage exclusif d'une transaction est incompatible avec le verrouillage en mode partagé d'autres transactions ; La deuxième transaction tente d'acquérir un verrou exclusif (X verrou) pour la mise à jour.Le blocage se produit parce que les deux transactions sont converties en verrous exclusifs (X verrous) et chaque transaction attend que l'autre transaction libère le verrou en mode partagé.

Pour éviter ce problème de blocage potentiel, utilisez les verrous de mise à jour (U verrous). Une seule transaction à la fois peut obtenir un verrou de mise à jour sur une ressource (U verrou). Si la transaction modifie la ressource, le verrou de mise à jour (U verrou) est converti en verrou exclusif (X verrouillage) .

Serrure exclusive

Les verrous exclusifs (X verrous) peuvent empêcher les transactions simultanées d'accéder aux ressources. Lors de l'utilisation d'un verrou exclusif (X verrou), aucune autre transaction ne peut modifier les données uniquement lors de l'utilisation de NOLOCK L'opération de lecture ne sera effectuée que lorsque vous y êtes invité ou que le niveau d'isolement de lecture n'est pas validé.

Déclarations de modification des données (telles que INSERT, UPDATE et DELETE ) fusionnent les opérations de modification et de lecture. L'instruction effectue d'abord une opération de lecture pour obtenir les données avant d'effectuer les opérations de modification requises. Par conséquent, les déclarations de modification de données demandent généralement des verrous partagés et des verrous exclusifs. Par exemple, l'instruction UPDATE peut modifier les lignes d'une table en fonction d'une jointure à une autre table. Dans ce cas, en plus de demander un verrouillage exclusif sur la ligne de mise à jour, l'instruction UPDATE demandera également un partage sur la ligne lue dans la table de jointure Verrouillage.

Verrouillage d'intention

Le moteur de base de données utilise des verrous d'intention pour protéger les verrous partagés (S lock) ou verrou exclusif (X lock) est placé sur la ressource sous-jacente de la hiérarchie des verrous. Les verrous d'intention sont appelés verrous d'intention car ils peuvent être acquis avant les verrous de niveau inférieur, notifiant ainsi l'intention de placer le verrou sur le niveau inférieur.

Cet article explique les requêtes qui ne peuvent pas être verrouillées. Pour plus de contenu connexe, veuillez faire attention au site Web chinois php.

Recommandations associées :

Expliquez le verrouillage de mise à jour (U) et le verrouillage exclusif (X) connaissances

Améliorations de SQL Server 2008 dans la gestion des conversions de types de données implicites dans les plans d'exécution

Comment implémenter une requête de relation parent-enfant de niveau infini en une seule phrase dans MySQL

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!