Table of Contents
1. What is a database lock in the context of SQL?
When two sessions or users of database try to update or delete the same data in a table, then there will be a concurrent update problem. In order to avoid this problem, database locks the data for the first user and allows him to update/delete the data. Once he is done with his update/delete, he COMMITs or ROLLBACK the transaction, which will release the lock on the data. When lock on the data is released, another user can lock it for his changes.
Therefore locking in the context of SQL is to hold the row or particular column which the user is going to update and not allowing any other session or user to insert/update/delete the data. It will not allow anyone to use the data until the lock on the data is released. Lock on the data will be released when the transaction is committed or rolled back.
2. What happens when another session tries to update the locked data?
Suppose two store keepers are updating the quantity of item X at the same time like below. When first user tries to update the data, he locks the data for him to complete the transaction. Therefore second store keepers request to lock the data will go to waiting state. He will wait to get lock on the data till first user releases it. Once first user releases the lock, he immediately acquires lock on it and updates his data. Once second user’s transaction is complete, he releases the lock by committing his transaction.
3. What are the different Database locking techniques?
Database lock can be placed at different levels – on single row or multiple rows or on particular column or on entire table. This database locking at different level is known as locking granularity. Let us discuss them one by one below:
- Database Level Locking : In this method, entire database is locked for update. Here, only one user or session will be active for any update and any other users cannot update the data. This method is not widely used, as it locks entire database. However, in Oracle the exclusive lock is same as Database lock and does not allow others to use entire database. It will be helpful when some support update is being executed like upgrading to new version of software etc.
- File Level Locking : Here, entire database file will be locked. When we say database file, it may include whole table, or part of a table or part of multiple tables. Since file lock can include either whole or partial data from different tables, this type of lock is less frequent.
- Table Level Locking : In this method, entire table will be locked. This will be useful when we need to update whole rows of the table. It will also be useful when we add/ remove some columns of the table where the changes affect entire table. Therefore, in Oracle this type of lock is also known as DDL lock.
- Page or Block Level Locking : In this method, page or block of the database file will be locked for update. A page or block might contain entire or partial data of the table. This page or block represents space in memory location where data is occupied. This may contain entire table data or partial data. Hence this type of locking is also less frequent.
- Row Level Locking : In this method entire row of a table is locked for update. It is most common type of locking mechanism.
- Column Level Locking : In this method some columns of a row of a table is locked for update. This type of lock requires lots of resource to manage and release locks. Hence it is very less frequently used locking type.
4. Are locks automatically used by databases?
Whenever a user issues UPDATE or DELETE command, database will implicitly place the lock on the data. It does not require user to explicitly type lock on the data. Whenever the database sees UPDATE or DELETE statement, lock is automatically placed on the data.
5. Can data be read when a lock is in place?
Reading the data when it is locked depends on the locking mechanism used. If the lock is read exclusive, then it will not allow to read locked data
6. What is the point of database locking?
We are locking the data in database while updating to avoid concurrent update problem. Concurrent Update problem is the one where multiple sessions of the database is trying to update the same data at the same time. A DB session is created when a user connects to the DB. Same user may connect to the DB multiple times and multiple DB sessions will be created for that user. It need not be different user connecting to the same DB. In different session, user may be trying to update the same data at the same time. Because of this there will be some data issue while updating. This is called concurrent update problem of database.
7. What is lock contention ?
Lock contention is the process where lock on the data will not be release as quickly as the COMMIT or ROLLBACK is issued. It might take time to release the lock due to slower systems, or may be another lock on another data is waiting for it to release the lock. This will make the system to wait for release the lock for indefinite period of time. This will lead to deadlock situation.
8. What is lock escalation? Provide an example.
Lock escalation is the process of escalating the locks to higher level due to extremely increasing locks at lower level. For example, if there are many locks at row level for a table, it will be better to escalate this lock to table level rather than having at each row level. This will help in reducing the overhead of maintaining the locks for each row. It reduces the number of locks on the table and increases the performance drastically.This type of lock escalation is supported by limited number of RDBMS – Sybase, SQL Server, DB2 etc. In these databases, lock escalation happens automatically. The database checks for the locks on the table and determines if it has to be raised to the higher level. But we can also control escalation by enabling or disabling these automatic checks like below.
ALTER TABLE EMPLOYEES SET (LOCK_ESCALATION = DISABLE); // Disables lock escalations ALTER TABLE EMPLOYEES SET (LOCK_ESCALATION = TABLE); // escalates to table level
It is mainly helpful in reducing the number of locks on the table and overhead of maintaining the locks. This in turn helps in increasing the performance of the query.
9. What is a database deadlock?
A deadlock is a situation in the database where one user or session is waiting for the lock to be acquired on the data, while another user or session is waiting for the first user or session to complete the transaction and release the lock. This means, waiting for acquiring lock forms a cycle and it will never release the lock at all. This situation is called deadlock.For example, suppose user X is waiting for Transaction TXN_Y to complete and release the lock. But at the same time user Y is waiting for user X to complete the transaction TXN_X and release the lock. But here TXN_X and TXN_Y are waiting for each other to complete the transaction and release the lock. Since both are waiting for each other, they will never release the lock. Hence this waiting will be forever. This situation is called deadlock.
10. What is the difference between deadlock prevention and detection?
In deadlock prevention, each lock request will be inspected to see if it can cause deadlock. If it does not create any deadlocks in the system, then the lock will be granted on the data. That means, by inspecting the lock request, the possibility of deadlock in the system is stopped. But his method requires lots of effort and resource to find the possibility of deadlock.Deadlock detection is one of the popular methods to avoid deadlock in the system. In this method one of the lock requests which has caused deadlock in the system is aborted so that other transaction can complete and release the lock. Now the aborted transaction will start and complete the transaction without any waiting.