Re: Ordered For Update
Date: Mon, 21 Nov 2011 06:04:14 -0800 (PST)
Message-ID: <72d168a3-0bec-4dd5-ae59-1c29d44735b4_at_i8g2000vbh.googlegroups.com>
On Nov 21, 5:36 am, David Budac <davidbu..._at_gmail.com> wrote:
> Hi all,
>
> I've got a question regarding SELECT FOR UPDATE behavior as I couldn't
> really find any detailed info anywhere. Would anyone know if this
> feature obeys the ORDER BY clause, while locking the rows? For
> example, would this query try to lock rows in the desired order?
>
> select * from t1 where some_key = :b1 order by id for update;
>
> According to the plan, the "for update" happens at the top, when the
> rows should already be ordered. So, is the locking happening in the
> same order the rows are being fetched? Am I misunderstanding the plan
> or the concept (or is there some kind of optimization going on behind
> the scenes)? Any suggestions on how to "trace" this are also very
> welcome.
>
> Thanks,
> David
I believe that the rows are locked as the rows are read from the table blocks - otherwise the table blocks would need to be visited twice - once before the ORDER BY and once after the ORDER BY.
Here is a quick test. First, we will create a table with 10,000 rows:
DROP TABLE T1 PURGE;
CREATE TABLE T1 AS
SELECT
ROWNUM C1,
RPAD('A',255,'A') C2
FROM
DUAL
CONNECT BY
LEVEL<=10000;
COMMIT;
Now we need 2 sessions (I will call them Session 1 and Session 2). We
will execute the same SELECT FOR UPDATE statement, with Session 1
first selecting in ascending order and then Session 2 selecting in
descending order.
In Session 1:
SELECT
C1,
C2
FROM
T1
WHERE
MOD(C1,100)=0
ORDER BY
C1
FOR UPDATE;
In Session 2:
SELECT
C1,
C2
FROM
T1
WHERE
MOD(C1,100)=0
ORDER BY
C1 DESC
FOR UPDATE;
(Session 2 is hung)
Let's try to identify the row that Session 2 is waiting to lock.
In Session 1:
COLUMN EVENT FORMAT A20
COLUMN OBJ# FORMAT 999999
COLUMN BLOCK# FORMAT 999999
COLUMN ROW# FORMAT 99
SELECT
SW.EVENT,
S.ROW_WAIT_OBJ# OBJ#, S.ROW_WAIT_FILE# FILE#, S.ROW_WAIT_BLOCK# BLOCK#, S.ROW_WAIT_ROW# ROW#
FROM
V$SESSION_WAIT SW,
V$SESSION S
WHERE
S.USERNAME IS NOT NULL
AND SW.SID=S.SID AND SW.EVENT NOT LIKE '%SQL*Net%' AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in thequeue', 'wait for unread message on broadcast channel');
EVENT OBJ# FILE# BLOCK# ROW# -------------------- ------- ---------- ------- ---- enq: TX - row lock c 71913 4 4262 18ontention
We have the 71913, absolute file number, block, and row in the block.
Let's select that row from the table:
SELECT
C1
FROM
T1
WHERE
ROWID=DBMS_ROWID.ROWID_CREATE(1, 71913, 4, 4262, 18);
C1
--- 100 The row with a C1 value of 100 is the first row that was returned by Session 1 (it will be the last row requested to be returned by Session 2), but is also the first row that matched the WHERE clause predicates for Session 2's SQL statement as the blocks were read (you could confirm the order in which the blocks are read by flushing the buffer cache before the select, setting the DB_FILE_MULTIBLOCK_READ_COUNT at the session level to 1, enabling a 10046 trace, and then executing the SELECT FOR UPDATE statement). Let's retrieve the execution plan for Session 2: SELECT S.SQL_ID, S.SQL_CHILD_NUMBER FROM V$SESSION_WAIT SW, V$SESSION S WHERE S.USERNAME IS NOT NULL AND SW.SID=S.SID AND SW.EVENT NOT LIKE '%SQL*Net%' AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue', 'wait for unread message on broadcast channel'); SQL_ID SQL_CHILD_NUMBER ------------- ---------------- 2dnpymtj0rc1r 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('2dnpymtj0rc1r',0,'TYPICAL')); SQL_ID 2dnpymtj0rc1r, child number 0 ------------------------------------- SELECT C1, C2 FROM T1 WHERE MOD(C1,100)=0 ORDER BY C1 DESC FOR UPDATE Plan hash value: 3991553210 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 33 (100)| | | 1 | FOR UPDATE | | | | | | | 2 | SORT ORDER BY | | 123 | 17466 | 33 (4)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T1 | 123 | 17466 | 32 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(MOD("C1",100)=0) Note ----- - dynamic sampling used for this statement (level=2) The execution plan appears to be slightly misleading - unless of course you remember that locking the rows after the ORDER BY would require revisiting the rows. The situation *could* be different in this test case if there was an index on column C1 - the index could be read in descending order, thus making it appear that the rows were locked in the order described by the ORDER BY clause. Charles Hooper http://hoopercharles.wordpress.com/ IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.Received on Mon Nov 21 2011 - 08:04:14 CST