ロック解除可能なクエリ
- jackloveオリジナル
- 2018-06-15 09:41:192289ブラウズ
最近、ロックの問題に対処していたときに、環境を使用しているときにかなり憂鬱なことに気づきました。 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
次に、接続を確立し、次のスクリプトを実行してロックを実装します。
-- =======================================
-- 测试连接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'dbo.tb', N'Table');
-- 显示锁
EXEC sp_lock@@SPID;
実行結果から、オブジェクトがロックされていることがわかります: IX
はテーブルレベルとページレベルでロックされ、Xはレコードレベルでロックされています。
tran_count |
database_name |
object_id |
|
|||||||
51 |
1 |
db_xlock_test |
21575115 |
|||||||
spid |
dbid |
ObjId |
IndId |
タイプ |
リソース |
モード |
ステータス |
|||
51 |
7 |
0 |
0 |
DB |
S |
GRANT |
||||
51 |
7 |
21575115 |
1 |
PAG |
0.095138889 |
IX |
グラント |
|||
51 |
7 |
21575115 |
0 | タブ |
IX |
グラント |
||||
51 |
1 |
1131151075 |
0 |
タブ |
は |
GRANT |
||||
51 |
7 |
21575115 |
1 |
キー |
(020068e8b274) |
X |
許可 |
|||
| 51 |
7 |
21575115 |
1 |
KEY |
-10086470766 |
X |
許可 |
|||
然后新建一个连接，执行下面的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执行完成。可是测试的结果去违反了这一原则。
接続 2 がブロックされない理由を理解するために、接続 2 に対して Trace を実行したところ、さらに憂鬱な問題が見つかりました。
|
| タイプ
|
|||
21575115 |
5 - オブジェクト |
6 - IS |
||
ロック:取得 |
1:77 |
0 |
6 - ページ |
6 - IS |
ロック:取得 |
[プランガイド] |
0 | 2 - データベース |
3 - S |
ロック:取得 |
21575115 |
5 - オブジェクト |
6 - IS |
|
ロック:取得 |
1:77 |
0 |
6 - ページ |
6 - IS |
ロック:取得しました |
1:80 |
0 |
6 - ページ |
6 - IS |
|
ロック:取得 |
1:89 |
0 |
6 - ページ |
6 - IS |
Traceの最初の2行は、2を接続するTraceの結果です。結果から、接続2は意図共有ロック(IS)のみを使用します。ロック互換性の原則に従って、テーブル レベルとページ レベルでのみ、IS および IX (1 の接続では、テーブル レベルとページでのみ IX ロックが使用されます)レベル) は競合していないため、2 に接続するクエリはブロックされません。クエリ データの量を増やした後、Trace の結果は、クエリが依然としてテーブル レベルとページ レベルで IS ロックのみを使用していることを示します (Trace の最後の 4 行) 結果）。
この問題の解決策は、接続 1 のロックの粒度を上げ、PAGLOCK テーブル プロンプトを使用してロックの粒度をページ レベルまで上げることです。 ISとXが一致するようにします。が競合している場合、接続は正常にブロックできます2。
しかし、問題は、なぜクエリがテーブルレベルとページレベル（IS）でのみロックを共有しようとしており、行レベル（）では共有しようとしているのかということです。 X )、これはオンライン ヘルプの指示とは異なるようです (これはまだ理解の偏りです)。
添付ファイル: オンラインヘルプのロックモードの説明
共有ロック
共有ロック (S ロック) を使用すると、クローズド同時実行制御の下で同時トランザクションが (SELECT) リソース を読み取ることができます。
ロックを更新
更新ロック (U ロック) は、一般的なデッドロックを防ぐことができます。 反復可能な読み取りまたはシリアル化可能なトランザクションでは、このトランザクションはリソース (ページまたは行) 上のデータ [ 共有ロック (S lock) ] を読み取り、データ [この操作では、ロックを排他ロック (X Lock) に変換する必要があります ]。 2 つのトランザクションがリソースの共有モード ロックを取得し、同時にデータを更新しようとすると、1 つのトランザクションはロックを排他ロック (X ロック) に変換しようとします。 あるトランザクションの排他ロックは他のトランザクションの共有モード ロックと互換性がないため、共有モードから排他ロックへの変換は一定期間待機する必要があり、ロック待機が発生します。 2 番目のトランザクションは、更新用の排他的ロック (X lock) の取得を試みます。両方のトランザクションが排他ロック (X ロック) に変換され、各トランザクションはもう一方のトランザクションが共有モードのロックを解放するのを待つため、デッドロックが発生します。
この潜在的なデッドロックの問題を回避するには、更新ロック (U ロック) を使用します。 リソースの更新ロック (U ロック) を取得できるトランザクションは一度に 1 つだけです。 トランザクションがリソースを変更する場合、更新ロック (U lock) は排他ロック (X lock) に変換されます。
専用ロック
排他ロック (X ロック) は、同時トランザクションによるリソースへのアクセスを妨げる可能性があります。 排他ロックを使用する場合（） データ変更ステートメント ( INSERT
、UPDATE 、 DELETE など) は、変更操作と読み取り操作を組み合わせます。 ステートメントは、必要な変更操作を実行する前に、まず読み取り操作を実行してデータを取得します。 したがって、データ変更ステートメントは通常、共有ロックと排他ロックを要求します。 たとえば、UPDATE ステートメントは、別のテーブルへの結合に基づいて、あるテーブルの行を変更する場合があります。 この場合、UPDATE ステートメントは、更新行の排他ロックのリクエストに加えて、結合テーブルで読み取られた行の共有ロックもリクエストします。 インテント ロック データベース エンジンは、インテント ロックを使用して、ロック階層内の基になるリソースに配置された共有ロック (S locks) または排他ロック (X locks) を保護します。 。 インテンション ロックは、下位レベルのロックよりも前に取得できるため、インテンション ロックと名付けられ、ロックを下位レベルに配置するようにインテンションが通知されます。 この記事では、ロックできないクエリについて説明します。その他の関連コンテンツについては、php 中国語 Web サイトを参照してください。 関連する推奨事項: 更新ロック (U) と排他ロック (X) の関連知識を説明する
以上がロック解除可能なクエリの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。