What is a Transaction?

The standard definition of Transaction state that “Every Query batch that runs in a SQL server is a Transaction.”, this means any query you run on a SQL server will be considered as a Transaction it could either be a simple SELECT query or any UPDATE or ALTER query.

If you run a query without mentioning the BEGIN TRAN keyword then it would be considered as an Implicit transition.

If you run a query which starts with BEGIN TRAN and ends with COMMIT or ROLLBACK then it would be considered as Explicit Transaction.

Transactional Properties

A Database Management System would be considered a Relational Database Management System if it follows the transactional properties (A.C.I.D).

A: Atomicity
C: Consistency
I: Isolation
D: Durability

1. Atomicity

A transaction work should be atomic in nature, which means if the user performs a transition, either the transaction should complete and perform all the asked operations or it should fail and don’t do anything. Atomicity deals with the transaction process and a Relational Database Management System transaction does not leave the transaction process in between.

2. Consistency

After the transaction is completed the database should not be left in an inconsistent state which means, the data on which transaction is applied must be logically correct and should lead to an error.

3. Isolation

If two transactions are applied on a similar database then both the transaction should be isolated from each other, and the user must see the end result. It can also be defined as a transaction that should see the data only after or before the concurrent transaction process is completed, which means if a one transaction process is in between the other transaction process should wait until the first transaction is completed.

For instance, if A performs a transaction process on data d1,and before the transaction process gets completed Balso performs another transaction process on the same data d1.Here,the isolation property will isolate the transaction process of Aand B,and the transaction process of Bwill only start after the transaction process of Agets completed.

4. Durability

Even if the system fails the transaction should be persistent, which means, if the system fails during a transaction process, the transaction should be dropped too without affecting the data.

The SQL Server takes care of the Atomicity, Consistency, and Durability of the system, and the user has to care of the Isolation property of the transaction.

What is Concurrency in SQL Server?

Concurrency is a situation that arises in a database due to the transaction process. Concurrency occurs when two or more than two users are trying to access the same data or information. DBMS concurrency considered a problem because accessing data simultaneously by two different users can lead to inconsistent results or invalid behavior.

Concurrency Problem Types

The concurrency problem mostly arises when both the users try to write on the data or when one is writing and the other is reading. Apart from this logic, there are some common types of concurrency problems:

Dirty Reads
Lost Updates
Non-repeatable Reads
Phantom Reads

1. Dirty Read

This problem occurs when another process reads the uncommitted data, for instance, if one process has changed data but not committed it yet, another process tries to read the same data, and this led to the inconsistent state. ****

2. Lost Updates

This problem occurs when two processes try to manipulate the same data simultaneously. This problem can lead to data loss or the second process might overwrite the first process manipulation.

3. Non-repeatable Reads

This problem occurs when one process is reading the data and another process is writing the data. In non-repeatable reads, the reading value might get the change because another process writes different data.

4. Phantom Reads

If two same queries executed by two users show different output then it would be a Phantom Read problem. For instance, If user A select a query to read some data, at the same time the user B insert some new data but the user A only get able to read the old data at the first attempt, but when User A re-query the same statement then he/she gets a different set of data.

Solve Concurrency problem

To overcome these Concurrency problems, SQL server provides 5 different levels of transaction Isolation, and these 5 Isolation Level work on two major Concurrency models:

Concurrency models

1. Pessimistic Model

In the Pessimistic model of managing concurrent data access, the readers can block writers and the writers can block readers.

2. Optimistic Mode

In the Optimistic model of managing concurrent data access, the readers cannot block writers and the writers cannot block readers, but the writer can block the writer.

Note readers:users performing the SELECT operations.

Note writer:users performing INSERT, ALTER, UPDATE, SET, etc. operations.

Isolation Level

When we link our main application with the SQL server database then the application link with the database with 5 different Isolation levels and these levels are:

Read Uncommitted
Read Committed
Repeatable Read
Serializable
Snapshot

Out of these 5 Isolation levels Read Uncommitted, Read Committed, Repeatable Read, and Serializable comes under pessimistic concurrency and snapshot comes under Optimistic concurrency.

1. Read Uncommitted

It is the first level of Isolation, and it comes under the pessimistic Model of concurrency. In Read Uncommitted, one transaction is allowed to read the data which is about to change by the commit of another process. Read Uncommitted allows the dirty read problem.

2. Read Committed

It is the second level of Isolation and falls under the pessimistic model of concurrency. In the Read Committed isolation level, we are only allowed to read data that is committed, which means it eliminates the dirty read problem. In this level, if you are reading data then the concurrent transactions which can delete or write data, have to hold until you finish your reading. ****

3. Repeatable Read

The Repeatable Read Isolation level is similar to the Read Committed Level, and it eliminates the Non-Repeatable Read Problem. In this level, the transaction has to wait till another transaction Update query, as well as Read Query, is executed. But here if the Insert transaction does not wait for anyone, and this leads to the Phantom read Problem.

4. Serializable

It is the highest-level Isolation of the pessimistic model, and by implementing this level of Isolation we can prevent the Phantom Read problem. In this level of Isolation, we can ask any transaction to wait until the current other transaction gets completed or executed.

5. Snapshot

Snapshot follows the optimistic model of concurrency, and this level of isolation takes a snapshot of the current data and uses it as a copy for the different transactions. Here each transaction has its own copy of data, so if a user tries to perform a transaction like an update, or insert, it asks him to re-verify all the operation before the process gets started executing.

Conclusion

Concurrency occurs when two transactions occur currently on the same set of data, and currency always leads to the data inconsistency and abnormal behavior of the transaction. RDBMS transactions have 4 properties which are known as ACID. There are four major common concurrency problems which can be solved by using two models of concurrency: pessimistic and optimistic. We can bind our application with SQL Server by 5 types of Isolation levels.

SQL Server & Concurrency Control