1# 2# Check concurrent locking issues: 3# Rows scanned but are not in the updated table should be locked when 4# rocksdb_lock_scanned_rows is on but not locked otherwise. 5# 6# To call this, set $isolation_level and $lock_scanned_rows and call this file 7# 8# let $isolation_level = REPEATABLE READ; 9# let $lock_scanned_rows = 0 (or 1) 10# --source suite/rocksdb/include/locking_issues_case7.inc 11# 12 13--echo 14--echo ----------------------------------------------------------------------- 15--echo - Locking issues case 7: 16--echo - Rows that are scanned as part of a query but not in the table being 17--echo - updated should not be locked unless rocksdb_lock_scanned_rows is on 18--echo ----------------------------------------------------------------------- 19 20--disable_warnings 21DROP TABLE IF EXISTS t1, t2; 22--enable_warnings 23 24SELECT @@global.rocksdb_lock_scanned_rows; 25 26if ($lock_scanned_rows) 27{ 28 let $original_val=query_get_value( 29 select @@global.rocksdb_lock_scanned_rows as val, val, 1); 30 SET GLOBAL rocksdb_lock_scanned_rows=ON; 31} 32 33CREATE TABLE t1(id INT PRIMARY KEY, value INT); 34CREATE TABLE t2(id INT PRIMARY KEY, value INT); 35INSERT INTO t1 VALUES (1,1), (2,2), (3,3); 36INSERT INTO t2 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); 37 38connect (con1,localhost,root,,); 39connect (con2,localhost,root,,); 40 41connection con1; 42eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level; 43BEGIN; 44 45connection con2; 46eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level; 47BEGIN; 48 49--echo lock_scanned_rows is $lock_scanned_rows 50if ($lock_scanned_rows == 1) 51{ 52 connection con1; 53 # This is expected to leave a lock id=3 in t2; 54 UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3; 55 56 connection con2; 57 --error ER_LOCK_WAIT_TIMEOUT 58 UPDATE t2 SET value=value+100 WHERE id=3; 59 60 # No other row in t2 should be locked; 61 UPDATE t2 SET value=value+100 WHERE id IN (1,2,4,5); 62 SELECT * FROM t2; 63} 64 65if ($lock_scanned_rows == 0) 66{ 67 connection con1; 68 # This should leave no locks on any row in t2; 69 UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3; 70 71 connection con2; 72 UPDATE t2 SET value=value+100; 73 SELECT * FROM t2; 74} 75 76connection con1; 77COMMIT; 78 79connection default; 80disconnect con1; 81disconnect con2; 82 83DROP TABLE t1; 84DROP TABLE t2; 85 86if ($lock_scanned_rows == 1) 87{ 88 eval SET GLOBAL rocksdb_lock_scanned_rows=$original_val; 89} 90