Transaction--> Locks
- How is my transaction tracked
- How does oracle ensures or rather maintains data integrity and table definition integrity during the due course of my transaction
- How is lock escalation handled in Oracle
- Where is the Lock info stored in Oracle
Does these question are of your interest, then proceed reading below,
A transaction begins with a DML/Select FOR UPDATE and ends with commit, rollback or even with a DDL. Every transaction is identified with a unique transaction ID. Let's try it out,
SQL> SQL> update t1 set id_val='ORC' where id = 1; 1 row updated. Elapsed: 00:00:00.16 SQL>
SQL> SQL> select xid from v$transaction; XID ---------------- 01000C0083060000 Elapsed: 00:00:00.01 SQL>
If you would like to see the decimal notation of the transaction id, then try below
SQL> SQL> select dbms_transaction.local_transaction_id from dual; LOCAL_TRANSACTION_ID -------------------------------------------------------------------------------- 1.12.1667 Elapsed: 00:00:00.02
SQL> select xid,ADDR,XIDUSN,XIDSLOT,XIDSQN from v$transaction; XID ADDR XIDUSN XIDSLOT XIDSQN ---------------- -------- ---------- ---------- ---------- 01000C0083060000 3B0F7554 1 12 1667 Elapsed: 00:00:00.01 SQL>
XIDUSN - Transaction ID's Undo Segment Number
XIDSLOT - Transaction ID's Slot Number
XIDSQN - Transaction ID's Sequence Number
Transaction ID is the single identifier which would help us to proceed further with the series of investigation,
SQL>
SQL> select * from v$locked_object;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ---------------- --------------- --------- -----------
1 12 1667 74916 203 HARI HARI-PC\HARI 6076:5924 3
Elapsed: 00:00:00.05
SQL>
LOCKED_MODE in v$locked_Object indicates in what mode the Object_ID is locked. In our case the Object_Id 74916 (Table Name - T2) is locked in SX(Row- Exclusive) mode i.,e "Rows are locked in the table in exclusive mode", Now lets see the types of locks the transaction is holding,
SQL>
SQL> select * from v$lock where sid=203;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
3D378880 3D3788AC 203 AE 100 0 4 0 435 0
0EEB6AA0 0EEB6AD0 203 TM 74916 0 3 0 13 0
3B0F7554 3B0F7594 203 TX 65548 1667 6 0 13 0
Elapsed: 00:00:00.05
SQL>
v$Lock helps us to identify the types of locks associated with the transaction. The locks bundled with the transaction are AE,TM, TX. Keeping aside AE at the moment lets see what these TX and TM are,
TX Lock/Enqueue - This represents the transaction and are enforced to maintain data integrity. Its the Row-Level locking, a modified row is always locked in exclusive manner so that other users cannot modify the row unless untill the parent transaction ends. And that's the reason it shows LMODE = 6 indicating exclusive lock on the row and REQUEST = 0 means am holding the lock and am the owner, therefore doesn't request any. The ID1(usn+slot) and ID2(seq) together represents the transaction ID. This exclusive lock doesn't mean DML are prohibited on the table, it's only on that particular row so that other session cannot modify it. So a lock is never escalated it can only be released when the transaction ends.
TM Lock/Enqueue - This represents the table and they lock the table from structural change, ensuring no change to data definition of the table during the due course of the transaction. And that's the reason it shows LMODE = 3 indicating rows are locked in exclusive mode in this table. ID1 shows the Object_ID being locked.
Oracle doesn't have the concept of Lock Manager which would normally act as the central locking system to handle locks in few database's, remember a central locking is prone to contention. In oracle the lock info is stored right at the block itself. The Data Block holds the lock bit information which in turn points to the ITL - Interested Transaction List and this in turn points to the undo segment header to validate the validity of the data.
