Shulou(Shulou.com)06/01 Report--

Author: flower of string Music | it can be reproduced, but the original origin of the article and the author's information and copyright notice must be indicated in the form of a hyperlink.

Link http://shiri512003.itpub.net/post/37713/499937

[@ more@]

Select for update nowait of mysql innodb

Select for update nowait students who are accustomed to oracle databases may not adapt well if they switch to the mysql environment-the current builtin version of innodb does not support nowait syntax. As early as 2008, it has been proposed as bug (http://bugs.mysql.com/bug.php?id=36285). Fortunately, innodb plugin1.0.2 began to support the session-level innodb_lock_wait_timeout control (http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-other-changes-innodb_lock_wait_timeout.html) mentioned in the bug article.

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.

A reply to the question

[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.

See that the problem is finally dropped by close. It is worth mentioning that some of the problems we have seen so far say that it is 5147 to do fix,mysql51 series when will it end? At present, the mainstream of the 50 series is the 508X series. Recently, facebook has released a 5084 fix pack, while percona has released a 5190-b21 version, and oracle has sharpened its knife to the 55 series.

Btw, as the implementation of select for update nowait, in fact, if the database can not be implemented, in fact, the application can do a timeout control, put the sql execution into the timeout control code, depending on the development language.

Test:

-- 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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.