Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.