Re: deadlock

Shitole Sanjay (OG) (sanjay) wrote:
: Oracle does acquire table level locks when tables involving dml
: operations are defined using foregin key integrity constraints.
:
: For example:
:
: create table dept(dept_no number(5) primary key,
: dept_name varchar2(30));
: create table emp(emp_no number(5) primary key,
: dept_no number(5),
: constraint dept_fk foreign key (dept_no)
: references dept on delete cascade);
: (Assumption: No index on dept_no column of emp table)
: Now perform following operations
:
: 1. User A : Delete dept where dept_no = 100;
: 2. User B : Update dept set dept_name = 'SALES' where dept_no =
: 200;
:
: Result: User B has to wait for lock acquired by User B.

Hello,

My understanding of row and table locks is this:

DML operations can acquire data locks at two levels, for specific rows or entire tables. An exclusive *row lock* is acquired for an individual row on behalf of a transaction when the row is modified by an INSERT, UPDATE, DELETE or SELECT ... FOR UPDATE statement. If a transaction obtains a *row lock*, it also acquires a *table lock* for the corresponding table, preventing DDL operations that would override data modifications in an ongoing transaction. A transaction acquires a *table lock* when a table is modified with the following DML statements: INSERT, UPDATE, DELETE, SELECT ... FOR UPDATE, and LOCK TABLE ... IN ... MODE. Any table lock acquired for a table prevents the acquisition of an exclusive DDL lock on that table. The restrictiveness of a table lock can be divided into several categories: row share table lock (RS), row exclusive table lock (RX), share table lock (S), share row exclusive table lock (SRX), and exclusive table lock (X). The restrictiveness of a table lock determines the other types of table locks that can be obtained on the same table.  

All data locks are released when the transaction commits or rolls back. Oracle automatically converts a table lock of lower restrictiveness to one of higher restrictiveness as appropriate.

--
Neven Tomov
Dept. of Computing & El. Eng.           Tel:   +44 131 449 5111 ext. 4191/4194
Heriot-Watt University, Riccarton,      Fax:   +44 131 451 3431
Edinburgh, Scotland, UK, EH14 4AS       Email: neven_at_cee.hw.ac.uk