Why no lock in MySQL for READ COMMITTED

1.3k views Asked by At

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 ?

1

There are 1 answers

2
Bill Karwin On BEST ANSWER

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.