SQL Server tutorial for beginners - Phantom reads example in sql server

Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/phantom-reads-example-in-sql-server.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/phantom-reads-example-in-sql-server_18.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 phantom read concurrency problem with examples. Phantom read 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 row that matches the WHERE clause of the query executed by the first transaction. We will use the following table tblEmployees in this demo Scrip to create the table tblEmployees Create table tblEmployees ( Id int primary key, Name nvarchar(50) ) Go Insert into tblEmployees values(1,'Mark') Insert into tblEmployees values(3, 'Sara') Insert into tblEmployees values(100, 'Mary') Transaction 1 starts first. Reads from Emp table where Id between 1 and 3. 2 rows retrieved for first read. Transaction 1 is doing some work and at this point Transaction 2 starts and inserts a new employee with Id = 2. Transaction 1 then makes a second read. 3 rows retrieved for second read, resulting in phantom read problem. Phantom 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 number of rows for read 1 and read 2, resulting in phantom read. -- Transaction 1 Begin Transaction Select * from tblEmployees where Id between 1 and 3 -- Do Some work waitfor delay '00:00:10' Select * from tblEmployees where Id between 1 and 3 Commit Transaction -- Transaction 2 Insert into tblEmployees values(2, 'Marcus') Serializable or any other higher isolation level should solve the phantom read problem. Fixing phantom read concurrency problem : To fix the phantom read problem, set transaction isolation level of Transaction 1 to serializable. This will place a range lock on the rows between 1 and 3, which prevents any other transaction from inserting new rows with in that range. This solves the phantom 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 number of rows. -- Transaction 1 Set transaction isolation level serializable Begin Transaction Select * from tblEmployees where Id between 1 and 3 -- Do Some work waitfor delay '00:00:10' Select * from tblEmployees where Id between 1 and 3 Commit Transaction -- Transaction 2 Insert into tblEmployees values(2, 'Marcus') Difference between repeatable read and serializable Repeatable read prevents only non-repeatable read. Repeatable read isolation level ensures that the data that one transaction has read, will be prevented from being updated or deleted by any other transaction, but it doe not prevent new rows from being inserted by other transactions resulting in phantom read concurrency problem. Serializable prevents both non-repeatable read and phantom read problems. Serializable isolation level ensures that the data that one transaction has read, will be prevented from being updated or deleted by any other transaction. It also prevents new rows from being inserted by other transactions, so this isolation level prevents both non-repeatable read and phantom read problems.