Oracle で Primary Key に同じ値を insert すると、トランザクション・ロック(TX)の競合が発生することを確認してみた。
テーブルを作成して、
create table emp( id char(3) primary key, name varchar2(10) );
あるセッションで行を insert して、
insert into emp(id, name) values('001', 'scott');
別のセッションで行を insert して、
insert into emp(id, name) values('001', 'scott');
トランザクション・ロック(TX)の競合状態を調べてみると、
set feedback off set pagesize 9999 set linesize 2000 set trimspool on set trimout on col block for 9999 col sid for 9999 col status for a8 col machine for a9 col username for a9 col type for a2 col lmode for 99 col request for 99 col program for a10 col serialno for 999999 col lock_time for a10 col sql for a20 select a.blocking_session block, a.sid sid, a.status status, substr(a.machine, 1,9) machine, a.username username, b.type type, b.lmode lmode, b.request request, a.program program, a.serial# serialno, to_char(b.ctime/60, '9990.9') lock_time, c.sql_text sql from v$session a, v$lock b, v$sqlarea c where a.sid=b.sid and a.sql_address = c.address; BLOCK SID STATUS MACHINE USERNAME TY LMODE REQUEST PROGRAM SERIALNO LOCK_TIME SQL ----- ----- -------- --------- --------- -- ----- ------- ---------- -------- ---------- -------------------- 189 200 ACTIVE solaris10 SCOTT TX 0 4 sqlplus@so 867 0.1 insert into emp(id, laris101 ( name) values('001', TNS V1-V3) 'scott') 189 200 ACTIVE solaris10 SCOTT TM 3 0 sqlplus@so 867 0.1 insert into emp(id, laris101 ( name) values('001', TNS V1-V3) 'scott') 189 200 ACTIVE solaris10 SCOTT TX 6 0 sqlplus@so 867 0.1 insert into emp(id, laris101 ( name) values('001', TNS V1-V3) 'scott')
排他行ロック(TYPE=TX, LMODE=6)と行排他テーブルロック(TYPE=TM, LMODE=3)がかかってるのはわかるけど、何で共有テーブルロック(TYPE=TX, REQUEST=4)で待機してるんだろう？
列の説明は以下の通り。
- TYPE列
- LOMODE、REQUEST列
- 1: NULL
- 2: 行共有 テーブルロック(SS)
- 3: 行排他 テーブルロック(SX)
- 4: 共有 テーブルロック(S)
- 5: 共有/排他 テーブルロック(SSX)
- 6: 排他 テーブルロック(X)
v$lock を見てみると、
select * from v$lock; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ----- -- ---------- ---------- ----- ------- ---------- ----- 608F09D0 608F09E4 219 XR 4 0 1 0 5054 0 608F0A2C 608F0A40 219 CF 0 0 2 0 5045 0 608F0AE4 608F0AF8 219 RS 25 1 2 0 5042 0 608F0B40 608F0B54 221 PW 1 0 3 0 5036 0 608F0B9C 608F0BB0 222 RT 1 0 6 0 5042 0 608F0BF8 608F0C0C 200 TX 131111 340 0 4 43 0 608F0D0C 608F0D20 221 MR 1 0 4 0 5039 0 608F0D68 608F0D7C 221 MR 2 0 4 0 5039 0 608F0DC4 608F0DD8 221 MR 3 0 4 0 5039 0 608F0E20 608F0E34 221 MR 4 0 4 0 5039 0 608F0E7C 608F0E90 221 MR 501 0 4 0 5039 0 608F0ED8 608F0EEC 220 TS 3 1 3 0 5036 0 6023C8D0 6023C8E8 189 TM 13507 0 3 0 44 0 6023C97C 6023C994 200 TM 13507 0 3 0 43 0 60286658 6028667C 189 TX 131111 340 6 0 44 1 602C6E40 602C6E64 200 TX 262188 332 6 0 43 0
なんかヒントになりそうな情報を見つけた。
ID1 and ID2 -> There represents the rollback segment and transaction table entries for that transaction. So when session 1 acquired the lock it got rollback segment and transaction table entry. When another session requested the same block, Oracle tried to generate a CR “Consistent read” image for the same by applying the rollback segment. But since there was exclusive lock it was not able to proceed. Unless first session relievs the lock, second session cannot proceed. So for second session its showing the value of ID1 and ID2 same as session 1 as session 2 was using the same values from rollback segment to make a CR copy.
Identifying Locks on Objects using V$LOCKS – Persistent Storage Solutions
ちなみにV$LOCKED_OBJECT.LOCKED_MODEにはDMLロック（TMタイプのエンキュー）の情報のみを持つ。TX の情報は持たない。
V$LOCKED_OBJECTは、システム上のすべてのトランザクションによって取得されるすべてのロックを示します。どのセッションがDMLロック（TMタイプのエンキュー）をどのオブジェクト上にどのモードで保持しているかを示します。
http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05771-03/dynviews_2.htm#98870
[参考]
ロックをつぶせ！ 最初に疑うべき原因 (2/3)：Oracleパフォーマンス障害の克服（2） - ＠IT
ロックに関する検証 その１ | Insight Technology, Inc.
データの同時実行性と整合性
http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05771-03/dynviews_2.htm#98732
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:25323841260334
Oracle-Database: TX Transaction locks - Example wait scenarios
