T SQL Advanced Tutorial By Sagar Jaybhay 2020

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

1. Dirty reads
2. Lost update
3. Nonrepetable reads
4. Phantom reads

SQL server transaction Isolation level

1. Read Uncommitted
2. Read committed
3. Repeatable read
4. Snapshot
5. 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 @amt=@amt-1000
update account set amount=@amt 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 @amt=@amt-2000
update account set amount=@amt 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

1. snapshot isolation never blocks the transaction.
2. It will display that data which is before another transaction processing
3. It means that snapshot isolation never locks resources and other transaction able read the data
4. But here one transaction is updating the data another is reading that data so it’s ok
5. 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.
6. 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.

My Other Site: https://sagarjaybhay.net

Sagar Jaybhay

Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.