A problem found during the Cobar test is that after a session executes lock table xxx write, other sessions, including the session, cannot query other tables, until a session executes the unlock tables statement, for example:
Session1:
Mysql> create table t (id int primary key) engine = innodb;
Query OK, 0 rows affected (0.08 sec)
Session2:
Lock table t write;
Session1:
Mysql> select * from sbtest limit 1;
ERROR 1100 (HY000): Table 'sbtest' was not locked with LOCK TABLES
Session2:
Unlock tables or quit;
Session1:
Can select other tables;
However, different sessions guarantee transaction isolation. Why? In fact, the answer lies in this sentence;
By default, MySQL autocommit = 1 and uses Cobar to initialize a shared backend connection;
When a session executes lock table write and returns the result, the backend connection will be released to MySQLChannel in the connection pool, in this case, the connection obtained when other sessions execute the query is actually the status of lock tablewrite, so the above phenomenon occurs;
It is also very easy to work around und, that is, execute setautocommit = 0 before executing lock tablewrite, so that the connection will not be released back until submission;
This article is from "MIKE's old blog" blog, please be sure to keep this source http://boylook.blog.51cto.com/7934327/1298646