Case Study: Limiting the Number of Joined Customers Using SQL Transaction Isolation Levels

Agenda: In many real-time applications, at times we have to limit the total number of users to a limit. Ex: while booking movie tickets, or gaming events, and so on.  The problem at hand is to limit the total number of users registered in such scenarios. 

We will consider virtual cricket match application registration for the use case. 

Background of the Scenario

Following is a walkthrough of the actions in the picture:

For example, suppose there are only 2 spots for contest A. When the user clicks Join Contest and the number of slots is filled then he should not be able to join.

The task seems to be unchallenging and effortless. Simply apply an if..else.. condition which checks if the maximum limit is exceeding and terminates the transaction before insertion if it exceeds. 

But here comes the catch in this supposititious scenario. There are lakhs of users using the application at the same second. Say a couple of thousand users click on the Join Contest button at the same time. Hence instead of 2 users, 1000 users are able to join the match. Their money from the wallet is also deducted which becomes a huge challenge. This is a serious predicament and a massive technical issue.

Solution: SQL Transaction Isolation Levels 

When multiple database transactions are occurring at the same time, transactions have to be isolated from each other so as to complete the transaction properly. The SQL standard defines four levels of isolation. 

 Now following database anomalies come into the picture:

1. Dirty read: A transaction reads data written by a concurrent uncommitted transaction.
2. Non-Repeatable reads: A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read).
3. Phantom Reads: A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.
4. Serialization anomalies: The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time.

To eliminate these anomalies, we use transaction isolation levels.

Choosing the best isolation level based, have a great impact on the database, each level of isolation comes with a trade-off, let’s discuss each of them:

1. Read Uncommitted

Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by another transaction, thereby allowing dirty reads. At this level, transactions are not isolated from each other.

Syntax: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

2. Read Committed

This isolation level guarantees that any data read is committed at the moment it is read. Thus it does not allow dirty reads. The transaction holds a read or write lock on the current row, and thus prevents other transactions from reading, updating, or deleting it.

Syntax: SET TRANSACTION ISOLATION LEVEL READ COMMITTED

3. Repeatable Reads

This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. Since other transactions cannot read, update or delete these rows, consequently it avoids non-repeatable reads.

Syntax: SET TRANSACTION ISOLATION LEVEL READ REPEATABLE READS

4. Serializable

This is the highest isolation level. A serializable execution is guaranteed to be serializable. Serializable execution is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing.

Syntax: SET TRANSACTION ISOLATION LEVEL READ SERIALIZABLE

The following table describes the various conditions and the applicability of transactions.

Use case: Virtual cricket match application registration
Table ‘[dbo][Players]’

NOTE: Following case shows the syntax of the SQL server. For other database platforms, syntax can be modified.

Consider a table ‘Players’ having the following structure:

Player_id (integer)	Player_name (string)	Contest_name (string)	Max_players (integer)	Amount (string)
10001	Avinash Sharma	Hot Contests	8	Rs.1288
10002	Somya Kumar	Contest for Champions	5	Rs.11008
10003	Irish Singh	Head-to-Head	2	Rs.774
10004	Abhishek Bharadwaj	Hot Contest	8	Rs.414

Database concept: There are Database Hazards That interrupt or corrupt database transactions. Following are the database hazards:

• 1. Dirty read
• 2. Update loss 
• 3. Phantom

These hazards are outbreaks that we tackle using Isolation Levels

SQL Transaction Isolation Levels to tackle the hazards. 

1. SQL Transaction Isolation level 1: READ UNCOMMITTED

Consider two users, Player 1 and Player 2. Following transactions are made by these users:

PLAYER 1 (Session 1)

-> GO
-> BEGIN TRANSACTION
-> SET ISOLATION LEVEL READ UNCOMMITTED
INSERT INTO [dbo].[Players] ([Player_id],[Player_name],
[Contest_name], [Maximum_players],[Amount]) VALUES (10005,
‘Vaibhav’, ‘Hot Contests’,8, 1288)
-> WAIT FOR DELAY 20s
-> ROLLBACK TRANSACTION 

PLAYER 2 (Session 2)

-> GO
-> BEGIN TRANSACTION
-> SET ISOLATION LEVEL READ UNCOMMITTED
-> SELECT * FROM [dbo].[Players] WHERE Player_id = 10005
\1 record returned
-> WAIT FOR DELAY 20s
-> SELECT * FROM [dbo].[Players] WHERE Player_id = 10005
\0 records returned (dirty read)
-> END TRANSACTION

RESULT

Player 1 inserts into the table and waits for the 20s. Meanwhile, Player 2 selects the record just inserted. Player 2 is able to see this uncommitted record. Now after 20s User 1 rollback transaction. When Player 2 selects again no record is fetched. This is a case of Dirty Read.

Hence READ UNCOMMITTED eliminates none of the anomalies and is the lowest stage of Isolation level.

DIRTY READ ———-Not removed
UPDATE LOSS——–Not removed
PHANTOM————–Not removed

2. SQL Transaction Isolation level 2: READ COMMITTED

Consider 2 players Player 1 and Player 2. Following transactions are made:

PLAYER 1 (Session 1)

-> GO
-> BEGIN TRANSACTION
-> SET ISOLATION LEVEL READ COMMITTED
INSERT INTO [dbo].Players VALUES (10005, ‘Vaibhav’, ‘Hot Contests’,8,
‘Rs.1288’)
-> WAIT FOR DELAY 20s
-> ROLLBACK TRANSACTION

PLAYER 2 (Session 2) **Dirty read removed

-> GO
-> BEGIN TRANSACTION
-> SET ISOLATION LEVEL READ COMMITTED
-> SELECT * FROM [dbo].[Players] WHERE Player_id = 10005
\No records are returned as it is not committed.
-> WAIT FOR DELAY 20s
-> SELECT * FROM [dbo].[Players] WHERE Player_id = 10005
\ No records returned as the transaction is rolled back.
-> END TRANSACTION

RESULT

Player 1 inserts into the table and waits for the 20s. Meanwhile, Player 2 selects the record just inserted. Player 2 is not able to see the record as it is uncommitted. Now after 20s Player 1 commits a transaction. When Player 2 selects again no record is fetched as it is rolled back.

Hence READ COMMITTED eliminates the first database hazard: DIRTY READS and is the second stage of Isolation level. 

DIRTY READ ———-Removed
UPDATE LOSS——–Not removed
PHANTOM————–Not removed

3. SQL Transaction Isolation level 3: REPEATABLE READ

Consider 2 Players: Player 1, Player 2. Following transactions are made by the users:

USER 1 (Session 1)

-> GO
-> BEGIN TRANSACTION
-> SET ISOLATION LEVEL REPEATABLE READ
-> UPDATE [dbo].[Players] SET [Amount]=1,20,000 WHERE
[Emp_id]=10005
-> WAIT FOR DELAY 20s
-> COMMIT TRANSACTION
-> END TRANSACTION

USER 2 (Session 2) ** Update loss removed

-> GO
-> BEGIN TRANSACTION
-> SET ISOLATION LEVEL REPEATABLE READ
-> SELECT * FROM [dbo].[Players] WHERE Player_id = 10005
//No records returned as there is a lock on this record.
-> WAIT FOR DELAY 20s
-> SELECT * FROM [dbo].[Players] WHERE Player_id = 10005
//One record is returned as the lock opens after the 20s.
-> END TRANSACTION

RESULT

Player 1 updates one record in the table but has not committed it yet and waits for the 20s. Meanwhile, Player 2 selects the record being updated by User 1. Player 2 is not able to see the record as it is uncommitted. Now after 20s Player 1 commits a transaction. When Player 2 selects the record again and the updated record is returned. Hence Player 2 is able to fetch the record only when the transaction is either ROLLBACK or COMMITTED. 

Hence REPEATABLE READ eliminates the second database hazard: UPDATE LOSS and is the third stage of Isolation level. 

DIRTY READ ———-Removed
UPDATE LOSS——–Removed
PHANTOM————–Not removed

4. SQL Transaction Isolation level 4: SERIALIZABLE

Consider 2 players Player 1, Player 2. Following transactions are made by the users:

USER 1 (Session 1)

-> GO
-> BEGIN TRANSACTION
-> SET ISOLATION LEVEL SERIALIZABLE
-> SELECT * FROM [dbo].[Players] WHERE Player_id = 10006
\10 records are returned and the user makes a report of the number
of records for this Player_id.
-> WAIT FOR DELAY 20s
-> SELECT * FROM [dbo].[Players] WHERE Player_id = 10006
//Fetches 2 more records. Extra records fetched.
-> END TRANSACTION

USER 2 (Session 2) ** Phantom removed

-> GO
-> BEGIN TRANSACTION
-> SET ISOLATION LEVEL SERIALIZABLE
-> INSERT INTO [dbo].[Players] ([Player_id],[Player_name],
[Contest_name],[Maximum_players],[Amount]) VALUES (10005,
‘Vaibhav’, ‘Head to Head’,8, ‘Rs.774’)
\\1 record inserted
-> INSERT INTO [dbo].[Players] ([Player_id],[Player_name],
[Contest_name],[Maximum_players],[Amount]) VALUES (10005,
‘Vaibhav’, ‘Hot Contests,8, ‘Rs.414’)
\\1 record inserted
-> COMMIT TRANSACTION
-> END TRANSACTION

RESULT

Player 1 selects one record in the table with Player_id=10006. 10 records are returned and Player 1 prepares the report on it. Meanwhile, Player 2 is inserting 2 records in the table for Player_id=10006. After a 20-second delay Player 2 commits a transaction. Now player 1 again selects records for Player_id=10006 and 12 records are returned. A mismatch of data occurs and the report is not correct. This is a massive transactional error called Phantom and database reports also become incorrect.

To tackle this hazard, we use a SERIALIZABLE isolation level which applies a lock on records between a specific range of PLAYER_ID says 10005-10010. Now the Player cannot insert a record within this range before the transaction is committed by Player 1.

Hence SERIALIZABLE eliminates the third database hazard: Phantom and is the fourth stage of Isolation level. 

DIRTY READ ———-Removed
UPDATE LOSS——–Removed
PHANTOM————- Removed

Hence SERIALIZATION is the strongest lock as it eliminates all three database hazards.

Hence concluding, understanding the concept of Transactional Isolation Level is crucial to resolve SQL transaction-related glitches and maintaining database integrity and a lot of Database Hazards can be eliminated making the transactions go smoothly.

Thanks For Reading
Read More on Aelum Blogs

Author: Abhikhya Ashi
Designation: Senior Php Developer