Database Reference
In-Depth Information
Session 2:
00:50:44 SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Session altered.
00:51:00 SQL> SET FEEDBACK ON
00:51:00 SQL> SET ARRAYSIZE 1
00:51:00 SQL> SET PAGESIZE 1
00:51:00 SQL> SET TIME ON
00:51:00 SQL> SET PAUSE "Hit return to continue"
00:51:00 SQL> SET PAUSE ON
00:51:00 SQL> SELECT tab.msgid
FROM "NDEBES"."POST_OFFICE_QUEUE_TABLE" tab
WHERE q_name=:q_name and (state=:state)
FOR UPDATE SKIP LOCKED;
Hit return to continue
Both sessions are waiting for input in order to display the first row. Note how session 2
now prints “Hit return to continue”. This was not the case in the first test, since the
FETCH
call
of session 2 could not complete due to the wait for a TX enqueue. Now hit return in session 2
…
Hit return to continue
54783A999BB544419CDB0D8D44702CD3
Hit return to continue
Session 2 succeeded in retrieving a row, even though session 1 issued
SELECT
FOR
UPDATE
SKIP
LOCKED
first. Now hit return in session 1.
…
Hit return to continue
34F01D98AF0444FF91B10C6D00CB5826
Hit return to continue
Session 1 also retrieved a row. Hitting the return key while alternating between sessions
shows that both get a fair share of the rows. Looking at
V$LOCK
confirms that this time neither
session is blocked
SQL> SELECT sid, type, id1, id2, lmode, request, block
FROM v$lock WHERE sid IN (134,158)
ORDER BY 1;
SID TYPE ID1 ID2 LMODE REQUEST BLOCK
--- ---- ------ --- ----- ------- -----
134 TM 16567 0 3 0 0
134 TX 196644 644 6 0 0
158 TX 65539 666 6 0 0
158 TM 16567 0 3 0 0
