关于innodb_rollback_on_timeout测验
发布时间：2022-06-26 11:35:51 所属栏目：MySql教程 来源：互联网
导读：innodb_rollback_on_timeout innodb_lock_wait_timeout 最开始看到innodb_rollback_on_timeout 时,没有好好理解transaction timeout的意思.以为只要是连接超时就会触发,今天研究了一下.只能说巨坑; 以下测试.隔离级别皆为RR模式.测试过程. 实验一: innodb_
|
innodb_rollback_on_timeout innodb_lock_wait_timeout 最开始看到innodb_rollback_on_timeout 时,没有好好理解transaction timeout的意思.以为只要是连接超时就会触发,今天研究了一下.只能说巨坑; 以下测试.隔离级别皆为RR模式.测试过程. 实验一: 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,'ddd'); | | 1 ---------------------------------------|----------------------------------------|------------------------------------------------ | begin; | 2 ---------------------------------------|----------------------------------------|------------------------------------------------ | update t8 set test1='yyy' where id =19;| 2 ---------------------------------------|----------------------------------------|------------------------------------------------ | | 2 update t8 set test1='xxx' where id =19;| | ---------------------------------------|----------------------------------------|------------------------------------------------ 堵塞 | | 2 ---------------------------------------|----------------------------------------|------------------------------------------------ 20s后超时 | | 1 事务结束; | commit; | 0 ---------------------------------------|----------------------------------------|------------------------------------------------ select * from t8; 20这个id没有;19被改为yyy; 实验二: 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,'ddd'); | | 1 ---------------------------------------|---------------------------------------|----------------------------------------------- | begin; | 2 |update t8 set test1='yyy' where id =19;| ---------------------------------------|---------------------------------------|----------------------------------------------- update t8 set test1='xxx' where id =19;| | 2 ---------------------------------------|---------------------------------------|----------------------------------------------- 堵塞 | | 2 ---------------------------------------|---------------------------------------|----------------------------------------------- 20s后超时 | | 2 事务未结束 | | ---------------------------------------|---------------------------------------|----------------------------------------------- commit; | commit; | 0 ---------------------------------------|---------------------------------------|----------------------------------------------- select * from t8; 20这个id存在;19被改为yyy; 结果就是:如果innodb_rollback_on_timeout 为on,事务在innodb_lock_wait_timeout超时后,当前sql会报超时,并使整个事务都回滚,然后结束事务; 如果innodb_rollback_on_timeout 为off(默认值),事务在innodb_lock_wait_timeout超时后,当前sql会超时,并终止,回滚当前sql,但整个事务不回滚,事务不结束,必须显示的回滚或提交; 一句话:innodb_rollback_on_timeout 没什么卵用.纯碎就是坑; （编辑：大连站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐