What is the Concurrent Update Problem in databases? Provide an example as well.
The concurrent update problem is something that can happen when multiple database sessions are permitted to update the same data at the same time – which is why it’s called the concurrent update problem. Whenever a database user connects to the database, a new session is created. Even if the same user connects to the database several times, a separate session is created each time the user connects to the database.
Example of the concurrent update problem
Let’s use an example of a credit card company that issues credit cards to customers and, of course, sends the customers bills and collects payments for the credit card statements.
Let’s say that there is a user X, who works in the accounts receivable department of the credit card company. And, let’s also say that there is a customer B who uses the credit card issued by that company, and he has an unpaid balance of $2,200. Customer B has sent in a payment of $800 for his credit card balance for the month of March, and user X has to process the payment by updating customer B’s total balance due to subtract $800.
But, at that same exact moment, user Y, who works in the billing department is about to update customer B’s balance, because the month of April has just passed and customer B has spent $500 just in the month of April. This means that user Y needs to add $500 to the unpaid balance for customer B.
Here is the sequence of events that take place which lead to the concurrency update problem:
- 1. User X sends a query to the database to ask for customer B’s balance, and he gets back $2,200.
- 2. Assume that two seconds later User Y does the same thing that User X just did, and he also gets back a balance of $2,200.
- 3. Now, in a couple more seconds, User X updates customer B’s balance by subtracting $800. So, customer B’s new balance is now $2,200 – $800, which is $1,400, and this balance is what’s set in the database by User X.
- 4. And, lastly, user Y makes his update to the balance he retrieved for customer B (which was also $2,200), and user Y just adds $500 to that balance – so it’s $2,200 + $500 = $2,700. The key here is that user Y is not aware of the update made by User X. So, User Y mistakenly sets the balance in the database to $2,700 for customer B.
So, from that sequence of events, the balance in the database for customer B is now $2,700. But, the balance should actually be $2,200 – $800 + $500 = $1,900. The real problem here is that the update made by User X has been overwritten by User Y.
How to prevent the concurrent update problem?
This problem could have been avoided with by applying locks, which you can read more about here: Database locking.