Locking in Oracle – DML Locks

DML Locks

These are the locks that Oracle places on your behalf when you execute DML. For the purposes of this post (and my sanity), let’s divide DML into 2 categories: Regular DML (the garden variety of INSERT, UPDATE, DELETE, SELECT FOR UPDATE) and Exotic DML (LOCK TABLE …)

This post describes the most common types of locks i.e. locks that are automatically placed on your behalf when you execute Regular DML.

1. Row Locks

These are the simplest part of the Oracle locking scheme to understand. Every row is either locked or it is unlocked. That’s it (no lock types to consider). When you execute regular DML, row locks are obtained on the affected rows.

A salient feature of Oracle’s locking mechanism is the the way row locks are recorded. The fact that a row is locked by a transaction is NOT stored in a centralized list of locks. Rather, the transaction id is stored as part of the row data itself. When your transaction needs to lock a row, Oracle inspects if the transaction specified on the row is still active. If yes, the row is locked by that transaction and your transaction is blocked. If not, the row is locked by your transaction and your transaction id is now stored on the row.

2. Table Locks

These are the ones that come in various modes. When a DML Table lock is in place, another session cannot execute DDL on this table, thus preventing alteration of the table structure while you still hold a table lock. DML table locks are also referred to as TM locks.

The fine Oracle manual tells you that Table locks can be taken in 5 different modes:

RS: row share
RX: row exclusive
S: share
SRX: share row exclusive
X: exclusive

However if you look closely, you will notice that you have to explicitly lock a table (using LOCK TABLE …) to get a lock in any of the last 3 modes (S, SRX, X). The other two modes (RS and RX) are the only ones automatically taken when you execute regular DML. So for now, let’s just consider these two.

The RS (row share) table lock is taken when you execute a SELECT … FOR UPDATE. This is in addition to the row locks on whatever rows are selected as a result of this statement. When this lock is in place other transactions may obtain RS, RX, S and SRX locks on this table. No X locks can be obtained though.

The RX (row exclusive) table lock is taken when you execute an INSERT, UPDATE or DELETE. Again, this table lock is in addition to the row locks on affected rows. When this lock is in place other transactions my place RS and RX locks but not S, SRX and X.

As far as regular DML is concerned there is no difference between the RS and RX locks. The difference only comes into play if you execute LOCK TABLE. This should be pretty rare anyway.

Anything else? Err, yes. In addition to the RS and RX locks on regular DML, S (share) table locks are taken on your table if all of the following conditions are satisfied:

  1. Your table has FKs to other tables (i.e. it has parent tables).
  2. You are updating the PK/UK columns or deleting on a Parent table of this table
  3. The foreign key from your table to this Parent table in unindexed

Usually you do NOT update the PK columns of a table. You usually index your FKs. So you shouldn’t hit the issue of this kind of lock when you execute regular DML, right? Well, almost. There is a little issue with the 9i Merge statement that might unexpectedly cause share table locks to appear.

Update: Applies only to 9.2.0.4 or earlier. See this discussion for details.

Advertisements

7 Comments »

  1. Jason said

    Nice writeup. Thank you for putting it together. I think I’m running into an issue where I have many concurrent threads updating a schema. I believe a source of deadlocks is due in part to child tables not having indexed FK columns. The concurrent updates are operating on discrete sets of data, however oracle still deadlocks at times.

    This write up has given me some ideas on why. I believe I need more indexes. Certainly on FK columns.

    First, I delete all child data then I delete from the parent table based on primary key. I’d expect a row lock, is this a reasonable assumption?

  2. Rahul said

    Jason,

    Glad this was useful.

    As long as you are deleting separate sets of data, the row locks shouldn’t cause a deadlock. However, the attempt to acquire Share(S) table locks in the absence of the index on FK may cause a deadlock.

    Consider one parent table and one child table. 2 threads(sessions) start off around the same time. The sessions perform the operations in thh following sequence:

    Session 1- Delete child data. Row locks on rows. RX table lock on child. Suceeds.
    Session 2 – Delete child data. Row locks on rows. RX table lock on child. Suceeds.
    Session 1 – Attempt to delete parents data by placing S table lock on child. This merely blocks since you cannot have an S table lock when someone else holds an RX table lock. Waiting for Session 2 to finish.
    Session 2 – Attempt to delete parent data by placing S table lock on child. deadlocks since you cannot have an S table lock when another session holds an RX lock and that other session is waiting for you.

    This is quite simple to test.


    -- Create tables
    -- Parent
    create table P (p_id number primary key,
    p_desc varchar2(30));
    -- Child
    create table C (c_id number primary key,
    p_id number,
    c_desc varchar2(30),
    constraint c_p_fk foreign key (p_id) references P(p_id));
    -- Insert parent data
    insert into P values (1, 'P First');
    insert into P values (2, 'P Second');
    insert into P values (3, 'P Third');
    -- Insert child data
    insert into C values (1, 1, 'C First');
    insert into C values (2, 2, 'C Second');
    insert into C values (3, 3, 'C Third');
    -- commit
    commit;

    Now open up 2 separate sessions.
    In session 1 run the following:


    -- Delete data corresponding to p_id = 1
    delete C where p_id = 1
    /
    -- delay so that you can logon to session 2 and run another delete
    BEGIN
    dbms_lock.sleep(30);
    END;
    /
    delete P where p_id = 1
    /
    commit
    /

    In session 2 run the following:


    -- Delete data corresponding to p_id = 2
    delete C where p_id = 2
    /
    delete P where p_id = 2
    /
    commit
    /

    One of the 2 sessions (randomly chosen by Oracle) should give you the following error. The other session will go thru fine.

    delete P where p_id = 1
    *
    ERROR at line 1:
    ORA-00060: deadlock detected while waiting for resource

    Now repeat this test after creating an index on the FK.


    -- Create index on FK
    create index c_p_fk_ix on C(p_id);
    -- Reset data back to original
    delete C;
    delete P;
    -- Insert Parent data
    insert into P values (1, 'P First');
    insert into P values (2, 'P Second');
    insert into P values (3, 'P Third');
    -- Insert Child data
    insert into C values (1, 1, 'C First');
    insert into C values (2, 2, 'C Second');
    insert into C values (3, 3, 'C Third');
    -- commit
    commit;

    Run the same blocks in sessions 1 and 2. There should be no deadlocks detected now.

    Hope this helps.

    Rahul

  3. Kiran said

    Is there a way in which I can lock a table in a transaction such that no other transaction is allowed to insert data into the table during its execution?

  4. Rahul said

    Useful links:

    10g R2 V$LOCK reference
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1147.htm#REFRN30121

    10g R2 Summary of Table Locks
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#sthref2078

  5. Soumya said

    I have excuted a command like ‘Alter table table_name disable table lock’ to prevent ddl against the table but to execute a delete command against the table which has a foreign key to other table the delete command can not work but i have to prevent ddl against that table.
    what is the solution other than writting a trigger?

  6. Chris said

    Is there any way to lock schema?

  7. Hello there! Do you use Twitter? I’d like to follow you if that would be ok. I’m absolutely
    enjoying your blog and look forward to new updates.

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: