mysql innodb之select for update nowait

作者: 弦乐之花 | 可以转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明

链接http://shiri512003.itpub.net/post/37713/499937

mysql innodb之select for update nowait

习惯了oracle数据库的select for update nowait的同学，如果转在mysql环境开发的话，也许会不太适应——目前builtin版本的innodb不支持nowait句法的。早在08年已经被作为bug提了出来（http://bugs.mysql.com/bug.php?id=36285）。还好innodb plugin1.0.2开始支持bug文章里提及的session级innodb_lock_wait_timeout控制（http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-other-changes-innodb_lock_wait_timeout.html）
Before InnoDB Plugin 1.0.2, the only way to set this parameter was in the MySQL option file (my.cnf or my.ini), and changing it required shutting down and restarting the server. Beginning with the InnoDB Plugin 1.0.2, the configuration parameter innodb_lock_wait_timeout can be set at runtime with the SET GLOBAL or SET SESSION commands.

该问题的回复
[5 May 17:08] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source
revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh46a80457) (merge vers: 5.1.46)
(pib:16)[6 May 16:58] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
看到该问题也是最终被close掉。值得一提的是目前看到的一些问题都说是5147做fix，mysql51系列啥时候是个头？？50系列目前主流的是508X系列吧，最近facebook发布了5084的补丁包，而percona则已经发布了5190-b21版本，oracle则磨刀霍霍向55系列。。。

btw，作为对select for update nowait的实现，其实如果数据库实现不了，其实应用做个超时控制就可以了，把sql执行放到超时控制代码里，具体情况要看开发语言了。

测试:
-- builtin innodb
user@test 10:14:37>set innodb_lock_wait_timeout=1;
ERROR 1238 (HY000): Variable 'innodb_lock_wait_timeout' is a read only variable

-- plugin innodb 1.0.7
user@sbtest 10:15:35>select @@innodb_version;
+------------------+
| @@innodb_version |
+------------------+
| 1.0.7 |
+------------------+
1 row in set (0.00 sec)

user@sbtest 10:15:43>set session innodb_lock_wait_timeout=1;
Query OK, 0 rows affected (0.00 sec)

user@sbtest 10:15:54>show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 1 |
+--------------------------+-------+
1 row in set (0.01 sec)

user@sbtest 10:16:02>show global variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 15 |
+--------------------------+-------+
1 row in set (0.00 sec)