What causes a table lock
Hi,
We are using Oracle 9i R2 on an AIX 4.3.3 server. We have been having problems with a deadlock between multiple processes, always against the same table. Looking at the stored procedures and the Java code that calls them, we can't figure out why we are blocking.
We have left the "block" running for up to 5 minutes and oracle never kills one or the other session, we must always do it. This is part of the reason we are really confused.
The second part that is confusing is using Enterprise Manager we can view both sessions and we always see the blocker is holding an EXCLUSIVE lock on the table. We can't find anywhere that we are doing a table lock, so we can't figure out what causes it. Details from Enterprise manager:
Lock Type: TX
Mode Held: Exclusive
Mode Requested: NONE
Object Name: Mailing
Object Type: TABLE
The blocking sessions all are trying to obtain ROW EXCLUSIVE locks on the MAILING table also.
In the session with the Exclusive lock, we have several other tables locked, but all are part of an update that have ROW EXCLUSIVE or ROW SHARE locks. They are all "TM" locks also.
We do see a ROW EXCLUSIVE lock in this transaction against the MAILING table. This is the lock we expected, since we are updating a row here.
What causes an EXCLUSIVE lock on a table like this?
Thanks
Chris