CODEWITHSUNDEEP
sqldatabaset-sqltransactionsdirtyread
ARCY_MAT
ARCY_MAT

Reputation: 11

How to make Dirty Read?

Will this be an example of Dirty Read?

T1 & T2 transactions

SET IMPLICIT_TRANSACTIONS OFF

Dirty read example

Upvotes: 1

Views: 6592

Answers (4)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

  • 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.

Upvotes: 0

Jerry An
Jerry An

Reputation: 1442

dirty read enter image description here

  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

Upvotes: 1

Shahar Shokrani
Shahar Shokrani

Reputation: 8762

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!

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

     BEGIN TRAN

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

  2. 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!

  1. 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.

Upvotes: 2

Jesse
Jesse

Reputation: 873

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.

Upvotes: 1

Related Questions