MySQL的wait_timeout参数太小导致表级锁失效

如果想让某个表只读不能被修改,可以在MySQL Server层面加表级锁:

mysql> lock tables sms read;
Query OK, 0 rows affected (0.01 sec)

然后其他的连接去更新或插入数据是不会成功的:

mysql> insert into sms(id) values (555);

因为要等待表级锁的释放,可以通过processlist看到这一点:

mysql> show processlist;
+——–+————-+———————+—————+———+——+——————————————-+—————————————–+
| Id | User | Host | db | Command | Time | State | Info |
+——–+————-+———————+—————+———+——+——————————————-+—————————————–+ |
| 364733 | root | localhost | banpingdb | Query | 0 | NULL | show processlist |
| 364734 | root | localhost | banpingdb | Query | 148 | Waiting for table level lock | insert into sms(id) values (555) |
+——–+————-+———————+—————+———+——+——————————————-+—————————————–+
2 rows in set (0.00 sec)

当然,更详细的信息也可以看到:

mysql> show engine innodb status G
————
TRANSACTIONS
————
Trx id counter 5AA0D8A
Purge done for trx’s n:o < 5AA0D77 undo n:o < 0
History list length 1063
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0, not started, process no 21967, OS thread id 1286076736
mysql tables in use 1, locked 1
MySQL thread id 364734, query id 3527582 localhost root Waiting for table level lock
insert into sms(id) values (555)
—TRANSACTION 0, not started, process no 21967, OS thread id 1284471104
mysql tables in use 1, locked 1
MySQL thread id 364733, query id 3527604 localhost root
show engine innodb status

但是,今天奇怪的发现,在等待了一段时间后,insert语句竟然莫名其妙的成功了,分析了一下原因。并没有什么地方控制表级锁的超时,就是因为表级锁被释放了,才能使得insert成功,而表级锁被释放的源头就是wait_timeout参数,这个参数的意思是:

The number of seconds the server waits for activity on a noninteractive connection before closing it.

如果这个值设置的小,比如说一分钟,那么一分钟后,不活动的连接就会被释放,而连接的释放会导致这个连接发出的表级锁被解锁。如果你的系统里这个参数很小,下面的这些信息一定是你经常看到的:

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 364743
Current database: banpingdb

这个错误提示的就是连接超时了重新连的意思。
还有另外一个参数是interactive_timeout,是针对interactive connection的超时时间,但是在global层面设置这个值会引起wait_timeout的连带改变。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>