In this article we will understand T SQL Advanced Tutorial means Transaction In SQL and Common Concurrency Problem and SQL server transaction Isolation level by Sagar Jaybhay
What is the Transaction?
A transaction is a group of commands that changed the data stored in a database. A transaction is treated as a single unit.
The transaction ensures that either all commands will succeed or none of them. Means anyone fails then all commands are rolled back and data that might change is reverted back to the original state. A transaction maintains the integrity of data in a database.
begin try begin transaction update dbo.account set amount = amount-100 where id=1 update dbo.account set amount=amount+100 where id=2 commit transaction print 'transaction committed' end try begin catch rollback transaction print 'transaction rolled-back' end catch

In the above example either both statements executed or none of them because it goes in catch block where we rolled-back transactions.
begin try begin transaction update dbo.account set amount = amount-100 where id=1 update dbo.account set amount=amount+100 where id='A' commit transaction print 'transaction commited' end try begin catch rollback transaction print 'tranaction rolledback' end catch

Common Concurrency Problem
- Dirty reads
- Lost update
- Nonrepetable reads
- Phantom reads
SQL server transaction Isolation level
- Read Uncommitted
- Read committed
- Repeatable read
- Snapshot
- Serializable
How to overcome the concurrency issues?
One way to overcome this issue is to allow only one user at the time allowed for the transaction.
Dirty Read Concurrency Problem:
A dirty read happens when one transaction permitted to read data that modified by another transaction but that yet not committed. Most of the time it will not cause any problem because if any case transaction fails then the first transaction rolled back its data and the second transaction not have dirty data that also not exist anymore.
To do 2 transactions on one machine open 2 query editor that is your 2 transaction machine and you do an operation like below

For the first transaction, we update the amount in the account table and then given a delay for 1 min 30 seconds and after this, we rollback the transaction. And in the second window, we select data from a table where we can see uncommitted data and after transaction rollback, we see committed data.
We have default isolation level read committed to set different for reading uncommitted data you can use below command.
set transaction isolation level read uncommitted; -- the First transaction begin transaction update account set amount=amount+1000000 where id=1; waitfor delay '00:01:30' rollback transaction -- Second Transaction set transaction isolation level read uncommitted; select * from account;

Lost Update
It means that 2 transactions read and update the same data. When one transaction silently overrides the data of another transaction modified this is called a lost update.
Both read committed and read uncommitted have lost update side effects.
Repeatable reads, snapshots, and serialization do not have these side effects.
Repeatable read has an additional locking mechanism that Is applied on a row that read by current transactions and prevents them from updated or deleted from another transaction.
-- first transaction begin transaction declare @amt float select @amt=amount from account where id =1; waitfor delay '00:01:20' set @[email protected] update account set [email protected] where id=1; print @amt commit transaction -- first tarnsaction -- second transaction begin transaction declare @amt float select @amt=amount from account where id =1; waitfor delay '00:00:20' set @[email protected] update account set [email protected] where id=1; print @amt commit transaction

Non-Repeatable read
It was when the first transaction reads the data twice and the second transaction updates the data in between the first and second transactions.
Phantom read
It happens when one transaction executes a query twice and it gets a different number of rows in the result set each time. This happens when a second transaction inserts a new record that matches where the clause of executed by the first query.
To fix phantom read problem we can use serializable and snapshot isolation levels. When we use the serializable isolation level it would apply the range lock. Means whatever range you have given in first transaction lock is applied to that range by doing so second transaction not able to insert data between this range.
Snapshot isolation level
Like a serializable isolation level snapshot also does not have any concurrency side effects.
What is the difference between serializable and Snapshot isolation level?
Serialization isolation level acquires it means during the transaction resources in our case tables acquires a lock for that current transaction. So acquiring the lock it reduces concurrency reduction.
Snapshot doesn’t acquire a lock it maintains versioning in TempDB. Since snapshot does not acquire lock resources it significantly increases the number of concurrent transactions while providing the same level of data consistency as serializable isolation does.
See below the image in that we use a serializable isolation level that acquires a lock so that we are able to see the execution of a query in progress.

Now in the below example, we set a database for allowing snapshot isolation. For that, we need to execute the below command.
alter database temp set allow_snapshot_isolation on
Doing so our database tempdb is allowed for snapshot transaction than on one window we use serialization isolation level and on the second we use snapshot isolation level. When we run both transactions we are able to see the snapshot isolation level transaction completed while serialization is in progress and after completing both transactions we see one window has updated data and others will have previous data. First

Now after completing both transactions

- snapshot isolation never blocks the transaction.
- It will display that data which is before another transaction processing
- It means that snapshot isolation never locks resources and other transaction able read the data
- But here one transaction is updating the data another is reading that data so it’s ok
- When both transactions updating same data then transaction blocks and this blocks until the first transaction complete and then transaction 2 throws error lost update why because preventing overwriting the data and it fails and error is transaction is aborted you can’t use snapshot isolation level update, delete insert that had been deleted or modified by another transaction.
- If you want to complete the second transaction you need to rerun that transaction and data is modified successfully.
Read Committed Snapshot Isolation Level
It is not a different isolation level. It is an only different way of implementing Read committed isolation level. one problem in that if anyone transaction is updating the record while reading the same data by another transaction is blocked.
Difference between Snapshot isolation level and Read Committed Snapshot isolation level.
Snapshot Isolation | Read Committed Snapshot isolation level |
It is vulnerable to update conflicts | No update conflicts here |
Can not use with a distributed transaction | It can work with a distributed transaction |
Provides transaction-level read consistency | It provides statement-level read consistency |
My Other Site: https://sagarjaybhay.net