Database Reference
In-Depth Information
Table 16-1 illustrates the entire sequence chronologically. Rows further down in the table
correspond to a later point in time. The timestamps (format is HH24:MI:SS) printed by
SQL*Plus due to
SET
TIME
ON
, serve to further document the sequence of events.
Table 16-1.
Concurrent SELECT FOR UPDATE SKIP LOCKED
Session 1
Session 2
00:50:51 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
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
34F01D98AF0444FF91B10C6D00CB5826
Hit return to continue
54783A999BB544419CDB0D8D44702CD3
Hit return to continue
25CC997C9ADE48FFABCE33E62C18A7F3
Hit return to continue
B78950028B3A4F42A5C9460DDDB9F9D7
Hit return to continue
353D44D753494D78B9C5E7B515263A6D
3 rows selected.
00:57:43 SQL>
89A118C42BFA4A22AE31932E3426E493
Hit return to continue
2544AA9A68C54A9FB9B6FE410574D85A
4 rows selected.
00:57:45 SQL>
This time the results are excellent. Both sessions fetched a fair share of the rows, no row
was fetched by more than one session, and no rows were skipped. Both sessions were properly
isolated from each other due to locking, yet no session blocked the other. The level 8 extended
SQL trace files for both sessions confirm this in that they do not contain a single wait for a TX
lock during the entire sequence of
FETCH
calls. The approach works just as well with three or
more sessions.
At the beginning of this chapter, I recommended the use of AQ for good reason. As we
learned in this chapter, it is inherently scalable, since it uses the undocumented
SKIP
LOCKED
clause when dequeuing messages. Another compelling reason for using AQ is that it has no
requirement for polling to find out whether messages are available or not. I have seen several
systems which were under high load due to applications that asked the DBMS instance several
dozen times a second whether there was work to do. In AQ parlance, one would say that these
applications were looking for messages to consume. The poor implementation resulted in one
CPU being almost 100% busy all the time.
