SQL Server, use transactions in a smarter way. The save transaction

Hi Guys,

Today we will talk more about transactions...

Oh no, not again! I am hearing some of you are saying  …you have already explained it yesterday!

Yes exactly but today's post is lighter and in just 5 minutes we see how to use transactions in a smarter and more flexible way!

Ready!

 

 

 

 

 

SQL Server, such as Oracle and others RMDBS, allows you to associate a name with a transaction and even allows to you to perform a partial rollback! 

If you think about it, having the ability to open a transaction and at the end of a procedure to commit or rollback alltogheter is a bit limiting.

For example, in case you have to go back to the beginning of the whole procedure, which may be very long.

 

Between Save Transaction and Partial Rollback

SQL Server provides this two commands: 

SAVE TRANSACTION transaction_name
ROLLBACK TRANSACTION transaction_name

 

Where transaction_name is the name of the transaction.

With the SAVE TRANSACTION <tran> you can save a precise point in the transaction.

 

With the ROLLBACK TRANSACTION <tran> you can rollback your data to a precise point you saved.

 

EXAMPLES

 

A) In this first example, i execute a procedure "first_part_of_the_procedure" that update my data.

At this point i save my transaction with the name "first_update_done".

Then i execute another procedure called "Second_part_of_the_procedure". If i get an error i rollback not all the entire transaction but only to the first_update_done point.

When i execute the commit my data have been updated by the first_part_of_the_procedure.

B) Second example 

 

In this case i have a simple table. 

I insert a row and i save the transaction with the name FirstInsert.

Then i insert another row and i save the transaction again but with the name SecondInsert.

Then i insert another row but execute the rollback to the savepoint SecondInsert.

Doing the commit e then a select of my table i have 2 rows!

That's all for today.

I hope you enjoyed the article!
Remeber to share knowledge!!!

Luca Biondi @ SQLServerPerformance blog 2021!

Previus post:SQL Server: Transactions, Lock e Deadlock. A little bit of theory explained in a simple way!