Database Reference
In-Depth Information
The row previously fetched by SQL*Plus session 1 is now displayed and the SQL trace file
shows that another FETCH call was done.
FETCH #1:c=0,e=158,p=0,cr=12,cu=0,mis=0,r=1,dep=0,og=1,tim=79430535911
*** 2007-07-10 23:48:01.833
FETCH #1:c=0,e=19466,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,tim=79848141763
SQL*Plus pauses again after displaying one row, since PAGESIZE=1 is set. Switching back to
session 2 there is still no progress. Even though I prevented SQL*Plus from fetching all the rele-
vant rows with a single FETCH call by reducing ARRAYSIZE from its default of 15 to just 1, the whole
process is single threaded and thus there's no room for benefiting from a multiprocessor system.
Finally, after fetching all rows and committing in session 1, session 2 can retrieve the
matching rows. In the real world though, there would be no rows left for session 2 to process,
since session 1 would have changed the status to a value other than 0 after finishing its job.
Here's the COMMIT in session 1:
Hit return to continue
2544AA9A68C54A9FB9B6FE410574D85A
7 rows selected.
23:56:03 SQL>
23:56:05 SQL> COMMIT;
Commit complete.
23:56:17 SQL>
Now session 2 wakes up.
Hit return to continue
34F01D98AF0444FF91B10C6D00CB5826
2544AA9A68C54A9FB9B6FE410574D85A
7 rows selected.
Even though the normal processing of SQL*Plus was altered, the result of this test is disap-
pointing. However, taking locking strategies of relational databases into account, the behavior
observed is exactly what one should expect. Let's investigate what happens when we add SKIP
LOCKED to the picture.
Session 1:
00:50:41 SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Session altered.
00:50:51 SQL> SET ARRAYSIZE 1
00:50:51 SQL> SET PAGESIZE 1
00:50:51 SQL> SET TIME ON
00:50:51 SQL> SET PAUSE "Hit return to continue"
00:50:51 SQL> SET PAUSE ON
00:50:51 SQL> SELECT tab.msgid
00:50:51 FROM "NDEBES"."POST_OFFICE_QUEUE_TABLE" tab
00:50:51 WHERE q_name=:q_name and (state=:state)
00:50:51 FOR UPDATE SKIP LOCKED;
Hit return to continue
Search WWH ::




Custom Search