Bug #7879 Changing read locks properly for replication purposes
Submitted: 13 Jan 2005 16:34 Modified: 21 Feb 2005 11:09
Reporter: Sinisa Milivojevic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0 and up OS:Any (all)
Assigned to: Mats Kindahl

[13 Jan 2005 16:34] Sinisa Milivojevic
Description:
All UPDATE, DELETE, INSERT queries MUST use TL_READ_NO_INSERT in tables that are read. 

This implies multi-table updates / deletes, updates and deletes with nested queries and INSERT from SELECT. Last one already uses proper lock, but it is changed if bin-logging is not enabled. 

How to repeat:
> mysql> create table t1 (a int not null primary key, b int) type = innodb;
> Query OK, 0 rows affected, 1 warning (0.14 sec)
>
> mysql> insert into t1 values (10, 15);
> Query OK, 1 row affected (3.86 sec)
>
> mysql> create table t2 (a int not null primary key, b int) type = innodb;
> Query OK, 0 rows affected, 1 warning (0.08 sec)
>
> mysql> insert into t2 values (1, 2);
> Query OK, 1 row affected (3.79 sec)
>
> mysql> update t1 set b = b + 1 where exists (select * from t2 where t1.a = 
> t2.a
> + 9);
> Query OK, 1 row affected (2 min 15.18 sec)
> Rows matched: 1  Changed: 1  Warnings: 0
>
> mysql>
>
>
>
> (gdb)
> Continuing.
>
> Breakpoint 1, ha_innobase::store_lock(THD*, st_thr_lock_data**, 
> thr_lock_type)
>    (this=0x8b3c5f8, thd=0x8b43890, to=0x8b44fd0, lock_type=TL_WRITE)
>    at ha_innodb.cc:5263
> 5263            row_prebuilt_t* prebuilt        = (row_prebuilt_t*) 
> innobase_pre
> built;
> (gdb) next
> 5265            if ((lock_type == TL_READ && thd->in_lock_tables) ||
> (gdb) print *this
> $1 = {<handler> = {<Sql_alloc> = {<No data fields>},
>    _vptr.handler = 0x83db6e8, table = 0x8b3c050,
>    ref = 0x8b3c808 '¥' <repeats 16 times>, "test",
>    dupp_ref = 0x8b3c810 "¥¥¥¥¥¥¥¥test", data_file_length = 16384,
>    max_data_file_length = 0, index_file_length = 0,
>    max_index_file_length = 11936128518282651045, delete_length = 0,
>    auto_increment_value = 0, records = 0, deleted = 0,
>    raid_chunksize = 2779096485, mean_rec_length = 0, create_time = 0,
>    check_time = 0, update_time = 0, save_end_range = {key = 0xa5a5a5a5 "",
>      length = 2779096485, flag = 2779096485}, end_range = 0xa5a5a5a5,
>    range_key_part = 0xa5a5a5a5, key_compare_result_on_equal = -1515870811,
>    eq_range = 165, errkey = 2779096485, sortkey = 2779096485,
>    key_used_on_scan = 0, active_index = 64, ref_length = 4,
>    block_size = 16384, raid_type = 0, raid_chunks = 2779096485,
>    ft_handler = 0x0, inited = NONE, auto_increment_column_changed = 165,
>    implicit_emptied = false}, innobase_prebuilt = 0x423be468,
>  user_thd = 0x8b43890, last_query_id = 2, lock = {thread = 147466,
>    next = 0x0, prev = 0x8b44cac, lock = 0x8b44c48, cond = 0x0,
>    type = TL_UNLOCK, thread_id = 147466, status_param = 0x0,
>    debug_print_param = 0x8b3c050}, share = 0x8b44c48,
>  alloc_ptr = 0xa5a5a5a5 "",
>  upd_buff = 0x8b44d48 '¥' <repeats 128 times>, "h4z\025\001\001",
>  key_val_buff = 0x8b44d88 '¥' <repeats 64 times>, "h4z\025\001\001",
>  upd_and_key_val_buff_len = 62, int_table_flags = 4236460, primary_key = 
> 0,
>  last_dup_key = 4294967295, start_of_scan = 0, last_match_mode = 
> 2779096485,
>  num_write_row = 1, auto_inc_counter_for_this_stat = 0}
> (gdb) next
> 5281            } else if (lock_type != TL_IGNORE) {
> (gdb)
> 5290                    prebuilt->select_lock_type = LOCK_NONE;
> (gdb)
> 5291                    prebuilt->stored_select_lock_type = LOCK_NONE;
> (gdb)
> 5294            if (lock_type != TL_IGNORE && lock.type == TL_UNLOCK) {
> (gdb)
> 5299                    if ((lock_type >= TL_WRITE_CONCURRENT_INSERT &&
> (gdb)
> 5303                            lock_type = TL_WRITE_ALLOW_WRITE;
> (gdb)
> 5312                    if (lock_type == TL_READ_NO_INSERT && 
> !thd->in_lock_tabl
> es) {
> (gdb)
> 5316                    lock.type=lock_type;
> (gdb) c
> Continuing.
>
> Breakpoint 1, ha_innobase::store_lock(THD*, st_thr_lock_data**, 
> thr_lock_type)
>    (this=0x8b52bd8, thd=0x8b43890, to=0x8b44fd4, lock_type=TL_READ)
>    at ha_innodb.cc:5263
> 5263            row_prebuilt_t* prebuilt        = (row_prebuilt_t*) 
> innobase_pre
> built;
> (gdb) next
> 5265            if ((lock_type == TL_READ && thd->in_lock_tables) ||
> (gdb)
> 5281            } else if (lock_type != TL_IGNORE) {
> (gdb) next
> 5290                    prebuilt->select_lock_type = LOCK_NONE;
> (gdb)
> 5291                    prebuilt->stored_select_lock_type = LOCK_NONE;
> (gdb) c
> Continuing.
>
> Breakpoint 2, ha_innobase::external_lock(THD*, int) (this=0x8b3c5f8,
>    thd=0x8b43890, lock_type=1) at ha_innodb.cc:4982
> 4982            row_prebuilt_t* prebuilt = (row_prebuilt_t*) 
> innobase_prebuilt;
> (gdb) c
> Continuing.
>
> Breakpoint 2, ha_innobase::external_lock(THD*, int) (this=0x8b52bd8,
>    thd=0x8b43890, lock_type=0) at ha_innodb.cc:4982
> 4982            row_prebuilt_t* prebuilt = (row_prebuilt_t*) 
> innobase_prebuilt;
> (gdb)
> Continuing.
>
> Breakpoint 2, ha_innobase::external_lock(THD*, int) (this=0x8b3c5f8,
>    thd=0x8b43890, lock_type=2) at ha_innodb.cc:4982
> 4982            row_prebuilt_t* prebuilt = (row_prebuilt_t*) 
> innobase_prebuilt;
> (gdb)
> Continuing.
>
> Breakpoint 2, ha_innobase::external_lock(THD*, int) (this=0x8b52bd8,
>    thd=0x8b43890, lock_type=2) at ha_innodb.cc:4982
> 4982            row_prebuilt_t* prebuilt = (row_prebuilt_t*) 
> innobase_prebuilt;
> (gdb)
> Continuing.

Suggested fix:
replace TL_READ with TL_READ_NO_INSERT in all of the above instances.
[13 Jan 2005 17:54] Heikki Tuuri
Sinisa,

also multi-table UPDATE and DELETE have this bug in 4.0. I am changing the affected versions to include 4.0.

Regards,

Heikki
[13 Jan 2005 20:52] Heikki Tuuri
Hi!

I have now fixed this for InnoDB type tables in 4.0, 4.1, 5.0.

If

thd->lex->sql_command != SQLCOM_SELECT

then InnoDB always uses a locking read, never a consistent read. Thus, InnoDB no longer depends on MySQL using the correct table lock TL_READ_NO_INSERT. But the bug remains for MyISAM tables until the code is modified to use TL_READ_NO_INSERT.

Regards,

Heikki
[21 Feb 2005 11:09] Mats Kindahl
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