Re: Enqueue Wait Events - TM - SSX

From: ORA600 <panandrao_at_gmail.com>
Date: 26 Jan 2006 00:30:30 -0800
Message-ID: <1138264230.669885.28500@g14g2000cwa.googlegroups.com>

I would say, Read the Concepts Manual. No better place to learn...the fundamentals.

TM lock is a table level lock which is *automatically* acquired when you run a DML or DDL statement (whichever is appropriate). TM locks prevent conflicting DDL statements from doing their work when a table is modified using INSERT, UPDATE or DELETE. In simple words, when there is an open transaction on a table, you should not be able to drop or alter the table.

A table could be locked in a particular mode, (SSX/SRX in your case) depending on the statement used.

SELECT ...FOR UPDATE ... would acquire a Row Exclusive (RX/SX) lock on the Table which is visible as a 'TM' type lock in LMODE column in V$LOCK. A DELETE statement, like yours would also do the same thing.

A TX Enqueue is acquired when you run a DML statement. These are row level locks. Your DELETE statement would acquire a TX Enqueue in Xclusive mode on all the affected rows. It is also visible in LMODE column in V$LOCK.

you mentioned that...

"We have confirmed that the referenced foreign key /primary key indexes do exist and valid"..

are you sure? would you want to check BOTH the parent and the child tables correctly? check the child table reference keys and if they indeed have indexes. are you using 'ON DELETE CASCADE' ?

Some locking behaviour in Oracle 9.2 has changed.

Ex. using EMP and DEPT with Referential Integrity Constraints, indexes on DEPTNO in both the tables and without ON DELETE CASCADE.

SQL> DELETE FROM EMP WHERE DEPTNO = 10; Lock Modes

---

EMP - TM Lock in SX Mode
EMP - TX Row level lock in Xclusive mode on the affect row. DEPT - TM Lock in SS Mode ==> Changed Behaviour in 9.2

With ON DELETE CASCADE,

If you delete from Parent table,

SQL> DELETE FROM DEPT WHERE DEPTNO = 10; DEPT - TM Lock in SX Mode
EMP - TM Lock in SX Mode
DEPT - TX Row level lock in Xclusive mode on the affect row.

If you delete from Child table,

SQL> DELETE FROM EMP WHERE DEPTNO = 10; EMP - TM Lock in SX Mode
EMP - TX Row level lock in Xclusive mode on the affect row. DEPT - TM Lock in SS Mode ==> Changed Behaviour in 9.2

Another possibility is that there would be a 3rd or probably 4th table involved in this Foreign Key chain of relationships and without indexes, that's when you would see SSX lock modes on the child table.

Lastly, read this nice note on Metalink. Note:15476.1.

regards
ORA600 Received on Thu Jan 26 2006 - 02:30:30 CST