今回はOracleのロックについて検証しました。
ロックとは、複数のセッションでデータを同時に変更できないようにする仕組み(排他制御)です。
Oracleに限らず、たいていのRDBMSはロックを使用してデータの整合性を確保します。
セッションが1つしか存在しないシステムならあまりロックを意識しなくても良いかもしれませんが、セッションが複数存在するシステムならロックを意識してアプリ設計しないとロック取得待ちやデッドロックの原因になります。
ロックモード
DBリソースへのアクセスを制御するロックの種類
共有ロック(読取りロック)
同じリソースを複数のセッションで同時にアクセスできるようにするためのロック
共有ロック取得後も別セッションから対象のリソースへアクセスできる
ただし、別セッションは表定義やデータを変更できない(共有ロックは取得可能、排他ロックは取得不可)
※PostreSQLなどはSELECT時に共有ロックを取得しますが、Oracleは読取り一貫性(CR)を採用しているため、SELECT時に共有ロックは取得しません
排他ロック(占有ロック)
同じリソースを複数のセッションで同時にアクセスできない(共有できない)ようにするためのロック
排他ロック取得後は別セッションはリソースへアクセスできない(共有ロック・排他ロックともに取得不可)
ロック範囲
リソースをロックする範囲
表レベル
特定の表(テーブル)全体をロックする
行レベル
表(テーブル)の特定の行(レコード)をロックする
エンキュー(Oracleエンキュー)
データの変更をシリアライズするため、共有メモリ構造体(ロック)を提供するサービス
ロックはキューで管理される(FIFO型のロック)
自動ロック
OracleはDDL文やDML文でトランザクションを発行すると自動的にロックを取得する
DMLロック(データ・ロック)
DML文を実行した場合、自動的に取得されるロック
行レベルと表レベルの2種類のロックが自動的に取得される
データ保護が目的
・行レベルのロック(行ロック、TXロック、トランザクション)
表の特定の行に対するロック
INSERT/UPDATE/DELETE/MERGE/SELECT FOR UPDATE文を実行した場合、自動的に取得される
ロック取得タイミング：トランザクション開始時
ロック解放タイミング：トランザクションのコミット/ロールバック
※特定の行に対する表の表ロックも取得する(データ更新中のDDL操作(表定義変更など)を防ぐため)
・表レベルのロック(表ロック、TMロック、DMLエンキュー)
特定の表に対するロック
INSERT/UPDATE/DELETE/MERGE/SELECT FOR UPDATE文を実行した場合、自動的に取得される
ロック取得タイミング：トランザクション開始時
ロック解放タイミング：トランザクションのコミット/ロールバック
なお、表ロックには下記のモードがある
[ロックモード2]行共有ロック(RS/SS：ROW SHARE、SSは何の略か分かりません)
表のロックを保持しているトランザクションが該当行をロックしており、該当行を更新する予定であることを示すモード
[ロックモード3]行排他ロック(RX/SX：ROW EXCLUSIVE、SXは何の略か分かりません)
表のロックを保持しているトランザクションが該当行を更新/SELECT FOR UPDATEしたことを示すモード
[ロックモード4]共有表ロック(S：SHARE)
表のロックを保持しているトランザクションがいても別のトランザクションも同じ表へ問合せできることを示すモード
1トランザクションが共有表ロックを保持する場合、表更新できるが、複数トランザクションが共有表ロックを保持する場合、表更新できない
[ロックモード5]共有行排他表ロック(SRX：SHARE ROW EXCLUSIVE)
表のロックを保持しているトランザクションがいても別のトランザクションも同じ表へ問合せできることを示すモード
共有表ロックを保持するトランザクションは表更新できるが、別トランザクションは表更新できない
[ロックモード6]排他表ロック(X：EXCLUSIVE)
表のロックを保持しているトランザクション以外、表への問合せ・更新できないことを示すモード
※LOCK TABLE文で手動で明示的に表ロックを取得できる
DDLロック(データディクショナリ・ロック)
DDL文を実行した場合、自動的に取得されるロック
変更対象のオブジェクトに対するDMLロックも自動的に取得される
スキーマ・オブジェクトの構造(表定義やビュー定義など)保護が目的
※明示的にDDLロックを取得できない
※DDL文のロック解除待ち時間(秒)はDDL_LOCK_TIMEOUT初期化パラメータで設定できる(デフォルト(0)は待機しない＝NOWAIT)
・排他DDLロック
別トランザクションがDDLロック/DMLロックを取得できないようにするロック
別トランザクションのスキーマ・オブジェクト定義に対するDDL操作(ALTER TABLE、DROP TABLEなど)による変更(破壊)を防ぐ
・共有DDLロック
別トランザクションが同じスキーマ・オブジェクト定義を変更(破壊)しない場合、同じスキーマ・オブジェクトにアクセスできるようにするロック
スキーマ・オブジェクト定義を変更(破壊)しないDDL文としてCREATE PROCEDUREなどがある
・ブレーク可能解析ロック
ライブラリキャッシュ(共有SQL領域)のSQL文がスキーマ・オブジェクトを参照する場合、対応するSQL文に対して取得されるロック
参照しているスキーマ・オブジェクト定義が変更/削除された場合、対応する共有SQL領域のSQL文を無効化する
SQL文の解析フェーズ中に共有プール内で取得される
システムロック
データファイルなどの内部構造の保護が目的
・ラッチ
SGA内の共有データの保護(排他制御)に使用される
共有メモリ領域へのアクセス開始～終了まで保持されるロック
マイクロ秒～ミリ秒レベルで獲得と解放を繰り返すが獲得に失敗した場合、スピンして獲得できるのを待機する
※スピン後でも獲得できずスリープする場合、待機イベント「latch free」などで待機する
・共有プール上でのメモリの割り当てや解放
・バッファキャッシュ上でのバッファの探索
・ライブラリキャッシュ上でのオブジェクトの探索
・セッションの開始
・チェックポイントの開始
・ログバッファの割り当て
など
・mutex
ラッチに似ているが、ラッチは複数オブジェクトをグルーピングして保護するが、mutexは単一オブジェクトを保護する
・内部ロック
ディクショナリキャッシュ内のデータ保護
制御ファイル、REDOログファイル、アーカイブログファイル、データファイルなどのファイル保護
表領域とUNDOセグメントの保護
今回は特定のデータに対してロックモード1～6のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証しました。
結果は下記のようになりました。
■検証環境
OS：Oracle Linux 6.5
DB/GI：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition
※2ノードRAC(管理者管理型DB)
■前提
・非CDB
■設定内容
DDLロックタイムアウト時間
SQL> select inst_id, name, value from gv$parameter where name = 'ddl_lock_timeout';
INST_ID NAME VALUE
------- ------------------------------ ----------------------------------------
1 ddl_lock_timeout 0
2 ddl_lock_timeout 0
■検証パターン
①ロックモード1に対するロック取得検証
②ロックモード2に対するロック取得検証
③ロックモード3に対するロック取得検証
④ロックモード4に対するロック取得検証
⑤ロックモード5に対するロック取得検証
⑥ロックモード6に対するロック取得検証
■検証 ※作業ログは全パターンを載せると膨大な量になるため、セッション2(HR)から一部DML(insert・update)、一部DDL(DROP TABLE)を実行したログのみ載せます
①ロックモード1に対するロック取得検証
特定のデータに対してロックモード1のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します
【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード1のロック取得
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
【作業ログ】
別セッションからロックモード1～6のロックが取得できたため作業ログは割愛します
②ロックモード2に対するロック取得検証
特定のデータに対してロックモード2のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します
【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード2のロック取得(select for update)
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
3. セッション1(SCOTT)で特定のデータに対してロックモード2のロック取得(LOCK TABLE)
4. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
【作業ログ】
SQL> select * from emp where EMPNO = 7369 for update;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);
1行が作成されました。
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
134 63853 HR
141 35627 SCOTT
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
134 TX X(排他ロック) 2
134 TM RX/SX(行排他ロック) 2
141 TX X(排他ロック) 2
141 TM RX/SX(行排他ロック) 2
※下記SQLでオブジェクトレベルでロック取得状況を確認できます
SQL> select o.owner, o.object_name, o.object_type, lo.SESSION_ID, decode(lo.LOCKED_MODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode from v$locked_object lo, dba_objects o where lo.OBJECT_ID = o.OBJECT_ID;
OWNER OBJECT_NAME OBJECT_TYPE SESSION_ID LCK_MODE
--------------- --------------- --------------- ---------- ------------------------------
SCOTT EMP TABLE 141 RX/SX(行排他ロック)
SCOTT EMP TABLE 134 RX/SX(行排他ロック)
--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
★応答なし
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
134 63853 HR 141
141 35627 SCOTT
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
134 TM RX/SX(行排他ロック) 2
134 TX NONE(ロックなし) 0
141 TX X(排他ロック) 1
141 TM RX/SX(行排他ロック) 2
--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
*
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
134 63853 HR
141 35627 SCOTT
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
141 TX X(排他ロック) 2
141 TM RX/SX(行排他ロック) 2
3. セッション1(SCOTT)で特定のデータに対してロックモード2のロック取得(LOCK TABLE)
SQL> LOCK TABLE emp IN ROW SHARE MODE nowait;
表がロックされました。
4. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);
1行が作成されました。
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
12 3652 HR
128 9275 SCOTT
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
12 TX X(排他ロック) 2
12 TM RX/SX(行排他ロック) 2
128 TM RS/SS(行共有ロック) 2
--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
1行が更新されました。
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
12 3652 HR
128 9275 SCOTT
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
12 TX X(排他ロック) 2
12 TM RX/SX(行排他ロック) 2
128 TM RS/SS(行共有ロック) 2
--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
*
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
12 3652 HR
128 9275 SCOTT
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
128 TM RS/SS(行共有ロック) 2
③ロックモード3に対するロック取得検証
特定のデータに対してロックモード3のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します
【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(insert)
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
3. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(update)
4. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
5. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(delete)
6. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
7. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(LOCK TABLE)
8. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
【作業ログ】
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);
1行が作成されました。
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);
★応答なし
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
257 48116 SCOTT
372 65105 HR 257
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
257 TX X(排他ロック) 1
257 TM RX/SX(行排他ロック) 2
372 TX X(排他ロック) 2
372 TM RX/SX(行排他ロック) 2
372 TX NONE(ロックなし) 0
3. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
1行が更新されました。
4. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);
1行が作成されました。
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
257 48116 SCOTT
372 65105 HR
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
257 TX X(排他ロック) 2
257 TM RX/SX(行排他ロック) 2
372 TX X(排他ロック) 2
372 TM RX/SX(行排他ロック) 2
--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
★応答なし
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
257 48116 SCOTT
372 65105 HR
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
257 TX X(排他ロック) 1
257 TM RX/SX(行排他ロック) 2
372 TM RX/SX(行排他ロック) 2
372 TX NONE(ロックなし) 0
--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
*
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
257 48116 SCOTT
372 65105 HR
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
257 TX X(排他ロック) 2
257 TM RX/SX(行排他ロック) 2
5. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(delete)
SQL> delete from scott.emp where EMPNO = 7369;
1行が削除されました。
6. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);
1行が作成されました。
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
257 48116 SCOTT
372 65105 HR
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
257 TX X(排他ロック) 2
257 TM RX/SX(行排他ロック) 2
372 TX X(排他ロック) 2
372 TM RX/SX(行排他ロック) 2
--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
★応答なし
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
257 48116 SCOTT
372 65105 HR 257
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
257 TX X(排他ロック) 1
257 TM RX/SX(行排他ロック) 2
372 TM RX/SX(行排他ロック) 2
372 TX NONE(ロックなし) 0
--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
*
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
257 48116 SCOTT
372 65105 HR
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
257 TX X(排他ロック) 2
257 TM RX/SX(行排他ロック) 2
7. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(LOCK TABLE)
SQL> LOCK TABLE emp IN ROW EXCLUSIVE MODE nowait;
表がロックされました。
8. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);
1行が作成されました。
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
17 25120 HR
355 64736 SCOTT
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
17 TX X(排他ロック) 2
17 TM RX/SX(行排他ロック) 2
355 TM RX/SX(行排他ロック) 2
--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
1行が更新されました。
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
17 25120 HR
355 64736 SCOTT
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
17 TX X(排他ロック) 2
17 TM RX/SX(行排他ロック) 2
355 TM RX/SX(行排他ロック) 2
--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
*
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
17 25120 HR
355 64736 SCOTT
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
355 TM RX/SX(行排他ロック) 2
④ロックモード4に対するロック取得検証
特定のデータに対してロックモード4のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します
【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード4のロック取得(LOCK TABLE)
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
【作業ログ】
SQL> LOCK TABLE emp IN SHARE MODE nowait;
表がロックされました。
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);
★応答なし
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
137 7809 SCOTT
371 20677 HR
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
137 TM S(共有ロック) 1
371 TM NONE(ロックなし) 0
--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
★応答なし
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
137 7809 SCOTT
371 20677 HR 137
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
137 TM S(共有ロック) 1
371 TM NONE(ロックなし) 0
--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
*
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
137 7809 SCOTT
371 20677 HR
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
137 TM S(共有ロック) 2
⑤ロックモード5に対するロック取得検証
特定のデータに対してロックモード5のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します
【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード5のロック取得(LOCK TABLE)
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
【作業ログ】
SQL> LOCK TABLE emp IN SHARE ROW EXCLUSIVE MODE nowait;
表がロックされました。
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);
★応答なし
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
137 61707 SCOTT
371 28633 HR 137
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
137 TM SRX/SSX(共有行排他ロック) 1
371 TM NONE(ロックなし) 0
--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
★応答なし
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
137 61707 SCOTT
371 28633 HR 137
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
137 TM SRX/SSX(共有行排他ロック) 1
371 TM NONE(ロックなし) 0
--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
*
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました
※別セッションでロック状況確認
SQL> --#ついでに
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
137 61707 SCOTT
371 28633 HR
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
137 TM SRX/SSX(共有行排他ロック) 2
⑥ロックモード6に対するロック取得検証
特定のデータに対してロックモード6のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します
【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード6のロック取得(LOCK TABLE)
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
【作業ログ】
SQL> LOCK TABLE emp IN EXCLUSIVE MODE nowait;
表がロックされました。
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);
★応答なし
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
12 12189 HR 377
377 62284 SCOTT
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
12 TM NONE(ロックなし) 0
377 TM X(排他ロック) 1
--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
★応答なし
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
12 12189 HR 377
377 62284 SCOTT
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
12 TM NONE(ロックなし) 0
377 TM X(排他ロック) 1
--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
*
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました
※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
12 12189 HR
377 62284 SCOTT
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
377 TM X(排他ロック) 2
■参考資料
データの同時実行性と整合性
