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.
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.
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:
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.
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
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.
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.
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.
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.
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.