【MySQL5.7】【锁等待】【innodb_lock_wait_timeout】
xixuefeng
MySQL, MySQL-DEV
2021-03-18 13:52:59
1,251 次浏览
【MySQL5.7】【锁等待】【innodb_lock_wait_timeout】已关闭评论
MySQL5.7在开启事务后的悲观锁的测试
session1：在会话1开启一个事务，执行一个悲观锁
|
1 2 3 4 5 6 7 8 9 10 11 12 |
root@localhost :[scott]> begin; Query OK, 0 rows affected (0.00 sec) root@localhost :[scott]> SELECT * FROM `emp` WHERE sal=800 FOR UPDATE; +-------+-------+-------+------+---------------------+--------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-------+------+---------------------+--------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | +-------+-------+-------+------+---------------------+--------+------+--------+ 1 row in set (0.00 sec) root@localhost :[scott]> |
session2：在会话2也开启一个事务，执行悲观锁，出现所等待现象
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
root@192.168.31.100:[scott]> begin; Query OK, 0 rows affected (0.00 sec) root@192.168.31.100:[scott]> # 查看innodb_lock_wait_timeout参数的当前值，也就是所等待的秒数，同时50也是默认值 root@192.168.31.100:[scott]> select @@innodb_lock_wait_timeout; +----------------------------+ | @@innodb_lock_wait_timeout | +----------------------------+ | 50 | +----------------------------+ 1 row in set (0.00 sec) root@192.168.31.100:[scott]> # 会话级修改一下参数innodb_lock_wait_timeout，改为等待3秒 root@192.168.31.100:[scott]> set session innodb_lock_wait_timeout=3; Query OK, 0 rows affected (0.00 sec) root@192.168.31.100:[scott]> select @@innodb_lock_wait_timeout; +----------------------------+ | @@innodb_lock_wait_timeout | +----------------------------+ | 3 | +----------------------------+ 1 row in set (0.00 sec) root@192.168.31.100:[scott]> # 执行一个悲观锁的查询，出现了所等待现象，等待了3秒后，提示超时 root@192.168.31.100:[scott]> SELECT * FROM `emp` WHERE sal=800 FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction root@192.168.31.100:[scott]> ## 从下面可以看出来，innodb_lock_wait_timeout最小值为1秒，哪怕是设置为0 root@192.168.31.100:[scott]> |
其他：innodb_lock_wait_timeout参数的最小值只能为1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
## 从下面可以看出来，innodb_lock_wait_timeout最小值为1秒，哪怕是设置为0 root@192.168.31.100:[scott]> set session innodb_lock_wait_timeout=0; Query OK, 0 rows affected, 1 warning (0.00 sec) root@192.168.31.100:[scott]> select @@innodb_lock_wait_timeout; +----------------------------+ | @@innodb_lock_wait_timeout | +----------------------------+ | 1 | +----------------------------+ 1 row in set (0.00 sec) root@192.168.31.100:[scott]> |
小结：
1：innodb引擎的锁等待时间，由innodb_lock_wait_timeout参数控制；
2：innodb_lock_wait_timeout参数的可设置的最小值为1；
3：设置参数会话级临时生效的命令：set session innodb_lock_wait_timeout=3;
4：查询参数命令：select @@innodb_lock_wait_timeout。