副問い合わせ付きfor updateの挙動確認

gistfile1.txt

-- oracle 11gr2

CREATE TABLE t1 (id NUMBER, data VARCHAR2(1));

INSERT INTO t1 VALUES(1, 'a');
INSERT INTO t1 VALUES(2, 'b');
INSERT INTO t1 VALUES(3, 'c');
INSERT INTO t1 VALUES(4, 'd');
commit;

SELECT * FROM t1;


    session 1                       session 2
--------------------------------------------------
SELECT id, data
FROM   t1
WHERE  id = (SELECT MIN(id)
             FROM   t1)
FOR UPDATE;


        ID DA
---------- --
         1 a


                                    SELECT id, data
                                    FROM   t1
                                    WHERE  id = (SELECT MIN(id)
                                                 FROM   t1)
                                    FOR UPDATE;
                                    -- wait --

DELETE FROM t1 WHERE id=1;

1行が削除されました。

COMMIT;
コミットが完了しました。            レコードが選択されませんでした。




----------------------------------------------------------------------

-- DB2 10.1 isolation level = CS

CREATE TABLE t1 (id INT, data VARCHAR(1));

INSERT INTO t1 VALUES(1, 'a');
INSERT INTO t1 VALUES(2, 'b');
INSERT INTO t1 VALUES(3, 'c');
INSERT INTO t1 VALUES(4, 'd');
commit;

SELECT * FROM t1;



    session 1                       session 2
--------------------------------------------------
select id, data
from   t1
where  id = (select min(id)
             from   t1)
for update;

ID          DATA
----------- ----
          1 a


                                    SELECT id, data
                                    FROM   t1
                                    WHERE  id = (SELECT MIN(id)
                                                 FROM   t1)
                                    FOR UPDATE;

                                    ID          DATA        -- ★待たされない！
                                    ----------- ----
                                              1 a

DELETE FROM t1;
DB20000I  SQL コマンドが正常に
完了しました。


COMMIT;
DB20000I  SQL コマンドが正常に
完了しました。


                                    SELECT id, data
                                    FROM   t1
                                    WHERE  id = (SELECT MIN(id)
                                                 FROM   t1)
                                    FOR UPDATE;

                                    ID          DATA
                                    ----------- ----
                                    
                                      0 レコードが選択されました。




-- DB2 10.1 isolation level = RS

    session 1                       session 2
--------------------------------------------------

set current isolation level = rs;
                                        set current isolation level = rs;

SELECT id, data
FROM   t1
WHERE  id = (SELECT MIN(id)
             FROM   t1)
FOR UPDATE;

ID          DATA
----------- ----
          1 a

                                    SELECT id, data
                                    FROM   t1
                                    WHERE  id = (SELECT MIN(id)
                                                 FROM   t1)
                                    FOR UPDATE;
                                    -- 待ち

DELETE FROM t1;

-------------------デッドロック---------------------------



DB2において、Oracle的なfor updateの動きをさせたい場合は、
SELECT ... WITH RS FOR UPDATE USE AND KEEP UPDATE LOCKS 
が必要。


    session 1                       session 2
--------------------------------------------------

SELECT id, data
FROM   t1
WHERE  id = (SELECT MIN(id)
             FROM   t1)
WITH RS USE AND KEEP UPDATE LOCKS;


ID          DATA
----------- ----
          1 a

                                    SELECT id, data
                                    FROM   t1
                                    WHERE  id = (SELECT MIN(id)
                                                 FROM   t1)
                                    WITH RS USE AND KEEP UPDATE LOCKS;
                                    ★ロック待ち

DELETE FROM t1 WHERE id=1;

COMMIT;

                                    ID          DATA
                                    ----------- ----
                                              2 b