Database Reference
In-Depth Information
CHAPTER 16
■ ■ ■
SELECT FOR UPDATE
SKIP LOCKED
S
ELECT
FOR
UPDATE
SKIP
LOCKED
is undocumented in Oracle9
i
and Oracle10
g
. It is used behind
the scenes by Advanced Queuing, a reliable messaging service built into the ORACLE DBMS.
Oracle11
g
Release 1 is the first DBMS release that includes documentation on
SELECT
FOR
UPDATE
SKIP
LOCKED
. The
SKIP
LOCKED
clause improves the scalability of applications that attempt to
concurrently update the same set of rows in a table. It eliminates wait time for TX locks. Consis-
tency and isolation are preserved. The DBMS server assigns a fair share of the rows to each
database client that is interested in an overlapping result set.
Advanced Queuing
Advanced Queuing (AQ) is Oracle's implementation of a reliable messaging service, which is
integrated into the ORACLE DBMS. AQ has been available since Oracle8. With the advent of
Oracle10
g
, it was renamed Streams AQ (see
Streams Advanced Queuing User's Guide and Refer-
ence Oracle10g Release 2
), since Streams—an alternative replication mechanism to Advanced
Replication—is built on top of message queuing and message propagation with AQ. Streams is
based on redo log mining (Log Miner), whereas Advanced Replication is trigger-based.
AQ messages are usually made up of a user-defined
abstract data type
(ADT; see
Application
Developer's Guide—Advanced Queuing
) built with
CREATE
TYPE
. The latter is called the
payload
of a message. In its simplest form, the payload is merely a BLOB instead of an ADT. Messages
may be created by calling
DBMS_AQ.ENQUEUE
, propagated from one queue to another (even from
one database to another or to another messaging system from a different vendor), and consumed
with
DBMS_AQ.DEQUEUE
. The details are beyond the scope of this topic. Please keep in mind though
that AQ ships with all editions of the ORACLE DBMS at no additional cost; is highly reliable
since it benefits from the infrastructure of the ORACLE DBMS server, which provides crash and
media recovery; and has Java, PL/SQL, and C/C++ interfaces. So if you do need message
queuing functionality in an upcoming project, AQ might be the right choice for you.
If you have ever taken a closer look behind the scenes of AQ, you may have noticed the
undocumented
SELECT
FOR
UPDATE
SKIP
LOCKED
statement. In case you weren't among the lucky
ones who were able to obtain a backstage pass, the well-kept secret will be unveiled instantly.
AQ uses
SKIP
LOCKED
when removing messages from a queue with
DBMS_AQ.DEQUEUE
to ensure
scalability by preventing waits for TX locks. In a situation where several processes dequeue
from the same queue simultaneously, locking would severely limit the scalability of applications
149
