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.