|
Problem is that server continues to use transaction after InnoDB has reported lock wait (this does not happen every time in test other times this test fails on duplicate key error). Here is instrumented output (16 == lock wait):
Version: '10.0.27-MariaDB-debug' socket: '/dev/shm/tmp/mysqld.1.sock' port: 16000 Source distribution
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 10 != SUCCESS 10
|
JAN: error code 16 != SUCCESS 10
|
2016-08-11 12:54:58 7faa2c5bd700 InnoDB: Assertion failure in thread 140368865384192 in file trx0trx.ic line 60
|
InnoDB: Failing assertion: state == TRX_STATE_NOT_STARTED
|
|
|
Server may not call InnoDB ::unlock_row() if InnoDB has already reported lock wait timeout or deadlock as transaction is automatically rolled-back (and naturally then trx state is not RX_STATE_ACTIVE). Stack:
#6 0x0000000000a1ceda in trx_state_eq (trx=0x7faa04174348, state=TRX_STATE_ACTIVE) at /home/jan/mysql/10.0/storage/xtradb/include/trx0trx.ic:60
|
#7 0x0000000000a29b86 in ha_innobase::unlock_row (this=0x7faa04033fa8) at /home/jan/mysql/10.0/storage/xtradb/handler/ha_innodb.cc:8575
|
#8 0x00000000006ac9df in rr_unlock_row (tab=0x7faa0404a640) at /home/jan/mysql/10.0/sql/sql_select.cc:10270
|
#9 0x00000000006bf7ef in evaluate_join_record (join=0x7faa04044848, join_tab=0x7faa0404a640, error=0) at /home/jan/mysql/10.0/sql/sql_select.cc:18089
|
#10 0x00000000006bf14d in sub_select (join=0x7faa04044848, join_tab=0x7faa0404a640, end_of_records=false) at /home/jan/mysql/10.0/sql/sql_select.cc:17882
|
#11 0x00000000006bf6c5 in evaluate_join_record (join=0x7faa04044848, join_tab=0x7faa0404a318, error=0) at /home/jan/mysql/10.0/sql/sql_select.cc:18065
|
#12 0x00000000006befa1 in sub_select (join=0x7faa04044848, join_tab=0x7faa0404a318, end_of_records=false) at /home/jan/mysql/10.0/sql/sql_select.cc:17843
|
#13 0x00000000006be7d1 in do_select (join=0x7faa04044848, fields=0x7faa2c5bb9d0, table=0x0, procedure=0x0) at /home/jan/mysql/10.0/sql/sql_select.cc:17505
|
#14 0x00000000006992b1 in JOIN::exec_inner (this=0x7faa04044848) at /home/jan/mysql/10.0/sql/sql_select.cc:3084
|
#15 0x00000000006964f4 in JOIN::exec (this=0x7faa04044848) at /home/jan/mysql/10.0/sql/sql_select.cc:2373
|
#16 0x0000000000699c04 in mysql_select (thd=0x3498fe0, rref_pointer_array=0x349d2b8, tables=0x7faa04013400, wild_num=0, fields=..., conds=0x7faa04030ee8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0x7faa04044788, unit=0x349c950, select_lex=0x349d040) at /home/jan/mysql/10.0/sql/sql_select.cc:3308
|
#17 0x00000000007334dc in mysql_multi_update (thd=0x3498fe0, table_list=0x7faa04013400, fields=0x349d158, values=0x349d5e0, conds=0x7faa04030ee8, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x349c950, select_lex=0x349d040, result=0x7faa2c5bbd50) at /home/jan/mysql/10.0/sql/sql_update.cc:1597
|
#18 0x000000000065a9fd in mysql_execute_command (thd=0x3498fe0) at /home/jan/mysql/10.0/sql/sql_parse.cc:3370
|
#19 0x00000000006638fe in mysql_parse (thd=0x3498fe0, rawbuf=0x7faa04013218 "UPDATE v1 LEFT JOIN t1 ON (v1.f1_2 = t1.f1_1) SET v1.f1_4 = 1 WHERE EXISTS ( SELECT * FROM t2 INNER JOIN v3 ON (f3_2 = f2) WHERE f3_1 = t1.f1_3 OR f3_2 > f2 )", length=158, parser_state=0x7faa2c5bc6b0) at /home/jan/mysql/10.0/sql/sql_parse.cc:6575
|
#20 0x0000000000655a16 in dispatch_command (command=COM_QUERY, thd=0x3498fe0, packet=0x349e821 "UPDATE v1 LEFT JOIN t1 ON (v1.f1_2 = t1.f1_1) SET v1.f1_4 = 1 WHERE EXISTS ( SELECT * FROM t2 INNER JOIN v3 ON (f3_2 = f2) WHERE f3_1 = t1.f1_3 OR f3_2 > f2 )", packet_length=158) at /home/jan/mysql/10.0/sql/sql_parse.cc:1309
|
#21 0x0000000000654c4c in do_command (thd=0x3498fe0) at /home/jan/mysql/10.0/sql/sql_parse.cc:999
|
#22 0x000000000078775a in do_handle_one_connection (thd_arg=0x3498fe0) at /home/jan/mysql/10.0/sql/sql_connect.cc:1378
|
#23 0x00000000007874a8 in handle_one_connection (arg=0x3498fe0) at /home/jan/mysql/10.0/sql/sql_connect.cc:1293
|
#24 0x0000000000d0632c in pfs_spawn_thread (arg=0x34a39b0) at /home/jan/mysql/10.0/storage/perfschema/pfs.cc:1860
|
#25 0x00007faa3567d6fa in start_thread (arg=0x7faa2c5bd700) at pthread_create.c:333
|
#26 0x00007faa34460b5d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109
|
|
|
Did I understand correctly that Innodb failed to lock and so no more lock possible untill .... ? (what? session/statement/transaction abortion)
|
|
Transaction requested a conflicting lock to a record that has already locked by other transaction. This requesting transaction will wait predefined time lock to be granted, if it is not granted during that time, we get lock timeout, rollback statement, and this error is reported to server. You may continue with next statement, previous statement was rolled back.
create table t1(a int not null primary key) engine=innodb;
|
insert into t1 values(1);
|
insert into t1 values(2);
|
insert into t1 values(3);
|
insert into t1 values(4);
|
insert into t1 values(5);
|
insert into t1 values(6);
|
# connection 1
|
begin;
|
update t1 set a = 60 where a = 4;
|
# connection 2
|
begin;
|
update t1 set a = 40 where a = 1;
|
update t1 set a = 20 where a = 4;
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
update t1 set a = 15 where a = 3;
|
rollback;
|
select * from t1;
|
a
|
1
|
2
|
3
|
4
|
5
|
6
|
# connection 1
|
commit;
|
select * from t1;
|
a
|
1
|
2
|
3
|
5
|
6
|
60
|
|
|
unlock_row() was added by me in MySQL Bug #3300. Does MariaDB incorporate fixes to these regressions?
[27 May 2010 11:45] Marko Mäkelä
Bug #53674 was caused by this bug fix.
[13 Jan 2011 9:18] Marko Mäkelä
This bug fix introduced Bug #59464 Race condition in row_vers_build_for_semi_consistent_read.
|
|
I do not know that was before I joined here, you need to check that from code.
|
|
It looks like a race condition (impossible to repeat with --debug) and so impossible easy to catch what was previous call of innodb.
Calls of unlock_row() looks OK in comparison with 5.7.
|
|
*semi_consistent_read calls also looks the same.
|
|
Regarding Jan’s comment on 2016-08-11, there are actually 3 different rollbacks that are possible inside InnoDB:
- Rollback the row
- Rollback to the start of the statement
- Rollback the whole transaction
The function row_mysql_handle_errors() is calling trx_rollback_to_savepoint(trx, savept) where savept is a row savepoint taken by the caller (row_insert_for_mysql() or row_update_for_mysql_using_upd_graph(), that is, INSERT/REPLACE or UPDATE/DELETE). So, row_mysql_handle_errors() is rolling back either the row or the transaction. For certain hard errors (including lock wait timeout if innodb_rollback_on_timeout=ON) InnoDB will roll back the entire transaction, after which an error should be reported to the client.
If row_mysql_handle_errors() returns an error to the SQL layer, the SQL layer would usually invoke a separate rollback to the start of the statement or a rollback of the entire transaction.
It could be that ha_innobase::unlock_row() is not really allowed after any of these rollbacks. Which rollbacks did take place?
|
|
I think that all related changes in MySQL must be evaluated and possibly ported to the SQL layer of MariaDB. An initial list of commits could be obtained with
git log -G'try_semi_consistent|unlock_row' --pretty=oneline origin/5.7 sql
|
This appears to include both code refactoring and bug fixes. I suspect that some bug fixes may be missing from MariaDB.
See also the original change that was apparently squashed with something else.
|
|
CREATE TABLE t1 (
|
f1_1 DATE,
|
f1_2 DATE NOT NULL,
|
f1_3 VARCHAR(10),
|
f1_4 INT UNSIGNED,
|
f_ind DATE NOT NULL,
|
KEY(f_ind)
|
) ENGINE=InnoDB;
|
CREATE OR REPLACE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1;
|
INSERT INTO t1 VALUES (NULL, '0000-00-00', 'z', 2, '2001-03-01'), ('0000-00-00', '2001-08-25', 'p', 2, '2006-03-03');
|
|
CREATE TABLE t2 (f2 INT UNSIGNED) ENGINE=InnoDB;
|
INSERT INTO t2 VALUES (1),(2);
|
|
CREATE TABLE t3 (
|
pk INT,
|
f3_1 VARCHAR(10),
|
f3_2 INT UNSIGNED,
|
primary key (pk)
|
) ENGINE=innodb;
|
CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3;
|
INSERT INTO t3 VALUES (1, NULL, 9), (2, 'v', 0);
|
|
--connect (con1,localhost,root,,test)
|
--send
|
UPDATE t1, t2, t3 SET pk = -1;
|
|
--connection default
|
UPDATE v1 LEFT JOIN t1 ON (v1.f1_2 = t1.f1_1) SET v1.f1_4 = 1 WHERE EXISTS ( SELECT * FROM t2 INNER JOIN v3 ON (f3_2 = f2) WHERE f3_1 = t1.f1_3 OR f3_2 > f2 );
|
|
--connection con1
|
--error ER_DUP_ENTRY
|
--reap
|
|
drop view v1,v3;
|
drop table t1,t2,t3;
|
|
|
Correct update error processing do not help:
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
|
index 94fbe0924b6..7262ced6db5 100644
|
--- a/sql/sql_update.cc
|
+++ b/sql/sql_update.cc
|
@@ -2151,11 +2151,22 @@ int multi_update::send_data(List<Item> ¬_used_values)
|
}
|
else
|
{
|
- if (error == HA_ERR_RECORD_IS_THE_SAME)
|
+ if (error == HA_ERR_RECORD_IS_THE_SAME ||
|
+ (error && ignore &&
|
+ !table->file->is_fatal_error(error, HA_CHECK_ALL)))
|
{
|
error= 0;
|
updated--;
|
}
|
+ else
|
+ {
|
+ if (error)
|
+ {
|
+ table->file->print_error(error, MYF(0));
|
+ DBUG_RETURN(1);
|
+ }
|
+ }
|
+
|
/* non-transactional or transactional table got modified */
|
/* either multi_update class' flag is raised in its branch */
|
if (table->file->has_transactions())
|
|
|
it looks like actual problem happened on reading a record (strange???)
#0 thd_mark_transaction_to_rollback (thd=0x555557c5e1e0, all=true) at sql/sql_class.cc:4410
|
(gdb) where
|
#0 thd_mark_transaction_to_rollback (thd=0x555557c5e1e0, all=true) at sql/sql_class.cc:4410
|
#1 0x00007ffff07eefdb in convert_error_code_to_mysql (error=DB_DEADLOCK, flags=1, thd=0x555557c5e1e0) at storage/innobase/handler/ha_innodb.cc:1643
|
#2 0x00007ffff07f9eba in ha_innodb::index_read (this=0x7fffb40d0748, buf=0x7fffb40d0f28 "\377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at storage/innobase/handler/ha_innodb.cc:8193
|
#3 0x00007ffff07fac69 in ha_innodb::index_first (this=0x7fffb40d0748, buf=0x7fffb40d0f28 "\377") at storage/innobase/handler/ha_innodb.cc:8552
|
#4 0x00007ffff07faebb in ha_innodb::rnd_next (this=0x7fffb40d0748, buf=0x7fffb40d0f28 "\377") at storage/innobase/handler/ha_innodb.cc:8649
|
#5 0x0000555555bf2a36 in handler::ha_rnd_next (this=0x7fffb40d0748, buf=0x7fffb40d0f28 "\377") at sql/handler.cc:2625
|
#6 0x0000555555d30824 in rr_sequential (info=0x7fffb403a058) at sql/records.cc:470
|
#7 0x0000555555a48dd1 in join_init_read_record (tab=0x7fffb4039fb0) at sql/sql_select.cc:18886
|
#8 0x0000555555a46c16 in sub_select (join=0x7fffb4030b48, join_tab=0x7fffb4039fb0, end_of_records=false) at sql/sql_select.cc:17980
|
#9 0x0000555555a46498 in do_select (join=0x7fffb4030b48, fields=0x7fffb402fad8, table=0x0, procedure=0x0) at sql/sql_select.cc:17645
|
#10 0x0000555555a20cd2 in JOIN::exec_inner (this=0x7fffb4030b48) at sql/sql_select.cc:3117
|
#11 0x0000555555a1def2 in JOIN::exec (this=0x7fffb4030b48) at sql/sql_select.cc:2403
|
#12 0x0000555555a2164c in mysql_select (thd=0x555557c5e1e0, rref_pointer_array=0x7fffb402fc68, tables=0x7fffb40c8968, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7fffb40c8ff8, unit=0x7fffb402f2d0, select_lex=0x7fffb402f9c0) at sql/sql_select.cc:3342
|
#13 0x00005555559b7be4 in mysql_derived_fill (thd=0x555557c5e1e0, lex=0x555557c61ab0, derived=0x7fffb40de018) at sql/sql_derived.cc:974
|
#14 0x00005555559b605f in mysql_handle_single_derived (lex=0x555557c61ab0, derived=0x7fffb40de018, phases=96) at sql/sql_derived.cc:195
|
#15 0x0000555555a374a7 in st_join_table::preread_init (this=0x7fffb403f960) at sql/sql_select.cc:11610
|
#16 0x0000555555a48d46 in join_init_read_record (tab=0x7fffb403f960) at sql/sql_select.cc:18881
|
#17 0x0000555555b4b2f9 in JOIN_TAB_SCAN::open (this=0x7fffb4040920) at sql/sql_join_cache.cc:3341
|
#18 0x0000555555b498fb in JOIN_CACHE::join_matching_records (this=0x7fffb40407f0, skip_last=false) at sql/sql_join_cache.cc:2260
|
#19 0x0000555555b49468 in JOIN_CACHE::join_records (this=0x7fffb40407f0, skip_last=false) at sql/sql_join_cache.cc:2097
|
#20 0x0000555555a46813 in sub_select_cache (join=0x7fffb4032a30, join_tab=0x7fffb403f960, end_of_records=true) at sql/sql_select.cc:17767
|
#21 0x0000555555a46a42 in sub_select (join=0x7fffb4032a30, join_tab=0x7fffb403f638, end_of_records=true) at sql/sql_select.cc:17937
|
#22 0x0000555555a464e5 in do_select (join=0x7fffb4032a30, fields=0x7fffb4015730, table=0x0, procedure=0x0) at sql/sql_select.cc:17648
|
#23 0x0000555555a20cd2 in JOIN::exec_inner (this=0x7fffb4032a30) at sql/sql_select.cc:3117
|
#24 0x0000555555a1def2 in JOIN::exec (this=0x7fffb4032a30) at sql/sql_select.cc:2403
|
#25 0x0000555555c98e06 in subselect_single_select_engine::exec (this=0x7fffb40df288) at sql/item_subselect.cc:3764
|
#26 0x0000555555c8eefd in Item_subselect::exec (this=0x7fffb40df988) at sql/item_subselect.cc:678
|
#27 0x0000555555c918e6 in Item_exists_subselect::val_int (this=0x7fffb40df988) at sql/item_subselect.cc:1538
|
#28 0x000055555592f1dd in Item::val_int_result (this=0x7fffb40df988) at sql/item.h:1015
|
#29 0x0000555555c18617 in Item_cache_int::cache_value (this=0x7fffb4040fc0) at sql/item.cc:9020
|
#30 0x0000555555c2039c in Item_cache_wrapper::cache (this=0x7fffb4040f00) at sql/item.cc:7819
|
#31 0x0000555555c150b8 in Item_cache_wrapper::val_int (this=0x7fffb4040f00) at sql/item.cc:7873
|
#32 0x0000555555a5cab9 in Item_func_trig_cond::val_int (this=0x7fffb40393b0) at sql/item_cmpfunc.h:500
|
#33 0x0000555555bfff42 in Item::val_bool (this=0x7fffb40393b0) at sql/item.cc:213
|
#34 0x0000555555c31af6 in Item_cond_and::val_int (this=0x7fffb4039550) at sql/item_cmpfunc.cc:4859
|
#35 0x0000555555a47190 in evaluate_join_record (join=0x7fffb4032218, join_tab=0x7fffb4038a20, error=0) at sql/sql_select.cc:18136
|
#36 0x0000555555a46e25 in sub_select (join=0x7fffb4032218, join_tab=0x7fffb4038a20, end_of_records=false) at sql/sql_select.cc:18022
|
#37 0x0000555555a473bc in evaluate_join_record (join=0x7fffb4032218, join_tab=0x7fffb40386f8, error=0) at sql/sql_select.cc:18203
|
#38 0x0000555555a46c78 in sub_select (join=0x7fffb4032218, join_tab=0x7fffb40386f8, end_of_records=false) at sql/sql_select.cc:17983
|
#39 0x0000555555a46498 in do_select (join=0x7fffb4032218, fields=0x7ffff043f9a0, table=0x0, procedure=0x0) at sql/sql_select.cc:17645
|
#40 0x0000555555a20cd2 in JOIN::exec_inner (this=0x7fffb4032218) at sql/sql_select.cc:3117
|
#41 0x0000555555a1def2 in JOIN::exec (this=0x7fffb4032218) at sql/sql_select.cc:2403
|
#42 0x0000555555a2164c in mysql_select (thd=0x555557c5e1e0, rref_pointer_array=0x555557c62510, tables=0x7fffb4013da0, wild_num=0, fields=..., conds=0x7fffb40df988, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0x7fffb4032158, unit=0x555557c61b78, select_lex=0x555557c62268) at sql/sql_select.cc:3342
|
#43 0x0000555555abc153 in mysql_multi_update (thd=0x555557c5e1e0, table_list=0x7fffb4013da0, fields=0x555557c62380, values=0x555557c62838, conds=0x7fffb40df988, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x555557c61b78, select_lex=0x555557c62268, result=0x7ffff043fd20) at sql/sql_update.cc:1592
|
#44 0x00005555559e1d69 in mysql_execute_command (thd=0x555557c5e1e0) at sql/sql_parse.cc:3369
|
#45 0x00005555559eaeb9 in mysql_parse (thd=0x555557c5e1e0, rawbuf=0x7fffb4013bb8 "UPDATE v1 LEFT JOIN t1 ON (v1.f1_2 = t1.f1_1) SET v1.f1_4 = 1 WHERE EXISTS ( SELECT * FROM t2 INNER JOIN v3 ON (f3_2 = f2) WHERE f3_1 = t1.f1_3 OR f3_2 > f2 )", length=158, parser_state=0x7ffff04406a0) at sql/sql_parse.cc:6634
|
#46 0x00005555559dcdbd in dispatch_command (command=COM_QUERY, thd=0x555557c5e1e0, packet=0x555557c64091 "UPDATE v1 LEFT JOIN t1 ON (v1.f1_2 = t1.f1_1) SET v1.f1_4 = 1 WHERE EXISTS ( SELECT * FROM t2 INNER JOIN v3 ON (f3_2 = f2) WHERE f3_1 = t1.f1_3 OR f3_2 > f2 )", packet_length=158) at sql/sql_parse.cc:1297
|
#47 0x00005555559dc071 in do_command (thd=0x555557c5e1e0) at sql/sql_parse.cc:1000
|
#48 0x0000555555b1163c in do_handle_one_connection (thd_arg=0x555557c5e1e0) at sql/sql_connect.cc:1377
|
#49 0x0000555555b1138b in handle_one_connection (arg=0x555557c5e1e0) at sql/sql_connect.cc:1292
|
#50 0x0000555555f2082c in pfs_spawn_thread (arg=0x5555579db5e0) at storage/perfschema/pfs.cc:1861
|
#51 0x00007ffff75666db in start_thread (arg=0x7ffff0441700) at pthread_create.c:463
|
#52 0x00007ffff62b488f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
(gdb)
|
|
|
Hare also the error lost after val_* call (of the subquery where the error happened)
|
|
Here is the fix, now the biggest problem to make test suite
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
|
index a8dfdff9809..808cbf4a103 100644
|
--- a/sql/item_subselect.cc
|
+++ b/sql/item_subselect.cc
|
@@ -3781,7 +3781,7 @@ int subselect_single_select_engine::exec()
|
}
|
thd->where= save_where;
|
thd->lex->current_select= save_select;
|
- DBUG_RETURN(0);
|
+ DBUG_RETURN(join->error);
|
}
|
|
int subselect_union_engine::exec()
|
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
|
index f7f6287c3fd..8d6927cfac8 100644
|
--- a/sql/sql_select.cc
|
+++ b/sql/sql_select.cc
|
@@ -18155,6 +18155,9 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab,
|
DBUG_RETURN(NESTED_LOOP_OK);
|
}
|
}
|
+ /* check for errors evaluating the condition */
|
+ if (join->thd->is_error())
|
+ DBUG_RETURN(NESTED_LOOP_ERROR);
|
}
|
/*
|
Check whether join_tab is not the last inner table
|
|
|
I tried to make scenario to repeat bug exectly but failed, obviousely I lack knowladge what to do innodb and where it should be stopped, following is not work (I hoped to let both thread open tables then run first till after update then secondand first (or first than second) both do not work
need information what actually lead to dead lock in innodb
--- a/sql/sql_update.cc
|
+++ b/sql/sql_update.cc
|
@@ -527,7 +527,7 @@ int mysql_update(THD *thd,
|
|
DBUG_EXECUTE_IF("show_explain_probe_update_exec_start",
|
dbug_serve_apcs(thd, 1););
|
-
|
+
|
if (!(select && select->quick))
|
status_var_increment(thd->status_var.update_scan_count);
|
|
@@ -1589,6 +1589,8 @@ bool mysql_multi_update(THD *thd,
|
thd->abort_on_warning= !ignore && thd->is_strict_mode();
|
List<Item> total_list;
|
|
+ DEBUG_SYNC(thd, "multi_update_before_loop1");
|
+ DEBUG_SYNC(thd, "multi_update_before_loop2");
|
res= mysql_select(thd, &select_lex->ref_pointer_array,
|
table_list, select_lex->with_wild,
|
total_list,
|
@@ -1597,6 +1599,7 @@ bool mysql_multi_update(THD *thd,
|
options | SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
|
OPTION_SETUP_TABLES_DONE,
|
*result, unit, select_lex);
|
+ DEBUG_SYNC(thd, "multi_update_after_loop");
|
|
DBUG_PRINT("info",("res: %d report_error: %d", res, (int) thd->is_error()));
|
res|= thd->is_error();
|
--source include/have_innodb.inc
|
|
--connect (con2,localhost,root,,test)
|
CREATE TABLE t1 (
|
f1_1 DATE,
|
f1_2 DATE NOT NULL,
|
f1_3 VARCHAR(10),
|
f1_4 INT UNSIGNED,
|
f_ind DATE NOT NULL,
|
KEY(f_ind)
|
) ENGINE=InnoDB;
|
CREATE OR REPLACE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1;
|
INSERT INTO t1 VALUES (NULL, '0000-00-00', 'z', 2, '2001-03-01'), ('0000-00-00', '2001-08-25', 'p', 2, '2006-03-03');
|
|
CREATE TABLE t2 (f2 INT UNSIGNED) ENGINE=InnoDB;
|
INSERT INTO t2 VALUES (1),(2);
|
|
CREATE TABLE t3 (
|
pk INT,
|
f3_1 VARCHAR(10),
|
f3_2 INT UNSIGNED,
|
primary key (pk)
|
) ENGINE=innodb;
|
CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3;
|
INSERT INTO t3 VALUES (1, NULL, 9), (2, 'v', 0);
|
|
--connect (con1,localhost,root,,test)
|
SET DEBUG_SYNC= 'multi_update_before_loop1 SIGNAL update1 WAIT_FOR continue1';
|
SET DEBUG_SYNC= 'multi_update_after_loop SIGNAL update3 WAIT_FOR continue3';
|
--send
|
UPDATE t1, t2, t3 SET pk = -1;
|
|
--connection con2
|
SET DEBUG_SYNC= 'now WAIT_FOR update1';
|
SET DEBUG_SYNC= 'multi_update_before_loop2 SIGNAL update2 WAIT_FOR continue2';
|
--send
|
UPDATE v1 LEFT JOIN t1 ON (v1.f1_2 = t1.f1_1) SET v1.f1_4 = 1 WHERE EXISTS ( SELECT * FROM t2 INNER JOIN v3 ON (f3_2 = f2) WHERE f3_1 = t1.f1_3 OR f3_2 > f2 );
|
|
--connection default
|
SET DEBUG_SYNC= 'now WAIT_FOR update2';
|
SET DEBUG_SYNC= 'now SIGNAL continue1';
|
SET DEBUG_SYNC= 'now WAIT_FOR update3';
|
SET DEBUG_SYNC= 'now SIGNAL continue3';
|
--connection con1
|
--error ER_DUP_ENTRY
|
--reap
|
|
SET DEBUG_SYNC= 'now SIGNAL continue2';
|
|
|
--connection con2
|
--reap
|
|
--connection default
|
SET DEBUG_SYNC= 'reset';
|
drop view v1,v3;
|
drop table t1,t2,t3;
|
|
|
Test case:
--source include/have_innodb.inc
|
--source include/count_sessions.inc
|
|
CREATE TABLE t1 (
|
pkey int NOT NULL PRIMARY KEY,
|
c int
|
) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES(1,1);
|
|
CREATE TABLE t2 (
|
pkey int NOT NULL PRIMARY KEY,
|
c int
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
INSERT INTO t2 VALUES (2, NULL);
|
|
# The following table is to increase tansaction weight on deadlock resolution
|
CREATE TABLE t3 (c int) engine = InnoDB STATS_PERSISTENT=0;
|
INSERT INTO t3 VALUES (10), (20), (30), (40), (50);
|
|
START TRANSACTION; # trx 1
|
# The following update is necessary to increase the transaction weight, which is
|
# calculated as the number of locks + the number of undo records during deadlock
|
# report. Victim's transaction should have minimum weight. We need trx 2 to be
|
# choosen as victim, that's why we need to increase the current transaction
|
# weight.
|
UPDATE t3 SET c=c+1000;
|
SELECT * FROM t1 FOR UPDATE;
|
|
--connect(con1, localhost,root,,)
|
START TRANSACTION; # trx 2
|
# 1) read record from t2, lock it
|
# 2) check if the read record should be skipped, i.e. read record from t1,
|
# as the record from t1 is locked by trx 1, the subselect will be suspended.
|
# see 'while' loop in mysql_update() and select->skip_record(thd) call
|
# for details.
|
--send UPDATE t2 SET pkey=pkey+10 WHERE c NOT IN (SELECT ref_0.pkey FROM t1 AS ref_0 INNER JOIN t1 AS ref_1 ON ref_0.c = ref_0.pkey)
|
|
--connection default
|
let $wait_condition=
|
SELECT count(*) = 1 FROM information_schema.processlist
|
WHERE (state = 'Sending data' or state = "Updating")
|
AND info LIKE 'UPDATE t2 SET pkey=pkey+10 WHERE%';
|
--source include/wait_condition.inc
|
|
# The record from t2 is locked by the previous update, so trx 2 is waiting for
|
# trx 1, and trx 1 will be blocked by trx 2 with the following SELECT. So we
|
# have deadlock here. And trx 2 is chosen as deadlock victim as trx 1 has
|
# greater weight.
|
SELECT * FROM t2 FOR UPDATE;
|
COMMIT;
|
|
--connection con1
|
# If the bug is not fixed, there will be assertion failure as mysql_update()
|
# will continue execution despite its subselect got deadlock error
|
--error ER_LOCK_DEADLOCK
|
--reap
|
COMMIT;
|
--disconnect con1
|
|
--connection default
|
DROP TABLE t1,t2,t3;
|
--source include/wait_until_count_sessions.inc
|
The fix of MDEV-29489 fixes this issue also.
|