Locking is very important aspect in a database. It is must to regulate access to (shared) resource so that one process/user doesn't change it while other is modifying the same.
If we question how database may be locking rows/data, we usually may think there may be a process/manager which must be doing the task internally.
The manager process maintains the information which process id wants/has to lock particular rows etc. Seems perfect for simple logical explanation, However if we consider this for OLTP systems, we can easily understand that such a single point of control may cause bottleneck.
All processes competing for locks(acquire or release) on resources via a single manager will ensure that the application/workload cant scale at all.
One of the major reason for Oracle being the top database is the locking mechanism which it implements. There is no such concept as lock manger etc.
Oracle places the lock related information in the actual data block itself.
Ex: when a row in a table is modified by a DML statement, the process must have accessed the database block and loaded in the database buffer cache.
During this, the information about the row now being locked is placed in the header of the block itself.
When another transaction wants to get the lock on the same row, it tries to access the block, and when it does so ,it can easily understand from the information in the header that the particular row is locked by another transaction and it must wait.
ITL(Interested transaction list) is the section in the header of each data block which keeps this information.
When a transaction wants to lock a row in the block it places the information in one of the ITL slots.
Consider another transaction at the same time wants to lock another row in the same block, then it will put information in the next ITL slot as shown in above data block diagram.
Ex: consider below session which is updating the record in emp table.
If we see the contents of the data block for the table ,we can see the ITL entries information.
We can observe that the 2nd ITL entry shows the transaction id and in "lck" column value 1, meaning 1 row is locked by it.
One should understand that the relation of ITL slots and rows being locked is not 1-1, the ITL slot records the information about the transaction id and lck holds
the number of rows locked by the transaction.
Ex: Consider another transaction which updates 4 rows in the table, you can see from the ITL entry the new transaction id and lck value as 4
The advantage of this internal mechanism:
As the locking related information is scattered throughout the data blocks and not at single central place, this helps in achieving scalability.
Questions to be answered in next session :
1. How many of these ITL slots are available for a block?
2. what is the limit?
3. What happens if all of them are utilized?
Comments
Post a Comment