http://VietEnglish.com

http://Edgar411.com

http://ForumGrouper.com

http://hpricot.com

OracleLocking.html
11-22-2001 Bikle
-----------------

Some Simple Locking Scenarios

This page presents a few thoughts about Oracle locking.

We start the page with some simple scenarios.

-Al issues command:
LOCK TABLE scott.emp IN EXCLUSIVE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN EXCLUSIVE MODE ;
Result:
Bobs' session hangs until Al commits or rolls back

-Al issues command:
LOCK TABLE scott.emp IN EXCLUSIVE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN EXCLUSIVE MODE NOWAIT;
Result:
ORA-00054: resource busy and acquire with NOWAIT specified


We can see right away in the two scenarios above that the NOWAIT
keyword is useful; it prevents a session from "hanging" when it is
blocked by a lock.

All of the scenarios below make use of the NOWAIT keyword.

-Al issues command:
LOCK TABLE scott.emp IN EXCLUSIVE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN SHARE MODE NOWAIT;
Result:
ORA-00054: resource busy and acquire with NOWAIT specified

-Al issues command:
LOCK TABLE scott.emp IN EXCLUSIVE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN ROW EXCLUSIVE MODE NOWAIT;
Result:
ORA-00054: resource busy and acquire with NOWAIT specified

-Al issues command:
LOCK TABLE scott.emp IN EXCLUSIVE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN ROW SHARE MODE NOWAIT;
Result:
ORA-00054: resource busy and acquire with NOWAIT specified

-Al issues command:
LOCK TABLE scott.emp IN SHARE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN SHARE MODE NOWAIT;
Result:
Table(s) Locked.

-Al issues command:
LOCK TABLE scott.emp IN SHARE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN ROW EXCLUSIVE MODE NOWAIT;
Result:
ORA-00054: resource busy and acquire with NOWAIT specified

-Al issues command:
LOCK TABLE scott.emp IN SHARE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN ROW SHARE MODE NOWAIT;
Result:
Table(s) Locked.

-Al issues command:
LOCK TABLE scott.emp IN ROW EXCLUSIVE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN SHARE MODE NOWAIT;
Result:
ORA-00054: resource busy and acquire with NOWAIT specified

-Al issues command:
LOCK TABLE scott.emp IN ROW EXCLUSIVE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN ROW EXCLUSIVE MODE NOWAIT;
Result:
Table(s) Locked.

-Al issues command:
LOCK TABLE scott.emp IN ROW EXCLUSIVE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN ROW SHARE MODE NOWAIT;
Result:
Table(s) Locked.

-Al issues command:
LOCK TABLE scott.emp IN ROW SHARE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN EXCLUSIVE MODE NOWAIT;
Result:
ORA-00054: resource busy and acquire with NOWAIT specified

-Al issues command:
LOCK TABLE scott.emp IN ROW SHARE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN SHARE MODE NOWAIT;
Result:
Table(s) Locked.

-Al issues command:
LOCK TABLE scott.emp IN ROW SHARE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN ROW SHARE MODE NOWAIT;
Result:
Table(s) Locked.

-Al issues command:
LOCK TABLE scott.emp IN ROW SHARE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN ROW EXCLUSIVE MODE NOWAIT;
Result:
Table(s) Locked.


-Al issues command:
LOCK TABLE scott.emp IN SHARE ROW EXCLUSIVE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN SHARE MODE NOWAIT;
Result:
ORA-00054: resource busy and acquire with NOWAIT specified

-Al issues command:
LOCK TABLE scott.emp IN SHARE ROW EXCLUSIVE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN ROW EXCLUSIVE MODE NOWAIT;
Result:
ORA-00054: resource busy and acquire with NOWAIT specified

-Al issues command:
LOCK TABLE scott.emp IN SHARE ROW EXCLUSIVE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN SHARE ROW EXCLUSIVE MODE NOWAIT;
Result:
ORA-00054: resource busy and acquire with NOWAIT specified

-Al issues command:
LOCK TABLE scott.emp IN SHARE ROW EXCLUSIVE MODE NOWAIT;
-Bob issues command:
LOCK TABLE scott.emp IN ROW SHARE MODE NOWAIT;
Result:
Table(s) Locked.

I can write down some general observations about the above results:

TABLE EXCLUSIVE locks prevent:
-Other table locks
-Other row   locks

In addition:
TABLE SHARE locks prevent:
-ROW EXCLUSIVE locks
-SHARE ROW EXCLUSIVE locks

ROW locks prevent TABLE locks sometimes.

Most ROW locks do not prevent ROW locks.

SHARE ROW EXCLUSIVE locks prevent:
-ROW EXCLUSIVE locks
-SHARE ROW EXCLUSIVE locks

Some reading of the Oracle documentation reveals the following
information and symbols related to locks:

EXCLUSIVE (X)  This prevents everything but queries
SHARE (S)      This prevents updates and some locks
ROW SHARE (RS) This prevents X locks; Allows S, RS, RX
ROW EXCLUSIVE (RX) This may be the most common lock which is used by INSERT, UPDATE, DELETE.  
SHARE ROW EXCLUSIVE (SRX)  This does not look useful; it prevents RX and SRX.
                           It would behave similar to an S.

Q: Can you describe what the syntax SELECT ... FOR UPDATE does?
A: A simple experiment reveals that it does exhibit locking behavior.
demo:
Al Issues: SELECT * FROM scott.emp FOR UPDATE OF ename;
Bob Issues:
SELECT * FROM scott.emp FOR UPDATE OF ename NOWAIT
Result:
ORA-00054: resource busy and acquire with NOWAIT specified

Fortunately he used the NOWAIT keyword or his session would have waited
on Al's COMMIT:

If Bob forgets the NOWAIT,
we can see the lock contention using the ultllockt script:
17:50:54 SQL> /* Print out the result in a tree structured fashion */
17:50:54 SQL> select    lpad(' ',3*(level-1)) || waiting_session waiting_session,
17:50:54   2          lock_type,
17:50:54   3          mode_requested,
17:50:54   4          mode_held,
17:50:54   5          lock_id1,
17:50:54   6          lock_id2
17:50:54   7   from lock_holders
17:50:54   8  connect by  prior waiting_session = holding_session
17:50:54   9    start with holding_session is null;

WAITING_SESSION
--------------------------------------------------------------------------------------------
LOCK_TYPE                  MODE_REQUESTED MODE_HELD      LOCK_ID1               LOCK_ID2
-------------------------- -------------- -------------- ---------------------- ------------
12
None
   23
Transaction                Exclusive      Exclusive      131119                 9239


Here is another demo of the SELECT ... FOR UPDATE syntax:

Al issues:
SELECT * FROM scott.emp WHERE ename='SMITH' FOR UPDATE OF ename NOWAIT;
Bob issues:
SELECT * FROM scott.emp WHERE ename='MILLER' FOR UPDATE OF ename NOWAIT;
Result:
Bob's SELECT runs fine.

Al issues:
SELECT * FROM scott.emp WHERE ename='SMITH' FOR UPDATE OF ename NOWAIT;
Bob issues:
SELECT * FROM scott.emp WHERE ename='SMITH' FOR UPDATE OF ename NOWAIT;
Result:
ORA-00054: resource busy and acquire with NOWAIT specified

Here is a demo of a deadlock.  We start the demo with a couple of selects:

Al issues:
SELECT * FROM scott.emp WHERE ename='SMITH' FOR UPDATE OF ename NOWAIT;
Bob issues:
SELECT * FROM scott.emp WHERE ename='MILLER' FOR UPDATE OF ename NOWAIT;

Now Bob wants to lock the SMITH record and wants to neglect to use NOWAIT keyword:
Bob issues:
SELECT * FROM scott.emp WHERE ename='SMITH' FOR UPDATE OF ename;
Bob is now waiting on Al to commit.

Al issues:
SELECT * FROM scott.emp WHERE ename='MILLER' FOR UPDATE OF ename;

Notice that Al neglected to use NOWAIT keyword.  Al is waiting for Bob
to commit who is waiting for Al to commit.  But Al cannot commit; his
session his hung since he neglected to use NOWAIT keyword.

Next, the Oracle kernel immediately works its magic.  
We see a message appear in Bob's window:
ORA-00060: deadlock detected while waiting for resource

Also we see that Bob was given a SQL> prompt.  Bob is no longer hung.
And we see that Al's session is hung.  Since Bob was given a SQL>
prompt, this means the Oracle kernel forced either Al to drop his lock
on SMITH or Bob to drop his SMITH lock request.  Since Al's session is
hung, Bob obviously still has a lock on MILLER.

So the question is, did the kernel force Al to drop his lock on SMITH
or did the kernel force Bob to drop his SMITH lock request.  The answer
is easy to get; if Bob can get a lock on SMITH this means the kernel
forced Al to drop his lock on SMITH.

Bob issues:
SELECT * FROM scott.emp WHERE ename='SMITH' FOR UPDATE OF ename;
Result:
We see a message appear in Al's window:
ORA-00060: deadlock detected while waiting for resource

This means the kernel had forced  Bob to drop his SMITH lock request.

Also we can infer the kernel had just forced Al to drop his MILLER
lock request.