SQLServer SQLで行ロックを行うサンプル

nas2022/01/22

SQLServerのSQLで行ロックを行うサンプルです。

目次

サンプル SQLServerの行ロック
行ロックの挙動の確認 WITH(UPDLOCK,ROWLOCK)
  行ロックに行ロックをした場合
  ロックされていた場合はエラーにする(NOWAIT)
  ロック状態を確認するSQL
ロックを解除する方法

SQLServerの行ロック

select 項目 from テーブル WITH(UPDLOCK,ROWLOCK)
where 条件;

 

行ロックの挙動の確認 WITH(UPDLOCK,ROWLOCK)

2つの接続から、行ロックをした時の挙動の確認します。

１．行のロックを行う

１つめの接続で、select文にWITHをつけて実行します。

SELECT *  FROM 
employee WITH(UPDLOCK,ROWLOCK)
WHERE id = 2

employeeテーブルのID=2の行をロックします。

 

２．SQLを実行してみる

２つめのタブで、select文を実行してみます。

select * from employee
where ID = '2';

１つめのタブのSQLと同じ条件で実行しています。
結果の取得はできます。
※1つめのSQLで「UPDLOCK」の代わりに「XLOCK」を指定していた場合はselectの結果が返ってきません。

 

３．Updateを実行してみる

２つめのタブで、update文を実行してみます。

update employee
set name = '竹田'
where ID = '2';

updateを実行した場合、実行されないままの状態になります。待ちの状態です。
以下の図のように反応が返ってきません。※「XLOCK」の場合も同じです。

 

４．commitを実行してみる

１つめのタブで、commitを実行すると、
２つめのタブのupdateの更新も実行されて終了します。
※手動設定にしているためこのupdate文に対してもコミットが必要です。
※「XLOCK」の場合も同じです。

 

行ロックに行ロックをした場合

WITH(UPDLOCK,ROWLOCK)でロックを掛けた状態に対して、別のトランザクションでWITH(UPDLOCK,ROWLOCK)した場合は、1つめのWITH(UPDLOCK,ROWLOCK)がcommitかrollbackされるまで2つめのWITH(UPDLOCK,ROWLOCK)は待ちの状態になります。

以下の図のように反応が返ってきません

 

ロックされていた場合はエラーにする(NOWAIT)

WITH(UPDLOCK,ROWLOCK)でロックを掛けた状態に対して、別のトランザクションでWITH(UPDLOCK,ROWLOCK)にNOWAITをつけて実行した場合は以下のエラーを返します。

「ロック要求がタイムアウトしました。」

 

以下は、no waitをつけたときの動きです。

１．１つめのタブで実行

SELECT *  FROM 
employee WITH(UPDLOCK,ROWLOCK)
WHERE id = 2

select文でWITH(UPDLOCK,ROWLOCK)をつけています。

 

２．２つめのタブで実行

SELECT *  FROM 
employee WITH(UPDLOCK,ROWLOCK,NOWAIT)
WHERE id = 2

2行目は、NOWAITをつけています。

NOWAITがあるため、待ちにはならず以下のエラーが発生します。

 

ロック状態を確認するSQL

ロックは、sys.dm_tran_locksテーブルで確認します。

SELECT *
FROM sys.dm_tran_locks

ロック状態がある場合はデータが表示されます。

 

ロックを解除する方法

１．ロック状態を確認します。項目としてrequest_session_idを指定します。

SELECT request_session_id,*
FROM sys.dm_tran_locks

実行結果は以下のとおりです。

 

２．killとresource_typeがKEYのrequest_session_idを指定します。
(※SQLを入力する箇所で実行します。Linuxのコマンドではありません。)

kill 56

実行結果は以下のとおりロックの行が解除されました。

関連の記事

SQLServer SSMSでSQL/ストアドを実行する

△上に戻る