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.
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.