Table Locking

Giganews Newsgroups
Subject: Table Locking
Posted by:  Joseph Dimech (j…
Date: Thu, 02 Dec 2004


I have recently been running into an odd problem and was wondering if
someone less has experienced the same thing. I am running Oracle 8.1.7 and
it seems that certain processes are creating  shared table locks (LOCK MODE
= 4 or 5). From my understanding, this type of LOCK will still allow other
process to acquire locks on their rows of interest but will not allow other
process to actually update to the table. In other words, another process can
select and lock but not finish the transaction because the original process
has a lock on the table of type 4 or 5. So the application acts as if it is

From my understanding, Shared Table Locks (LOCK MODE = 4) or Share Row
Exclusive Table Locks (LOCK MODE = 5) can only occur when an explicit "LOCK
TABLE ..." statement is  executed by a process. However, noting in my code
does this. My processes are designed to only acquire an Row Exclusive Table
Locks(LOCK MODE = 3) for their database transactions. Just simple INSERT,
DELETE, UPDATE, or SELECT FOR UPDATE type of SQL statements.

My question is: what could be causing processes to attain LOCK MODES 4 and 5
when there is noting explicitly in my code to do so? I have written some
scripts using the V$ views to try to analyze the problem. Is there any
canned oracle tools or scripts whcih may be able may give me more
information so can pinpoint the problem? Is there any logs which I should
look at which may give some clues? Thanks in advance!