MySQL Bugs: #6285: LOCK TABLES does not start a transaction with InnoDB as described in manual

Bug #6285	LOCK TABLES does not start a transaction with InnoDB as described in manual
Submitted:	27 Oct 2004 14:22	Modified:	28 Oct 2004 8:08
Reporter:	Levap Aretnyd	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.2-alpha-debug	OS:	Windows (Windows 2003)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
Manual in section "16.11.10 How to Cope with Deadlocks" says that:
"Note that LOCK TABLES implicitly starts a transaction, just like the statement BEGIN, and UNLOCK TABLES implicitly ends the transaction in a COMMIT. "
I have started a replication, and if a block LOCK TABLES ... UNLOCK with some inserts or updates in it is issued on the master, on a slaves are updates appearing as they are executed on master, not all at once after UNLOCK (manual says that it does implicit commit).

How to repeat:
Start up a replication and execute following statements on master (in replicated database):

mysql> create table test (id int not null auto_increment primary key, val int not null) engine=innodb;
Query OK, 0 rows affected (0.31 sec)

mysql> lock tables test write;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (null,456);
Query OK, 1 row affected (0.03 sec)

- now with master client still running, connect to the slave (it could be running already) and execute following:

mysql> select * from test;
+----+-----+
| id | val |
+----+-----+
|  1 | 456 |
+----+-----+
1 row in set (0.00 sec)

- if a transaction was started along with LOCK TABLES as described in manual, this row would not appear at this time, but after UNLOCK TABLES was executed on a master.

Hi!

Thank you, I have now updated the manual. I removed that vague claim. I also added

SET AUTOCOMMIT=0;

to the example code, so that MySQL's table locks and InnoDB's table locks behave nicely in unison.

The fact is that if AUTOCOMMIT=1, then there is a commit after each SQL statement also inside LOCK TABLES.

Regards,

Heikki