Non repeatable read example in sql server
Text version of the video
http://csharp-video-tutorials.blogspo...
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.
/ @aarvikitchen5572
Slides
http://csharp-video-tutorials.blogspo...
All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...
All SQL Server Slides
http://csharp-video-tutorials.blogspo...
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...
All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic
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
En esta página del sitio puede ver el video en línea Non repeatable read example in sql server de Duración hora minuto segunda en buena calidad , que subió el usuario kudvenkat 17 agosto 2015, comparta el enlace con amigos y conocidos, en youtube este video ya ha sido visto 105,924 veces y le gustó 649 a los espectadores. Disfruta viendo!
