Bug #1097 transaction
Submitted: 19 Aug 2003 7:16 Modified: 20 Aug 2003 4:56
Reporter: Andreas Sakowski Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:4.0.14 OS:Windows (windows)
Assigned to: CPU Architecture:Any

[19 Aug 2003 7:16] Andreas Sakowski
Description:
if you start a transaction and then you lock the table the transaction does not work. if you don't lock the table then the transaction works well. but with lock a new row is nevertheless inserted after a rollback. this problem shall to be under linux too. the statements under 'how to repeat' normally should to create no rows. but there is a new row. in 4.0.12 under linux it works well.

How to repeat:
CREATE TABLE `bla` (
  `id` int(11) NOT NULL auto_increment,
  `wert` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=InnoDB;
begin;
lock tables bla write;
insert into bla( wert ) values ( 1 );
rollback ;
select * from bla ;
[20 Aug 2003 4:56] Alexander Keremidarski
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

http://mysql.online.bg/doc/en/LOCK_TABLES.html
...
NOTE: LOCK TABLES is not transaction-safe and will implicitly commit any active transactions before attempting to lock the tables.