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

On Nov 21, 5:36*am, David Budac <davidbu... (AT) gmail (DOT) com> wrote:
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

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

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.

On Nov 21, 9:04*am, Charles Hooper <hooperc2... (AT) gmail (DOT) com> wrote:
Charles, Nice example.

HTH -- Mark D Powell --

On Nov 21, 11:34*am, Mark D Powell <Mark.Powe... (AT) hp (DOT) com> wrote:
Thanks Mark.

I should have clarified that V$SESSION_WAIT reports the OBJECT_ID,
while DBMS_ROWID.ROWID_CREATE function expects the DATA_OBJECT_ID.
The two values will be the same in this test case.

I extended the test case a bit with two indexed access path examples
here:
http://hoopercharles.wordpress.com/2...e-rows-locked/

Charles Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.