About innodb_rollback_on_timeout testing
Innodb_rollback_on_timeout
Innodb_lock_wait_timeout
When I first saw innodb_rollback_on_timeout, I didn't understand the meaning of transaction timeout. Thought that as long as the connection timeout will trigger, today studied. Can only say that the giant pit
The following tests. Isolation levels are all in RR mode. The testing process.
Experiment 1:
Innodb_rollback_on_timeout = on
Innodb_lock_wait_timeout=20
-- |-- |--
Session 1 | session 2 | session 3
-- |-- |--
Begin; | | select * from information_schema.innodb_trx
| | order by trx_started |
-- |-- |--
Insert into T8 values (20); | | 1
-- |-- |--
| | begin; | 2 |
-- |-- |--
| | update T8 set test1='yyy' where id = 19; | 2 |
-- |-- |--
| | 2 |
Update T8 set test1='xxx' where id = 19; | |
-- |-- |--
Jam | | 2
-- |-- |--
Timeout after 20s | | 1
Transaction ends; | commit; | 0
-- |-- |--
Select * from T8
20 this id does not have; 19 is changed to yyy
Experiment 2:
Innodb_rollback_on_timeout = off
Innodb_lock_wait_timeout=20
-- |-- |--
Session 1 | session 2 | session 3
-- |-- |--
Begin; | | select * from information_schema.innodb_trx
| | order by trx_started |
-- |-- |--
Insert into T8 values (20); | | 1
-- |-- |--
| | begin; | 2 |
| | update T8 set test1='yyy' where id = 19; | |
-- |-- |--
Update T8 set test1='xxx' where id = 19; | | 2
-- |-- |--
Jam | | 2
-- |-- |--
Timeout after 20s | | 2
The transaction did not end | |
-- |-- |--
Commit; | commit; | 0
-- |-- |--
Select * from T8
20 this id exists; 19 is changed to yyy
The result is: if innodb_rollback_on_timeout is on, the current sql will report a timeout after the innodb_lock_wait_timeout timeout, roll back the entire transaction, and then end the transaction
If innodb_rollback_on_timeout is off (default), after the transaction times out in innodb_lock_wait_timeout, the current sql will time out and terminate, rolling back the current sql, but the whole transaction will not be rolled back, the transaction will not end, and the rollback or commit that must be displayed
Bottom line: innodb_rollback_on_timeout is of no use. Pure crushing is a pit.
