Wait Query in Sql or Dirty Read testing

A Dirty read takes no notice of any lock taken by another process. The read is officially “dirty” when it reads data that is uncommitted. This can become problematic if the uncommitted transaction fails or for some other reason is rolled back.

 

Imagine a scenario in which you are shopping on a website and place an item into your basket and proceed to payment. The site's checkout process decrements the stock by one and starts to charge your card all in the one transaction. At that time, a second unrelated process starts. The website's back office stock interface runs and makes a dirty read of all the product inventory levels, reading the reduced value. Unfortunately, there is a problem with your transaction (insufficient funds), and your purchase transaction is rolled back. The website stock level has now reverted to the original level, but the stock interface has just reported a different value.

 

Alter Proc Sp_Dirty
AS
Begin
    /*
    Create table Person(Id int Identity(1,1),FirstName varchar(50),LastName varchar(50))
    Insert into Person values('Murli  ','D');Insert into Person values('Deepak  ','D');Insert into Person values('Jamil  ','A')
    */   
    BEGIN TRANSACTION;   
    UPDATE Person     SET FirstName = 'Sujatha'    WHERE LastName = 'D';
    Select * from Person
    WAITFOR DELAY '00:00:05.000';
    ROLLBACK TRANSACTION;
    --Commit
    SELECT FirstName    ,LastName    FROM Person    WHERE LastName = 'D';
End

--=========For Calling =========

Select * from Person
Go
Exec Sp_Dirty