Locking and Concurrency Part I

Locks and Transactions

TX and TM LocksWe can explain the same with the help of a test as shown below

SQL> conn test/testConnected.SQL> drop table t;
Table dropped.

SQL> Create table t(x int);
Table created.

SQL> insert into t values(1);
1 row created.

SQL> insert into t values(2);
1 row created.

SESSION 2

SQL> conn test/testConnected.

SQL> DROP TABLE T;DROP TABLE T*ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specified

Investigating the Locks Held for user test session

SQL> select SID,LMODE,ADDR,TYPE FROM V$LOCK WHERE SID=(SELECT SID FROM V$SESSION WHERE USERNAME='TEST');
SID LMODE ADDR TY
---------- ---------- -------- --
12 6 66392948 TX
12 3 6634E058 TM
2 rows selected.

SQL> SELECT SES_ADDR ,ADDR FROM V$TRANSACTION ;
SES_ADDR ADDR
-------- --------
65A3E1FC 66392948 ->Points to the TX lock above
1 row selected.

ROW LEVEL LOCK
-----------------------
Now, let's look at what a row level lock is and when one is created:
A row-level lock (TX type) is automatically acquired for each row that is modified by a statement using insert, update, delete, and select ...for update. This lock keeps anyone else from modifying the same row at the same time.

WHEN IS THE LOCK CREATED?
-----------------------
Now let's consider when the lock is created:
A DML transaction will get at least two locks: a shared table lock (TM type) and an exclusive row-level lock (TX type). Internally, when a transaction goes to modify a row, Oracle turns a byte on in the row header pointing to the interested transaction lock (ITL) slot used by that transaction. At the row level, a lock mode can only be exclusive. If you are dealing with a partition table, the transaction will acquire a table partition lock for each required partition as well as a table lock.
Now the session 2 tried to drop the table, but failed with an error, because the TM lock acquired does prevent any DDL statements being executed against the table when another transaction is still active on the table.

Read Consistency Model
SQL> truncate table t;
Table truncated.
SQL> begin for i in 1..5 loop insert into t values (i); end loop; end;/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select * from t;
X
----------
12345
5 rows selected.

SQL> select X from T where x < 3 for update;
X
----------
12
2 rows selected.
SQL> update t set x=10 where x=1;
1 row updated.

SESSION 2
SQL> INSERT INTO T VALUES (6);
1 row created.
SQL> SELECT * FROM T;
X
----------
123456
6 rows selected.

Investigating about Locks and Transactions
SQL> select SID, LMODE,ADDR,TYPE FROM V$LOCK WHERE SID IN (12,16);
SID LMODE ADDR TY
---------- ---------- -------- --
12 6 66392D24 TX
12 3 6634E058 TM
16 6 66397790 TX
16 3 6634E0DC TM
4 rows selected.

SQL> SELECT SES_ADDR ,ADDR FROM V$TRANSACTION ;
SES_ADDR ADDR
-------- --------
65A3E1FC 66392D24->points to sid 1265A407BC 66397790->points to sid 16
2 rows selected.

We have two transactions active
Looking into the session 2 output for select * from t , please note that it does not see the updated values in session1 as the transaction started at session 1 is still active.This explains one of the read consistency methods in oracle, ie data is always consistent.

Behaviour of Row Exclusive Locks

SESSION 1

SQL> truncate table t;
Table truncated.

SQL> select * from t;
no rows selected

SQL> lock table t in row exclusive mode;
Table(s) Locked.

SQL> select * from t;
no rows selected

SESSION 2

SQL> insert into t values (11);
1 row created.
SQL> SELECT * FROM T;
X
----------
11
1 row selected.

Investigating on the Locks and Transactions

SQL> select SID, LMODE,ADDR,TYPE FROM V$LOCK WHERE SID IN (12,16);
SID LMODE ADDR TY
---------- ---------- -------- --
12 3 6634E058 TM
16 6 66397790 TX
16 3 6634E0DC TM
3 rows selected.
SQL> SELECT SES_ADDR ,ADDR FROM V$TRANSACTION ;
SES_ADDR ADDR
-------- --------
65A407BC 66397790->points to sid 16
1 row selected.
Even though session 1 issued a "lock table t in row exclusive mode", session 2 (SID 16) is able to initiate a transaction.
Row ExclusiveThe least restrictive of the locks. Allows other transactions to insert, update, delete or lock other rows in the same table but at the same time prohibits to lock a table in exclusive mode.
ie from session 2 if we issueSQL> lock table t in exclusive mode;This hangs because Row Exclusive "Prohibits lock table... in exclusive mode".

Behaviour of Table Exclusive Lock

CASE A
SQL> conn test/testConnected.SQL> drop table t;
Table dropped.
SQL> Create table t(x int);
Table created.
SQL> select * from t;
no rows selected
SQL> begin for i in 1..10 loop insert into t values (i); end loop; end; /
PL/SQL procedure successfully completed.
SQL> select * from t;
X
----------
12345678910
10 rows selected.

SESSION 2

SQL> lOCK TABLE T IN EXCLUSIVE MODE;
This session hangs

Analzing the Locks and Transactions

SQL>SELECT SID FROM V$SESSION WHERE USERNAME='TEST';
SID
----------
12
16
2 rows selected.

SQL> select SID, LMODE,request,TYPE FROM V$LOCK WHERE SID IN (12,16)
SID LMODE REQUEST TY
---------- ---------- ---------- --
12 6 0 TX
12 3 0 TM
16 0 6 TM ->waiting for an exclusive row lock
3 rows selected.

SQL> SELECT SES_ADDR ,ADDR FROM V$TRANSACTION ;
SES_ADDR ADDR
-------- --------65A3E1FC 66392D24 ->Points to TX lock
1 row selected.

SESSION 1

SQL> rollback;
Rollback complete.

Now lets see what has happened about the locks
SQL> select SID, LMODE,request,TYPE FROM V$LOCK WHERE SID IN (12,16);
SID LMODE REQUEST TY
---------- ---------- ---------- --
16 6 0 TM
1 row selected.

SID 16 has acquired a lock on the table in row exclusive mode.

CASE B

SESSION 1SQL>lock table t in exclusive mode;

SESSION 2

SQL> insert into t values (12);This session hangs

Investigating on the Locks and Transactions

SQL> select SID, LMODE,request,TYPE FROM V$LOCK WHERE SID IN (12,16);
SID LMODE REQUEST TY
---------- ---------- ---------- --
12 6 0 TM
16 0 3 TM
2 rows selected.

SQL> SELECT SES_ADDR ,ADDR FROM V$TRANSACTION ;
no rows selected

There are no active transactions as of now.

Note thats SID 12 Has not initiated a TRANSACTION ,Just acquired a table level Lock so no Locks of the Type "TX" and SID 16 Is waiting to acquire a row exclusive Lock , not yet Initiated a Transaction as verified from V$TRANSACTION .

Exclusive Lock is : "The most restrictive lock. Only a single transaction can lock a table in X mode"