作者: 弦樂(lè)之花 | 可以轉(zhuǎn)載, 但必須以超鏈接形式標(biāo)明文章原始出處和作者信息及版權(quán)聲明

創(chuàng)新互聯(lián)公司長(zhǎng)期為近千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開(kāi)放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為青白江企業(yè)提供專業(yè)的成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)，青白江網(wǎng)站改版等技術(shù)服務(wù)。擁有十余年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開(kāi)發(fā)。

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

MySQL innodb之select for update nowait

習(xí)慣了oracle數(shù)據(jù)庫(kù)的select for update nowait的同學(xué)，如果轉(zhuǎn)在mysql環(huán)境開(kāi)發(fā)的話，也許會(huì)不太適應(yīng)——目前builtin版本的innodb不支持nowait句法的。早在08年已經(jīng)被作為bug提了出來(lái)（http://bugs.mysql.com/bug.php?id=36285）。還好innodb plugin1.0.2開(kāi)始支持bug文章里提及的session級(jí)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.

該問(wèn)題的回復(fù)
[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.
看到該問(wèn)題也是最終被close掉。值得一提的是目前看到的一些問(wèn)題都說(shuō)是5147做fix，mysql51系列啥時(shí)候是個(gè)頭？？50系列目前主流的是508X系列吧，最近facebook發(fā)布了5084的補(bǔ)丁包，而percona則已經(jīng)發(fā)布了5190-b21版本，oracle則磨刀霍霍向55系列。。。

btw，作為對(duì)select for update nowait的實(shí)現(xiàn)，其實(shí)如果數(shù)據(jù)庫(kù)實(shí)現(xiàn)不了，其實(shí)應(yīng)用做個(gè)超時(shí)控制就可以了，把sql執(zhí)行放到超時(shí)控制代碼里，具體情況要看開(kāi)發(fā)語(yǔ)言了。

測(cè)試:
-- 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)