MySQL Bugs: #4852: lock tables ... write still works wrong

Bug #4852	lock tables ... write still works wrong
Submitted:	2 Aug 2004 7:04	Modified:	29 Sep 2008 22:33
Reporter:	Igor Blagodetelev	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.0.21-nightly-20040729-Max-log	OS:	Linux (Linux RHEL AS 3.0)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
This bug is similar to #3894, but after "ChangeSet@1.1873" I tried to use query "select id from table_for_lock_innodb where id=1 for update", and just have gotten the same error: "ERROR 1205: Lock wait timeout exceeded; Try restarting transaction".

How to repeat:
Need two concurrent connection for database test: A and B.

A:
mysql> create table table_for_lock_innodb (id int) type=innodb;
Query OK, 0 rows affected (0.05 sec)

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

B:
mysql> lock tables table_for_lock_innodb write;
===== WAIT UNLOCK =====

A:
mysql> select id from table_for_lock_innodb where id=1 for update;
====== Unknown wait and after lock timeout: ======
ERROR 1205: Lock wait timeout exceeded; Try restarting transaction

B:
====== Still wait for unlock ======

A:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

B:
Query OK, 0 rows affected (1 min 33.23 sec)

mysql>

The same error occurred if I've used "insert into table_for_lock_innodb values (1);".

Igor,

the 4.0 tree does not yet contain recent 'pushed' changes that are associated with this bug. The 'commit' message of bk does not mean that the change is yet pushed to the central repository.

Since the fix of this bug requires changing the auto-commit behavior in LOCK TABLES, we have to review the change carefully.

Best regards,

Heikki

.......
heikki@hundin:~/mysql-4.0/sql> bk changes
ChangeSet@1.1935.1.2, 2004-08-01 13:26:01+02:00, serg@serg.mylan
  bug#4816. index search for NULL in blob

ChangeSet@1.1935.1.1, 2004-07-31 22:39:10+02:00, serg@serg.mylan
  BUG#4393, BUG#4356 - incorrect decimals in fix_length_and_dec() in some functi
ons

ChangeSet@1.1936, 2004-07-31 22:33:20+02:00, guilhem@mysql.com
  Fix for:
  Bug #4810 "deadlock with KILL when the victim was in a wait state"
  (I included mutex unlock into exit_cond() for future safety)
  and BUG#4827 "KILL while START SLAVE may lead to replication slave crash"

ChangeSet@1.1930.1.1, 2004-07-31 09:49:32+02:00, guilhem@mysql.com
  removing assertion (will be moved to 4.1) for non-debug to compile

ChangeSet@1.1928.1.1, 2004-07-30 18:53:54+02:00, serg@serg.mylan
  install-sh: copy not move like any reasonably modern install does

ChangeSet@1.1931.1.1, 2004-07-30 17:02:26+03:00, heikki@hundin.mysql.fi
  row0sel.c, page0page.ic:
    Add still more diagnostic code to track buffer pool corruption in one AMD64/
Linux computer

ChangeSet@1.1932, 2004-07-30 16:58:25+03:00, marko@hundin.mysql.fi
  dict0crea.c:
    Restore accidentally deleted comment to dict_create_sys_tables_tuple()

ChangeSet@1.1929.1.1, 2004-07-30 14:04:52+02:00, lenz@mysql.com
   - make sure the Windows "-classic" server binaries actually include
     "-classic" in the version string

ChangeSet@1.1930, 2004-07-30 10:20:52+02:00, guilhem@mysql.com
  sql_class.h:
    removing safe_mutex_assert_owner, as it would require an include,
    and Konstantin already has cleaned up things with assert.h in 4.1;
    so I'll put the safe_mutex_assert_owner in 4.1 instead.

ChangeSet@1.1915.1.2, 2004-07-30 01:00:52+02:00, guilhem@mysql.com
  Reverting a line I had just added to slave.cc (mutex is already locked
  when we come at this place).

ChangeSet@1.1915.1.1, 2004-07-30 00:53:25+02:00, guilhem@mysql.com
  Avoiding a theoretically possible crash (pthread_mutex_lock(0)) which could (a
t least in POSIX Threads books)
  happen on SMP machines, when a thread is going to wait on a condition and it i
s KILLed at the
  same time.
  Cleaning code a bit by adding a test in enter_cond() that we have the mutex (w
as already the case in all places
  where it's called except one which is fixed here).

ChangeSet@1.1923.3.1, 2004-07-29 23:33:23+02:00, serg@serg.mylan
  language fix

ChangeSet@1.1927, 2004-07-28 16:37:56+03:00, jani@rhols221.adsl.netsonic.fi
  Changed log() to mtr_log(), because of a redefination
  when compiled with metroworks compiler for Netware.

ChangeSet@1.1923.1.3, 2004-07-27 19:14:50+03:00, heikki@hundin.mysql.fi
  buf0flu.c, os0file.c:
    Add more diagnostic code to determine when an lsn field gets corrupt; tracks
 a crash reported from one Itanium computer

ChangeSet@1.1923.2.1, 2004-07-27 09:12:20+02:00, serg@serg.mylan
  avoid functions in configure

ChangeSet@1.1923.1.1, 2004-07-27 09:00:17+03:00, heikki@hundin.mysql.fi
  row0sel.c, page0page.ic:
    Add diagnostic code to print hex dumps and track corruption of next record o
ffsets in an index page

ChangeSet@1.1925, 2004-07-26 15:32:52+02:00, ingo@mysql.com
  Fixed some DBUG_PRINT format problems.

ChangeSet@1.1924, 2004-07-26 13:58:08+02:00, ingo@mysql.com
  Fixed a probable typo. Unfortunately we will not be able to test this ;-)

ChangeSet@1.1923, 2004-07-26 10:52:40+02:00, serg@serg.mylan
  BUG#4717 - check for valid table names in ALTER TABLE ... RENAME

ChangeSet@1.1922, 2004-07-23 19:41:19+02:00, serg@serg.mylan
  Merge bk-internal:/home/bk/mysql-4.0/
  into serg.mylan:/usr/home/serg/Abk/mysql-4.0

ChangeSet@1.1919.1.1, 2004-07-23 19:12:20+02:00, serg@serg.mylan
  workaround for Sun Forte / x86
  BUG#4681

ChangeSet@1.1921, 2004-07-23 12:44:09+02:00, lenz@mysql.com
   - match shared library file name suffixes on Mac OS X (.dylib)
     and HP-UX (.sl) for creating "mysql-shared" packages there

ChangeSet@1.1920, 2004-07-23 12:27:28+02:00, lenz@mysql.com
   - Use the same compile options for the upcoming darwin8 OS in
     configure.in (thanks to Al Begley from Apple for the hint)

ChangeSet@1.1919, 2004-07-22 19:28:11+02:00, serg@serg.mylan
  distclean:
        rm -f lex_hash.h
  BUG#4583

The problem is that there are two sets of table locks. LOCK TABLES obtains an InnoDB table lock and a MySQL table lock. A transaction commit (at end of each statement in AUTOCOMMIT=1 mode) releases all InnoDB locks, but not MySQL table locks.

It looks like this problem can be solved by modifying LOCK TABLES so that it starts a transaction by performing an implicit BEGIN. One problem would still remain: an explicit COMMIT between LOCK TABLES and UNLOCK TABLES will release InnoDB locks but not the MySQL table locks.

The most straightforward way of fixing this bug would be to make LOCK TABLES start a transaction. However, some applications running in AUTOCOMMIT=1 mode simply close the connection when they are finished, and rely on the server to unlock the tables. Were LOCK TABLES modified to do an implicit BEGIN, the transaction would be rolled back on abrupt connection termination, breaking such applications. In AUTOCOMMIT=1 mode, all statements would have been committed individually, and nothing would be rolled back.
Unfortunately, for the sake of backward compatibility, this bug cannot be fixed in MySQL 4.0 or 4.1. The transaction system will be revised in MySQL 5.0.
The work-around is to run the connection in AUTOCOMMIT=0 mode when using LOCK TABLES,
or to issue a BEGIN statement before LOCK TABLES.

Can't repeat it on 6.0.8. I believe this was fixed in 5.1 with Bug#12713. Needs to be re-verified for documentation purposes.

Not repeatable with 5.1.