If you have any open transaction(s); and if there is any statement that causes implicit commit, especially DDL statements; then the current active transaction will be committed and transaction will be closed automatically. Here is the list of statements that causes implicit commit in MySQL.
But other day; we had an issue in production as all of a sudden everything started failing with the following error on RENAME TABLE statements:
|
1 2 3 |
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction |
This happened because I changed one of the table type to InnoDB from MyISAM; and as that table is only used for read-only purpose and most part of ETL operations failed with the above error even though no table is explicitly locked or running in any open transactions; and after debugging it further; found that its implicit commit that is not properly closing any open transactions when auto commit is set to ON mode.
Here is the simple repro in MySQL (5.0 and 5.1):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
mysql> create table outer_tab(id int)Engine=InnoDB; Query OK, 0 rows affected (0.12 sec) mysql> insert into outer_tab values(10); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from outer_tab; +------+ | id | +------+ | 10 | +------+ 1 row in set (0.00 sec) mysql> set autocommit=1; Query OK, 0 rows affected (0.00 sec) mysql> create table inner_tab(id int)Engine=InnoDB; Query OK, 0 rows affected (0.18 sec) mysql> rename table inner_tab to tmp_x; ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction mysql> |
SET AUTOCOMMIT=1 should perform implicit commit and close any open transactions as per the documentation; but it only commits any pending transactional statements; but does not mark transaction as closed; and here is the code that does that in set_var.cc::set_option_autocommit().
|
1 2 3 4 5 6 7 8 9 10 11 |
if ((org_options & OPTION_NOT_AUTOCOMMIT)) { /* We changed to auto_commit mode */ thd->options&= ~(ulonglong) (OPTION_BEGIN | OPTION_KEEP_LOG); thd->transaction.all.modified_non_trans_table= FALSE; thd->server_status|= SERVER_STATUS_AUTOCOMMIT; if (ha_commit(thd)) return 1; } |
This is handled by MySQL engine and is in-dependant of storage engines. The fix here is to reset SERVER_STATUS_IN_TRANS in thd->server_status after the successful ha_commit() or by calling end_active_trans() function instead of ha_commit() in the above set_option_autocommit() function; and this holds good for any other implicit commit statements as well, so that it marks transaction as closed. I also filed a bug report
In a side note; here is the same example against Oracle 10G:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SQL> SQL> create table outer_tab(id int); Table created. SQL> insert into outer_tab values(10); 1 row created. SQL> commit; Commit complete. SQL> SQL> set autocommit off; SQL> select * from outer_tab; ID ---------- 10 SQL> set autocommit on; SQL> SQL> create table inner_tab(id int); Table created. SQL> SQL> rename inner_tab to tmp_x; Table renamed. |
|
1 2 3 |
Thanks Venu for sharing and also filling a bug report; we had this issue from our JDBC related apps; and no luck in getting the repro and google search http://tinyurl.com/23ck8e9 resulted in no good suggestions or what might have caused it; atl east now we know where to look for (autocommit part)