Concurrent update problem in a database can be defined as the occur when multiple sessions are allowed to update their data present in a single database. Concurrent update problem may lead to data integrity loss and data corruption.
- Read: More Interview Questions
Example for Concurrent Update Problem
Imagine that we have an application which manages the database of a store. The store will have several retail salesman and products. In the normal process, the number of product in stock will be reduced by one as soon as the product is billed by the customer. This scenario won’t reveal any problem in our application but the scenario is different when two salesmen are selling the same object at the same time. The concurrent update problem in our store will follow the sequence given below,
- The salesman will send a query to the database about the stock availability in the store. Let us consider there are 10 products in the store and 10 will be returned to the salesman as a query result.
- After few seconds later, let us consider that a second salesman who requests the same query from database. He will also receive the same result.
- After selling the product to the customer; the first salesman will update the stock on the product as nine similar update will be done by the second salesman after selling the product to second customer.
- Now the database will list the stock remaining as nine while only 8 products are available in the store. This process is called as concurrent update problem.
How to Prevent Concurrent Update Problem?
Concurrent update problem can be prevented by restricting the database access to a single user. However this method is not feasible for vast databases which require regular and immediate updates. The database locking is the only feasible method to prevent concurrent update problem.
- Do you Know: SQL Clustered & Non-clustered Index
What is Database Locking?
Database locking is the process of restricting the access to certain data stored in the database until particular user/update session is complete. Database locking is used to prevent data loss and concurrent update problem. The period during which a user waits to gain access to a database is called as lock/wait period. There are several types of database locking which are classified according to the restrictions placed on the database.
Types of Database Locking
- Database Level Locking
- File Level Locking
- Table Level Locking
- Page or Block Level Locking
- Column Level Locking
- Row Level Locking
Database Level Locking
This locking process is used to restrict the access to the entire database. Database level locking allow access to only one user/session. This locking is not commonly used unless a restriction is needed for entire database.
File Level Locking
Using this locking method, files present in a database can be locked out from access. This locking method is not used unless an update is required for the whole file.
Table Level Locking
Table level locking is the most commonly used database locking method. This locking process is used to restrict the access to a particular table. Rows and columns present in a table are altered or updated using table level locking.
Page or Block level locking is used when updates are required for page or block present in a database. Row and column level locking are used to update certain rows and columns present in a database. If row and column locking is applied to all rows and columns present in a table then it will be promoted to table level locking.
When two users try to lock a database at the same time, lock contention problem arises. Lock contention problem can be eradicated by giving priority to the user who requested the lock first.