How to make Dirty Read?

Question

Will this be an example of Dirty Read?

T1 & T2 transactions

SET IMPLICIT_TRANSACTIONS OFF

Dirty read example

Answer 1

Here is an example how to construct a "dirty read" by looking at the MyCleanTable

Id | Value
----------
1  | I am clean!
2  | I am clean!

---

1. Execute a simple query transaction_a with uncommitted isolation level

   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   
   BEGIN TRAN
   
       SELECT * FROM [MyCleanTable]
   

The OUTPUT:

Id | Value
----------
1  | I am clean!
2  | I am clean!

2. Update the table with a dirty value with transaction_b:

    BEGIN TRAN
   
        UPDATE [ConcurrencyDemo].[dbo].[MyCleanTable]
        SET [Value] = 'I am dirty'  
        WHERE [Id] = 1 
   

3. Execute another query within transaction_a and commit it:

       SELECT * FROM [ConcurrencyDemo].[dbo].[MyCleanTable]
   
   COMMIT;
   

The OUTPUT:

Id | Value
----------
1  | I am dirty! ------> "dirty read"
2  | I am clean!

4. Rollback the update of transaction_b:

   ROLLBACK;
   

At this stage the table was rolled back into its original values:

Id | Value
----------
1  | I am clean!
2  | I am clean!

But the query of section 3 was reading a dirty value.

Answer 2

1. Transaction One starts
2. Transaction Two starts
3. Transaction One do an update operation
4. Transaction Two do a select operation and see the dirty data generated by Transaction One
5. Transaction One commit or rollback

In order to realize dirty read, we should execute the following command first

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Answer 3

That is not a dirty read. The details of what that does are here: implicit transactions

To get a dirty read, you need to put select column1 from dbo.table1 WITH (NOLOCK). The With (NOLOCK) reads uncommitted data which might disappear and never really exist if the transaction is rolled back in some way.

The other way to get a dirty read is to use: isolation level read uncommitted.

Answer 4

• Dirty read is that a transction reads the uncommitted data which other transactions insert, update or delete.

I experimented dirty read with MSSQL(SQL Server) and 2 command prompts.

First, I set READ UNCOMMITTED isolation level to occur dirty read:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Then, I created person table with id and name as shown below.

person table:

id	name
1	John
2	David

Now, I did these steps below with MSSQL queries:

Flow	Transaction 1 (T1)	Transaction 2 (T2)	Explanation
Step 1	BEGIN; GO;		T1 starts.
Step 2		BEGIN; GO;	T2 starts.
Step 3	SELECT * FROM person WHERE id = 2; GO; 2 David		T1 reads David.
Step 4		UPDATE person SET name = 'Tom' WHERE id = 2; GO;	T2 updates David to Tom.
Step 5	SELECT * FROM person WHERE id = 2; GO; 2 Tom		T1 reads Tom instead of David before T2 commits. *Dirty read occurs!!
Step 6		COMMIT; GO;	T2 commits.
Step 7	COMMIT; GO;		T1 commits.