| Bug #4672 | InnoDB LOCK TABLES/UNLOCK TABLES deadlock. | ||
|---|---|---|---|
| Submitted: | 21 Jul 2004 14:24 | Modified: | 29 Sep 2008 22:12 |
| Reporter: | Robbert-Jan Roos | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.0.20 | OS: | Linux (debian/linux 3.0) |
| Assigned to: | Marko Mäkelä | CPU Architecture: | Any |
[21 Jul 2004 14:25]
Robbert-Jan Roos
Hmm. I think this works better than the copy and paste into the description field.
Attachment: mysql.java (text/x-java), 1.85 KiB.
[22 Jul 2004 22:55]
Dean Ellis
Verified against 4.0.21 (1.1918). Thank you for the report. DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( a int primary key ) TYPE=InnoDB; INSERT INTO t1 VALUES ( 1 ); Connection 1: LOCK TABLE t1 WRITE; Connection 2: LOCK TABLE t1 WRITE; Connection 1: UPDATE t1 SET a = 2; Connection 2 status remains Locked Connection 1 status remains Searching rows for update Connection 1 blocks for innodb_lock_wait_seconds then aborts. Connection 2 waits for the lock then acquires it (after same timeout).
[23 Jul 2004 15:20]
Heikki Tuuri
Hi!
Since 4.0.19, InnoDB sets also its own, internal table lock when you call the MySQL's LOCK TABLES.
What happens in your program in LOCK TABLES ... WRITE is that:
1) MySQL calls ha_innobase::external_lock() to set an internal InnoDB table lock;
2) MySQL sets its own table lock;
3) MySQL calls an implicit commit which releases the internal InnoDB table lock! This call of an implicit commit is rather illogical.
The end result is that connection 1 does NOT have an internal InnoDB table lock when it does the UPDATE, but connection 2 does have that InnoDB table lock, because it is waiting for the MySQL table lock! Connection 1 cannot UPDATE then.
Workaround: do
SET AUTOCOMMIT=0;
and do the COMMITs explicitly. Then MySQL does not call an implicit commit after LOCK TABLES, and InnoDB preserves its internal table locks until the next call of UNLOCK TABLES or COMMIT.
Hmm... the best way to fix the bug is to remove that call of an implicit commit at the end of LOCK TABLES. The question for Marko is to find out if that breaks something in MySQL.
Regards,
Heikki
................
The gdb printout below shows that if you do not use BEGIN, MySQL calls innobase_commit immediately at the end of a LOCK TABLES command.
(gdb)
Continuing.
Breakpoint 2, ha_innobase::store_lock(THD*, st_thr_lock_data**, thr_lock_type)
(this=0x8637930, thd=0x861d690, to=0x8627bc0, lock_type=TL_WRITE)
at ha_innodb.cc:4978
4978 row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_pre
built;
(gdb) bt
#0 ha_innobase::store_lock(THD*, st_thr_lock_data**, thr_lock_type) (
this=0x8637930, thd=0x861d690, to=0x8627bc0, lock_type=TL_WRITE)
at ha_innodb.cc:4978
#1 0x0815b05c in get_lock_data (thd=0x861d690, table_ptr=0x8625640, count=1,
get_old_locks=false, write_lock_used=0x470b4584) at lock.cc:439
#2 0x0815a3f9 in mysql_lock_tables(THD*, st_table**, unsigned) (
thd=0x861d690, tables=0x8625640, count=1) at lock.cc:93
#3 0x081985f8 in lock_tables(THD*, st_table_list*, unsigned) (thd=0x861d690,
tables=0x0, count=1) at sql_base.cc:1714
#4 0x081984e2 in open_and_lock_tables(THD*, st_table_list*) (thd=0x861d690,
tables=0x86255e0) at sql_base.cc:1671
#5 0x081792a2 in mysql_execute_command(THD*) (thd=0x861d690)
at sql_parse.cc:3073
#6 0x0817b9d7 in mysql_parse(THD*, char*, unsigned) (thd=0x861d690,
inBuf=0x8625588 "LOCK TABLE t1 WRITE", length=140629664)
at sql_parse.cc:4035
#7 0x08174c55 in dispatch_command(enum_server_command, THD*, char*, unsigned)
(command=COM_QUERY, thd=0x861d690, packet=0x86701a1 "LOCK TABLE t1 WRITE",
packet_length=20) at sql_parse.cc:1457
#8 0x08174567 in do_command(THD*) (thd=0x861d690) at sql_parse.cc:1272
#9 0x08173a3e in handle_one_connection (arg=0x8637930) at sql_parse.cc:1016
#10 0x40053f60 in pthread_start_thread () from /lib/i686/libpthread.so.0
#11 0x400540fe in pthread_start_thread_event () from /lib/i686/libpthread.so.0
#12 0x401f5327 in clone () from /lib/i686/libc.so.6
(gdb) c
Continuing.
Breakpoint 1, ha_innobase::external_lock(THD*, int) (this=0x8637930,
thd=0x861d690, lock_type=1) at ha_innodb.cc:4696
4696 row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_prebuilt;
(gdb) bt
#0 ha_innobase::external_lock(THD*, int) (this=0x8637930, thd=0x861d690,
lock_type=1) at ha_innodb.cc:4696
#1 0x0815a69d in lock_external (thd=0x861d690, tables=0x8625640, count=1)
at lock.cc:183
#2 0x0815a4a0 in mysql_lock_tables(THD*, st_table**, unsigned) (
thd=0x861d690, tables=0x8625640, count=1) at lock.cc:116
#3 0x081985f8 in lock_tables(THD*, st_table_list*, unsigned) (thd=0x861d690,
tables=0x0, count=1) at sql_base.cc:1714
#4 0x081984e2 in open_and_lock_tables(THD*, st_table_list*) (thd=0x861d690,
tables=0x86255e0) at sql_base.cc:1671
#5 0x081792a2 in mysql_execute_command(THD*) (thd=0x861d690)
at sql_parse.cc:3073
#6 0x0817b9d7 in mysql_parse(THD*, char*, unsigned) (thd=0x861d690,
inBuf=0x8625588 "LOCK TABLE t1 WRITE", length=140629664)
at sql_parse.cc:4035
#7 0x08174c55 in dispatch_command(enum_server_command, THD*, char*, unsigned)
(command=COM_QUERY, thd=0x861d690, packet=0x86701a1 "LOCK TABLE t1 WRITE",
packet_length=20) at sql_parse.cc:1457
#8 0x08174567 in do_command(THD*) (thd=0x861d690) at sql_parse.cc:1272
#9 0x08173a3e in handle_one_connection (arg=0x8637930) at sql_parse.cc:1016
#10 0x40053f60 in pthread_start_thread () from /lib/i686/libpthread.so.0
#11 0x400540fe in pthread_start_thread_event () from /lib/i686/libpthread.so.0
#12 0x401f5327 in clone () from /lib/i686/libc.so.6
(gdb) c
Continuing.
Breakpoint 3, lock_table (flags=80, table=0x42378e68, mode=5, thr=0x4237ddb0)
at lock0lock.c:3391
3391 if (flags & BTR_NO_LOCKING_FLAG) {
Current language: auto; currently c
(gdb) c
Continuing.
Breakpoint 4, innobase_commit(THD*, void*) (thd=0x861d690,
trx_handle=0x8418375) at ha_innodb.cc:1057
1057 DBUG_ENTER("innobase_commit");
Current language: auto; currently c++
(gdb) bt
#0 innobase_commit(THD*, void*) (thd=0x861d690, trx_handle=0x8418375)
at ha_innodb.cc:1057
#1 0x081f0e12 in ha_commit_trans(THD*, st_thd_trans*) (thd=0x861d690,
trans=0x861e74c) at handler.cc:525
#2 0x08195661 in close_thread_tables(THD*, bool, bool) (thd=0x861d690,
lock_in_use=false, skip_derived=false) at sql_base.cc:384
#3 0x08175880 in dispatch_command(enum_server_command, THD*, char*, unsigned)
(command=COM_QUERY, thd=0x861d690, packet=0x86701a1 "", packet_length=20)
at sql_parse.cc:1762
#4 0x08174567 in do_command(THD*) (thd=0x861d690) at sql_parse.cc:1272
#5 0x08173a3e in handle_one_connection (arg=0x8418375) at sql_parse.cc:1016
#6 0x40053f60 in pthread_start_thread () from /lib/i686/libpthread.so.0
#7 0x400540fe in pthread_start_thread_event () from /lib/i686/libpthread.so.0
#8 0x401f5327 in clone () from /lib/i686/libc.so.6
(gdb)
[2 Aug 2004 14:34]
Marko Mäkelä
A possible fix is discussed in Bug#4852.
[6 Sep 2004 12:24]
Marko Mäkelä
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.
[10 Sep 2004 9:26]
Robbert-Jan Roos
Perhaps it would be possible to signal the condition and print a warning in the error log as long as the bug isn't fixed. Should save somebody a lot of debugging.
[29 Sep 2008 16:45]
Konstantin Osipov
Can't repeat against 6.0.8-alpha-valgrind-max-debug. I belive this was fixed in 5.1, with the fix for Bug#12713.
[29 Sep 2008 22:12]
MySQL Verification Team
I couldn't repeat with 5.1/6.0.
Description: In mysql 4.0.20 creating and releasing a write lock on an innodb table will deadlock causing all threads trying to update the same table to hang. I verified against mysql 4.0.18 and there it works fine. How to repeat: Compile and run the following java code, and see for yourself: CREATE TABLE `keynum` ( `id` int(10) unsigned NOT NULL auto_increment, `keyid` int(11) default NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB == begin mysql.java == import java.sql.*; import java.util.*; class Tmp extends Thread { private int newId() { Connection c = null; int result = 10000; try { c = DriverManager.getConnection( "jdbc:mysql://localhost/test", "root","pass"); Statement stmt = c.createStatement(); stmt.executeUpdate("LOCK TABLES keynum WRITE"); stmt.close(); PreparedStatement pstmt = c.prepareStatement( "SELECT keyid FROM keynum"); ResultSet rs = pstmt.executeQuery(); rs.next(); result = rs.getInt("keyid"); result++; rs.close(); PreparedStatement pstmt2 = c.prepareStatement( "UPDATE keynum SET keyid=?"); pstmt2.setInt(1, result); pstmt2.executeUpdate(); pstmt2.close(); } catch(Exception e) { System.out.println(e.toString()); System.exit(1); } finally { try { try { if (c != null) { Statement stmt = c.createStatement(); stmt.executeUpdate("UNLOCK TABLES"); stmt.close(); } } catch(SQLException e) { System.out.println(e.toString()); } } finally { close(c); } } return result; } private void close(Connection c) { if (c != null) { try { if (!c.isClosed()) { c.close(); } } catch(SQLException e) { System.out.println(e.toString()); } } } public void run() { while(true) { System.out.println("New id: "+newId()); } } } public class mysql { static { try { Class.forName("com.mysql.jdbc.Driver"); } catch(Exception e) { System.out.println(e); System.exit(1); } } public static void main(String[] args) throws Exception { List tmp = new ArrayList(); for(int i = 0 ; i < 10 ; i++) { tmp.add(new Tmp()); } Iterator i = tmp.iterator(); while(i.hasNext()) { Thread t = (Thread)i.next(); t.start(); } i = tmp.iterator(); while(i.hasNext()) { Thread t = (Thread)i.next(); t.join(); } } } Suggested fix: I leave this up to you :)