MySQL Bugs: #8144: Locking problem in TRUNCATE TABLE

Bug #8144	Locking problem in TRUNCATE TABLE
Submitted:	26 Jan 2005 13:49	Modified:	28 Jan 2005 11:39
Reporter:	Marko Mäkelä	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.3-bk	OS:	Any (all)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
When running TRUNCATE TABLE and INSERT in parallel on the same table, InnoDB can display the following warning:

050126 15:07:40  InnoDB: Error: a record lock wait happens in a dictionary operation!
InnoDB: Table name `test/ibtest09`.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com

Here's a stack trace:

#0  lock_rec_enqueue_waiting (type_mode=2565, rec=0x40778070 "supremum", 
    index=0x40378b68, thr=0x4037e3e0) at lock0lock.c:1809
#1  0x0838c0f8 in lock_rec_insert_check_and_lock (flags=0, 
    rec=0x40779ec4 "\200", index=0x40378b68, thr=0x4037e3e0, inherit=0xa)
    at lock0lock.c:4824
#2  0x0830af2d in btr_cur_ins_lock_and_undo (flags=0, cursor=0x40378b68, 
    entry=0x4037da68, thr=0x4037e3e0, inherit=0x1) at btr0cur.c:850
#3  0x0830b3e6 in btr_cur_optimistic_insert (flags=0, cursor=0xbe3fee14, 
    entry=0x4037da68, rec=0xbe3fe80c, big_rec=0xbe3fe810, thr=0x4037e3e0, 
    mtr=0xbe3fe9a4) at btr0cur.c:1009
#4  0x082ccb2e in row_ins_index_entry_low (mode=2, index=0x40378b68, 
    entry=0x4037da68, ext_vec=0x0, n_ext_vec=0, thr=0x4037e3e0)
    at row0ins.c:1997
#5  0x082cce5b in row_ins_index_entry (index=0x40378b68, entry=0x4037da68, 
    ext_vec=0x0, n_ext_vec=0, thr=0x4037e3e0) at row0ins.c:2075
#6  0x082cd2d7 in row_ins_index_entry_step (node=0x4037d610, thr=0x40361868)
    at row0ins.c:2154
#7  0x082cd771 in row_ins (node=0x4037d610, thr=0x4037e3e0) at row0ins.c:2286
#8  0x082cdcde in row_ins_step (thr=0x4037e3e0) at row0ins.c:2390
#9  0x082cfe82 in row_insert_for_mysql (
    mysql_rec=0x40361868 "\006\\n\005ò®L\b\001", prebuilt=0x40366e68)
    at row0mysql.c:948
#10 0x082328a9 in ha_innobase::write_row (this=0x8bc8020, 
    record=0x8bc85b8 "ýA") at ha_innodb.cc:2617
#11 0x081d9302 in write_record (thd=0x8b87110, table=0x8ba9940, 
    info=0xbe3ff1c4) at sql_insert.cc:853
#12 0x081d81b1 in mysql_insert (thd=0x8b87110, table_list=0x8ba30e0, 
    fields=@0x8b8752c, values_list=@0x8b87550, update_fields=@0x8b87544, 
    update_values=@0x8b87538, duplic=DUP_ERROR, ignore=false)
    at sql_insert.cc:386
#13 0x0818ebf0 in mysql_execute_command (thd=0x8b87110) at sql_parse.cc:2972
#14 0x08193653 in mysql_parse (thd=0x8b87110, 
    inBuf=0x8ba2f90 "insert into ibtest09 (D, B, C, F, G, H) values (5, 'kjgclgrtfuylfluyfyufyulfulfyyulofuyolfyufyufuyfyufyufyufyufyyufujhfghdkkkkkkkkkk' ,'kjgclgrtfuylfluyfyufyulfulfyyulofuyolfyufyufuyfyufyufyufyufyyufu"..., 
    length=146305360) at sql_parse.cc:4738
#15 0x0818adcb in dispatch_command (command=COM_QUERY, thd=0x8b87110, 
    packet=0x8b9af61 "insert into ibtest09 (D, B, C, F, G, H) values (5, 'kjgclgrtfuylfluyfyufyulfulfyyulofuyolfyufyufuyfyufyufyufyufyyufujhfghdkkkkkkkkkk' ,'kjgclgrtfuylfluyfyufyulfulfyyulofuyolfyufyufuyfyufyufyufyufyyufu"..., 
    packet_length=235) at sql_parse.cc:1533
#16 0x0818a6e5 in do_command (thd=0x8b87110) at sql_parse.cc:1341
#17 0x08189a57 in handle_one_connection (arg=0x40361868) at sql_parse.cc:1060
#18 0x40051e51 in pthread_start_thread () from /lib/libpthread.so.0
#19 0x40051ecf in pthread_start_thread_event () from /lib/libpthread.so.0
#20 0x401d86ea in clone () from /lib/libc.so.6

How to repeat:
Run two instances of a test in parallel. The test does INSERT, DELETE and UPDATE in a loop, and occasionally TRUNCATE TABLE, all on the same table.

The bug cannot be triggered by running one instance of the test, nor by running TRUNCATE TABLE in parallel with a loop of INSERT, DELETE and UPDATE.

Suggested fix:
Make sure that the dictionary mutex is properly acquired and released properly.

Here's a simple test case to repeat the problem. Run two connections in parallel, with these commands:

create table t(a bigint primary key not null auto_increment)type=innodb;

truncate table t;
insert into t values();
truncate table t;
insert into t values();
...

Marko,

you should not set trx->dict_operation = TRUE in TRUNCATE. That flag is a hint that in crash recovery we may need to drop the table automatically. In the case of TRUNCATE we do not wish that.

The error message has not surfaced before because DROP TABLE creates its own temporary transaction object.

Regards,

Heikki

trx0trx.h:
        ibool           dict_operation; /* TRUE if the trx is used to create
                                        a table, create an index, or drop a
                                        table */

lock0lock.c:

        if (trx->dict_operation) {
                ut_print_timestamp(stderr);
                fputs(
"  InnoDB: Error: a record lock wait happens in a dictionary operation!\n"
"InnoDB: Table name ", stderr);
                ut_print_name(stderr, trx, index->table_name);
                fputs(".\n"
"InnoDB: Submit a detailed bug report to http://bugs.mysql.com\n",
                        stderr);
        }

Thanks, Heikki.
I removed the offending assignment. The code also complained "MySQL is trying to truncate table though there are still open handles to it". Per your instructions, I removed that check and made TRUNCATE TABLE acquire an exclusive write lock in InnoDB. Now concurrent TRUNCATE and INSERT on the same table seem to work flawlessly. I'll still run some more tests before committing the fixes.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html