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 ?
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.
Both of the explanations above are true whether you use transaction isolation level READ COMMITTED or REPEATABLE READ.