SQL Server tutorial for beginners - Non repeatable read example in sql server

Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/non-repeatable-read-example-in-sql.html 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. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Slides http://csharp-video-tutorials.blogspot.com/2015/08/non-repeatable-read-example-in-sql_17.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss non repeatable read concurrency problem with an example. Non repeatable read problem happens when one transaction reads the same data twice and another transaction updates that data in between the first and second read of transaction one. Transaction 1 starts first. Reads ItemsInStock. Gets a value of 10 for first read. Transaction 1 is doing some work and at this point Transaction 2 starts and UpdatesItemsInStock to 5. Transaction 1 then makes a second read. At this point Transaction 1 gets a value of 5, reulting in non-repeatable read problem. Non-repeatable read example : Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window, execute Transaction 2 code. Notice that when Transaction 1 completes, it gets different values for read 1 and read 2, resulting in non-repeatable read. -- Transaction 1 Begin Transaction Select ItemsInStock from tblInventory where Id = 1 -- Do Some work waitfor delay '00:00:10' Select ItemsInStock from tblInventory where Id = 1 Commit Transaction -- Transaction 2 Update tblInventory set ItemsInStock = 5 where Id = 1 Repeatable read or any other higher isolation level should solve the non-repeatable read problem. Fixing non repeatable read concurrency problem : To fix the non-repeatable read problem, set transaction isolation level of Transaction 1 to repeatable read. This will ensure that the data that Transaction 1 has read, will be prevented from being updated or deleted elsewhere. This solves the non-repeatable read problem. When you execute Transaction 1 and 2 from 2 different instances of SQL Server management studio, Transaction 2 is blocked until Transaction 1 completes and at the end of Transaction 1, both the reads get the same value for ItemsInStock. -- Transaction 1 Set transaction isolation level repeatable read Begin Transaction Select ItemsInStock from tblInventory where Id = 1 -- Do Some work waitfor delay '00:00:10' Select ItemsInStock from tblInventory where Id = 1 Commit Transaction -- Transaction 2 Update tblInventory set ItemsInStock = 5 where Id = 1