Freischaltbare Abfrage

Als ich kürzlich mit einem Sperrproblem zu tun hatte, stellte ich fest, dass die Verwendung der Sperre X tatsächlich fehlschlug Simulieren Sie dieses Problem. Sie können das folgende T-SQL-Skript verwenden, um eine Testumgebung einzurichten.

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

Erstellen Sie dann ein Verbinden Sie sich und führen Sie das folgende Skript aus, um die Sperre zu implementieren.

-- =======================================
-- 测试连接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;

Anhand der Ausführungsergebnisse können Sie den Status des gesperrten Objekts sehen: Tabellenebene und Seitenebene Das Obige ist IX gesperrt, und der Datensatz ist X gesperrt.

spid	tran_count		Datenbankname			object_id
51	1		db_xlock_test			21575115
spid	dbid	ObjId		IndId	Typ		Ressource		Modus	Status
51	7	0		0	DB				S	GEWÄHRLEISTUNG
51	7	21575115		1	PAG		0,095138889		IX	GEWÄHRUNG
51	7	21575115		0	TAB				IX	GEWÄHRUNG
51	1	1131151075		0	TAB				IST	GEWÄHRUNG
51	7	21575115		1	SCHLÜSSEL		(020068e8b274)		X	GRANT
51	7	21575115		1	SCHLÜSSEL		-10086470766		X	GEWÄHRUNG

然后新建一个连接，执行下面的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执行完成。可是测试的结果去违反了这一原则。

Um zu verstehen, warum die Verbindung 2 nicht blockiert ist, erstellen Sie einen 2 🎜>Trace, ein deprimierenderes Problem gefunden, die Ergebnisse von Trace sind wie folgt:

EventClass	TextData	Objekt-ID	Typ	Modus
Sperre: Erworben		21575115	5 - OBJEKT	6 - IS
Sperre:Erworben	1:77	0	6 - SEITE	6 - IS
Sperre: Erworben	[PLANGUIDE]	0	2 - DATENBANK	3 - S
Sperre: Erworben		21575115	5 - OBJEKT	6 - IS
Sperre:Erworben	1:77	0	6 - SEITE	6 - IS
Sperre: Erworben	1:80	0	6 - SEITE	6 - IS
Sperre:Erworben	1:89	0	6 - SEITE	6 - IS

Die ersten beiden Zeilen von

Trace sind die Ergebnisse von Trace, das 2 Aus den Ergebnissen geht hervor, dass die Verbindung 2 nur die Absichts-Shared-Sperre (IS) verwendet, und zwar nur bei Tabellenebene und Seitenebene, gemäß Lock-Kompatibilitätsprinzipien, IS und IX (Verbindung 1 in der Tabelle Die Ebene und die Seitenebene verwenden nur die Sperre IX und es gibt keinen Konflikt, daher verbindet die Abfrage 2 wird nicht blockiert. Nachdem die Menge der Abfragedaten erhöht wurde, zeigen die Ergebnisse von Trace, dass die Abfrage immer noch nur die Sperre IS auf Tabellenebene und Seite verwendet Ebene ( TraceDie letzten 4 Zeilen des Ergebnisses).

Für dieses Problem besteht die Lösung natürlich darin, die Granularität der Verbindung zu erhöhen 1zu sperren und PAGLOCKTabelle fordert dazu auf, die Sperrgranularität auf Seitenebene zu erhöhen, sodass die Verbindung hergestellt wird, wenn IS mit X in Konflikt steht kann erfolgreich blockiert werden2.

Aber die Frage ist, warum die Abfrage nur beabsichtigt, die Sperre auf Tabellenebene und Seitenebene zu teilen (IS), anstatt Sperren auf Zeilenebene zu teilen (X), dies scheint sich von den Anweisungen in der Online-Hilfe zu unterscheiden (dies ist immer noch eine Abweichung im Verständnis).

Anhang: Anleitung zum Sperrmodus in der Online-Hilfe

Gemeinsame Sperre

Gemeinsame Sperren (S Sperren) ermöglichen das Lesen gleichzeitiger Transaktionen unter geschlossener Parallelitätskontrolle (AUSWÄHLEN) Ressourcen.

Aktualisierungssperre

Update-Sperre (U -Sperre) kann häufige Deadlocks verhindern. In einer wiederholbaren Lese- oder serialisierbaren Transaktion liest diese Transaktion Daten [Erhält eine gemeinsame Sperre (S ( X Sperren) ]. Wenn zwei Transaktionen eine Shared-Mode-Sperre für eine Ressource erwerben und dann versuchen, Daten gleichzeitig zu aktualisieren, versucht eine Transaktion, die Sperre in eine exklusive Sperre umzuwandeln (X sperren ). Die Konvertierung vom Shared-Modus in die exklusive Sperre muss eine gewisse Zeit warten, da die exklusive Sperre einer Transaktion nicht mit der Shared-Mode-Sperre anderer Transaktionen kompatibel ist. Die zweite Transaktion versucht, eine exklusive Sperre (X Sperre) für das Update zu erwerben.Der Deadlock tritt auf, weil beide Transaktionen in exklusive Sperren umgewandelt werden (X Sperren) und jede Transaktion darauf wartet, dass die andere Transaktion die Sperre im gemeinsam genutzten Modus freigibt.

Um dieses potenzielle Deadlock-Problem zu vermeiden, verwenden Sie Update-Sperren (U -Sperren). Nur ​​jeweils eine Transaktion kann eine Aktualisierungssperre für eine Ressource erhalten (U -Sperre). Wenn die Transaktion die Ressource ändert, wird die Aktualisierungssperre (U Sperre) in eine exklusive Sperre (X sperren) .

Exklusives Schloss

Exklusive Sperren (X Sperren) können verhindern, dass gleichzeitige Transaktionen auf Ressourcen zugreifen. Bei Verwendung einer exklusiven Sperre (X -Sperre) kann keine andere Transaktion die Daten ändern; nur bei Verwendung von NOLOCK Der Lesevorgang wird nur ausgeführt, wenn Sie dazu aufgefordert werden oder die Leseisolationsstufe nicht festgelegt ist.

Datenänderungsanweisungen (z. B. INSERT, UPDATE und DELETE ) führen Änderungs- und Lesevorgänge zusammen. Die -Anweisung führt zunächst einen Lesevorgang durch, um die Daten abzurufen, bevor die erforderlichen Änderungsvorgänge ausgeführt werden. Daher erfordern Datenänderungsanweisungen normalerweise gemeinsame Sperren und exklusive Sperren. Zum Beispiel kann die Anweisung UPDATE Zeilen in einer Tabelle basierend auf einem Join mit einer anderen Tabelle ändern. In diesem Fall fordert die UPDATE -Anweisung zusätzlich zur Anforderung einer exklusiven Sperre für die Aktualisierungszeile auch eine Freigabe für die in der Join-Tabelle gelesene Zeile an Sperren.

Absichtssperre

Die Datenbank-Engine verwendet Absichtssperren, um gemeinsame Sperren zu schützen (S Sperre) oder exklusive Sperre (X Sperre) wird auf der zugrunde liegenden Ressource der Sperrhierarchie platziert. Absichtssperren werden Absichtssperren genannt, weil sie vor Sperren auf niedrigerer Ebene erworben werden können und so die Absicht mitteilen, die Sperre auf der niedrigeren Ebene zu platzieren.

In diesem Artikel werden die Abfragen erläutert, die nicht gesperrt werden können. Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website.

Verwandte Empfehlungen:

Erklären Sie die Aktualisierungssperre (U) und die Exklusivsperre (X). Wissen

SQL Server 2008-Verbesserungen bei der Handhabung impliziter Datentypkonvertierungen in Ausführungsplänen

So implementieren Sie eine Eltern-Kind-Beziehungsabfrage auf unendlicher Ebene in einem einzigen Satz in MySQL

Das obige ist der detaillierte Inhalt vonFreischaltbare Abfrage. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!