Re: Ordered For Update

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Mon, 21 Nov 2011 08:34:55 -0800 (PST)
Message-ID: <07b24263-b8d8-473d-84fc-b02d38ed674b_at_s6g2000vbc.googlegroups.com>



On Nov 21, 9:04 am, Charles Hooper <hooperc2..._at_gmail.com> wrote:
> 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 the
> queue', 'wait for unread message on broadcast channel');
>
> EVENT                   OBJ#      FILE#  BLOCK# ROW#
> -------------------- ------- ---------- ------- ----
> enq: TX - row lock c   71913          4    4262   18
> ontention
>
> 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 Hooperhttp://hoopercharles.wordpress.com/
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Charles, Nice example.

HTH -- Mark D Powell -- Received on Mon Nov 21 2011 - 10:34:55 CST

Original text of this message