Serializability, locking of concurrent transactions in SQL: advantages, disadvantage

Serializability in concurrent transactions: To eliminate database corruption, SQL uses a concept known as serializability. According to serializability, a user must see a consistent view of a database during a transaction. A user must not see uncommitted database changes of other users. Also, a user must not see the committed changes made by other users in the middle of a transaction. For a concurrent transaction, a user can access the database in such a way, as if there’s no one else accessing the database. The database management system makes it look like that every transaction is executed one after the other. To use this concept effectively smaller transactions should be created or larger transaction should be broken to smaller transactions.

Locking in concurrent transactions: To solve problems in concurrent transactions, the concept of locking is used. In this concept, a transaction is locked for a particular user and only this user can modify the database. Other transactions have to wait until this transaction is finished.

The below given figure shows a typical locking scheme for two transactions: Transaction 1 and Transaction 2. When Transaction 1 is accessing the TABLE 1, it is locked for TABLE 2 until Transaction 1 has executed the COMMIT statement. Similarly When Transaction 2 is accessing the TABLE 2; it is locked for TABLE 1 until Transaction 1 has executed the COMMIT statement. When Transaction 1 runs COMMIT statement, TABLE 1 is unlocked and then it is locked for TABLE 2. Finally, when TABLE 2 executes the COMMIT statement, both the tables are unlocked.

Locking of concurrent transactions in SQL

Advantages of locking:

1) Lost update or uncommitted data problem is solved.

2) Dirty read problem is solved.

3) Inconsistent data or non repeatable read problem is solved.

4) Phantom insert problem is solved.

Disadvantage of locking: When a transaction has locked a database, other transaction may have to wait for a long time.

Leave a Reply