oracle lock – look it again

Writing about web page http://www.akadia.com/services/ora_locks_survival_guide.html

Java developers only care about DML row statements

All the row statements: select for update, insert,update,delete,merge will acquire two locks displayed in dba_locks:
1) type:DML, Mode: SS or SX
2) type: Transaction, Mode: Exclusive. This is not table exclusive. You can have two concurrent transaction exclusive locks in one table.

Weird: when i issue select .. for update, I see DML SX (Shouldn’t it be SS ?) + Transaction Exclusive.

When one row is locked by select for update and another session tries to select for update or delete this row, I can see the transaction exclusive lock (associated with the row lock) status is blocking.

Will inserting block another operation? At least for select for update, delete or update, I cannot image a situation they conflict with inserting. It will certainly block DDL changes.

Blocking locks are almost always TX (transaction) locks or TM (table) locks .(Check TX or TM from v$lock. In dba_locks, the TM lock is DML + Exclusive, TX lokc is Transaction + Exclusive)

Java developers usually do not explicitly DML lock tables

So we do not care about the following statements.

Lock table in exclusive mode:
acquire lock: Type DML , mode: Exclusive

Lock table in share row exclusive mode:
acquire lock: type DML, mode: S/Row-X (SSX)

But

RI constraints are validated by the database via a simple SELECT from the dependent (parent) table in question-very simple, very straightforward. If a row is deleted or a primary key is modified within the parent table, all associated child tables need to be scanned to make sure no orphaned records will result. If a row is inserted or the foreign key is modified, the parent table is scanned to ensure that the new foreign key value(s) is valid. If a DELETE CASCADE clause is included, all associated child table records are deleted. Problems begin to arise when we look at how the referential integrity is enforced.

Oracle assumes the existence of an index over every foreign key within a table. This assumption is valid for a primary key constraint or even a unique key constraint but a little presumptuous for every foreign key.

If an index exists on the foreign key column of the child table, no DML locks, other than a lock over the rows being modified, are required.(???)
If the index is not created, a share lock is taken out on the child table for the duration of the transaction.(Seems not true from my testing. Maybe should be “duration of the statement”)

It means a row statement can cause Oracle use lock table statements internally.

Test
parent table t, child table c , using foreign key on delete cascade:
I deleted on row in parent, then I can see two SX locks (one for t, one for c) are issued.
I can delete rows in c not references by the to be deletedrows in p.
I have to wait if I want to delete rows in c that references the rows in p that to be deleted.
If we do not use “on delete cascade”, you can update c while update t.

Hongfeng Sun : 26 Oct 2006 15:35 |  Tags: Oracle |  Comments (0) |  Report a problem