Concurrent Access in DBMS – problem and solution

Last updated on December 12th, 2022 at 10:39 am

In a client-server database system, it is possible for two clients to attempt to access, or alter, the data in a database at the same time. This is referred to as concurrent access in DBMS (DataBase Management System). Let’s see how concurrent access can be a potential problem and what are the methods to preserve the integrity of the data.

Why is concurrent access a potential problem? | why concurrency control is needed in DBMS?

Now the questions are: Why is concurrent access a potential problem? And, why concurrency control is needed in DBMS? To understand this let’s take an example.

If two or more clients attempt to alter data concurrently then it is possible that the integrity of the database will be compromised.
For example, a bank balance is currently £10000.
– A payment of £100 is made into the account and so the balance is checked. £100 is added to the figure and the new balance of £10100 is written to the account. The ‘write’ process was slightly slow, however.
– At the same time, another transaction took place in which the account holder spent £8000. This processing was slightly quicker and the balance
was reduced to £2000; however, the first transaction had not yet been completed and, when it did, the balance was overwritten as £10100.

Methods to preserve the integrity of the data

There are several possible methods to preserve the integrity of the data during concurrent access in DBMS.

Record Locks

Record locks: When a record is initially accessed it is locked and cannot be accessed again until the lock has been removed. In the case above the record for that customer would be locked by the first transaction until it was complete. Only then could the second transaction be processed.

Serialisation

Serialisation: The database system manages the transactions so that only one can be carried out at a time. As both transactions were received at the same time the second transaction would be held in a queue momentarily until the first transaction had been completed.

Timestamp ordering

Timestamp ordering: Each time access to the database is made, the timestamp is recorded. In the above example, the second transaction would change the timestamp at which the balance was last changed.

When the first transaction attempts to complete it would see that the timestamp had changed and so would start again.

Commitment ordering

Commitment ordering: Transactions are arranged in an order that avoids potential conflicts, using an algorithm within the database system.

Reference: AQA A-level computer science