ねら～ITエンジニア雑記

やきうのお兄ちゃんが綴るOracle Databaseメインのブログ

(1). SELECT ... FOR UPDATE ⇒ (2). SELECT ... FOR UPDATE ⇒ (3). ALTER TABLE ... ⇒ (4). 単なる SELECT の順番でSQLを実行して、ロック(エンキュー)の獲得状況を V$LOCKから確認してみる。(Oracle Database)

Oracle Database

タイトル長いけど、やってみるやで彡(ﾟ)(ﾟ) まずはデータ用意から。

CREATE TABLE TBL_A (C1 NUMBER);
INSERT INTO TBL_A VALUES(1);
INSERT INTO TBL_A VALUES(2);
COMMIT;

以下の順番でSQLを流してみると……

Session1のSQLを実行(SELECT ... FOR UPDATE文)

SELECT 1 FROM TBL_A WHERE C1 = 1 FOR UPDATE;

         1
----------
         1

22:37:11 SQL>

Session2のSQLを実行(Session1と同じSELECT ... FOR UPDATE文)

-- ※待たされる。
SELECT 1 FROM TBL_A WHERE C1 = 1 FOR UPDATE;

Session3のSQL(ALTER文)

-- ※待たされる。
ALTER TABLE TBL_A ADD (C2 VARCHAR2(10));

Session4のSQL(単なるSELECT文)

SELECT * FROM TBL_A WHERE C1 = 2;

        C1
----------
         2

22:37:29 SQL>

Session4のSQL、待たされるかと思ったら、待たされないな？彡(ﾟ)(ﾟ)

下記マニュアルを調べてみた。どうやら単なるSELECT文は表ロック(TM)取らないですやね。

表ロック(TM)
https://docs.oracle.com/cd/E16338_01/server.112/b56306/consist.htm#BABDDFHB
表ロックはTMロックとも呼ばれ、INSERT、UPDATE、DELETE、MERGE、
FOR UPDATE句付きのSELECTまたはLOCK TABLE文で表を変更する場合にトランザクションに取得されます。
：

上記のALTER文が待たされている状況で、下記マニュアルのSQLで
ロック状況を確認してみると……彡(ﾟ)(ﾟ)

Oracle Databaseパフォーマンス・チューニング・ガイド 12cリリース2 (12.2) E72901-02
http://docs.oracle.com/cd/E82638_01/TGDBA/instance-tuning-using-performance-views.htm#GUID-07982549-507F-4465-8843-7F753BCF8F99
ロックおよびロック・ホルダーの検索
待機中のロックのホルダーおよびウェイタのみを表示するには、次の文を使用します。

SET LINESIZE 300;
COL sess FOR A20;
SELECT DECODE(request,0,'Holder: ','Waiter: ') || 
          sid sess, id1, id2, lmode, request, type
   FROM V$LOCK
 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
   ORDER BY id1, request;


SESS                        ID1        ID2      LMODE    REQUEST TY
-------------------- ---------- ---------- ---------- ---------- --
Holder: 31               393228       2537          6          0 TX ★ホルダー、待たせている。
Waiter: 27               393228       2537          0          4 TX ★ウェイター、待たされている。
Waiter: 35               393228       2537          0          6 TX ★ウェイター、待たされている。

あら意外、ALTER文もTXエンキュー(行ロック)待ちで待たされてるのか。

生のV$LOCKを見てみると、上記の状況でも全てのセッションで
TMエンキュー(表ロック)は取れちゃってるんやね彡(-)(-)

SELECT sid sess, id1, id2, lmode, request, type FROM V$LOCK
WHERE sid IN (31, 27, 35) ORDER BY sid, type, request;

      SESS        ID1        ID2      LMODE    REQUEST TY
---------- ---------- ---------- ---------- ---------- --
        27        133          0          4          0 AE
        27      80465          0          6          0 OD
        27      80465          0          3          0 TM ★LMODE=3で取得できている
        27     196617       2315          6          0 TX
        27     393228       2537          0          4 TX
        31        133          0          4          0 AE
        31      80465          0          3          0 TM ★LMODE=3で取得できている
        31     393228       2537          6          0 TX
        35        133          0          4          0 AE
        35      80465          0          3          0 TM ★LMODE=3で取得できている
        35     393228       2537          0          6 TX

11 rows selected.

23:04:19 SQL>

下記マニュアルも見てクレメンス彡(ﾟ)(ﾟ)

表ロック(TM)
https://docs.oracle.com/cd/E16338_01/server.112/b56306/consist.htm#BABDDFHB

※追記：上記状態のSession4で追加ケースを試してみました。
　どちらもSELECT ... FOR UPDATE文は返ってきますやね彡(ﾟ)(ﾟ)

Session4'のSQL(SELECT ... FOR UPDATE文、Session1 や Session2とは異なるレコード)

SQL> SELECT 1 FROM TBL_A WHERE C1 = 2 FOR UPDATE;

         1
----------
         1

SQL>

Session4''のSQL(SET TRANSACTION ... してから SELECT ... FOR UPDATE文）

SQL> SET TRANSACTION NAME 'TM_LOCK_TEST';

Transaction set.

SQL> SELECT 1 FROM TBL_A WHERE C1 = 2 FOR UPDATE;

         1
----------
         1

SQL>