I read following lines of code on msdn but I am not clear with this and want to simulate.
Nonrepeatable reads occur when a second transaction accesses the same row several times and reads different data every time. This involves multiple reads of the same row. Every time, the information is changed by another transaction.
I didn't understand how second transaction can access the data if row is locked by first transaction under read committed isolation level.
You can simulate it in this way. Open Microsoft SQL Management Studio. Open 2 tabs and start a transaction in both.
Select data in first window and modify it in second and commit changes.
Then, re run previous select. Data has been changed. This is non repeatable read phenomen.
You can simulate non-repeatable read with SQL Server and 2 command prompts.
First, set READ COMMITTED isolation level to occur non-repeatable read:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Then, create person table with id and name as shown below.
person table:| id | name |
|---|---|
| 1 | John |
| 2 | David |
Now, take these steps below with MSSQL queries:
| Flow | Transaction 1 (T1) | Transaction 2 (T2) | Explanation |
|---|---|---|---|
| Step 1 | BEGIN TRAN;GO; |
T1 starts. | |
| Step 2 | BEGIN TRAN;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 | COMMIT;GO; |
T2 commits. | |
| Step 6 | SELECT * FROM person WHERE id = 2;GO;2 Tom |
T1 reads Tom instead of David after T2 commits.*Non-repeatable read occurs!! |
|
| Step 7 | COMMIT;GO; |
T1 commits. |
Nonrepeatable reads occur when a second transaction accesses the same row several times and reads different data every time. This involves multiple reads of the same row. Every time, the information is changed by another transaction.
The point here is:
transaction A reads the data using SELECT ..... and takes a shared (read) lock on those rows it reads - but just while it reads them (and releases the lock right away after first reading them
transaction B can now modify some of those rows
when transaction A comes back and read those rows again, using SELECT ... - it might get different results this time around.
This is the problem that you have when using the default setting - READ COMMITTED - a read operation takes a shared lock only just while reading and releases it right away after it's read the data. After that, other transaction can (and will!) modify the data, so reading the same rows again might yield a different result.
With REPEATABLE READ, the reading transaction keeps a shared (read) lock on those rows it reads until the end of the transaction - thus preventing other transactions from modifying the data before it ends.
So really - any read operation using the default READ COMMITTED isolation level is a non-repeatable read. No need to simulate - those are the real things!
Open 2 query windows in SSMS
First query:
set transaction isolation level read committed
begin transaction
select * from _tmp
Second
update _tmp set id = 2
Create and populate table
create table _tmp ( id int)
insert _tmp (id )values(1)
Run first query, then run second then run select statement from first query
You'll see 1 first time and 2 second
how second transaction can access the data if row is locked by first transaction
You are wrong in your assumption that the first transaction would lock the row. It will only lock it for a short time, just enough to read it, then release the lock. Subsequent reads will lock it again, so anything can happen to the row in the meantime.
Consider this time sequence and see how transaction T1, under read committed isolation, sees different results for the same SELECT:
T1: begin transaction
T1: select * from table <-- returns row 1 and row 2
T2: begin; insert into table row 3; commit;
T1: select * from table <-- returns row 1, row 2 and row 3
T3: begin; delete row 2 from table ; commit;
T1: select * from table <-- returns row and row3
