ServiceNow
Workflows

IT Workflows
Employee Workflows
Customer Workflows
Creator Workflows

IT Workflows
We offer workflows that can transform IT into a fully-fledged growth engine. Take advantage of the benefits of operational flexibility by increasing workforce adaptability...
Read More

Employee Workflows
Make employee experiences more integrated and engaging with ServiceNow Employee Workflows. Motivate your staff by making it simple for them to obtain what they require when they require it...
Read More

Customer Workflows
We connect clients, front-line agents, middle and back-office staff on a single ServiceNow platform with ServiceNow Customer Workflows using digital processes to scale customer operations...
Read More

Creator Workflows
With a low-code platform, you can quickly create digital workflow apps. Scale quickly across the organization to build natural, linked experiences that users enjoy...
Read More

Solutions
ServiceNow Solutions

IT Service Management
IT Operations Management
IT Asset Management
Governance, Risk, and Compliance
Security Operations

HR Service Delivery
Customer Service Management
Field Service Management
Integration Hub

IT Service Management
IT service management includes all the ServiceNow services and processes that ensure end to end...
Read More

IT Operations Management
ServiceNow ITOM consists of a set of modules in ServiceNow to manage the operations...
Read More

IT Asset Management
It is the set of business practices to manage the lifecycle of various assets in IT like software assets...
Read More

Governance, Risk, and Compliance
Almost every aspect of the business is driven by governance, risk, and compliance...
Read More

Security Operation
To minimize risk, ServiceNow SecOps allows you to immediately identify, prioritize, and respond to threats...
Read More

HR Service Delivery
With ServiceNow HRSD, you can increase company productivity and provide your employees...
Read More

Customer Service Management
Proactively solve problems and take actions to rectify common requests more efficiently with ServiceNow...
Read More

Field Service Management
ServiceNow Field Service Management enables you to efficiently and safely manage field service activity...
Read More

Integration Hub
For the fastest time to value, lowest TCO, and ease of use with zero learning curve for all ServiceNow developers...
Read More

Digital Transformation
Digital Transformation

DevOps
Robotic process automation
User Experience Design

DevOps
Devops includes the set of practices that automates the IT operations and accelerate the delivery...
Read More

Robotic process automationt
We help organizations to integrate different techniques like machine learning and artificial...
Read More

User Experience Design
User experience design is the creation of the effective flow between users and software...
Read More

Product Engineering
Product Engineering

IT Consulting
New Application development
Product testing & QA
Re-engineering & Migration
Support & maintenance

IT Consulting
We help Companies to Better their IT infrastructure and to Implement various IT strategies. Alignment of all the services to boost the overall performance...
Read More

New Application development
We are an IT service provider company using high-value business processes and technical stacks to build digital products...
Read More

Product testing & QA
We help clients to deliver quality products and services. Skilled Quality Assurance Team to perform a series...
Read More

Re-engineering & Migration
Re-Engineering and Migration is the concept of Comparing the Existing business model to the desired model...
Read More

Support & maintenance
We provide a range of support and maintenance services to our clients in the form of version upgrades, post-project support...
Read More

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

  • By Aelum consulting
  • June 3, 2021
  • 954 Views

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:

  1. Users register themselves.
  2. Users see a list of different cricket matches that will start within an hour timeframe. The timer is running in the background.
  3. The User clicks on a cricket match from the list. Now the user has to create a virtual team of cricket players to play the match. 
  4. The User clicks on a cricket match from the list. Now the user has to create a virtual team of cricket players to play the match. 
  5. As soon as the team is created a list of contests appears. Users now have to join any contest. Some amount will be deducted from the wallet depending on the contest the user is joining.
  6. Now there is a limit of users who can join a particular contest.

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.

學習筆記] SQL 大小事Isolation Level 與SARGs | Marsen's Blog

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

1 thought on “Case Study: Limiting the Number of Joined Customers Using SQL Transaction Isolation Levels”