Re: Ordered For Update

From: Charles Hooper <hooperc2001_at_gmail.com>
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 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 Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Mon Nov 21 2011 - 08:04:14 CST

Original text of this message