- commits - lists.mariadb.org

[Commits] 686be2914fb: Range Locking: add the testcases missed in the move-to-8.0 commit
by psergey 30 Nov '20

30 Nov '20

revision-id: 686be2914fb36e6082162684642fbd62c44a00bf (fb-prod8-202009-51-g686be2914fb) parent(s): d3b369d996a1838c7f04a28db6f6c58dafec71db author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-11-30 20:59:35 +0300 message: Range Locking: add the testcases missed in the move-to-8.0 commit ... use the correct (adjusted) versions of files. --- .../suite/rocksdb/include/have_range_locking.inc | 2 +- .../suite/rocksdb/r/hermitage-range_locking.result | 4 ++-- mysql-test/suite/rocksdb/r/range_locking.result | 6 ++++-- .../suite/rocksdb/r/range_locking_rev_cf.result | 18 ++++++++++------ .../rocksdb/r/range_locking_seek_for_update.result | 24 ++++++++++++++-------- .../rocksdb/r/range_locking_shared_locks.result | 8 +++++--- mysql-test/suite/rocksdb/t/range_locking.inc | 6 +++--- .../rocksdb/t/range_locking_deadlock_tracking.test | 8 ++++---- .../rocksdb/t/range_locking_seek_for_update.test | 6 +++--- .../rocksdb/t/range_locking_shared_locks.test | 4 ++-- 10 files changed, 52 insertions(+), 34 deletions(-) diff --git a/mysql-test/suite/rocksdb/include/have_range_locking.inc b/mysql-test/suite/rocksdb/include/have_range_locking.inc index bb502d623ac..a8600daea77 100644 --- a/mysql-test/suite/rocksdb/include/have_range_locking.inc +++ b/mysql-test/suite/rocksdb/include/have_range_locking.inc @@ -1,3 +1,3 @@ -if (`select count(*) = 0 from information_schema.session_variables where variable_name = 'rocksdb_use_range_locking' and variable_value = 'ON';`) { +if (`select count(*) = 0 from performance_schema.session_variables where variable_name = 'rocksdb_use_range_locking' and variable_value = 'ON';`) { --skip Test requires range locking } diff --git a/mysql-test/suite/rocksdb/r/hermitage-range_locking.result b/mysql-test/suite/rocksdb/r/hermitage-range_locking.result index af6a0028629..3938fa38b6c 100644 --- a/mysql-test/suite/rocksdb/r/hermitage-range_locking.result +++ b/mysql-test/suite/rocksdb/r/hermitage-range_locking.result @@ -148,7 +148,7 @@ begin; connection con1; update test set value = value + 10; connection con2; -select variable_value into @a from information_schema.global_status where variable_name='rocksdb_snapshot_conflict_errors'; +select variable_value into @a from performance_schema.global_status where variable_name='rocksdb_snapshot_conflict_errors'; select * from test; id value 1 10 @@ -474,7 +474,7 @@ begin; connection con1; update test set value = value + 10; connection con2; -select variable_value into @a from information_schema.global_status where variable_name='rocksdb_snapshot_conflict_errors'; +select variable_value into @a from performance_schema.global_status where variable_name='rocksdb_snapshot_conflict_errors'; select * from test; id value 1 10 diff --git a/mysql-test/suite/rocksdb/r/range_locking.result b/mysql-test/suite/rocksdb/r/range_locking.result index 3641fb1943e..07a01ec13b5 100644 --- a/mysql-test/suite/rocksdb/r/range_locking.result +++ b/mysql-test/suite/rocksdb/r/range_locking.result @@ -448,8 +448,10 @@ select * from t1 where pk1=4 and pk2 between 5 and 8 order by pk1 desc, pk2 desc for update; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range PRIMARY PRIMARY 8 NULL # 100.00 Using where; Backward index scan +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`pk1` = 4) and (`test`.`t1`.`pk2` between 5 and 8)) order by `test`.`t1`.`pk1` desc,`test`.`t1`.`pk2` desc select * from t1 where pk1=4 and pk2 between 5 and 8 order by pk1 desc, pk2 desc diff --git a/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result b/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result index d33d4872fab..3dd1fcfa320 100644 --- a/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result +++ b/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result @@ -182,8 +182,10 @@ connection con1; begin; explain select * from t1 where kp1=2 for update; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref kp1 kp1 4 const # NULL +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref kp1 kp1 4 const # 100.00 NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`kp1` AS `kp1`,`test`.`t1`.`kp2` AS `kp2`,`test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`kp1` = 2) select * from t1 where kp1=2 for update; kp1 kp2 a 2 3 1234 @@ -377,8 +379,10 @@ select * from t1 where pk1=3 order by pk1 desc, pk2 desc for update; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY PRIMARY 4 const # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref PRIMARY PRIMARY 4 const # 100.00 Backward index scan +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`pk1` = 3) order by `test`.`t1`.`pk1` desc,`test`.`t1`.`pk2` desc select * from t1 where pk1=3 order by pk1 desc, pk2 desc @@ -408,8 +412,10 @@ select * from t1 where pk1=4 and pk2 between 5 and 8 order by pk1 desc, pk2 desc for update; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range PRIMARY PRIMARY 8 NULL # 100.00 Using where; Backward index scan +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`pk1` = 4) and (`test`.`t1`.`pk2` between 5 and 8)) order by `test`.`t1`.`pk1` desc,`test`.`t1`.`pk2` desc select * from t1 where pk1=4 and pk2 between 5 and 8 order by pk1 desc, pk2 desc diff --git a/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result b/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result index cddf039bc4f..a19331b81c4 100644 --- a/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result +++ b/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result @@ -34,8 +34,10 @@ $cf_id $trx_id 00${indexnr}8000000b X # explain select * from t1 where pk>=500 order by pk limit 3 for update; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`pk` >= 500) order by `test`.`t1`.`pk` limit 3 select * from t1 where pk>=500 order by pk limit 3 for update; pk a 500 500 @@ -52,8 +54,10 @@ pk a 11 11 explain select * from t1 order by pk limit 3 for update; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 3 NULL +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 3 100.00 NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`pk` limit 3 select * from t1 order by pk limit 3 for update; pk a 0 0 @@ -104,8 +108,10 @@ pk a # right before the range we are about to lock (nothing) explain select * from t1 where pk >=5 order by pk limit 3 for update; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`pk` >= 5) order by `test`.`t1`.`pk` limit 3 begin; set debug_sync='rocksdb.locking_iter_scan SIGNAL about_to_lock_range WAIT_FOR spoiler_inserted'; select * from t1 where pk >=5 order by pk limit 3 for update; @@ -126,8 +132,10 @@ delete from t1 where pk=3; # the range we are locking explain select * from t1 where pk >=5 order by pk limit 1 for update; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`pk` >= 5) order by `test`.`t1`.`pk` limit 1 begin; set debug_sync='rocksdb.locking_iter_scan SIGNAL about_to_lock_range WAIT_FOR spoiler_inserted'; select * from t1 where pk >=5 order by pk limit 1 for update; diff --git a/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result b/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result index b50e383fd82..722889e6721 100644 --- a/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result +++ b/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result @@ -104,8 +104,10 @@ connection default; begin; explain select * from t1 where a between 2 and 5 lock in share mode; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 4 NULL # 100.00 Using where; Using index +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` between 2 and 5) select * from t1 where a between 2 and 5 lock in share mode; pk a 2 2 @@ -212,7 +214,7 @@ set @save_mlm= @@rocksdb_max_lock_memory; set @cur_mem_usage= (select variable_value from -information_schema.GLOBAL_STATUS +performance_schema.global_status where variable_name='rocksdb_locktree_current_lock_memory'); set global rocksdb_max_lock_memory = cast(@cur_mem_usage+4 as SIGNED); diff --git a/mysql-test/suite/rocksdb/t/range_locking.inc b/mysql-test/suite/rocksdb/t/range_locking.inc index c676ccb66db..4f1db4399cb 100644 --- a/mysql-test/suite/rocksdb/t/range_locking.inc +++ b/mysql-test/suite/rocksdb/t/range_locking.inc @@ -204,7 +204,7 @@ insert into t1 select 3, a, 1234 from t0; connect (con1,localhost,root,,); connection con1; begin; ---replace_column 9 # +--replace_column 10 # explain select * from t1 where kp1=2 for update; select * from t1 where kp1=2 for update; @@ -458,7 +458,7 @@ insert into t1 values (0x1112222,0x1112222,0); connection default; begin; --echo # Should use ref access w/o filesort: ---replace_column 9 # +--replace_column 10 # explain select * from t1 where pk1=3 @@ -480,7 +480,7 @@ rollback; begin; --echo # Should use range access with 2 keyparts and w/o filesort: ---replace_column 9 # +--replace_column 10 # explain select * from t1 where pk1=4 and pk2 between 5 and 8 diff --git a/mysql-test/suite/rocksdb/t/range_locking_deadlock_tracking.test b/mysql-test/suite/rocksdb/t/range_locking_deadlock_tracking.test index a1fdafa4613..93a449289ec 100644 --- a/mysql-test/suite/rocksdb/t/range_locking_deadlock_tracking.test +++ b/mysql-test/suite/rocksdb/t/range_locking_deadlock_tracking.test @@ -85,10 +85,10 @@ let $wait_condition = select count(*) = 1 from information_schema.rocksdb_trx where thread_id = $con2 and waiting_key != ""; --source include/wait_condition.inc -select variable_value into @a from information_schema.global_status where variable_name='rocksdb_row_lock_deadlocks'; +select variable_value into @a from performance_schema.global_status where variable_name='rocksdb_row_lock_deadlocks'; --error ER_LOCK_DEADLOCK select * from t where i=1 for update; -select case when variable_value-@a = 1 then 'true' else 'false' end as deadlocks from information_schema.global_status where variable_name='rocksdb_row_lock_deadlocks'; +select case when variable_value-@a = 1 then 'true' else 'false' end as deadlocks from performance_schema.global_status where variable_name='rocksdb_row_lock_deadlocks'; rollback; connection con2; @@ -104,7 +104,7 @@ set global rocksdb_max_latest_deadlocks = 5; --replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ show engine rocksdb transaction status; ---disable_parsing +--disable_testcase BUG#0000 echo Deadlock #5; connection con1; begin; @@ -146,7 +146,7 @@ rollback; connection default; --replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ show engine rocksdb transaction status; ---enable_parsing +--enable_testcase echo Deadlock #6; connection con1; create table t1 (id int primary key, value int) engine=rocksdb; diff --git a/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test b/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test index 32590af1799..c1f0fe312e0 100644 --- a/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test +++ b/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test @@ -40,7 +40,7 @@ let $select_from_is_rowlocks_current_trx_only=1; --echo # --echo # SeekForUpdate Test #1: A query with type=range (without upper bound) and LIMIT --echo # ---replace_column 9 # +--replace_column 10 # explain select * from t1 where pk>=500 order by pk limit 3 for update; select * from t1 where pk>=500 order by pk limit 3 for update; @@ -92,7 +92,7 @@ select * from t1 where pk<10; --echo # Test what happens when another transaction commits a row --echo # right before the range we are about to lock (nothing) ---replace_column 9 # +--replace_column 10 # explain select * from t1 where pk >=5 order by pk limit 3 for update; @@ -117,7 +117,7 @@ delete from t1 where pk=3; --echo # Now, repeat the test but let the other transaction insert the row into --echo # the range we are locking ---replace_column 9 # +--replace_column 10 # explain select * from t1 where pk >=5 order by pk limit 1 for update; diff --git a/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test b/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test index d569a21ebec..c6e4e457897 100644 --- a/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test +++ b/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test @@ -104,7 +104,7 @@ let $TRX2_ID=`select transaction_id from information_schema.rocksdb_trx where th connection default; begin; ---replace_column 9 # +--replace_column 10 # explain select * from t1 where a between 2 and 5 lock in share mode; select * from t1 where a between 2 and 5 lock in share mode; @@ -177,7 +177,7 @@ set @save_mlm= @@rocksdb_max_lock_memory; set @cur_mem_usage= (select variable_value from - information_schema.GLOBAL_STATUS + performance_schema.global_status where variable_name='rocksdb_locktree_current_lock_memory');

1 0

[Commits] d3b369d996a: Range Locking: add the testcases missed in the move-to-8.0 commit
by psergey 30 Nov '20

30 Nov '20

revision-id: d3b369d996a1838c7f04a28db6f6c58dafec71db (fb-prod8-202009-50-gd3b369d996a) parent(s): 4229a7ee0a4e8f2b95ed7db7d87e9baf561dc88d author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-11-30 20:22:43 +0300 message: Range Locking: add the testcases missed in the move-to-8.0 commit .. in the 78674a3d77089376d8f511566c697077ff1bdb5d Range Locking code, moved to mysql-8.0 based branch --- mysql-test/suite/rocksdb/combinations | 1 + .../suite/rocksdb/include/have_range_locking.inc | 3 + .../rocksdb/include/select_from_is_rowlocks.inc | 66 +++ .../suite/rocksdb/r/hermitage-range_locking.result | 652 +++++++++++++++++++++ ...issue243_transactionStatus-range_locking.result | 151 +++++ .../r/level_repeatable_read-range_locking.result | 106 ++++ mysql-test/suite/rocksdb/r/range_locking.result | 516 ++++++++++++++++ .../r/range_locking_deadlock_tracking.result | 235 ++++++++ .../rocksdb/r/range_locking_escalation.result | 27 + .../rocksdb/r/range_locking_refresh_iter.result | 50 ++ .../suite/rocksdb/r/range_locking_rev_cf.result | 476 +++++++++++++++ .../rocksdb/r/range_locking_seek_for_update.result | 271 +++++++++ .../rocksdb/r/range_locking_shared_locks.result | 249 ++++++++ .../suite/rocksdb/t/hermitage-range_locking.test | 15 + .../issue243_transactionStatus-range_locking.test | 10 + .../t/level_repeatable_read-range_locking.test | 9 + mysql-test/suite/rocksdb/t/range_locking.inc | 544 +++++++++++++++++ mysql-test/suite/rocksdb/t/range_locking.test | 6 + .../rocksdb/t/range_locking_deadlock_tracking.test | 194 ++++++ .../rocksdb/t/range_locking_escalation-master.opt | 1 + .../suite/rocksdb/t/range_locking_escalation.test | 38 ++ .../rocksdb/t/range_locking_refresh_iter.test | 69 +++ .../suite/rocksdb/t/range_locking_rev_cf.test | 12 + .../rocksdb/t/range_locking_seek_for_update.test | 288 +++++++++ .../rocksdb/t/range_locking_shared_locks.test | 202 +++++++ 25 files changed, 4191 insertions(+) diff --git a/mysql-test/suite/rocksdb/combinations b/mysql-test/suite/rocksdb/combinations index c9a4494607a..54cce2c205b 100644 --- a/mysql-test/suite/rocksdb/combinations +++ b/mysql-test/suite/rocksdb/combinations @@ -6,3 +6,4 @@ rocksdb_write_policy=write_prepared [range_locking] rocksdb_use_range_locking=1 + diff --git a/mysql-test/suite/rocksdb/include/have_range_locking.inc b/mysql-test/suite/rocksdb/include/have_range_locking.inc new file mode 100644 index 00000000000..bb502d623ac --- /dev/null +++ b/mysql-test/suite/rocksdb/include/have_range_locking.inc @@ -0,0 +1,3 @@ +if (`select count(*) = 0 from information_schema.session_variables where variable_name = 'rocksdb_use_range_locking' and variable_value = 'ON';`) { + --skip Test requires range locking +} diff --git a/mysql-test/suite/rocksdb/include/select_from_is_rowlocks.inc b/mysql-test/suite/rocksdb/include/select_from_is_rowlocks.inc new file mode 100644 index 00000000000..e5f54d68914 --- /dev/null +++ b/mysql-test/suite/rocksdb/include/select_from_is_rowlocks.inc @@ -0,0 +1,66 @@ +--echo # select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +# +# An include to print contents of I_S.ROCKSB_LOCKS +# +# Implicit "parameters" +# - Currently it prints locks on t1.PRIMARY +# +# Explicit "parameter" variables: +# - $TRX1_ID - print this transaction as "TRX1" +# - $TRX2_ID - print this transaction as "TRX2" +# +# - $select_from_is_rowlocks_current_trx_only +# - $order_by_rowkey + +--disable_query_log +set @cf_id=(select column_family from information_schema.rocksdb_ddl + where table_name='t1' and index_name='PRIMARY'); +set @rtrx_id=(select transaction_id from information_schema.rocksdb_trx + where thread_id=connection_id()); +set @indexnr= (select lower(lpad(hex(index_number),8,'0')) from information_schema.rocksdb_ddl + where table_name='t1' and index_name='PRIMARY'); + +set @indexnr_next= (select lower(lpad(hex(index_number+1),8,'0')) + from information_schema.rocksdb_ddl + where table_name='t1' and index_name='PRIMARY'); + +let $extra_where = where 1; + +if ($select_from_is_rowlocks_current_trx_only) +{ + let $extra_where = where transaction_id=(select transaction_id from information_schema.rocksdb_trx where connection_id()=thread_id); +} + +# If TRX1_ID is not specified, get the current transaction: +let $transaction_col= replace(transaction_id, @rtrx_id, "\$trx_id"); +if ($TRX1_ID) +{ + let $transaction_col = replace(transaction_id, '$TRX1_ID', "\$TRX1_ID"); +} + +if ($TRX2_ID) +{ + let $transaction_col = replace($transaction_col, '$TRX2_ID', "\$TRX2_ID"); +} + +if ($order_by_rowkey) +{ + let $extra_order_by = ORDER BY 3,2; +} + +if (!$order_by_rowkey) +{ + --sorted_result +} + +eval select + replace(column_family_id, @cf_id, "\$cf_id") as COLUMN_FAMILY_ID, + $transaction_col as TRANSACTION_ID, + replace( + replace(`key`, @indexnr, '\${indexnr}'), + @indexnr_next, '\${indexnr+1}' + ) as `KEY`, + mode +from information_schema.rocksdb_locks $extra_where $extra_order_by; + +--enable_query_log diff --git a/mysql-test/suite/rocksdb/r/hermitage-range_locking.result b/mysql-test/suite/rocksdb/r/hermitage-range_locking.result new file mode 100644 index 00000000000..af6a0028629 --- /dev/null +++ b/mysql-test/suite/rocksdb/r/hermitage-range_locking.result @@ -0,0 +1,652 @@ +DROP TABLE IF EXISTS test; +connect con1,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +connect con2,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +connect con3,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +connection con1; +create table test (id int primary key, value int) engine=rocksdb; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test; +id value +1 10 +2 20 +update test set value = 101 where id = 1; +connection con2; +select * from test; +id value +1 10 +2 20 +connection con1; +rollback; +connection con2; +select * from test; +id value +1 10 +2 20 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +update test set value = 101 where id = 1; +connection con2; +select * from test; +id value +1 10 +2 20 +connection con1; +update test set value = 11 where id = 1; +commit; +connection con2; +select * from test; +id value +1 11 +2 20 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +update test set value = 11 where id = 1; +connection con2; +update test set value = 22 where id = 2; +connection con1; +select * from test where id = 2; +id value +2 20 +connection con2; +select * from test where id = 1; +id value +1 10 +connection con1; +commit; +connection con2; +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +update test set value = 11 where id = 1; +update test set value = 19 where id = 2; +connection con2; +update test set value = 12 where id = 1; +connection con1; +commit; +connection con2; +connection con3; +select * from test; +id value +1 11 +2 19 +connection con2; +update test set value = 18 where id = 2; +connection con3; +select * from test; +id value +1 11 +2 19 +connection con2; +commit; +connection con3; +select * from test; +id value +1 12 +2 18 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test where value = 30; +id value +connection con2; +insert into test (id, value) values(3, 30); +commit; +connection con1; +select * from test where value % 3 = 0; +id value +3 30 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +update test set value = value + 10; +connection con2; +select variable_value into @a from information_schema.global_status where variable_name='rocksdb_snapshot_conflict_errors'; +select * from test; +id value +1 10 +2 20 +delete from test where value = 20; +connection con1; +commit; +connection con2; +select * from test; +id value +2 30 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test where id = 1; +id value +1 10 +connection con2; +select * from test where id = 1; +id value +1 10 +connection con1; +update test set value = 11 where id = 1; +connection con2; +update test set value = 12 where id = 1; +connection con1; +commit; +connection con2; +select * from test; +id value +1 12 +2 20 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test where id = 1; +id value +1 10 +connection con2; +select * from test where id = 1; +id value +1 10 +select * from test where id = 2; +id value +2 20 +update test set value = 12 where id = 1; +update test set value = 18 where id = 2; +commit; +connection con1; +select * from test where id = 2; +id value +2 18 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test where value % 5 = 0; +id value +1 10 +2 20 +connection con2; +update test set value = 12 where value = 10; +commit; +connection con1; +select * from test where value % 3 = 0; +id value +1 12 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test where id = 1; +id value +1 10 +connection con2; +select * from test; +id value +1 10 +2 20 +update test set value = 12 where id = 1; +update test set value = 18 where id = 2; +commit; +connection con1; +delete from test where value = 20; +select * from test where id = 2; +id value +2 18 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test where id in (1,2); +id value +1 10 +2 20 +connection con2; +select * from test where id in (1,2); +id value +1 10 +2 20 +connection con1; +update test set value = 11 where id = 1; +connection con2; +update test set value = 21 where id = 2; +connection con1; +commit; +connection con2; +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test where value % 3 = 0; +id value +connection con2; +select * from test where value % 3 = 0; +id value +connection con1; +insert into test (id, value) values(3, 30); +connection con2; +insert into test (id, value) values(4, 42); +connection con1; +commit; +connection con2; +commit; +select * from test where value % 3 = 0; +id value +3 30 +4 42 +connection con1; +select * from test where value % 3 = 0; +id value +3 30 +4 42 +connection default; +drop table test; +disconnect con1; +disconnect con2; +disconnect con3; +DROP TABLE IF EXISTS test; +connect con1,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +connect con2,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +connect con3,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +connection con1; +create table test (id int primary key, value int) engine=rocksdb; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test; +id value +1 10 +2 20 +update test set value = 101 where id = 1; +connection con2; +select * from test; +id value +1 10 +2 20 +connection con1; +rollback; +connection con2; +select * from test; +id value +1 10 +2 20 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +update test set value = 101 where id = 1; +connection con2; +select * from test; +id value +1 10 +2 20 +connection con1; +update test set value = 11 where id = 1; +commit; +connection con2; +select * from test; +id value +1 10 +2 20 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +update test set value = 11 where id = 1; +connection con2; +update test set value = 22 where id = 2; +connection con1; +select * from test where id = 2; +id value +2 20 +connection con2; +select * from test where id = 1; +id value +1 10 +connection con1; +commit; +connection con2; +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +update test set value = 11 where id = 1; +update test set value = 19 where id = 2; +connection con2; +update test set value = 12 where id = 1; +connection con1; +commit; +connection con2; +connection con3; +select * from test; +id value +1 11 +2 19 +connection con2; +update test set value = 18 where id = 2; +connection con3; +select * from test; +id value +1 11 +2 19 +connection con2; +commit; +connection con3; +select * from test; +id value +1 11 +2 19 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test where value = 30; +id value +connection con2; +insert into test (id, value) values(3, 30); +commit; +connection con1; +select * from test where value % 3 = 0; +id value +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +update test set value = value + 10; +connection con2; +select variable_value into @a from information_schema.global_status where variable_name='rocksdb_snapshot_conflict_errors'; +select * from test; +id value +1 10 +2 20 +delete from test where value = 20; +connection con1; +commit; +connection con2; +select * from test; +id value +2 20 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test where id = 1; +id value +1 10 +connection con2; +select * from test where id = 1; +id value +1 10 +connection con1; +update test set value = 11 where id = 1; +connection con2; +update test set value = 12 where id = 1; +connection con1; +commit; +connection con2; +select * from test; +id value +1 12 +2 20 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test where id = 1; +id value +1 10 +connection con2; +select * from test where id = 1; +id value +1 10 +select * from test where id = 2; +id value +2 20 +update test set value = 12 where id = 1; +update test set value = 18 where id = 2; +commit; +connection con1; +select * from test where id = 2; +id value +2 20 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test where value % 5 = 0; +id value +1 10 +2 20 +connection con2; +update test set value = 12 where value = 10; +commit; +connection con1; +select * from test where value % 3 = 0; +id value +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test where id = 1; +id value +1 10 +connection con2; +select * from test; +id value +1 10 +2 20 +update test set value = 12 where id = 1; +update test set value = 18 where id = 2; +commit; +connection con1; +delete from test where value = 20; +select * from test where id = 2; +id value +2 20 +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test where id in (1,2); +id value +1 10 +2 20 +connection con2; +select * from test where id in (1,2); +id value +1 10 +2 20 +connection con1; +update test set value = 11 where id = 1; +connection con2; +update test set value = 21 where id = 2; +connection con1; +commit; +connection con2; +commit; +connection con1; +truncate table test; +insert into test (id, value) values (1, 10), (2, 20); +begin; +connection con2; +begin; +connection con3; +begin; +connection con1; +select * from test where value % 3 = 0; +id value +connection con2; +select * from test where value % 3 = 0; +id value +connection con1; +insert into test (id, value) values(3, 30); +connection con2; +insert into test (id, value) values(4, 42); +connection con1; +commit; +connection con2; +commit; +select * from test where value % 3 = 0; +id value +3 30 +4 42 +connection con1; +select * from test where value % 3 = 0; +id value +3 30 +4 42 +connection default; +drop table test; +disconnect con1; +disconnect con2; +disconnect con3; diff --git a/mysql-test/suite/rocksdb/r/issue243_transactionStatus-range_locking.result b/mysql-test/suite/rocksdb/r/issue243_transactionStatus-range_locking.result new file mode 100644 index 00000000000..28bbcb48c96 --- /dev/null +++ b/mysql-test/suite/rocksdb/r/issue243_transactionStatus-range_locking.result @@ -0,0 +1,151 @@ +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( +id INT, +val1 INT, +val2 INT, +PRIMARY KEY (id) +) ENGINE=rocksdb; +INSERT INTO t1 VALUES(1,1,1),(2,1,2); +SELECT * FROM t1; +id val1 val2 +1 1 1 +2 1 2 +UPDATE t1 SET val1=2 WHERE id=2; +SELECT * FROM t1; +id val1 val2 +1 1 1 +2 2 2 +SHOW ENGINE rocksdb TRANSACTION STATUS; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +SET AUTOCOMMIT=0; +START TRANSACTION; +INSERT INTO t1 VALUES(20,1,1),(30,30,30); +SELECT * FROM t1; +id val1 val2 +1 1 1 +2 2 2 +20 1 1 +30 30 30 +UPDATE t1 SET val1=20, val2=20 WHERE id=20; +SELECT * FROM t1; +id val1 val2 +1 1 1 +2 2 2 +20 20 20 +30 30 30 +DELETE FROM t1 WHERE id=30; +SHOW ENGINE rocksdb TRANSACTION STATUS; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +---SNAPSHOT, ACTIVE NUM sec +MySQL thread id TID, OS thread handle PTR, query id QID localhost root ACTION +SHOW ENGINE rocksdb TRANSACTION STATUS +lock count 8, write count 4 +insert count 2, update count 1, delete count 1 +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +ROLLBACK; +SHOW ENGINE rocksdb TRANSACTION STATUS; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +START TRANSACTION; +INSERT INTO t1 VALUES(40,40,40); +SHOW ENGINE rocksdb TRANSACTION STATUS; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +COMMIT; +SHOW ENGINE rocksdb TRANSACTION STATUS; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +SET AUTOCOMMIT=1; +DROP TABLE t1; +DROP TABLE IF EXISTS t2; +CREATE TABLE t2 ( +id1 INT, +id2 INT, +value INT, +PRIMARY KEY (id1), +KEY (id2) +) ENGINE=rocksdb; +SET AUTOCOMMIT=0; +START TRANSACTION; +INSERT INTO t2 VALUES(1,2,0),(10,20,30); +UPDATE t2 SET value=3 WHERE id2=2; +DELETE FROM t2 WHERE id1=10; +SHOW ENGINE rocksdb TRANSACTION STATUS; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +ROLLBACK; +SET AUTOCOMMIT=1; +DROP TABLE t2; diff --git a/mysql-test/suite/rocksdb/r/level_repeatable_read-range_locking.result b/mysql-test/suite/rocksdb/r/level_repeatable_read-range_locking.result new file mode 100644 index 00000000000..37dc45a17f4 --- /dev/null +++ b/mysql-test/suite/rocksdb/r/level_repeatable_read-range_locking.result @@ -0,0 +1,106 @@ +DROP TABLE IF EXISTS t1; +connect con1,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +connect con2,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +connection con1; +CREATE TABLE t1 (a INT, pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=rocksdb; +START TRANSACTION; +SELECT a FROM t1; +a +connection con2; +BEGIN; +INSERT INTO t1 (a) VALUES(1); +connection con1; +SELECT a FROM t1; +a +connection con2; +INSERT INTO t1 (a) VALUES (2); +connection con1; +SELECT a FROM t1; +a +INSERT INTO t1 (a) SELECT a+100 FROM t1; +SELECT a FROM t1; +a +connection con2; +SELECT a FROM t1; +a +1 +2 +COMMIT; +SELECT a FROM t1; +a +1 +2 +connection con1; +SELECT a FROM t1; +a +INSERT INTO t1 (a) SELECT a+200 FROM t1; +SELECT a FROM t1; +a +201 +202 +COMMIT; +SELECT a FROM t1; +a +1 +2 +201 +202 +connection con2; +SELECT a FROM t1; +a +1 +2 +201 +202 +connection default; +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=rocksdb; +INSERT INTO t2 (a) VALUES (1); +COMMIT; +connection con1; +BEGIN; +SELECT a from t2; +a +1 +INSERT INTO t2 (a) VALUES (1), (3); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +connection con2; +INSERT INTO t2 (a) VALUES (2); +COMMIT; +connection con1; +SELECT a from t2; +a +1 +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t1; +DROP TABLE t2; +CREATE TABLE t3 ( +pk int unsigned PRIMARY KEY, +count int unsigned DEFAULT '0' +) ENGINE=ROCKSDB; +connect con1,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +connect con2,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +connection con1; +BEGIN; +SELECT * FROM t3; +pk count +connection con2; +BEGIN; +INSERT INTO t3 (pk) VALUES(1) ON DUPLICATE KEY UPDATE count=count+1; +COMMIT; +connection con1; +INSERT INTO t3 (pk) VALUES(1) ON DUPLICATE KEY UPDATE count=count+1; +COMMIT; +SELECT count FROM t3; +count +1 +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t3; diff --git a/mysql-test/suite/rocksdb/r/range_locking.result b/mysql-test/suite/rocksdb/r/range_locking.result new file mode 100644 index 00000000000..3641fb1943e --- /dev/null +++ b/mysql-test/suite/rocksdb/r/range_locking.result @@ -0,0 +1,516 @@ +show variables like 'rocksdb_use_range_locking'; +Variable_name Value +rocksdb_use_range_locking ON +create table t1 ( +pk int, +a int, +primary key (pk) comment 'default' +) engine=rocksdb; +insert into t1 values +(10,10),(20,20),(30,30); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +### Test: check that range lock inhibits a point lock +connection con1; +begin; +select * from t1 where pk between 5 and 25 for update; +pk a +10 10 +20 20 +connection con2; +insert into t1 values (15,15); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +connection con1; +rollback; +## Test: check that range lock inhibits another range lock +connection con1; +begin; +select * from t1 where pk between 5 and 25 for update; +pk a +10 10 +20 20 +connection con2; +begin; +select * from t1 where pk between 15 and 35 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +rollback; +connection con1; +rollback; +## Test: check that regular read does not get a range lock +connection con1; +begin; +select * from t1 where pk between 5 and 25; +pk a +10 10 +20 20 +connection con2; +begin; +select * from t1 where pk between 15 and 35 for update; +pk a +20 20 +30 30 +rollback; +connection con1; +rollback; +## Test that locks are not released when a statement inside +## a transaction is rolled back +create table t2 ( +pk int, +a int, +primary key (pk) comment 'default', +unique key(a) comment 'default' +) engine=rocksdb; +insert into t2 values (1,1),(2,2); +begin; +insert into t2 values (3,3); +insert into t2 values (10,2); +ERROR 23000: Duplicate entry '2' for key 'a' +connection con2; +begin; +select * from t2 where pk=3 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t2.PRIMARY +rollback; +connection con1; +rollback; +drop table t2; +connection default; +disconnect con1; +disconnect con2; +drop table t1; +# +# Test INFORMATION_SCHEMA.lock_info in range-locking mode +# +connect con1,localhost,root,,; +connection con1; +create table t0 (a int primary key); +begin; +insert into t0 values (1); +connection default; +create table t1 ( +pk int, +a int, +primary key (pk) comment 'default' +) engine=rocksdb; +insert into t1 values +(10,10),(20,20),(30,30); +begin; +select * from t1 where pk=10 for update; +pk a +10 10 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}8000000a X +delete from t1 where pk between 25 and 40; +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}8000000a X +$cf_id $trx_id 00${indexnr}80000019 - 01${indexnr}80000028 X +rollback; +begin; +# The following will show a range lock on 2-9 and also a point lock on 10. +# This is how things currently work. (after MDEV-21314, not anymore) +select * from t1 where pk between 2 and 9 for update; +pk a +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}80000002 - 01${indexnr}80000009 X +rollback; +drop table t1; +connection con1; +rollback; +drop table t0; +connection default; +disconnect con1; +# +# MDEV-18104: MyRocks-Gap-Lock: range locking bounds are incorrect for multi-part keys +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +kp1 int not null, +kp2 int not null, +a int, +primary key(kp1, kp2) comment 'default' +) engine=rocksdb; +insert into t1 select 1, a, 1234 from t0; +insert into t1 select 2, a, 1234 from t0; +insert into t1 select 3, a, 1234 from t0; +connect con1,localhost,root,,; +connection con1; +begin; +select * from t1 where kp1=2 for update; +kp1 kp2 a +2 0 1234 +2 1 1234 +2 2 1234 +2 3 1234 +2 4 1234 +2 5 1234 +2 6 1234 +2 7 1234 +2 8 1234 +2 9 1234 +connection default; +# The lock on kp1=2 should inhibit the following INSERT: +insert into t1 values ( 2,5,9999); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +rollback; +connection con1; +rollback; +connection default; +disconnect con1; +drop table t0,t1; +# +# Test that locks on ranges on non-unique secondary keys inhibit +# modifications of the contents of these ranges +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +kp1 int not null, +kp2 int not null, +a int, +key(kp1, kp2) comment 'default' +) engine=rocksdb; +insert into t1 select 1, a, 1234 from t0; +insert into t1 values (2, 3, 1234); +insert into t1 values (2, 5, 1234); +insert into t1 values (2, 7, 1234); +insert into t1 select 3, a, 1234 from t0; +connect con1,localhost,root,,; +connection con1; +begin; +explain +select * from t1 where kp1=2 for update; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref kp1 kp1 4 const # NULL +select * from t1 where kp1=2 for update; +kp1 kp2 a +2 3 1234 +2 5 1234 +2 7 1234 +connection default; +begin; +insert into t1 values (2, 9, 9999); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.kp1 +delete from t1 where kp1=2 and kp2=5; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.kp1 +update t1 set kp1=333 where kp1=2 and kp2=3; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.kp1 +update t1 set kp1=2 where kp1=1 and kp2=8; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.kp1 +rollback; +connection con1; +rollback; +disconnect con1; +connection default; +drop table t0,t1; +# +# Transaction isolation test +# +create table t1 (pk int primary key, a int) engine=rocksdb; +insert into t1 values (1,1),(2,2),(3,3); +connect con1,localhost,root,,; +# TRX1: Start, Allocate a snapshot +connection con1; +begin; +select * from t1; +pk a +1 1 +2 2 +3 3 +# TRX2: Make a change that TRX1 will not see +connection default; +update t1 set a=2222 where pk=2; +# TRX1: Now, make a change that would overwrite TRX2'x change and commit +connection con1; +update t1 set a=a+1 where pk=2; +commit; +# Examine the result: +# pk=2, a=2223 means UPDATE in TRX1 used "read committed" (InnoDB-like isolation) +# pk=2, a=3 means UPDATE in TRX1 silently overwrote TRX2 +# (and with key tracking, one would get an error on the second UPDATE) +connection default; +select * from t1; +pk a +1 1 +2 2223 +3 3 +disconnect con1; +connection default; +drop table t1; +# +# Same test as above, but check the range scan +# +create table t1 ( +pk int, +a int, +primary key (pk) comment 'default' +) engine=rocksdb; +insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6); +connect con1,localhost,root,,; +# TRX1: Start, Allocate a snapshot +connection con1; +begin; +select * from t1; +pk a +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +# TRX2: Make a change that TRX1 will not see +connection default; +update t1 set a=2222 where pk between 3 and 5; +# TRX1: Now, make a change that would overwrite TRX2'x change and commit +connection con1; +update t1 set a=a+1 where pk between 3 and 5; +commit; +# Examine the result: +# pk={3,4,5} a=2223 means UPDATE in TRX1 used "read committed" (InnoDB-like isolation) +connection default; +select * from t1; +pk a +1 1 +2 2 +3 2223 +4 2223 +5 2223 +6 6 +disconnect con1; +connection default; +drop table t1; +# +# Same as above, but test SELECT FOR UPDATE. +# +create table t1 ( +pk int, +a int, +primary key (pk) comment 'default' +) engine=rocksdb; +insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6); +connect con1,localhost,root,,; +# TRX1: Start, Allocate a snapshot +connection con1; +begin; +select * from t1; +pk a +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +# TRX2: Make a change that TRX1 will not see +connection default; +update t1 set a=222 where pk=2; +update t1 set a=333 where pk=3; +# TRX1: Check what select [FOR UPDATE] sees +connection con1; +select * from t1 where pk in (2,3); +pk a +2 2 +3 3 +select * from t1 where pk=2 for update; +pk a +2 222 +select * from t1 where pk=2; +pk a +2 2 +commit; +disconnect con1; +connection default; +drop table t1; +# +# Another no-snapshot-checking test, this time for single-statement +# transaction +# +create table t1 ( +pk int, +a int, +name varchar(16), +primary key(pk) comment 'default' +) engine=rocksdb; +insert into t1 values (1,1, 'row1'), (2,2,'row2'); +connect con1,localhost,root,,; +connection con1; +select get_lock('row1', 100); +get_lock('row1', 100) +1 +connection default; +# The following will read the first row (1,1,'row1'), and stop. +update t1 set a=a+100 where get_lock(name, 1000)=1; +connection con1; +update t1 set a=5 where pk=2; +select release_lock('row1'); +release_lock('row1') +1 +connection default; +# Look at the row with pk=2: +# 2, 105, row2 - means the UPDATE was reading current data (Correct) +# 2, 102, row - means the UPDATE read the snapshot (incorrect) +select * from t1; +pk a name +1 101 row1 +2 105 row2 +# Try releasing both locks (in 5.6, we will be holding only the second one) +select release_lock(name) from t1; +release_lock(name) +NULL +1 +disconnect con1; +connection default; +drop table t1; +# +# Check that I_S.processlist.state is set correctly now. +# +create table t1( +pk int, +a int, +primary key(pk) comment 'default' +) engine=rocksdb; +insert into t1 values (1,1),(2,2),(3,3); +begin; +select * from t1 where pk=2 for update; +pk a +2 2 +connect con1,localhost,root,,; +begin; +set rocksdb_lock_wait_timeout=300; +select * from t1 where pk=2 for update; +connection default; +# Now, will wait until we see con1 have state="Waiting for row lock" +rollback; +connection con1; +pk a +2 2 +rollback; +disconnect con1; +connection default; +drop table t1; +# +# Test range locking for ranges with HA_READ_PREFIX_LAST +# +create table t0(a int) engine=rocksdb; +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +pk1 int, +pk2 int, +a int, +primary key(pk1, pk2) comment 'default' +) engine=rocksdb; +insert into t1 +select +A.a, B.a, A.a*10+B.a +from +t0 A, t0 B; +connect con1,localhost,root,,; +connection con1; +begin; +insert into t1 values (0x1112222,0x1112222,0); +connection default; +begin; +# Should use ref access w/o filesort: +explain +select * from t1 +where pk1=3 +order by pk1 desc, pk2 desc +for update; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref PRIMARY PRIMARY 4 const # Using where +select * from t1 +where pk1=3 +order by pk1 desc, pk2 desc +for update; +pk1 pk2 a +3 9 39 +3 8 38 +3 7 37 +3 6 36 +3 5 35 +3 4 34 +3 3 33 +3 2 32 +3 1 31 +3 0 30 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}80000003 - 01${indexnr}80000003 X +rollback; +# +# Test range locking for ranges with HA_READ_PREFIX_LAST_OR_PREV +# +begin; +# Should use range access with 2 keyparts and w/o filesort: +explain +select * from t1 +where pk1=4 and pk2 between 5 and 8 +order by pk1 desc, pk2 desc +for update; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where +select * from t1 +where pk1=4 and pk2 between 5 and 8 +order by pk1 desc, pk2 desc +for update; +pk1 pk2 a +4 8 48 +4 7 47 +4 6 46 +4 5 45 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}8000000480000005 - 01${indexnr}8000000480000008 X +rollback; +connection con1; +rollback; +connection default; +drop table t0, t1; +# +# A bug: range locking was not used when scan started at table start or end +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t10(a int); +insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t1 ( +pk int not null, +a int, +primary key(pk) +) engine=rocksdb; +insert into t1 select a*2,a*2 from t10; +connection con1; +begin; +select * from t1 where pk=500 for update; +pk a +500 500 +connection default; +begin; +select * from t1 where pk<10 order by pk limit 10 for update; +pk a +0 0 +2 2 +4 4 +6 6 +8 8 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr} - 00${indexnr}8000000a X +rollback; +begin; +select * from t1 where pk>1990 order by pk desc limit 10 for update; +pk a +1998 1998 +1996 1996 +1994 1994 +1992 1992 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}800007c6 - 00${indexnr+1} X +rollback; +connection con1; +rollback; +disconnect con1; +connection default; +drop table t0,t10,t1; diff --git a/mysql-test/suite/rocksdb/r/range_locking_deadlock_tracking.result b/mysql-test/suite/rocksdb/r/range_locking_deadlock_tracking.result new file mode 100644 index 00000000000..1cfbfeb8f13 --- /dev/null +++ b/mysql-test/suite/rocksdb/r/range_locking_deadlock_tracking.result @@ -0,0 +1,235 @@ +set @prior_lock_wait_timeout = @@rocksdb_lock_wait_timeout; +set @prior_deadlock_detect = @@rocksdb_deadlock_detect; +set @prior_max_latest_deadlocks = @@rocksdb_max_latest_deadlocks; +set global rocksdb_deadlock_detect = on; +set global rocksdb_lock_wait_timeout = 10000; +# Clears deadlock buffer of any prior deadlocks. +set global rocksdb_max_latest_deadlocks = 0; +set global rocksdb_max_latest_deadlocks = @prior_max_latest_deadlocks; +create table t (i int primary key) engine=rocksdb; +insert into t values (1), (2), (3); +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +Deadlock #1 +begin; +select * from t where i=1 for update; +i +1 +begin; +select * from t where i=2 for update; +i +2 +select * from t where i=2 for update; +select * from t where i=1 for update; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +rollback; +i +2 +rollback; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +Deadlock #2 +begin; +select * from t where i=1 for update; +i +1 +begin; +select * from t where i=2 for update; +i +2 +select * from t where i=2 for update; +select * from t where i=1 for update; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +rollback; +i +2 +rollback; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +set global rocksdb_max_latest_deadlocks = 10; +Deadlock #3 +begin; +select * from t where i=1 for update; +i +1 +begin; +select * from t where i=2 for update; +i +2 +select * from t where i=2 for update; +select * from t where i=1 for update; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +rollback; +i +2 +rollback; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +set global rocksdb_max_latest_deadlocks = 1; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +set rocksdb_deadlock_detect_depth = 2; +Deadlock #4 +begin; +select * from t where i=1 for update; +i +1 +begin; +select * from t where i=2 for update; +i +2 +begin; +select * from t where i=3 for update; +i +3 +select * from t where i=2 for update; +select * from t where i=3 for update; +select variable_value into @a from information_schema.global_status where variable_name='rocksdb_row_lock_deadlocks'; +select * from t where i=1 for update; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +select case when variable_value-@a = 1 then 'true' else 'false' end as deadlocks from information_schema.global_status where variable_name='rocksdb_row_lock_deadlocks'; +deadlocks +true +rollback; +i +3 +rollback; +i +2 +rollback; +set global rocksdb_max_latest_deadlocks = 5; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +Deadlock #6 +create table t1 (id int primary key, value int) engine=rocksdb; +insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5); +begin; +update t1 set value=value+100 where id=1; +update t1 set value=value+100 where id=2; +begin; +update t1 set value=value+200 where id=3; +update t1 set value=value+100 where id=3; +update t1 set value=value+200 where id=1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +select * from t1; +id value +1 101 +2 102 +3 103 +4 4 +5 5 +drop table t1; +set global rocksdb_lock_wait_timeout = @prior_lock_wait_timeout; +set global rocksdb_deadlock_detect = @prior_deadlock_detect; +drop table t; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +set global rocksdb_max_latest_deadlocks = 0; +# Clears deadlock buffer of any existent deadlocks. +set global rocksdb_max_latest_deadlocks = @prior_max_latest_deadlocks; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + diff --git a/mysql-test/suite/rocksdb/r/range_locking_escalation.result b/mysql-test/suite/rocksdb/r/range_locking_escalation.result new file mode 100644 index 00000000000..ae4c01cad72 --- /dev/null +++ b/mysql-test/suite/rocksdb/r/range_locking_escalation.result @@ -0,0 +1,27 @@ +show variables like 'rocksdb_use_range_locking'; +Variable_name Value +rocksdb_use_range_locking ON +show variables like 'rocksdb_max_lock_memory'; +Variable_name Value +rocksdb_max_lock_memory 1024 +show status like 'rocksdb_locktree_escalation_count'; +Variable_name Value +rocksdb_locktree_escalation_count 0 +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +pk int primary key, +a int +) engine=rocksdb; +insert into t1 +select +A.a + B.a*10 + C.a*100 + D.a*1000, +12345 +from t0 A, t0 B, t0 C, t0 D; +select count(*) from t1; +count(*) +10000 +show status like 'rocksdb_locktree_escalation_count'; +Variable_name Value +rocksdb_locktree_escalation_count 3321 +drop table t0,t1; diff --git a/mysql-test/suite/rocksdb/r/range_locking_refresh_iter.result b/mysql-test/suite/rocksdb/r/range_locking_refresh_iter.result new file mode 100644 index 00000000000..f96d5f5f45c --- /dev/null +++ b/mysql-test/suite/rocksdb/r/range_locking_refresh_iter.result @@ -0,0 +1,50 @@ +select @@rocksdb_use_range_locking; +@@rocksdb_use_range_locking +1 +set debug_sync='RESET'; +create table ten(a int primary key); +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k(a int primary key); +insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; +create table t1 ( +pk int primary key, +a int +) engine=rocksdb; +insert into t1 select a,a from ten; +insert into t1 select a+40, a+40 from ten; +insert into t1 select a+100, a+100 from one_k; +delete from t1 where pk=44; +set global rocksdb_force_flush_memtable_and_lzero_now=1; +begin; +set debug_sync='rocksdb.check_flags_rmi SIGNAL con1_stopped WAIT_FOR con1_cont'; +update t1 set a=a+100 where pk < 3 or pk between 10 and 50; +set debug_sync='now WAIT_FOR con1_stopped'; +insert into t1 values (44,5000); +delete from t1 where pk= 42; +update t1 set a=5000 where pk between 40 and 45; +set global rocksdb_force_flush_memtable_and_lzero_now=1; +set debug_sync='now SIGNAL con1_cont'; +select * from t1 where pk<100; +pk a +0 100 +1 101 +2 102 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +40 5100 +41 5100 +43 5100 +44 5100 +45 5100 +46 146 +47 147 +48 148 +49 149 +commit; +set debug_sync='RESET'; +drop table t1, ten, one_k; diff --git a/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result b/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result new file mode 100644 index 00000000000..d33d4872fab --- /dev/null +++ b/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result @@ -0,0 +1,476 @@ +show variables like 'rocksdb_use_range_locking'; +Variable_name Value +rocksdb_use_range_locking ON +create table t1 ( +pk int, +a int, +primary key (pk) comment 'rev:cf1' +) engine=rocksdb; +insert into t1 values +(10,10),(20,20),(30,30); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +### Test: check that range lock inhibits a point lock +connection con1; +begin; +select * from t1 where pk between 5 and 25 for update; +pk a +10 10 +20 20 +connection con2; +insert into t1 values (15,15); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +connection con1; +rollback; +## Test: check that range lock inhibits another range lock +connection con1; +begin; +select * from t1 where pk between 5 and 25 for update; +pk a +10 10 +20 20 +connection con2; +begin; +select * from t1 where pk between 15 and 35 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +rollback; +connection con1; +rollback; +## Test: check that regular read does not get a range lock +connection con1; +begin; +select * from t1 where pk between 5 and 25; +pk a +10 10 +20 20 +connection con2; +begin; +select * from t1 where pk between 15 and 35 for update; +pk a +20 20 +30 30 +rollback; +connection con1; +rollback; +## Test that locks are not released when a statement inside +## a transaction is rolled back +create table t2 ( +pk int, +a int, +primary key (pk) comment 'rev:cf1', +unique key(a) comment '' +) engine=rocksdb; +insert into t2 values (1,1),(2,2); +begin; +insert into t2 values (3,3); +insert into t2 values (10,2); +ERROR 23000: Duplicate entry '2' for key 'a' +connection con2; +begin; +select * from t2 where pk=3 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t2.PRIMARY +rollback; +connection con1; +rollback; +drop table t2; +connection default; +disconnect con1; +disconnect con2; +drop table t1; +# +# Test INFORMATION_SCHEMA.lock_info in range-locking mode +# +connect con1,localhost,root,,; +connection con1; +create table t0 (a int primary key); +begin; +insert into t0 values (1); +connection default; +create table t1 ( +pk int, +a int, +primary key (pk) comment 'rev:cf1' +) engine=rocksdb; +insert into t1 values +(10,10),(20,20),(30,30); +begin; +select * from t1 where pk=10 for update; +pk a +10 10 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}8000000a X +delete from t1 where pk between 25 and 40; +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}8000000a X +$cf_id $trx_id 00${indexnr}80000028 - 01${indexnr}80000019 X +rollback; +begin; +# The following will show a range lock on 2-9 and also a point lock on 10. +# This is how things currently work. (after MDEV-21314, not anymore) +select * from t1 where pk between 2 and 9 for update; +pk a +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}80000009 - 01${indexnr}80000002 X +rollback; +drop table t1; +connection con1; +rollback; +drop table t0; +connection default; +disconnect con1; +# +# MDEV-18104: MyRocks-Gap-Lock: range locking bounds are incorrect for multi-part keys +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +kp1 int not null, +kp2 int not null, +a int, +primary key(kp1, kp2) comment 'rev:cf1' +) engine=rocksdb; +insert into t1 select 1, a, 1234 from t0; +insert into t1 select 2, a, 1234 from t0; +insert into t1 select 3, a, 1234 from t0; +connect con1,localhost,root,,; +connection con1; +begin; +select * from t1 where kp1=2 for update; +kp1 kp2 a +2 0 1234 +2 1 1234 +2 2 1234 +2 3 1234 +2 4 1234 +2 5 1234 +2 6 1234 +2 7 1234 +2 8 1234 +2 9 1234 +connection default; +# The lock on kp1=2 should inhibit the following INSERT: +insert into t1 values ( 2,5,9999); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +rollback; +connection con1; +rollback; +connection default; +disconnect con1; +drop table t0,t1; +# +# Test that locks on ranges on non-unique secondary keys inhibit +# modifications of the contents of these ranges +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +kp1 int not null, +kp2 int not null, +a int, +key(kp1, kp2) comment 'rev:cf1' +) engine=rocksdb; +insert into t1 select 1, a, 1234 from t0; +insert into t1 values (2, 3, 1234); +insert into t1 values (2, 5, 1234); +insert into t1 values (2, 7, 1234); +insert into t1 select 3, a, 1234 from t0; +connect con1,localhost,root,,; +connection con1; +begin; +explain +select * from t1 where kp1=2 for update; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref kp1 kp1 4 const # NULL +select * from t1 where kp1=2 for update; +kp1 kp2 a +2 3 1234 +2 5 1234 +2 7 1234 +connection default; +begin; +insert into t1 values (2, 9, 9999); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.kp1 +delete from t1 where kp1=2 and kp2=5; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.kp1 +update t1 set kp1=333 where kp1=2 and kp2=3; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.kp1 +update t1 set kp1=2 where kp1=1 and kp2=8; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.kp1 +rollback; +connection con1; +rollback; +disconnect con1; +connection default; +drop table t0,t1; +# +# Transaction isolation test +# +create table t1 (pk int primary key, a int) engine=rocksdb; +insert into t1 values (1,1),(2,2),(3,3); +connect con1,localhost,root,,; +# TRX1: Start, Allocate a snapshot +connection con1; +begin; +select * from t1; +pk a +1 1 +2 2 +3 3 +# TRX2: Make a change that TRX1 will not see +connection default; +update t1 set a=2222 where pk=2; +# TRX1: Now, make a change that would overwrite TRX2'x change and commit +connection con1; +update t1 set a=a+1 where pk=2; +commit; +# Examine the result: +# pk=2, a=2223 means UPDATE in TRX1 used "read committed" (InnoDB-like isolation) +# pk=2, a=3 means UPDATE in TRX1 silently overwrote TRX2 +# (and with key tracking, one would get an error on the second UPDATE) +connection default; +select * from t1; +pk a +1 1 +2 2223 +3 3 +disconnect con1; +connection default; +drop table t1; +# +# Same test as above, but check the range scan +# +create table t1 ( +pk int, +a int, +primary key (pk) comment 'rev:cf1' +) engine=rocksdb; +insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6); +connect con1,localhost,root,,; +# TRX1: Start, Allocate a snapshot +connection con1; +begin; +select * from t1; +pk a +6 6 +5 5 +4 4 +3 3 +2 2 +1 1 +# TRX2: Make a change that TRX1 will not see +connection default; +update t1 set a=2222 where pk between 3 and 5; +# TRX1: Now, make a change that would overwrite TRX2'x change and commit +connection con1; +update t1 set a=a+1 where pk between 3 and 5; +commit; +# Examine the result: +# pk={3,4,5} a=2223 means UPDATE in TRX1 used "read committed" (InnoDB-like isolation) +connection default; +select * from t1; +pk a +6 6 +5 2223 +4 2223 +3 2223 +2 2 +1 1 +disconnect con1; +connection default; +drop table t1; +# +# Same as above, but test SELECT FOR UPDATE. +# +create table t1 ( +pk int, +a int, +primary key (pk) comment 'rev:cf1' +) engine=rocksdb; +insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6); +connect con1,localhost,root,,; +# TRX1: Start, Allocate a snapshot +connection con1; +begin; +select * from t1; +pk a +6 6 +5 5 +4 4 +3 3 +2 2 +1 1 +# TRX2: Make a change that TRX1 will not see +connection default; +update t1 set a=222 where pk=2; +update t1 set a=333 where pk=3; +# TRX1: Check what select [FOR UPDATE] sees +connection con1; +select * from t1 where pk in (2,3); +pk a +2 2 +3 3 +select * from t1 where pk=2 for update; +pk a +2 222 +select * from t1 where pk=2; +pk a +2 2 +commit; +disconnect con1; +connection default; +drop table t1; +# +# Check that I_S.processlist.state is set correctly now. +# +create table t1( +pk int, +a int, +primary key(pk) comment 'rev:cf1' +) engine=rocksdb; +insert into t1 values (1,1),(2,2),(3,3); +begin; +select * from t1 where pk=2 for update; +pk a +2 2 +connect con1,localhost,root,,; +begin; +set rocksdb_lock_wait_timeout=300; +select * from t1 where pk=2 for update; +connection default; +# Now, will wait until we see con1 have state="Waiting for row lock" +rollback; +connection con1; +pk a +2 2 +rollback; +disconnect con1; +connection default; +drop table t1; +# +# Test range locking for ranges with HA_READ_PREFIX_LAST +# +create table t0(a int) engine=rocksdb; +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +pk1 int, +pk2 int, +a int, +primary key(pk1, pk2) comment 'rev:cf1' +) engine=rocksdb; +insert into t1 +select +A.a, B.a, A.a*10+B.a +from +t0 A, t0 B; +connect con1,localhost,root,,; +connection con1; +begin; +insert into t1 values (0x1112222,0x1112222,0); +connection default; +begin; +# Should use ref access w/o filesort: +explain +select * from t1 +where pk1=3 +order by pk1 desc, pk2 desc +for update; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref PRIMARY PRIMARY 4 const # Using where +select * from t1 +where pk1=3 +order by pk1 desc, pk2 desc +for update; +pk1 pk2 a +3 9 39 +3 8 38 +3 7 37 +3 6 36 +3 5 35 +3 4 34 +3 3 33 +3 2 32 +3 1 31 +3 0 30 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}80000003 - 01${indexnr}80000003 X +rollback; +# +# Test range locking for ranges with HA_READ_PREFIX_LAST_OR_PREV +# +begin; +# Should use range access with 2 keyparts and w/o filesort: +explain +select * from t1 +where pk1=4 and pk2 between 5 and 8 +order by pk1 desc, pk2 desc +for update; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where +select * from t1 +where pk1=4 and pk2 between 5 and 8 +order by pk1 desc, pk2 desc +for update; +pk1 pk2 a +4 8 48 +4 7 47 +4 6 46 +4 5 45 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}8000000480000008 - 01${indexnr}8000000480000005 X +rollback; +connection con1; +rollback; +connection default; +drop table t0, t1; +# +# A bug: range locking was not used when scan started at table start or end +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t10(a int); +insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t1 ( +pk int not null, +a int, +primary key(pk) +) engine=rocksdb; +insert into t1 select a*2,a*2 from t10; +connection con1; +begin; +select * from t1 where pk=500 for update; +pk a +500 500 +connection default; +begin; +select * from t1 where pk<10 order by pk limit 10 for update; +pk a +0 0 +2 2 +4 4 +6 6 +8 8 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr} - 00${indexnr}8000000a X +rollback; +begin; +select * from t1 where pk>1990 order by pk desc limit 10 for update; +pk a +1998 1998 +1996 1996 +1994 1994 +1992 1992 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}800007c6 - 00${indexnr+1} X +rollback; +connection con1; +rollback; +disconnect con1; +connection default; +drop table t0,t10,t1; diff --git a/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result b/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result new file mode 100644 index 00000000000..cddf039bc4f --- /dev/null +++ b/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result @@ -0,0 +1,271 @@ +show variables like 'rocksdb_use_range_locking'; +Variable_name Value +rocksdb_use_range_locking ON +create table t0(a int primary key); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +pk int, +a int, +primary key (pk) +) engine=rocksdb; +insert into t1 select +A.a + B.a*10 + C.a*100, +A.a + B.a*10 + C.a*100 +from +t0 A, t0 B, t0 C; +# Make another connection to get the lock tree out of the STO-mode +connect con1,localhost,root,,; +connection con1; +begin; +select * from t1 where pk=10 for update; +pk a +10 10 +connection default; +begin; +select * from t1 where pk=11 for update; +pk a +11 11 +# Now, we will just see locks on 10=0xA and 11=0xB: +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}8000000b X +# +# SeekForUpdate Test #1: A query with type=range (without upper bound) and LIMIT +# +explain +select * from t1 where pk>=500 order by pk limit 3 for update; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where +select * from t1 where pk>=500 order by pk limit 3 for update; +pk a +500 500 +501 501 +502 502 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}8000000b X +$cf_id $trx_id 00${indexnr}800001f4 - 00${indexnr}800001f6 X +rollback; +begin; +select * from t1 where pk=11 for update; +pk a +11 11 +explain +select * from t1 order by pk limit 3 for update; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 3 NULL +select * from t1 order by pk limit 3 for update; +pk a +0 0 +1 1 +2 2 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr} - 00${indexnr}80000002 X +$cf_id $trx_id 00${indexnr}8000000b X +rollback; +connection con1; +rollback; +disconnect con1; +connection default; +drop table t0, t1; +# +# Concurrent tests: let one thread do SeekForUpdate and the other +# interfere by committing modifications +# +create table t0(a int primary key); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +pk int, +a int, +primary key (pk) +) engine=rocksdb; +insert into t1 select +A.a + B.a*10 + C.a*100, +A.a + B.a*10 + C.a*100 +from +t0 A, t0 B, t0 C; +select * from t1 where pk<10; +pk a +0 0 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +delete from t1 where pk<10; +select * from t1 where pk<10; +pk a +# Test what happens when another transaction commits a row +# right before the range we are about to lock (nothing) +explain +select * from t1 where pk >=5 order by pk limit 3 for update; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where +begin; +set debug_sync='rocksdb.locking_iter_scan SIGNAL about_to_lock_range WAIT_FOR spoiler_inserted'; +select * from t1 where pk >=5 order by pk limit 3 for update; +connect con1,localhost,root,,; +connection con1; +set debug_sync='now WAIT_FOR about_to_lock_range'; +insert into t1 values (3,3); +set debug_sync='now SIGNAL spoiler_inserted'; +connection default; +pk a +10 10 +11 11 +12 12 +rollback; +delete from t1 where pk=3; +# +# Now, repeat the test but let the other transaction insert the row into +# the range we are locking +explain +select * from t1 where pk >=5 order by pk limit 1 for update; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where +begin; +set debug_sync='rocksdb.locking_iter_scan SIGNAL about_to_lock_range WAIT_FOR spoiler_inserted'; +select * from t1 where pk >=5 order by pk limit 1 for update; +connection con1; +set debug_sync='now WAIT_FOR about_to_lock_range'; +insert into t1 values (8,8); +set debug_sync='now SIGNAL spoiler_inserted'; +connection default; +pk a +8 8 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}80000005 - 00${indexnr}8000000a X +rollback; +delete from t1 where pk=8; +# +# Repeat the third time, this time deleting the row that SeekForUpdate saw +# +insert into t1 values (7,7); +begin; +set debug_sync='rocksdb.locking_iter_scan SIGNAL about_to_lock_range WAIT_FOR spoiler_inserted'; +select * from t1 where pk >=5 order by pk limit 1 for update; +connection con1; +set debug_sync='now WAIT_FOR about_to_lock_range'; +delete from t1 where pk=7; +set debug_sync='now SIGNAL spoiler_inserted'; +connection default; +pk a +10 10 +rollback; +# +# Repeat the above test, but let the read fail with ER_LOCK_WAIT_TIMEOUT +# error. MyRocks code should now be prepared that data reads cause this +# error +# +insert into t1 values (7,7); +begin; +set debug_sync='rocksdb.locking_iter_scan SIGNAL about_to_lock_range WAIT_FOR spoiler_inserted'; +select * from t1 where pk >=5 order by pk limit 1 for update; +connection con1; +set debug_sync='now WAIT_FOR about_to_lock_range'; +begin; +delete from t1 where pk=7; +set debug_sync='now SIGNAL spoiler_inserted'; +connection default; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +rollback; +connection con1; +rollback; +connection default; +# +# Backward scan test +# +connection con1; +begin; +select * from t1 where pk=500 for update; +pk a +500 500 +connection default; +insert into t1 values +(1001, 1001), +(1005, 1005), +(1007, 1007), +(1010, 1010); +begin; +select * from t1 order by pk desc limit 2 for update; +pk a +1010 1010 +1007 1007 +# The below will lock from pk=1007 (0x3ef) till the end of the table: +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}800003ef - 00${indexnr+1} X +rollback; +begin; +select * from t1 where pk <1007 order by pk desc limit 2 for update; +pk a +1005 1005 +1001 1001 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}800003e9 - 00${indexnr}800003ef X +connection con1; +rollback; +connection default; +rollback; +# +# Backward scan test 2: error condition +# +connection con1; +begin; +select * from t1 where pk=1010 for update; +pk a +1010 1010 +connection default; +begin; +select * from t1 order by pk desc limit 2 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +rollback; +connection con1; +rollback; +begin; +select * from t1 where pk=1007 for update; +pk a +1007 1007 +connection default; +begin; +select * from t1 order by pk desc limit 2 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +rollback; +connection con1; +rollback; +disconnect con1; +connection default; +drop table t0,t1; +# +# A test: full table scan doesn't lock gaps +# +create table t1 ( +pk int primary key, +a int +) engine=rocksdb; +insert into t1 values (10,10),(20,20),(30,30); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +begin; +select * from t1 for update; +pk a +10 10 +20 20 +30 30 +connection con2; +insert into t1 values (5,5); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +connection con1; +rollback; +disconnect con1; +disconnect con2; +connection default; +drop table t1; diff --git a/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result b/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result new file mode 100644 index 00000000000..b50e383fd82 --- /dev/null +++ b/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result @@ -0,0 +1,249 @@ +select @@rocksdb_use_range_locking; +@@rocksdb_use_range_locking +1 +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +pk int primary key, +a int +) engine=rocksdb; +insert into t1 select a,a from t0; +# A basic test for shared locks +begin; +select * from t1 where pk=3 for update; +pk a +3 3 +select * from t1 where pk=5 lock in share mode; +pk a +5 5 +connect con1,localhost,root,,; +connection con1; +begin; +select * from t1 where pk=5 lock in share mode; +pk a +5 5 +# Now for pk=5 we should see two locks by TRX1 and TRX2 with mode=S: +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $TRX1_ID 00${indexnr}80000003 X +$cf_id $TRX1_ID 00${indexnr}80000005 S +$cf_id $TRX2_ID 00${indexnr}80000005 S +rollback; +# Now, TRX2_ID should be gone: +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $TRX1_ID 00${indexnr}80000003 X +$cf_id $TRX1_ID 00${indexnr}80000005 S +connection default; +# Get a read lock on pk=3 (where we have a write lock). +# The result should be that we will still have a write lock +select * from t1 where pk=3 for update; +pk a +3 3 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $TRX1_ID 00${indexnr}80000003 X +$cf_id $TRX1_ID 00${indexnr}80000005 S +# Get a write lock on pk=5 (where we have a read lock). +# The result should be that we will have a write lock. +select * from t1 where pk=5 for update; +pk a +5 5 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $TRX1_ID 00${indexnr}80000003 X +$cf_id $TRX1_ID 00${indexnr}80000005 X +connection default; +rollback; +# +# Test if a read lock inhibits write locks +# +begin; +select * from t1 where pk=2 lock in share mode; +pk a +2 2 +select * from t1 where pk=8 for update; +pk a +8 8 +connection con1; +begin; +select * from t1 where pk=2 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +select * from t1 where pk between 0 and 4 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +delete from t1 where pk=2; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +# Get a shared lock +select * from t1 where pk=2 lock in share mode; +pk a +2 2 +# But this should still prevent us from acquiring a write lock on that value: +select * from t1 where pk=2 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +rollback; +connection default; +rollback; +drop table t1; +create table t1 ( +pk int not null primary key, +a int not null, +key(a) +) engine=rocksdb; +insert into t1 +select +A.a+10*B.a+100*C.a+1000*D.a, A.a+10*B.a+100*C.a+1000*D.a +from +t0 A, t0 B, t0 C, t0 D; +set global rocksdb_force_flush_memtable_now=1; +connection con1; +begin; +select * from t1 where pk=900 for update; +pk a +900 900 +connection default; +begin; +explain +select * from t1 where a between 2 and 5 lock in share mode; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 4 NULL # Using where; Using index +select * from t1 where a between 2 and 5 lock in share mode; +pk a +2 2 +3 3 +4 4 +5 5 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $TRX1_ID 00${indexnr+1}80000002 - 01${indexnr+1}80000005 X +$cf_id $TRX1_ID 00${indexnr}80000002 S +$cf_id $TRX1_ID 00${indexnr}80000003 S +$cf_id $TRX1_ID 00${indexnr}80000004 S +$cf_id $TRX1_ID 00${indexnr}80000005 S +$cf_id $TRX1_ID 00${indexnr}80000006 S +$cf_id $TRX2_ID 00${indexnr}80000384 X +rollback; +disconnect con1; +drop table t0,t1; +# +# Test shared point locks and lock escalation +# +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +pk int primary key, +a int +) engine=rocksdb; +insert into t1 +select 1000 + 100*A.a + 10*B.a + C.a, 12345 from t0 A, t0 B, t0 C; +show status like 'rocksdb_locktree_current_lock_memory'; +Variable_name Value +rocksdb_locktree_current_lock_memory 0 +connect con1,localhost,root,,; +connection con1; +begin; +# CON1: get some shared locks +select * from t1 where pk=1001 lock in share mode; +pk a +1001 12345 +select * from t1 where pk=1100 lock in share mode; +pk a +1100 12345 +select * from t1 where pk=1200 lock in share mode; +pk a +1200 12345 +select * from t1 where pk=2500 lock in share mode; +pk a +connection default; +begin; +# DEFAULT: get the same locks so we have locks with multiple owners +select * from t1 where pk=1001 lock in share mode; +pk a +1001 12345 +select * from t1 where pk=1100 lock in share mode; +pk a +1100 12345 +select * from t1 where pk=1200 lock in share mode; +pk a +1200 12345 +# DEFAULT: get shared locks with one owner: +select * from t1 where pk=2510 lock in share mode; +pk a +# DEFAULT: exclusive locks on 0-10: +insert into t1 select A.a, 0 from t0 A; +connection con1; +# CON1: exclusive locks on 2000-2010: +insert into t1 select 2000+A.a, 0 from t0 A; +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $TRX2_ID 00${indexnr}80000000 X +$cf_id $TRX2_ID 00${indexnr}80000001 X +$cf_id $TRX2_ID 00${indexnr}80000002 X +$cf_id $TRX2_ID 00${indexnr}80000003 X +$cf_id $TRX2_ID 00${indexnr}80000004 X +$cf_id $TRX2_ID 00${indexnr}80000005 X +$cf_id $TRX2_ID 00${indexnr}80000006 X +$cf_id $TRX2_ID 00${indexnr}80000007 X +$cf_id $TRX2_ID 00${indexnr}80000008 X +$cf_id $TRX2_ID 00${indexnr}80000009 X +$cf_id $TRX1_ID 00${indexnr}800003e9 S +$cf_id $TRX2_ID 00${indexnr}800003e9 S +$cf_id $TRX1_ID 00${indexnr}8000044c S +$cf_id $TRX2_ID 00${indexnr}8000044c S +$cf_id $TRX1_ID 00${indexnr}800004b0 S +$cf_id $TRX2_ID 00${indexnr}800004b0 S +$cf_id $TRX1_ID 00${indexnr}800007d0 X +$cf_id $TRX1_ID 00${indexnr}800007d1 X +$cf_id $TRX1_ID 00${indexnr}800007d2 X +$cf_id $TRX1_ID 00${indexnr}800007d3 X +$cf_id $TRX1_ID 00${indexnr}800007d4 X +$cf_id $TRX1_ID 00${indexnr}800007d5 X +$cf_id $TRX1_ID 00${indexnr}800007d6 X +$cf_id $TRX1_ID 00${indexnr}800007d7 X +$cf_id $TRX1_ID 00${indexnr}800007d8 X +$cf_id $TRX1_ID 00${indexnr}800007d9 X +$cf_id $TRX1_ID 00${indexnr}800009c4 S +$cf_id $TRX2_ID 00${indexnr}800009ce S +connection default; +show status like 'rocksdb_locktree_current_lock_memory'; +Variable_name Value +rocksdb_locktree_current_lock_memory 7896 +set @save_mlm= @@rocksdb_max_lock_memory; +# Set the limit to cause lock escalation: +set @cur_mem_usage= (select +variable_value +from +information_schema.GLOBAL_STATUS +where +variable_name='rocksdb_locktree_current_lock_memory'); +set global rocksdb_max_lock_memory = cast(@cur_mem_usage+4 as SIGNED); +connection con1; +insert into t1 select 3000+A.a, 0 from t0 A; +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $TRX2_ID 00${indexnr}80000000 - 00${indexnr}80000009 X +$cf_id $TRX1_ID 00${indexnr}800003e9 S +$cf_id $TRX2_ID 00${indexnr}800003e9 S +$cf_id $TRX1_ID 00${indexnr}8000044c S +$cf_id $TRX2_ID 00${indexnr}8000044c S +$cf_id $TRX1_ID 00${indexnr}800004b0 S +$cf_id $TRX2_ID 00${indexnr}800004b0 S +$cf_id $TRX1_ID 00${indexnr}800007d0 - 00${indexnr}800007d9 X +$cf_id $TRX1_ID 00${indexnr}800009c4 S +$cf_id $TRX2_ID 00${indexnr}800009ce S +$cf_id $TRX1_ID 00${indexnr}80000bb8 X +$cf_id $TRX1_ID 00${indexnr}80000bb9 X +$cf_id $TRX1_ID 00${indexnr}80000bba X +$cf_id $TRX1_ID 00${indexnr}80000bbb X +$cf_id $TRX1_ID 00${indexnr}80000bbc X +$cf_id $TRX1_ID 00${indexnr}80000bbd X +$cf_id $TRX1_ID 00${indexnr}80000bbe X +$cf_id $TRX1_ID 00${indexnr}80000bbf X +$cf_id $TRX1_ID 00${indexnr}80000bc0 X +$cf_id $TRX1_ID 00${indexnr}80000bc1 X +connection con1; +rollback; +connection default; +rollback; +disconnect con1; +set global rocksdb_max_lock_memory= cast(@save_mlm as SIGNED); +drop table t0, t1; diff --git a/mysql-test/suite/rocksdb/t/hermitage-range_locking.test b/mysql-test/suite/rocksdb/t/hermitage-range_locking.test new file mode 100644 index 00000000000..55203af9cf8 --- /dev/null +++ b/mysql-test/suite/rocksdb/t/hermitage-range_locking.test @@ -0,0 +1,15 @@ +--source include/have_rocksdb.inc + +# Range locking uses InnoDB-like transaction isolation, which +# means the results differ from "true" Repeatable Read. +--source suite/rocksdb/include/have_range_locking.inc + + +# Hermitage is an attempt to test transaction isolation levels. +# https://github.com/ept/hermitage + +let $trx_isolation = READ COMMITTED; +--source hermitage.inc + +let $trx_isolation = REPEATABLE READ; +--source hermitage.inc diff --git a/mysql-test/suite/rocksdb/t/issue243_transactionStatus-range_locking.test b/mysql-test/suite/rocksdb/t/issue243_transactionStatus-range_locking.test new file mode 100644 index 00000000000..465fb9099da --- /dev/null +++ b/mysql-test/suite/rocksdb/t/issue243_transactionStatus-range_locking.test @@ -0,0 +1,10 @@ +# +# A range-locking variant of issue243_transactionStatus.test + +--source include/have_rocksdb.inc +--source suite/rocksdb/include/have_range_locking.inc + +let $forced_range_locking=1; +--source issue243_transactionStatus.test + + diff --git a/mysql-test/suite/rocksdb/t/level_repeatable_read-range_locking.test b/mysql-test/suite/rocksdb/t/level_repeatable_read-range_locking.test new file mode 100644 index 00000000000..6c42c7be12c --- /dev/null +++ b/mysql-test/suite/rocksdb/t/level_repeatable_read-range_locking.test @@ -0,0 +1,9 @@ +--source include/have_rocksdb.inc + +# Range locking uses InnoDB-like transaction isolation, which +# means the results differ from "true" Repeatable Read. +--source suite/rocksdb/include/have_range_locking.inc + +let $trx_isolation = REPEATABLE READ; +--source transaction_isolation.inc + diff --git a/mysql-test/suite/rocksdb/t/range_locking.inc b/mysql-test/suite/rocksdb/t/range_locking.inc new file mode 100644 index 00000000000..c676ccb66db --- /dev/null +++ b/mysql-test/suite/rocksdb/t/range_locking.inc @@ -0,0 +1,544 @@ +# +# Range locking tests. +# + +--source include/have_rocksdb.inc +--source suite/rocksdb/include/have_range_locking.inc + +--enable_connect_log + + +show variables like 'rocksdb_use_range_locking'; + +eval create table t1 ( + pk int, + a int, + primary key (pk) comment '$pk_cf' +) engine=rocksdb; + +insert into t1 values +(10,10),(20,20),(30,30); + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); + +--echo ### Test: check that range lock inhibits a point lock +connection con1; +begin; +select * from t1 where pk between 5 and 25 for update; + +connection con2; +--error ER_LOCK_WAIT_TIMEOUT +insert into t1 values (15,15); + +connection con1; +rollback; + +--echo ## Test: check that range lock inhibits another range lock +connection con1; +begin; +select * from t1 where pk between 5 and 25 for update; + +connection con2; +begin; +--error ER_LOCK_WAIT_TIMEOUT +select * from t1 where pk between 15 and 35 for update; +rollback; + +connection con1; +rollback; + +--echo ## Test: check that regular read does not get a range lock +connection con1; +begin; +select * from t1 where pk between 5 and 25; + +connection con2; +begin; +# This must not block +select * from t1 where pk between 15 and 35 for update; +rollback; + +connection con1; +rollback; + +--echo ## Test that locks are not released when a statement inside +--echo ## a transaction is rolled back +eval +create table t2 ( + pk int, + a int, + primary key (pk) comment '$pk_cf', + unique key(a) comment '$sk_cf' +) engine=rocksdb; + +insert into t2 values (1,1),(2,2); + +begin; +insert into t2 values (3,3); +--error ER_DUP_ENTRY +insert into t2 values (10,2); + +connection con2; +begin; +# This must time out: +--error ER_LOCK_WAIT_TIMEOUT +select * from t2 where pk=3 for update; + +rollback; +connection con1; +rollback; +drop table t2; + +# cleanup +connection default; +disconnect con1; +disconnect con2; +drop table t1; + +--echo # +--echo # Test INFORMATION_SCHEMA.lock_info in range-locking mode +--echo # + +connect (con1,localhost,root,,); +connection con1; +eval create table t0 (a int primary key); +begin; +insert into t0 values (1); +connection default; + + +eval +create table t1 ( + pk int, + a int, + primary key (pk) comment '$pk_cf' +) engine=rocksdb; + +insert into t1 values +(10,10),(20,20),(30,30); + +begin; +select * from t1 where pk=10 for update; + +#let TRX1_ID=`(select transaction_id from information_schema.rocksdb_trx where thread_id=connection_id())` ; +let $select_from_is_rowlocks_current_trx_only=1; +--source suite/rocksdb/include/select_from_is_rowlocks.inc + +delete from t1 where pk between 25 and 40; + +--source suite/rocksdb/include/select_from_is_rowlocks.inc + +rollback; +begin; +--echo # The following will show a range lock on 2-9 and also a point lock on 10. +--echo # This is how things currently work. (after MDEV-21314, not anymore) +select * from t1 where pk between 2 and 9 for update; +--source suite/rocksdb/include/select_from_is_rowlocks.inc +rollback; + +drop table t1; +connection con1; +rollback; +drop table t0; +connection default; +disconnect con1; + +--echo # +--echo # MDEV-18104: MyRocks-Gap-Lock: range locking bounds are incorrect for multi-part keys +--echo # + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +eval +create table t1 ( + kp1 int not null, + kp2 int not null, + a int, + primary key(kp1, kp2) comment '$pk_cf' +) engine=rocksdb; + +insert into t1 select 1, a, 1234 from t0; +insert into t1 select 2, a, 1234 from t0; +insert into t1 select 3, a, 1234 from t0; + +connect (con1,localhost,root,,); +connection con1; + +begin; +select * from t1 where kp1=2 for update; + +connection default; +--echo # The lock on kp1=2 should inhibit the following INSERT: +--error ER_LOCK_WAIT_TIMEOUT +insert into t1 values ( 2,5,9999); +rollback; + +connection con1; +rollback; +connection default; +disconnect con1; +drop table t0,t1; + +--echo # +--echo # Test that locks on ranges on non-unique secondary keys inhibit +--echo # modifications of the contents of these ranges +--echo # + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +eval +create table t1 ( + kp1 int not null, + kp2 int not null, + a int, + key(kp1, kp2) comment '$pk_cf' +) engine=rocksdb; + +insert into t1 select 1, a, 1234 from t0; +insert into t1 values (2, 3, 1234); +insert into t1 values (2, 5, 1234); +insert into t1 values (2, 7, 1234); +insert into t1 select 3, a, 1234 from t0; + +connect (con1,localhost,root,,); +connection con1; +begin; +--replace_column 9 # +explain +select * from t1 where kp1=2 for update; +select * from t1 where kp1=2 for update; + +connection default; +begin; +--error ER_LOCK_WAIT_TIMEOUT +insert into t1 values (2, 9, 9999); + +--error ER_LOCK_WAIT_TIMEOUT +delete from t1 where kp1=2 and kp2=5; + +# Update that "moves a row away" from the locked range +--error ER_LOCK_WAIT_TIMEOUT +update t1 set kp1=333 where kp1=2 and kp2=3; + +# Update that "moves a row into" the locked range +--error ER_LOCK_WAIT_TIMEOUT +update t1 set kp1=2 where kp1=1 and kp2=8; + +rollback; + +connection con1; +rollback; +disconnect con1; +connection default; +drop table t0,t1; + +--echo # +--echo # Transaction isolation test +--echo # + +create table t1 (pk int primary key, a int) engine=rocksdb; +insert into t1 values (1,1),(2,2),(3,3); + +connect (con1,localhost,root,,); + +--echo # TRX1: Start, Allocate a snapshot +connection con1; +begin; +select * from t1; + +--echo # TRX2: Make a change that TRX1 will not see +connection default; +update t1 set a=2222 where pk=2; + +--echo # TRX1: Now, make a change that would overwrite TRX2'x change and commit +connection con1; +update t1 set a=a+1 where pk=2; +commit; + +--echo # Examine the result: +--echo # pk=2, a=2223 means UPDATE in TRX1 used "read committed" (InnoDB-like isolation) +--echo # pk=2, a=3 means UPDATE in TRX1 silently overwrote TRX2 +--echo # (and with key tracking, one would get an error on the second UPDATE) +connection default; +select * from t1; + +disconnect con1; +connection default; +drop table t1; + +--echo # +--echo # Same test as above, but check the range scan +--echo # + +eval +create table t1 ( + pk int, + a int, + primary key (pk) comment '$pk_cf' +) engine=rocksdb; +insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6); + +connect (con1,localhost,root,,); + +--echo # TRX1: Start, Allocate a snapshot +connection con1; +begin; +select * from t1; + +--echo # TRX2: Make a change that TRX1 will not see +connection default; +update t1 set a=2222 where pk between 3 and 5; + +--echo # TRX1: Now, make a change that would overwrite TRX2'x change and commit +connection con1; +update t1 set a=a+1 where pk between 3 and 5; +commit; + +--echo # Examine the result: +--echo # pk={3,4,5} a=2223 means UPDATE in TRX1 used "read committed" (InnoDB-like isolation) +connection default; +select * from t1; + +disconnect con1; +connection default; +drop table t1; + +--echo # +--echo # Same as above, but test SELECT FOR UPDATE. +--echo # +eval +create table t1 ( + pk int, + a int, + primary key (pk) comment '$pk_cf' +) engine=rocksdb; +insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6); + +connect (con1,localhost,root,,); + +--echo # TRX1: Start, Allocate a snapshot +connection con1; +begin; +select * from t1; + +--echo # TRX2: Make a change that TRX1 will not see +connection default; +update t1 set a=222 where pk=2; +update t1 set a=333 where pk=3; + +--echo # TRX1: Check what select [FOR UPDATE] sees +connection con1; +select * from t1 where pk in (2,3); +select * from t1 where pk=2 for update; +select * from t1 where pk=2; + +commit; + +disconnect con1; +connection default; +drop table t1; + +if (!$PK_USES_REVERSE_CF) { +--echo # +--echo # Another no-snapshot-checking test, this time for single-statement +--echo # transaction +--echo # +eval +create table t1 ( + pk int, + a int, + name varchar(16), + primary key(pk) comment '$pk_cf' +) engine=rocksdb; +insert into t1 values (1,1, 'row1'), (2,2,'row2'); + +connect (con1,localhost,root,,); +connection con1; +select get_lock('row1', 100); + +connection default; + +--echo # The following will read the first row (1,1,'row1'), and stop. + +send update t1 set a=a+100 where get_lock(name, 1000)=1; + +# Wait till the default connection has stopped: +connection con1; + +let $wait_condition= + SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE = "User lock" + AND INFO = "update t1 set a=a+100 where get_lock(name, 1000)=1"; +--source include/wait_condition.inc + +# Update the second row +update t1 set a=5 where pk=2; + +select release_lock('row1'); + +connection default; +reap; + +--echo # Look at the row with pk=2: +--echo # 2, 105, row2 - means the UPDATE was reading current data (Correct) +--echo # 2, 102, row - means the UPDATE read the snapshot (incorrect) +select * from t1; + +--echo # Try releasing both locks (in 5.6, we will be holding only the second one) +select release_lock(name) from t1; + +disconnect con1; +connection default; +drop table t1; +} + +--echo # +--echo # Check that I_S.processlist.state is set correctly now. +--echo # +eval +create table t1( + pk int, + a int, + primary key(pk) comment '$pk_cf' +) engine=rocksdb; +insert into t1 values (1,1),(2,2),(3,3); + +begin; +select * from t1 where pk=2 for update; + +--connect (con1,localhost,root,,) +begin; +set rocksdb_lock_wait_timeout=300; +send select * from t1 where pk=2 for update; + +connection default; +--echo # Now, will wait until we see con1 have state="Waiting for row lock" +let $wait_condition= + SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE = "Waiting for row lock" + AND INFO = "select * from t1 where pk=2 for update"; +--source include/wait_condition.inc + +rollback; +connection con1; +--reap +rollback; + +disconnect con1; +connection default; +drop table t1; + +--echo # +--echo # Test range locking for ranges with HA_READ_PREFIX_LAST +--echo # +create table t0(a int) engine=rocksdb; +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +eval +create table t1 ( + pk1 int, + pk2 int, + a int, + primary key(pk1, pk2) comment '$pk_cf' +) engine=rocksdb; + +insert into t1 +select + A.a, B.a, A.a*10+B.a +from + t0 A, t0 B; + + +# Get a lock in another connection so that the primary transaction is not using +# STO optimization, and its locks can be seen in I_S.rocksdb_locks +--connect (con1,localhost,root,,) +connection con1; +begin; +insert into t1 values (0x1112222,0x1112222,0); + +connection default; +begin; +--echo # Should use ref access w/o filesort: +--replace_column 9 # +explain +select * from t1 +where pk1=3 +order by pk1 desc, pk2 desc +for update; + +select * from t1 +where pk1=3 +order by pk1 desc, pk2 desc +for update; + +let $select_from_is_rowlocks_current_trx_only=1; +--source suite/rocksdb/include/select_from_is_rowlocks.inc +rollback; + +--echo # +--echo # Test range locking for ranges with HA_READ_PREFIX_LAST_OR_PREV +--echo # + +begin; +--echo # Should use range access with 2 keyparts and w/o filesort: +--replace_column 9 # +explain +select * from t1 +where pk1=4 and pk2 between 5 and 8 +order by pk1 desc, pk2 desc +for update; + +select * from t1 +where pk1=4 and pk2 between 5 and 8 +order by pk1 desc, pk2 desc +for update; + +let $select_from_is_rowlocks_current_trx_only=1; +--source suite/rocksdb/include/select_from_is_rowlocks.inc +rollback; + +connection con1; +rollback; + +connection default; +drop table t0, t1; + +--echo # +--echo # A bug: range locking was not used when scan started at table start or end +--echo # +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t10(a int); +insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; + +create table t1 ( + pk int not null, + a int, + primary key(pk) +) engine=rocksdb; + +insert into t1 select a*2,a*2 from t10; + +connection con1; +begin; +select * from t1 where pk=500 for update; +connection default; + +begin; +select * from t1 where pk<10 order by pk limit 10 for update; + +let $select_from_is_rowlocks_current_trx_only=1; +--source suite/rocksdb/include/select_from_is_rowlocks.inc +rollback; + +begin; +select * from t1 where pk>1990 order by pk desc limit 10 for update; +let $select_from_is_rowlocks_current_trx_only=1; +--source suite/rocksdb/include/select_from_is_rowlocks.inc +rollback; + +connection con1; +rollback; +disconnect con1; + +connection default; +drop table t0,t10,t1; diff --git a/mysql-test/suite/rocksdb/t/range_locking.test b/mysql-test/suite/rocksdb/t/range_locking.test new file mode 100644 index 00000000000..5c599238a0a --- /dev/null +++ b/mysql-test/suite/rocksdb/t/range_locking.test @@ -0,0 +1,6 @@ + +--let pk_cf=default +--let sk_cf=default + +--source range_locking.inc + diff --git a/mysql-test/suite/rocksdb/t/range_locking_deadlock_tracking.test b/mysql-test/suite/rocksdb/t/range_locking_deadlock_tracking.test new file mode 100644 index 00000000000..a1fdafa4613 --- /dev/null +++ b/mysql-test/suite/rocksdb/t/range_locking_deadlock_tracking.test @@ -0,0 +1,194 @@ +--source suite/rocksdb/include/have_range_locking.inc + +# +# This is deadlock_tracking.test, variant for running with Range Locking: +# - Deadlock #5 is disabled, it requires LOCK IN SHARE MODE tests +# - In the result file, SHOW ENGINE ROCKSDB TRANSACTION STATUS does not print +# deadlock information. +# +set @prior_lock_wait_timeout = @@rocksdb_lock_wait_timeout; +set @prior_deadlock_detect = @@rocksdb_deadlock_detect; +set @prior_max_latest_deadlocks = @@rocksdb_max_latest_deadlocks; +set global rocksdb_deadlock_detect = on; +set global rocksdb_lock_wait_timeout = 10000; +--echo # Clears deadlock buffer of any prior deadlocks. +set global rocksdb_max_latest_deadlocks = 0; +set global rocksdb_max_latest_deadlocks = @prior_max_latest_deadlocks; +let $engine = rocksdb; + +--source include/count_sessions.inc +connect (con1,localhost,root,,); +let $con1= `SELECT CONNECTION_ID()`; + +connect (con2,localhost,root,,); +let $con2= `SELECT CONNECTION_ID()`; + +connect (con3,localhost,root,,); +let $con3= `SELECT CONNECTION_ID()`; + +connection default; +eval create table t (i int primary key) engine=$engine; +insert into t values (1), (2), (3); +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +show engine rocksdb transaction status; + +echo Deadlock #1; +--source include/simple_deadlock.inc +connection default; +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +show engine rocksdb transaction status; + +echo Deadlock #2; +--source include/simple_deadlock.inc +connection default; +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +show engine rocksdb transaction status; +set global rocksdb_max_latest_deadlocks = 10; + +echo Deadlock #3; +--source include/simple_deadlock.inc +connection default; +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +show engine rocksdb transaction status; +set global rocksdb_max_latest_deadlocks = 1; +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +show engine rocksdb transaction status; + +connection con3; +set rocksdb_deadlock_detect_depth = 2; + +echo Deadlock #4; +connection con1; +begin; +select * from t where i=1 for update; + +connection con2; +begin; +select * from t where i=2 for update; + +connection con3; +begin; +select * from t where i=3 for update; + +connection con1; +send select * from t where i=2 for update; + +connection con2; +let $wait_condition = select count(*) = 1 from information_schema.rocksdb_trx +where thread_id = $con1 and waiting_key != ""; +--source include/wait_condition.inc + +send select * from t where i=3 for update; + +connection con3; +let $wait_condition = select count(*) = 1 from information_schema.rocksdb_trx +where thread_id = $con2 and waiting_key != ""; +--source include/wait_condition.inc + +select variable_value into @a from information_schema.global_status where variable_name='rocksdb_row_lock_deadlocks'; +--error ER_LOCK_DEADLOCK +select * from t where i=1 for update; +select case when variable_value-@a = 1 then 'true' else 'false' end as deadlocks from information_schema.global_status where variable_name='rocksdb_row_lock_deadlocks'; +rollback; + +connection con2; +reap; +rollback; + +connection con1; +reap; +rollback; + +connection default; +set global rocksdb_max_latest_deadlocks = 5; +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +show engine rocksdb transaction status; + +--disable_parsing +echo Deadlock #5; +connection con1; +begin; +select * from t where i=1 for update; + +connection con2; +begin; +select * from t where i=2 for update; + +connection con3; +begin; +select * from t where i=3 lock in share mode; + +connection con1; +select * from t where i=100 for update; +select * from t where i=101 for update; +send select * from t where i=2 for update; + +connection con2; +let $wait_condition = select count(*) = 1 from information_schema.rocksdb_trx +where thread_id = $con1 and waiting_key != ""; +--source include/wait_condition.inc + +select * from t where i=3 lock in share mode; +select * from t where i=200 for update; +select * from t where i=201 for update; + +--error ER_LOCK_DEADLOCK +select * from t where i=1 lock in share mode; +rollback; + +connection con1; +reap; +rollback; + +connection con3; +rollback; + +connection default; +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +show engine rocksdb transaction status; +--enable_parsing +echo Deadlock #6; +connection con1; +create table t1 (id int primary key, value int) engine=rocksdb; +insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5); +begin; +update t1 set value=value+100 where id=1; +update t1 set value=value+100 where id=2; + +connection con2; +begin; +update t1 set value=value+200 where id=3; + +connection con1; +send update t1 set value=value+100 where id=3; + +connection con2; +let $wait_condition = select count(*) = 1 from information_schema.rocksdb_trx +where thread_id = $con1 and waiting_key != ""; +--source include/wait_condition.inc +--error ER_LOCK_DEADLOCK +update t1 set value=value+200 where id=1; + +# con2 tx is automatically rolled back +connection con1; +reap; +select * from t1; +drop table t1; + +connection default; + +disconnect con1; +disconnect con2; +disconnect con3; + +set global rocksdb_lock_wait_timeout = @prior_lock_wait_timeout; +set global rocksdb_deadlock_detect = @prior_deadlock_detect; +drop table t; +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /INDEX_ID: [0-9a-f]*/IDX_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +show engine rocksdb transaction status; +set global rocksdb_max_latest_deadlocks = 0; +--echo # Clears deadlock buffer of any existent deadlocks. +set global rocksdb_max_latest_deadlocks = @prior_max_latest_deadlocks; +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /INDEX_ID: [0-9a-f]*/IDX_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +show engine rocksdb transaction status; +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/rocksdb/t/range_locking_escalation-master.opt b/mysql-test/suite/rocksdb/t/range_locking_escalation-master.opt new file mode 100644 index 00000000000..d0087e2a77b --- /dev/null +++ b/mysql-test/suite/rocksdb/t/range_locking_escalation-master.opt @@ -0,0 +1 @@ +--rocksdb_use_range_locking=1 --rocksdb_max_lock_memory=1024 diff --git a/mysql-test/suite/rocksdb/t/range_locking_escalation.test b/mysql-test/suite/rocksdb/t/range_locking_escalation.test new file mode 100644 index 00000000000..cd4f8a30a46 --- /dev/null +++ b/mysql-test/suite/rocksdb/t/range_locking_escalation.test @@ -0,0 +1,38 @@ +# +# Range Locking - Lock Escalation Tests. +# + +--source include/have_rocksdb.inc +--enable_connect_log + + +show variables like 'rocksdb_use_range_locking'; +show variables like 'rocksdb_max_lock_memory'; +show status like 'rocksdb_locktree_escalation_count'; +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + pk int primary key, + a int +) engine=rocksdb; + +#begin; +#insert into t1 values (1000111,100011); +#connect (con1,localhost,root,,); +#connection con1; + +insert into t1 +select + A.a + B.a*10 + C.a*100 + D.a*1000, + 12345 +from t0 A, t0 B, t0 C, t0 D; + +select count(*) from t1; + +#connection default; +#disconnect con1; +show status like 'rocksdb_locktree_escalation_count'; + +drop table t0,t1; + diff --git a/mysql-test/suite/rocksdb/t/range_locking_refresh_iter.test b/mysql-test/suite/rocksdb/t/range_locking_refresh_iter.test new file mode 100644 index 00000000000..a9e3de29aac --- /dev/null +++ b/mysql-test/suite/rocksdb/t/range_locking_refresh_iter.test @@ -0,0 +1,69 @@ +--source include/have_rocksdb.inc +--source suite/rocksdb/include/have_range_locking.inc + +select @@rocksdb_use_range_locking; + +--disable_warnings +set debug_sync='RESET'; +--enable_warnings +# +# Testcase for iterator snapshot refresh +# +create table ten(a int primary key); +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table one_k(a int primary key); +insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; + +create table t1 ( + pk int primary key, + a int +) engine=rocksdb; + +insert into t1 select a,a from ten; +insert into t1 select a+40, a+40 from ten; +insert into t1 select a+100, a+100 from one_k; +delete from t1 where pk=44; +set global rocksdb_force_flush_memtable_and_lzero_now=1; + +# Ok, now the table has these PK ranges: +# 0..9 40..49 100...1000 +# and all rows have pk=a +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); + +connection con1; +begin; +set debug_sync='rocksdb.check_flags_rmi SIGNAL con1_stopped WAIT_FOR con1_cont'; +send +update t1 set a=a+100 where pk < 3 or pk between 10 and 50; + +# The query is how stuck at the start of the second range. + + +## con2> +connection con2; +set debug_sync='now WAIT_FOR con1_stopped'; + +# Make some changes to check if the iterator is reading current data or +# snapshot +insert into t1 values (44,5000); +delete from t1 where pk= 42; +update t1 set a=5000 where pk between 40 and 45; +set global rocksdb_force_flush_memtable_and_lzero_now=1; + +set debug_sync='now SIGNAL con1_cont'; + +connection con1; +#--error ER_GET_ERRMSG +reap; +select * from t1 where pk<100; + +commit; +disconnect con1; +disconnect con2; +connection default; +set debug_sync='RESET'; + +drop table t1, ten, one_k; + diff --git a/mysql-test/suite/rocksdb/t/range_locking_rev_cf.test b/mysql-test/suite/rocksdb/t/range_locking_rev_cf.test new file mode 100644 index 00000000000..8b993764235 --- /dev/null +++ b/mysql-test/suite/rocksdb/t/range_locking_rev_cf.test @@ -0,0 +1,12 @@ +# +# Range locking tests. +# + +--source include/have_rocksdb.inc +--source suite/rocksdb/include/have_range_locking.inc + +--let pk_cf=rev:cf1 +--let PK_USES_REVERSE_CF=1 + +--source range_locking.inc + diff --git a/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test b/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test new file mode 100644 index 00000000000..32590af1799 --- /dev/null +++ b/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test @@ -0,0 +1,288 @@ +# +# Range Locking : tests for SeekForUpdate feature +# + +--source include/have_rocksdb.inc +--source include/have_debug_sync.inc +--source suite/rocksdb/include/have_range_locking.inc +--enable_connect_log +show variables like 'rocksdb_use_range_locking'; + +create table t0(a int primary key); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + pk int, + a int, + primary key (pk) +) engine=rocksdb; + +insert into t1 select + A.a + B.a*10 + C.a*100, + A.a + B.a*10 + C.a*100 +from + t0 A, t0 B, t0 C; + +--echo # Make another connection to get the lock tree out of the STO-mode +connect (con1,localhost,root,,); +connection con1; +begin; +select * from t1 where pk=10 for update; + +connection default; +begin; +select * from t1 where pk=11 for update; + +let $select_from_is_rowlocks_current_trx_only=1; +--echo # Now, we will just see locks on 10=0xA and 11=0xB: +--source suite/rocksdb/include/select_from_is_rowlocks.inc + +--echo # +--echo # SeekForUpdate Test #1: A query with type=range (without upper bound) and LIMIT +--echo # +--replace_column 9 # +explain +select * from t1 where pk>=500 order by pk limit 3 for update; +select * from t1 where pk>=500 order by pk limit 3 for update; +--source suite/rocksdb/include/select_from_is_rowlocks.inc +rollback; + + +begin; +select * from t1 where pk=11 for update; +explain +select * from t1 order by pk limit 3 for update; +select * from t1 order by pk limit 3 for update; + +--source suite/rocksdb/include/select_from_is_rowlocks.inc + +rollback; +connection con1; +rollback; +disconnect con1; +connection default; +drop table t0, t1; + + +--echo # +--echo # Concurrent tests: let one thread do SeekForUpdate and the other +--echo # interfere by committing modifications +--echo # + +create table t0(a int primary key); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + pk int, + a int, + primary key (pk) +) engine=rocksdb; + +insert into t1 select + A.a + B.a*10 + C.a*100, + A.a + B.a*10 + C.a*100 +from + t0 A, t0 B, t0 C; + +select * from t1 where pk<10; +delete from t1 where pk<10; +select * from t1 where pk<10; + + +--echo # Test what happens when another transaction commits a row +--echo # right before the range we are about to lock (nothing) + +--replace_column 9 # +explain +select * from t1 where pk >=5 order by pk limit 3 for update; + +begin; + +set debug_sync='rocksdb.locking_iter_scan SIGNAL about_to_lock_range WAIT_FOR spoiler_inserted'; +send select * from t1 where pk >=5 order by pk limit 3 for update; + +connect (con1,localhost,root,,); +connection con1; +set debug_sync='now WAIT_FOR about_to_lock_range'; +insert into t1 values (3,3); +set debug_sync='now SIGNAL spoiler_inserted'; + +connection default; +reap; +rollback; + +delete from t1 where pk=3; + +--echo # +--echo # Now, repeat the test but let the other transaction insert the row into +--echo # the range we are locking + +--replace_column 9 # +explain +select * from t1 where pk >=5 order by pk limit 1 for update; + +begin; + +set debug_sync='rocksdb.locking_iter_scan SIGNAL about_to_lock_range WAIT_FOR spoiler_inserted'; +send +select * from t1 where pk >=5 order by pk limit 1 for update; + +connection con1; +set debug_sync='now WAIT_FOR about_to_lock_range'; +insert into t1 values (8,8); +set debug_sync='now SIGNAL spoiler_inserted'; + +connection default; +reap; + +--source suite/rocksdb/include/select_from_is_rowlocks.inc + +rollback; +delete from t1 where pk=8; + +--echo # +--echo # Repeat the third time, this time deleting the row that SeekForUpdate saw +--echo # +insert into t1 values (7,7); + +begin; + +set debug_sync='rocksdb.locking_iter_scan SIGNAL about_to_lock_range WAIT_FOR spoiler_inserted'; +send +select * from t1 where pk >=5 order by pk limit 1 for update; + +connection con1; +set debug_sync='now WAIT_FOR about_to_lock_range'; +delete from t1 where pk=7; +set debug_sync='now SIGNAL spoiler_inserted'; + +connection default; +reap; + +rollback; + +--echo # +--echo # Repeat the above test, but let the read fail with ER_LOCK_WAIT_TIMEOUT +--echo # error. MyRocks code should now be prepared that data reads cause this +--echo # error +--echo # +insert into t1 values (7,7); + +begin; + +set debug_sync='rocksdb.locking_iter_scan SIGNAL about_to_lock_range WAIT_FOR spoiler_inserted'; +send +select * from t1 where pk >=5 order by pk limit 1 for update; + +connection con1; +set debug_sync='now WAIT_FOR about_to_lock_range'; +begin; +delete from t1 where pk=7; +set debug_sync='now SIGNAL spoiler_inserted'; + +connection default; +--error ER_LOCK_WAIT_TIMEOUT +reap; + +rollback; + +connection con1; +rollback; +connection default; + +--echo # +--echo # Backward scan test +--echo # +connection con1; +begin; +select * from t1 where pk=500 for update; +connection default; + +insert into t1 values + (1001, 1001), + (1005, 1005), + (1007, 1007), + (1010, 1010); + +begin; +select * from t1 order by pk desc limit 2 for update; + +let $select_from_is_rowlocks_current_trx_only=1; + +--echo # The below will lock from pk=1007 (0x3ef) till the end of the table: +--source suite/rocksdb/include/select_from_is_rowlocks.inc + +rollback; + +begin; +select * from t1 where pk <1007 order by pk desc limit 2 for update; +--source suite/rocksdb/include/select_from_is_rowlocks.inc + +connection con1; +rollback; + +connection default; +rollback; + +--echo # +--echo # Backward scan test 2: error condition +--echo # +connection con1; +begin; +select * from t1 where pk=1010 for update; + +connection default; +begin; +--error ER_LOCK_WAIT_TIMEOUT +select * from t1 order by pk desc limit 2 for update; +rollback; + +connection con1; +rollback; +begin; +select * from t1 where pk=1007 for update; + +connection default; +begin; +--error ER_LOCK_WAIT_TIMEOUT +select * from t1 order by pk desc limit 2 for update; +rollback; + +connection con1; +rollback; + +disconnect con1; +connection default; +drop table t0,t1; + +--echo # +--echo # A test: full table scan doesn't lock gaps +--echo # + +create table t1 ( + pk int primary key, + a int +) engine=rocksdb; + +insert into t1 values (10,10),(20,20),(30,30); + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); + +connection con1; +begin; + +select * from t1 for update; + +connection con2; + +--error ER_LOCK_WAIT_TIMEOUT +insert into t1 values (5,5); + +connection con1; +rollback; + +disconnect con1; +disconnect con2; +connection default; +drop table t1; diff --git a/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test b/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test new file mode 100644 index 00000000000..d569a21ebec --- /dev/null +++ b/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test @@ -0,0 +1,202 @@ +# +# Test for shared lock support for range locking +# +--source include/have_rocksdb.inc +--source suite/rocksdb/include/have_range_locking.inc +--enable_connect_log + +select @@rocksdb_use_range_locking; + +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + pk int primary key, + a int +) engine=rocksdb; + + +insert into t1 select a,a from t0; + +--echo # A basic test for shared locks + +begin; +select * from t1 where pk=3 for update; +select * from t1 where pk=5 lock in share mode; +let $TRX1_ID=`select transaction_id from information_schema.rocksdb_trx where thread_id=connection_id()`; + +connect (con1,localhost,root,,); +connection con1; +begin; +select * from t1 where pk=5 lock in share mode; +let $TRX2_ID=`select transaction_id from information_schema.rocksdb_trx where thread_id=connection_id()`; +--echo # Now for pk=5 we should see two locks by TRX1 and TRX2 with mode=S: +--source suite/rocksdb/include/select_from_is_rowlocks.inc + +rollback; +--echo # Now, TRX2_ID should be gone: +--source suite/rocksdb/include/select_from_is_rowlocks.inc + +connection default; + +--echo # Get a read lock on pk=3 (where we have a write lock). +--echo # The result should be that we will still have a write lock +select * from t1 where pk=3 for update; +--source suite/rocksdb/include/select_from_is_rowlocks.inc + +--echo # Get a write lock on pk=5 (where we have a read lock). +--echo # The result should be that we will have a write lock. +select * from t1 where pk=5 for update; +--source suite/rocksdb/include/select_from_is_rowlocks.inc + +connection default; +rollback; + +--echo # +--echo # Test if a read lock inhibits write locks +--echo # + +begin; +select * from t1 where pk=2 lock in share mode; +select * from t1 where pk=8 for update; + +connection con1; +begin; + +--error ER_LOCK_WAIT_TIMEOUT +select * from t1 where pk=2 for update; + +--error ER_LOCK_WAIT_TIMEOUT +select * from t1 where pk between 0 and 4 for update; + +--error ER_LOCK_WAIT_TIMEOUT +delete from t1 where pk=2; + +--echo # Get a shared lock +select * from t1 where pk=2 lock in share mode; + +--echo # But this should still prevent us from acquiring a write lock on that value: +--error ER_LOCK_WAIT_TIMEOUT +select * from t1 where pk=2 for update; + +rollback; +connection default; +rollback; + +drop table t1; +create table t1 ( + pk int not null primary key, + a int not null, + key(a) +) engine=rocksdb; + +insert into t1 +select + A.a+10*B.a+100*C.a+1000*D.a, A.a+10*B.a+100*C.a+1000*D.a +from + t0 A, t0 B, t0 C, t0 D; +set global rocksdb_force_flush_memtable_now=1; + +connection con1; +begin; +select * from t1 where pk=900 for update; +let $TRX2_ID=`select transaction_id from information_schema.rocksdb_trx where thread_id=connection_id()`; + +connection default; +begin; +--replace_column 9 # +explain +select * from t1 where a between 2 and 5 lock in share mode; +select * from t1 where a between 2 and 5 lock in share mode; +let $TRX1_ID=`select transaction_id from information_schema.rocksdb_trx where thread_id=connection_id()`; + +--source suite/rocksdb/include/select_from_is_rowlocks.inc + +rollback; + +disconnect con1; + +drop table t0,t1; + +--echo # +--echo # Test shared point locks and lock escalation +--echo # +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + pk int primary key, + a int +) engine=rocksdb; + +insert into t1 +select 1000 + 100*A.a + 10*B.a + C.a, 12345 from t0 A, t0 B, t0 C; + +show status like 'rocksdb_locktree_current_lock_memory'; + +connect (con1,localhost,root,,); +connection con1; + +begin; +--echo # CON1: get some shared locks +select * from t1 where pk=1001 lock in share mode; +select * from t1 where pk=1100 lock in share mode; +select * from t1 where pk=1200 lock in share mode; + +select * from t1 where pk=2500 lock in share mode; +let $TRX1_ID=`select transaction_id from information_schema.rocksdb_trx where thread_id=connection_id()`; + +connection default; +begin; +--echo # DEFAULT: get the same locks so we have locks with multiple owners +select * from t1 where pk=1001 lock in share mode; +select * from t1 where pk=1100 lock in share mode; +select * from t1 where pk=1200 lock in share mode; + +--echo # DEFAULT: get shared locks with one owner: +select * from t1 where pk=2510 lock in share mode; +let $TRX2_ID=`select transaction_id from information_schema.rocksdb_trx where thread_id=connection_id()`; + + +--echo # DEFAULT: exclusive locks on 0-10: +insert into t1 select A.a, 0 from t0 A; + +connection con1; +--echo # CON1: exclusive locks on 2000-2010: +insert into t1 select 2000+A.a, 0 from t0 A; + +let $order_by_rowkey=1; +#select * from information_schema.rocksdb_locks; +--source suite/rocksdb/include/select_from_is_rowlocks.inc + +connection default; +show status like 'rocksdb_locktree_current_lock_memory'; +set @save_mlm= @@rocksdb_max_lock_memory; + +--echo # Set the limit to cause lock escalation: +set @cur_mem_usage= (select + variable_value + from + information_schema.GLOBAL_STATUS + where + variable_name='rocksdb_locktree_current_lock_memory'); + +set global rocksdb_max_lock_memory = cast(@cur_mem_usage+4 as SIGNED); + +connection con1; +insert into t1 select 3000+A.a, 0 from t0 A; + +#select * from information_schema.rocksdb_locks; +--source suite/rocksdb/include/select_from_is_rowlocks.inc + +connection con1; +rollback; +connection default; +rollback; + +disconnect con1; +set global rocksdb_max_lock_memory= cast(@save_mlm as SIGNED); + +drop table t0, t1; + +

1 0

[Commits] 4229a7ee0a4: Post-merge fixes: adapt MyRocks to new Range-locking RocksDB interface
by psergey 30 Nov '20

30 Nov '20

revision-id: 4229a7ee0a4e8f2b95ed7db7d87e9baf561dc88d (fb-prod8-202009-49-g4229a7ee0a4) parent(s): d8dcc6caacbb3658420322747b53d38efddf9977 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-11-30 19:58:15 +0300 message: Post-merge fixes: adapt MyRocks to new Range-locking RocksDB interface Part#1: - Get to compile - Use the right GetLockStatusData call depending on the lock manager used --- rocksdb | 2 +- storage/rocksdb/ha_rocksdb.cc | 10 ++-- storage/rocksdb/ha_rocksdb.h | 2 + storage/rocksdb/rdb_i_s.cc | 107 ++++++++++++++++++++++++++++++------------ 4 files changed, 86 insertions(+), 35 deletions(-) diff --git a/rocksdb b/rocksdb index bb68c56f990..0e30dd985c7 160000 --- a/rocksdb +++ b/rocksdb @@ -1 +1 @@ -Subproject commit bb68c56f990da1531f1b84ae8488e5f3ccf416e7 +Subproject commit 0e30dd985c72b6f11576a2a2e43c62bcea01f9b9 diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index 333e2370919..18e4809aa8a 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -767,7 +767,7 @@ static bool rocksdb_skip_locks_if_skip_unique_check = false; static ulonglong rocksdb_max_lock_memory; static bool rocksdb_use_range_locking = 0; -static std::shared_ptr<rocksdb::RangeLockMgrHandle> range_lock_mgr; +std::shared_ptr<rocksdb::RangeLockManagerHandle> range_lock_mgr; std::atomic<uint64_t> rocksdb_row_lock_deadlocks(0); std::atomic<uint64_t> rocksdb_row_lock_wait_timeouts(0); @@ -3604,7 +3604,7 @@ class Rdb_transaction_impl : public Rdb_transaction { rocksdb::Status lock_range(rocksdb::ColumnFamilyHandle *const cf, const rocksdb::Endpoint &start_endp, const rocksdb::Endpoint &end_endp) override { - ++m_lock_count; + ++m_row_lock_count; return m_rocksdb_tx->GetRangeLock(cf, start_endp, end_endp); } private: @@ -3846,7 +3846,7 @@ class Rdb_transaction_impl : public Rdb_transaction { if (use_locking_iterator) { locking_iter_created(); return GetLockingIterator(m_rocksdb_tx, options, column_family, - is_rev_cf, &m_lock_count); + is_rev_cf, &m_row_lock_count); } else return m_rocksdb_tx->GetIterator(options, column_family); @@ -6352,7 +6352,7 @@ static int rocksdb_init_internal(void *const p) { if (range_lock_mgr) { - range_lock_mgr->set_max_lock_memory(rocksdb_max_lock_memory); + range_lock_mgr->SetMaxLockMemory(rocksdb_max_lock_memory); sql_print_information("RocksDB: USING NEW RANGE LOCKING"); sql_print_information("RocksDB: Max lock memory=%llu", rocksdb_max_lock_memory); } @@ -15929,7 +15929,7 @@ void rocksdb_set_max_lock_memory(THD *thd, struct SYS_VAR*, void* /*var_ptr*/, const void *save) { const uint64_t new_val = *static_cast<const uint64_t *>(save); if (rocksdb_max_lock_memory != new_val) { - if (range_lock_mgr->set_max_lock_memory(new_val)) { + if (range_lock_mgr->SetMaxLockMemory(new_val)) { /* NO_LINT_DEBUG */ sql_print_warning("MyRocks: failed to set max_lock_memory"); push_warning_printf(thd, Sql_condition::SL_WARNING, diff --git a/storage/rocksdb/ha_rocksdb.h b/storage/rocksdb/ha_rocksdb.h index 81300833852..89a06a52d70 100644 --- a/storage/rocksdb/ha_rocksdb.h +++ b/storage/rocksdb/ha_rocksdb.h @@ -1250,4 +1250,6 @@ extern std::atomic<uint64_t> rocksdb_select_bypass_executed; extern std::atomic<uint64_t> rocksdb_select_bypass_rejected; extern std::atomic<uint64_t> rocksdb_select_bypass_failed; +extern std::shared_ptr<rocksdb::RangeLockManagerHandle> range_lock_mgr; + } // namespace myrocks diff --git a/storage/rocksdb/rdb_i_s.cc b/storage/rocksdb/rdb_i_s.cc index d6292343d32..765f432f0ce 100644 --- a/storage/rocksdb/rdb_i_s.cc +++ b/storage/rocksdb/rdb_i_s.cc @@ -1771,39 +1771,88 @@ static int rdb_i_s_lock_info_fill_table( } /* cf id -> rocksdb::KeyLockInfo */ - std::unordered_multimap<uint32_t, rocksdb::KeyLockInfo> lock_info = - rdb->GetLockStatusData(); - - for (const auto &lock : lock_info) { - const uint32_t cf_id = lock.first; - const auto &key_lock_info = lock.second; - auto key_hexstr = rdb_hexdump(key_lock_info.key.c_str(), - key_lock_info.key.length(), FN_REFLEN); - if (key_lock_info.has_key2) - { - const auto key2_hexstr = rdb_hexdump(key_lock_info.key2.c_str(), - key_lock_info.key2.length(), - FN_REFLEN - key_hexstr.size() - 3); - key_hexstr.append(" - "); - key_hexstr.append(key2_hexstr); - } + if (range_lock_mgr) { + // Use Range Lock Manager's interface for obtaining more specific + // information about the acquired locks + auto lock_info = range_lock_mgr->GetRangeLockStatusData(); + + for (const auto &lock : lock_info) { + const uint32_t cf_id = lock.first; + const auto &range_lock_info = lock.second; + + std::string key_hexstr; + // For keys: :0 keys should look like point keys + if (!range_lock_info.start.inf_suffix && + !range_lock_info.end.inf_suffix && + (range_lock_info.start.slice == + range_lock_info.end.slice)) { + // Ok the lock is held on a single-point range. + // Show it like a single-point key + key_hexstr = rdb_hexdump(range_lock_info.start.slice.c_str(), + range_lock_info.start.slice.length(), + FN_REFLEN); + } else { + key_hexstr = rdb_hexdump(range_lock_info.start.slice.c_str(), + range_lock_info.start.slice.length(), + FN_REFLEN); + if (range_lock_info.start.inf_suffix) + key_hexstr.append(":1"); + + key_hexstr.append("-"); + + std::string key2 = rdb_hexdump(range_lock_info.end.slice.c_str(), + range_lock_info.end.slice.length(), + FN_REFLEN); + if (range_lock_info.end.inf_suffix) + key2.append(":1"); + key_hexstr.append(key2); + } - for (const auto &id : key_lock_info.ids) { - tables->table->field[RDB_LOCKS_FIELD::COLUMN_FAMILY_ID]->store(cf_id, - true); - tables->table->field[RDB_LOCKS_FIELD::TRANSACTION_ID]->store(id, true); + for (const auto &id : range_lock_info.ids) { + tables->table->field[RDB_LOCKS_FIELD::COLUMN_FAMILY_ID]->store(cf_id, + true); + tables->table->field[RDB_LOCKS_FIELD::TRANSACTION_ID]->store(id, true); - tables->table->field[RDB_LOCKS_FIELD::KEY]->store( - key_hexstr.c_str(), key_hexstr.size(), system_charset_info); - tables->table->field[RDB_LOCKS_FIELD::MODE]->store( - key_lock_info.exclusive ? "X" : "S", 1, system_charset_info); + tables->table->field[RDB_LOCKS_FIELD::KEY]->store( + key_hexstr.c_str(), key_hexstr.size(), system_charset_info); + tables->table->field[RDB_LOCKS_FIELD::MODE]->store( + range_lock_info.exclusive ? "X" : "S", 1, system_charset_info); - /* Tell MySQL about this row in the virtual table */ - ret = static_cast<int>( - my_core::schema_table_store_record(thd, tables->table)); + /* Tell MySQL about this row in the virtual table */ + ret = static_cast<int>( + my_core::schema_table_store_record(thd, tables->table)); - if (ret != 0) { - break; + if (ret != 0) { + break; + } + } + } + } else { + std::unordered_multimap<uint32_t, rocksdb::KeyLockInfo> lock_info = + rdb->GetLockStatusData(); + + for (const auto &lock : lock_info) { + const uint32_t cf_id = lock.first; + const auto &key_lock_info = lock.second; + auto key_hexstr = rdb_hexdump(key_lock_info.key.c_str(), + key_lock_info.key.length(), FN_REFLEN); + for (const auto &id : key_lock_info.ids) { + tables->table->field[RDB_LOCKS_FIELD::COLUMN_FAMILY_ID]->store(cf_id, + true); + tables->table->field[RDB_LOCKS_FIELD::TRANSACTION_ID]->store(id, true); + + tables->table->field[RDB_LOCKS_FIELD::KEY]->store( + key_hexstr.c_str(), key_hexstr.size(), system_charset_info); + tables->table->field[RDB_LOCKS_FIELD::MODE]->store( + key_lock_info.exclusive ? "X" : "S", 1, system_charset_info); + + /* Tell MySQL about this row in the virtual table */ + ret = static_cast<int>( + my_core::schema_table_store_record(thd, tables->table)); + + if (ret != 0) { + break; + } } } }

1 0

[Commits] d8dcc6caacb: Merge branch 'fb-mysql-8.0.17-range-locking-sept20' into fb-mysql-8.0.17
by Sergei Petrunia 30 Nov '20

30 Nov '20

revision-id: d8dcc6caacbb3658420322747b53d38efddf9977 (fb-prod8-202009-48-gd8dcc6caacb) parent(s): 7e916290e883c42ae3aab8fb2bb954b7b8537076 78674a3d77089376d8f511566c697077ff1bdb5d author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-11-30 18:10:09 +0300 message: Merge branch 'fb-mysql-8.0.17-range-locking-sept20' into fb-mysql-8.0.17 .gitmodules | 2 +- mysql-test/suite/rocksdb/combinations | 3 + .../suite/rocksdb/include/not_range_locking.inc | 5 + mysql-test/suite/rocksdb/r/rocksdb.result | 2 + .../rocksdb/r/rocksdb_timeout_rollback.result | 3 + mysql-test/suite/rocksdb/r/unique_sec.result | 4 + .../suite/rocksdb/r/unique_sec_rev_cf.result | 4 + mysql-test/suite/rocksdb/t/deadlock_tracking.test | 7 +- .../t/drop_cf_before_show_deadlock_info.test | 4 + mysql-test/suite/rocksdb/t/hermitage.inc | 14 +- mysql-test/suite/rocksdb/t/hermitage.test | 3 + mysql-test/suite/rocksdb/t/i_s_deadlock.test | 4 + mysql-test/suite/rocksdb/t/issue111.test | 4 + .../rocksdb/t/issue243_transactionStatus.test | 4 + .../suite/rocksdb/t/level_repeatable_read.test | 3 + mysql-test/suite/rocksdb/t/lock_info.test | 3 + mysql-test/suite/rocksdb/t/locking_issues.test | 3 + mysql-test/suite/rocksdb/t/max_row_locks.test | 1 + mysql-test/suite/rocksdb/t/rocksdb.test | 3 + .../suite/rocksdb/t/rocksdb_concurrent_delete.test | 4 + mysql-test/suite/rocksdb/t/rocksdb_locks.test | 3 + .../suite/rocksdb/t/rocksdb_timeout_rollback.test | 2 + mysql-test/suite/rocksdb/t/rpl_row_not_found.inc | 2 + .../suite/rocksdb/t/select_lock_in_share_mode.test | 3 + mysql-test/suite/rocksdb/t/unique_check.test | 5 + mysql-test/suite/rocksdb/t/unique_sec.inc | 10 +- mysql-test/suite/rocksdb/t/varbinary_format.test | 4 + mysql-test/suite/rocksdb/t/varchar_format.test | 2 + .../r/rocksdb_max_lock_memory_basic.result | 7 + .../r/rocksdb_use_range_locking_basic.result | 7 + .../t/rocksdb_max_lock_memory_basic.test | 5 + .../t/rocksdb_use_range_locking_basic.test | 5 + rocksdb | 2 +- storage/rocksdb/CMakeLists.txt | 8 +- storage/rocksdb/ha_rocksdb.cc | 572 +++++++++++++++++++-- storage/rocksdb/ha_rocksdb.h | 14 +- storage/rocksdb/rdb_i_s.cc | 12 +- storage/rocksdb/rdb_locking_iter.cc | 108 ++++ storage/rocksdb/rdb_locking_iter.h | 190 +++++++ 39 files changed, 988 insertions(+), 53 deletions(-) diff --cc storage/rocksdb/ha_rocksdb.cc index 52ded9a7206,d19c07bc611..333e2370919 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@@ -752,7 -739,14 +759,15 @@@ static uint32_t rocksdb_select_bypass_r static uint32_t rocksdb_select_bypass_debug_row_delay = 0; static unsigned long long // NOLINT(runtime/int) rocksdb_select_bypass_multiget_min = 0; +static bool rocksdb_skip_locks_if_skip_unique_check = false; + // Range locking: how much memory to use. + // (note that this is different from rocksdb_max_row_locks as + // that one is a hard per-thread count limit, and this one is a + // global memory limit) + static ulonglong rocksdb_max_lock_memory; + + static bool rocksdb_use_range_locking = 0; + static std::shared_ptr<rocksdb::RangeLockMgrHandle> range_lock_mgr; std::atomic<uint64_t> rocksdb_row_lock_deadlocks(0); std::atomic<uint64_t> rocksdb_row_lock_wait_timeouts(0); @@@ -2905,8 -2881,20 +2965,20 @@@ class Rdb_transaction virtual void set_sync(bool sync) = 0; - virtual void release_lock(rocksdb::ColumnFamilyHandle *const column_family, + virtual void release_lock(const Rdb_key_def &key_descr, const std::string &rowkey) = 0; + virtual + rocksdb::Status lock_range(rocksdb::ColumnFamilyHandle *const cf, + const rocksdb::Endpoint &start, + const rocksdb::Endpoint &end) = 0; + + rocksdb::Status lock_singlepoint_range(rocksdb::ColumnFamilyHandle *const cf, + const rocksdb::Slice &point) { + // Normally, one needs to "flip" the endpoint type for reverse-ordered CFs. + // But here we are locking just one point so this is not necessary. + rocksdb::Endpoint endp(point, false); + return lock_range(cf, endp, endp); + } virtual bool prepare(const rocksdb::TransactionName &name) = 0; diff --cc storage/rocksdb/ha_rocksdb.h index 72f1202d0a2,ab0c3df2865..81300833852 --- a/storage/rocksdb/ha_rocksdb.h +++ b/storage/rocksdb/ha_rocksdb.h @@@ -346,14 -346,24 +346,24 @@@ class ha_rocksdb : public my_core::hand rocksdb::Slice *lower_bound_slice, rocksdb::Slice *upper_bound_slice); void setup_scan_iterator(const Rdb_key_def &kd, rocksdb::Slice *slice, - const bool use_all_keys, const uint eq_cond_len) + const bool use_all_keys, const uint eq_cond_len, + bool use_locking_iterator) MY_ATTRIBUTE((__nonnull__)); + + int set_range_lock(Rdb_transaction *tx, + const Rdb_key_def &kd, + const enum ha_rkey_function &find_flag, + const rocksdb::Slice &slice, + const key_range *const end_key, + bool flip_rev_cf, + bool *use_locking_iterator); + void release_scan_iterator(void); - rocksdb::Status get_for_update( - Rdb_transaction *const tx, - rocksdb::ColumnFamilyHandle *const column_family, - const rocksdb::Slice &key, rocksdb::PinnableSlice *value) const; + rocksdb::Status get_for_update(Rdb_transaction *const tx, + const Rdb_key_def &kd, + const rocksdb::Slice &key, + rocksdb::PinnableSlice *value) const; int get_row_by_rowid(uchar *const buf, const char *const rowid, const uint rowid_size, const bool skip_lookup = false,

1 0

[Commits] 111963477b5: Make LEX::print support single-table DELETE.
by psergey 30 Nov '20

30 Nov '20

revision-id: 111963477b5ed57b00d347424d7a4d479c044c64 (mariadb-10.5.4-364-g111963477b5) parent(s): e34e53b554e8c3e05e3bd918204cf7ba494b1ae3 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-11-30 15:19:25 +0300 message: Make LEX::print support single-table DELETE. --- sql/sql_lex.cc | 41 +++++++++++++++++++++++++++++++++++++++-- 1 file changed, 39 insertions(+), 2 deletions(-) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index ce3bf902931..dc5449d0ab4 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3567,8 +3567,10 @@ void LEX::print(String *str, enum_query_type query_type) str->append(STRING_WITH_LEN("UPDATE ")); if (ignore) str->append(STRING_WITH_LEN("IGNORE ")); - // table name - str->append(query_tables->alias); + // table name. If the query was using a view, we need + // the underlying table name, not the view name + TABLE_LIST *base_tbl= query_tables->table->pos_in_table_list; + base_tbl->print(thd, table_map(0), str, query_type); str->append(STRING_WITH_LEN(" SET ")); // print item assignments List_iterator<Item> it(sel->item_list); @@ -3608,6 +3610,41 @@ void LEX::print(String *str, enum_query_type query_type) sel->select_limit->print(str, query_type); } } + else if (sql_command == SQLCOM_DELETE) + { + SELECT_LEX *sel= first_select_lex(); + str->append(STRING_WITH_LEN("DELETE ")); + if (ignore) + str->append(STRING_WITH_LEN("IGNORE ")); + + str->append(STRING_WITH_LEN("FROM ")); + // table name. If the query was using a view, we need + // the underlying table name, not the view name + TABLE_LIST *base_tbl= query_tables->table->pos_in_table_list; + base_tbl->print(thd, table_map(0), str, query_type); + + if (sel->where) + { + str->append(STRING_WITH_LEN(" WHERE ")); + sel->where->print(str, query_type); + } + + if (sel->order_list.elements) + { + str->append(STRING_WITH_LEN(" ORDER BY ")); + for (ORDER *ord= sel->order_list.first; ord; ord= ord->next) + { + if (ord != sel->order_list.first) + str->append(STRING_WITH_LEN(", ")); + (*ord->item)->print(str, query_type); + } + } + if (sel->select_limit) + { + str->append(STRING_WITH_LEN(" LIMIT ")); + sel->select_limit->print(str, query_type); + } + } else DBUG_ASSERT(0); // Not implemented yet }

1 0

[Commits] c1ef4d3: MDEV-24242 Query returns wrong result while using big_tables=1
by IgorBabaev 25 Nov '20

25 Nov '20

revision-id: c1ef4d36898f038702c2678f23dc6081b399ccf8 (mariadb-10.5.4-335-gc1ef4d3) parent(s): 33d41167c54fed9116cd8f0dfa01b43733988e6d author: Igor Babaev committer: Igor Babaev timestamp: 2020-11-24 20:05:54 -0800 message: MDEV-24242 Query returns wrong result while using big_tables=1 When executing set operations in a pipeline using only one temporary table additional scans of intermediate results may be needed. The scans are performed with usage of the rnd_next() handler function that might leave record buffers used for the temporary table not in a state that is good for following writes into the table. For example it happens for aria engine when the last call of rnd_next() encounters only deleted records. Thus a cleanup of record buffers is needed after each such scan of the temporary table. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/set_operation.result | 34 ++++++++++++++++++++++++++++++++++ mysql-test/main/set_operation.test | 28 ++++++++++++++++++++++++++++ sql/sql_union.cc | 4 ++++ 3 files changed, 66 insertions(+) diff --git a/mysql-test/main/set_operation.result b/mysql-test/main/set_operation.result index a021033..24d2c7f 100644 --- a/mysql-test/main/set_operation.result +++ b/mysql-test/main/set_operation.result @@ -1155,3 +1155,37 @@ count(*) 319 drop table t1; drop table t2; +# +# MDEV-24242: set expression with empty intermediate result +# when tmp_memory_table_size is set to 0 +# +create table t1 (a int, b int) engine=MyISAM; +insert into t1 values (1,1), (2,2); +create table t2 (a int, b int) engine=MyISAM; +insert into t2 values (11,11), (12,12), (13,13); +select * from t1 +except all +select * from t1 +except +select * from t1 +union all +select * from t2; +a b +12 12 +11 11 +13 13 +set tmp_memory_table_size=0; +select * from t1 +except all +select * from t1 +except +select * from t1 +union all +select * from t2; +a b +12 12 +11 11 +13 13 +set tmp_memory_table_size=default; +drop table t1,t2; +# End of 10.4 tests diff --git a/mysql-test/main/set_operation.test b/mysql-test/main/set_operation.test index c43725c..c422042f 100644 --- a/mysql-test/main/set_operation.test +++ b/mysql-test/main/set_operation.test @@ -524,3 +524,31 @@ select count(*) from drop table t1; drop table t2; + +--echo # +--echo # MDEV-24242: set expression with empty intermediate result +--echo # when tmp_memory_table_size is set to 0 +--echo # + +create table t1 (a int, b int) engine=MyISAM; +insert into t1 values (1,1), (2,2); +create table t2 (a int, b int) engine=MyISAM; +insert into t2 values (11,11), (12,12), (13,13); + +let $q= +select * from t1 +except all +select * from t1 +except +select * from t1 +union all +select * from t2; + +eval $q; +set tmp_memory_table_size=0; +eval $q; +set tmp_memory_table_size=default; + +drop table t1,t2; + +--echo # End of 10.4 tests diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 021720f..9e51bb4 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -880,6 +880,10 @@ bool select_unit_ext::send_eof() table->file->ha_rnd_end(); } + /* Clean up table buffers for the next set operation from pipeline */ + if (next_sl) + restore_record(table,s->default_values); + if (unlikely(error)) table->file->print_error(error, MYF(0));

1 0

[Commits] 4d6107d: MDEV-19179 Regression: SELECT ... UNION ... with inconsistent column names fails
by IgorBabaev 18 Nov '20

18 Nov '20

revision-id: 4d6107d9302c4c66c5783cdb3429f0726ce7ad3c (mariadb-10.2.31-580-g4d6107d) parent(s): 984a06db2ce2b2e3c7c5028245905417f2141cd7 author: Igor Babaev committer: Igor Babaev timestamp: 2020-11-18 13:21:19 -0800 message: MDEV-19179 Regression: SELECT ... UNION ... with inconsistent column names fails A bogus error message was issued when a condition was pushed into a materialized derived table or view specified as union of selects with aggregation when the corresponding columns of the selects had different names. This happened because the expression pushed into having clauses of the selects was adjusted for the names of the first select of the union. The easiest solution was to rename the columns of the other selects to be name compatible with the columns of the first select. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/r/derived_cond_pushdown.result | 41 +++++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 28 +++++++++++++++++++++ sql/item.h | 6 +++++ sql/sql_derived.cc | 22 +++++++++++++++-- 4 files changed, 95 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index d4e8fef..25237aa 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -10593,4 +10593,45 @@ a abc DROP VIEW v1; DROP TABLE t1; +# +# MDEV-19179: pushdown into UNION of aggregation selects whose +# corresponding columns have different names +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +select * +from (select min(a) as x from t1 union all select max(a) as y from t1) t +where x>0; +x +1 +7 +explain extended select * +from (select min(a) as x from t1 union all select max(a) as y from t1) t +where x>0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 Using where +2 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 +3 UNION t1 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `t`.`x` AS `x` from (select min(`test`.`t1`.`a`) AS `x` from `test`.`t1` having `x` > 0 union all select max(`test`.`t1`.`a`) AS `x` from `test`.`t1` having `x` > 0) `t` where `t`.`x` > 0 +prepare stmt from "select * +from (select min(a) as x from t1 union all select max(a) as y from t1) t +where x>0"; +execute stmt; +x +1 +7 +execute stmt; +x +1 +7 +deallocate prepare stmt; +create view v1(m) as +select min(a) as x from t1 union all select max(a) as y from t1; +select * from v1 where m > 0; +m +1 +7 +drop view v1; +drop table t1; # End of 10.2 tests diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index a7df65f..31b4904 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -2184,4 +2184,32 @@ SELECT * FROM v1 WHERE IF( a REGEXP 'def', 'foo', a ) IN ('abc', 'foobar'); DROP VIEW v1; DROP TABLE t1; +--echo # +--echo # MDEV-19179: pushdown into UNION of aggregation selects whose +--echo # corresponding columns have different names +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +let $q= +select * +from (select min(a) as x from t1 union all select max(a) as y from t1) t +where x>0; + +eval $q; +eval explain extended $q; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +create view v1(m) as +select min(a) as x from t1 union all select max(a) as y from t1; +select * from v1 where m > 0; + +drop view v1; +drop table t1; + --echo # End of 10.2 tests diff --git a/sql/item.h b/sql/item.h index a49f9e8..ed20074 100644 --- a/sql/item.h +++ b/sql/item.h @@ -818,6 +818,12 @@ class Item: public Value_source, void set_name_for_rollback(THD *thd, const char *str, uint length, CHARSET_INFO *cs); void rename(char *new_name); + void share_name_with(Item *item) + { + name= item->name; + name_length= item->name_length; + is_autogenerated_name= item->is_autogenerated_name; + } void init_make_field(Send_field *tmp_field,enum enum_field_types type); virtual void cleanup(); virtual void make_field(THD *thd, Send_field *field); diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 39499e6..5379dd4 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1199,7 +1199,8 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) DBUG_RETURN(false); st_select_lex_unit *unit= derived->get_unit(); - st_select_lex *sl= unit->first_select(); + st_select_lex *first_sl= unit->first_select(); + st_select_lex *sl= first_sl; if (derived->prohibit_cond_pushdown) DBUG_RETURN(false); @@ -1311,7 +1312,24 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) if (!extracted_cond_copy) continue; } - + + /* + Rename the columns of all non-first selects of a union to be compatible + by names with the columns of the first select. It will allow to use copies + of the same expression pushed into having clauses of different selects. + */ + if (sl != first_sl) + { + DBUG_ASSERT(sl->item_list.elements == first_sl->item_list.elements); + List_iterator_fast<Item> it(sl->item_list); + List_iterator_fast<Item> nm_it(unit->types); + Item * item; + while((item= it++)) + { + item->share_name_with(nm_it++); + } + } + /* Transform the references to the 'derived' columns from the condition pushed into the having clause of sl to make them usable in the new context

1 0

[Commits] 5a733ab: MDEV-19179 Regression: SELECT ... UNION ... with inconsistent column names fails
by IgorBabaev 18 Nov '20

18 Nov '20

revision-id: 5a733abaa2780164ddbdb9a26d8eeaba47fb3642 (mariadb-10.2.31-580-g5a733ab) parent(s): 984a06db2ce2b2e3c7c5028245905417f2141cd7 author: Igor Babaev committer: Igor Babaev timestamp: 2020-11-18 12:20:00 -0800 message: MDEV-19179 Regression: SELECT ... UNION ... with inconsistent column names fails A bogus error message was issued when a condition was pushed into a materialized derived table or view specified as union of selects with aggregation when the corresponding columns of the selects had different names. This happened because the expression pushed into having clauses of the selects was adjusted for the names of the first select of the union. The easiest solution was to rename the columns of the other selects to be name compatible with the columns of the first select. --- mysql-test/r/derived_cond_pushdown.result | 41 +++++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 28 +++++++++++++++++++++ sql/item.h | 6 +++++ sql/sql_derived.cc | 22 +++++++++++++++-- 4 files changed, 95 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index d4e8fef..25237aa 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -10593,4 +10593,45 @@ a abc DROP VIEW v1; DROP TABLE t1; +# +# MDEV-19179: pushdown into UNION of aggregation selects whose +# corresponding columns have different names +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +select * +from (select min(a) as x from t1 union all select max(a) as y from t1) t +where x>0; +x +1 +7 +explain extended select * +from (select min(a) as x from t1 union all select max(a) as y from t1) t +where x>0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 Using where +2 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 +3 UNION t1 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `t`.`x` AS `x` from (select min(`test`.`t1`.`a`) AS `x` from `test`.`t1` having `x` > 0 union all select max(`test`.`t1`.`a`) AS `x` from `test`.`t1` having `x` > 0) `t` where `t`.`x` > 0 +prepare stmt from "select * +from (select min(a) as x from t1 union all select max(a) as y from t1) t +where x>0"; +execute stmt; +x +1 +7 +execute stmt; +x +1 +7 +deallocate prepare stmt; +create view v1(m) as +select min(a) as x from t1 union all select max(a) as y from t1; +select * from v1 where m > 0; +m +1 +7 +drop view v1; +drop table t1; # End of 10.2 tests diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index a7df65f..31b4904 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -2184,4 +2184,32 @@ SELECT * FROM v1 WHERE IF( a REGEXP 'def', 'foo', a ) IN ('abc', 'foobar'); DROP VIEW v1; DROP TABLE t1; +--echo # +--echo # MDEV-19179: pushdown into UNION of aggregation selects whose +--echo # corresponding columns have different names +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +let $q= +select * +from (select min(a) as x from t1 union all select max(a) as y from t1) t +where x>0; + +eval $q; +eval explain extended $q; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +create view v1(m) as +select min(a) as x from t1 union all select max(a) as y from t1; +select * from v1 where m > 0; + +drop view v1; +drop table t1; + --echo # End of 10.2 tests diff --git a/sql/item.h b/sql/item.h index a49f9e8..ed20074 100644 --- a/sql/item.h +++ b/sql/item.h @@ -818,6 +818,12 @@ class Item: public Value_source, void set_name_for_rollback(THD *thd, const char *str, uint length, CHARSET_INFO *cs); void rename(char *new_name); + void share_name_with(Item *item) + { + name= item->name; + name_length= item->name_length; + is_autogenerated_name= item->is_autogenerated_name; + } void init_make_field(Send_field *tmp_field,enum enum_field_types type); virtual void cleanup(); virtual void make_field(THD *thd, Send_field *field); diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 39499e6..f1bd2a5 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1199,7 +1199,8 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) DBUG_RETURN(false); st_select_lex_unit *unit= derived->get_unit(); - st_select_lex *sl= unit->first_select(); + st_select_lex *first_sl= unit->first_select(); + st_select_lex *sl= first_sl; if (derived->prohibit_cond_pushdown) DBUG_RETURN(false); @@ -1311,7 +1312,24 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) if (!extracted_cond_copy) continue; } - + + /* + Rename the columns of all non-first selects of a union to be compatible + by names with the columns of the first select. It will allow to use copies + of the same expression pushed into having clauses of different selects. + */ + if (sl != first_sl) + { + DBUG_ASSERT(sl->item_list.elements == first_sl->item_list.elements); + List_iterator_fast<Item> it(sl->item_list); + List_iterator_fast<Item> nm_it(sl->master_unit()->types); + Item * item; + while((item= it++)) + { + item->share_name_with(nm_it++); + } + } + /* Transform the references to the 'derived' columns from the condition pushed into the having clause of sl to make them usable in the new context

1 0

[Commits] d809cdf: MDEV-24220 Server crash in base_list_iterator::next or
by IgorBabaev 17 Nov '20

17 Nov '20

revision-id: d809cdf8e4a68fd3af147d99f9308fbf789d0b86 (mariadb-10.2.31-581-gd809cdf) parent(s): 190e8a4c2aeb417b405756b193e135c542d46b34 author: Igor Babaev committer: Igor Babaev timestamp: 2020-11-17 14:28:30 -0800 message: MDEV-24220 Server crash in base_list_iterator::next or in TABLE_LIST::is_recursive_with_tables After the patch for MDEV-23619 the code of st_select_lex::cleanup started using the list st_select_lex::leaf_tables. This list is built for any query with FROM clause in the function setup_tables(). If such query is used in a stored procedure it must be ensured that the list is empty before each new call of the procedure. Otherwise if the first call of the procedure is successful while the second call reports an error before the setup_tables() is invoked then list st_select_lex::leaf_tables would point to a piece of memory that has been already freed. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/r/sp.result | 20 ++++++++++++++++++++ mysql-test/t/sp.test | 25 +++++++++++++++++++++++++ sql/sql_union.cc | 1 + 3 files changed, 46 insertions(+) diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index c4d3779..b679f3f 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -8467,3 +8467,23 @@ $$ ERROR 22007: Incorrect integer value: 'y' for column ``.``.`a` at row 1 DROP TABLE t1; SET sql_mode=DEFAULT; +# +# MDEV-24220: error when opening a table for the second call of SP +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE VIEW v1 AS SELECT MAX(a) as f FROM t1; +CREATE PROCEDURE p1() +BEGIN +SELECT * FROM v1; +END $ +CALL p1; +f +2 +ALTER TABLE t1 DROP a; +CALL p1; +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; +#End of 10.2 tests diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 99b8430..f13b3fb 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -10001,3 +10001,28 @@ $$ DELIMITER ;$$ DROP TABLE t1; SET sql_mode=DEFAULT; + +--echo # +--echo # MDEV-24220: error when opening a table for the second call of SP +--echo # + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE VIEW v1 AS SELECT MAX(a) as f FROM t1; +--delimiter $ +CREATE PROCEDURE p1() +BEGIN + SELECT * FROM v1; +END $ +--delimiter ; + +CALL p1; +ALTER TABLE t1 DROP a; +-- error ER_VIEW_INVALID +CALL p1; + +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; + +--echo #End of 10.2 tests diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 9a16237..7716f79 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1568,6 +1568,7 @@ bool st_select_lex::cleanup() delete join; join= 0; } + leaf_tables.empty(); for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ; lex_unit= lex_unit->next_unit()) {

1 0

[Commits] 4d16442: MDEV-24220 Server crash in base_list_iterator::next or
by IgorBabaev 17 Nov '20

17 Nov '20

revision-id: 4d1644216fde19c7198b9c15110b513dac0f5a72 (mariadb-10.2.31-581-g4d16442) parent(s): 190e8a4c2aeb417b405756b193e135c542d46b34 author: Igor Babaev committer: Igor Babaev timestamp: 2020-11-16 19:59:50 -0800 message: MDEV-24220 Server crash in base_list_iterator::next or in TABLE_LIST::is_recursive_with_tables After the patch for MDEV-23619 the code of st_select_lex::cleanup started using the list st_select_lex::leaf_tables. This list is built for any query with FROM clause in the function setup_tables(). If such query is used in a stored procedure it must be ensured that the list is empty before each new call of the procedure. Otherwise if the first call of the procedure is successful while the second call reports an error before the setup_tables() is invoked then list st_select_lex::leaf_tables would point to a piece of memory that has been already freed. --- mysql-test/r/sp.result | 20 ++++++++++++++++++++ mysql-test/t/sp.test | 25 +++++++++++++++++++++++++ sql/sql_union.cc | 1 + 3 files changed, 46 insertions(+) diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index c4d3779..b679f3f 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -8467,3 +8467,23 @@ $$ ERROR 22007: Incorrect integer value: 'y' for column ``.``.`a` at row 1 DROP TABLE t1; SET sql_mode=DEFAULT; +# +# MDEV-24220: error when opening a table for the second call of SP +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE VIEW v1 AS SELECT MAX(a) as f FROM t1; +CREATE PROCEDURE p1() +BEGIN +SELECT * FROM v1; +END $ +CALL p1; +f +2 +ALTER TABLE t1 DROP a; +CALL p1; +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; +#End of 10.2 tests diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 99b8430..f13b3fb 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -10001,3 +10001,28 @@ $$ DELIMITER ;$$ DROP TABLE t1; SET sql_mode=DEFAULT; + +--echo # +--echo # MDEV-24220: error when opening a table for the second call of SP +--echo # + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE VIEW v1 AS SELECT MAX(a) as f FROM t1; +--delimiter $ +CREATE PROCEDURE p1() +BEGIN + SELECT * FROM v1; +END $ +--delimiter ; + +CALL p1; +ALTER TABLE t1 DROP a; +-- error ER_VIEW_INVALID +CALL p1; + +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; + +--echo #End of 10.2 tests diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 9a16237..7716f79 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1568,6 +1568,7 @@ bool st_select_lex::cleanup() delete join; join= 0; } + leaf_tables.empty(); for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ; lex_unit= lex_unit->next_unit()) {

1 0