sql server concurrent transactions

Text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

In this video we will discuss
1. What a transaction is
2. The problems that might arise when tarnsactions are run concurrently
3. The different transaction isolation levels provided by SQL Server to address concurrency side effects

First let us understand what is a transaction
A transaction is a group of commands that change the data stored in a database. A transaction, is treated as a single unit of work. A transaction ensures that, either all of the commands succeed, or none of them. If one of the commands in the transaction fails, all of the commands fail, and any data that was modified in the database is rolled back. In this way, transactions maintain the integrity of data in a database.

Example : The following transaction ensures that both the UPDATE statements succeed or both of them fail if there is a problem with one UPDATE statement.

-- Transfer $100 from Mark to Mary Account
BEGIN TRY
BEGIN TRANSACTION
UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1
UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2
COMMIT TRANSACTION
PRINT 'Transaction Committed'
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'Transaction Rolled back'
END CATCH

Databases are powerful systems and are potentially used by many users or applications at the same time. Allowing concurrent transactions is essential for performance but may introduce concurrency issues when two or more transactions are working with the same data at the same time.

Some of the common concurrency problems
Dirty Reads
Lost Updates
Nonrepeatable Reads
Phantom Reads

We will discuss what these problems are in detail with examples in our upcomning videos

One way to solve all these concurrency problems is by allowing only one user to execute, only one transaction at any point in time. Imagine what could happen if you have a large database with several users who want to execute several transactions. All the transactions get queued and they may have to wait a long time before they could get a chance to execute their transactions. So you are getting poor performance and the whole purpose of having a powerful database system is defeated if you serialize access this way.

At this point you might be thinking, for best performance let us allow all transactions to execute concurrently. The problem with this approach is that it may cause all sorts of concurrency problems (i.e Dirty Reads, Lost Updates, Nonrepeatable Reads, Phantom Reads) if two or more transactions work with the same data at the same time.

SQL Server provides different transaction isolation levels, to balance concurrency problems and performance depending on our application needs.
Read Uncommitted
Read Committed
Repeatable Read
Snapshot
Serializable

The isolation level that you choose for your transaction, defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Depending on the isolation level you have chosen you get varying degrees of performance and concurrency problems. The table here has the list of isoltaion levels along with concurrency side effects.

Isolation Level Dirty Reads Lost Update Nonrepeatable Reads Phantom Reads
Read Uncommitted Yes Yes Yes Yes
Read Committed No Yes Yes Yes
Repeatable Read No No No Yes
Snapshot No No No No
Serializable No No No No

If you choose the lowest isolation level (i.e Read Uncommitted), it increases the number of concurrent transactions that can be executed at the same time, but the down side is you have all sorts of concurrency issues. On the other hand if you choose the highest isolation level (i.e Serializable), you will have no concurrency side effects, but the downside is that, this will reduce the number of concurrent transactions that can be executed at the same time if those transactions work with same data.

In our upcoming videos we will discuss the concurrency problems in detail with examples

[krzysztofs]

Thank you for teaching us Venkat and 'have a great day' !

[ronicesarrc]

Although I am using Oracle DBMS at university, your videos have helped me a lot with some SQL concepts. Thanks

[archanayadav]

Hi Venkat,
Yours videos are really helpful .I would suggest you to also upload the videos for SSIS which would make us easy to understand.

[pramodmaurya]

Thanks for your knowledge sharing.. thanks a lot.

[raqibul]

We want more advance level video on SQL Server.Thanks in advance.

[mykulpierce]

The transaction block would solve a number of bugs in video games where duplication of items becomes possible due to game or server lag.

[jeewanintube]

We wish you continue the advance level tutorial on sql server.

[harinath]

Hi Venkat, Thank you so much for all these tutorials....Can you please upload Oracle Tutorials if it is possible!! Thanks in advance :)

[harishnayal]

hey venkat, am really enjoying your whole video tutorial, please upload AngularJs tutorial as soon as possible..thankyou

[qodebrojames]

Your videos on sql have been really helpful. I try and hit the like button on every one of the videos that I find helpful. If you don’t mind me asking, what application do you use to record your screen and make these videos?

[joshw]

Hi Venkat, we watch all your .net and sql recordings from sydney. They are Great. Will u be doing full stack recordings as well ?

[prudhvik]

hi venkat,


instead of passing string in id, if we pass int like 7 or 8 which is not in the table, its still updating the correct id and making the transaction to commit. is there any way to rollback when we pass wrong int? instead of str

[MohanrajPeriasamy]

Thanks Venkat...Any plan videos on AngularJS?

[hussainmadar]

when we update a balance, in which the id is integer but not present in the accounts table then it adds the 100$ to id=1 but doest subtract from id= 2

[vickykumawat]

hey venkat please make a video on backup/restore and attach/Deattach process

[foudilbenouci]

Why id='A' is not a compile error ?

[sushilct]

how can i achieve multiple stored procedures in a single transaction sql server.

[sripondy]

I have one question How do we clear Cookies and IE caching in the asp.net application. In which page should we include the  code (like login page or master page or log out page )

[gkmishra]

Difference between IList and List in C#

[mohammadsiraj]

Hi Venkat, Thanks for your tutorial. I have used wrong ID number but the transaction is not rolled back. I think it's big problem. My table doesn't have ID number 20. can you check the issue and let me know. First transaction is updated but second transaction is not updated.


BEGIN TRY
BEGIN TRANSACTION
UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1
UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 20
COMMIT TRANSACTION
PRINT 'Transaction Committed'
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'Transaction Rolled back'
END CATCH


I got below output


(1 row affected)

(0 rows affected)
Transaction Committed

---My Table has only 4 records
insert into Accounts values
(1, 'Mark', 1000),
(2, 'Mary', 1000),
(3, 'John', 2000),
(4, 'David', 2500)