Locks in Oracle - Part 2 Copy

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

Are these 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,

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,

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,

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.