1

I am using MySQL 8 in my windows machine. trying to see READ COMMITTED isolation level .

innodb_lock_wait_timeout = 5;
innodb_rollback_on_timeout =1;


T1:  start transaction;
     update todo set title='RC' where id=1;
     

T2;
    start transaction;
    set session transaction isolation level read committed;
    select title from todo where id=1;
    got output

As per my understanding , T1 has write lock for id=1 and T2 should not get output. T2 should get lock timeout exception.

Why T2 is not getting lock timeout & getting committed result ?

How to get lock timeout ?

Gnana
  • 2,130
  • 5
  • 26
  • 57

1 Answers1

0

T2 is running a non-locking SELECT statement. It doesn't need to wait for the lock held by T1, because T2 can read the row version that was committed at the time T2 start transaction happened.

If you run a locking SELECT statement, it would need to wait for the lock held by T1.

SELECT title FROM todo WHERE id=1 FOR UPDATE;

Both of the explanations above are true whether you use transaction isolation level READ COMMITTED or REPEATABLE READ.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for the clarification. But other DBs such as MsSQL not expecting for update syntax, it works for normal select. What will happen if I select without update and prepare report. it will show wrong report. To avoid that , Do I need to add for update in all the query ? – Gnana Oct 20 '20 at 04:32
  • Using REPEATABLE READ and non-locking SELECT queries is _better_ for reports. It allows you to run multiple SELECT statements in a transaction, and be sure all SELECT statements are based on the same data, viewing a snapshot in time. It does this _without_ locking. – Bill Karwin Oct 20 '20 at 14:42