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