Debian Codegraph @

mysql-test/suite/innodb/t/lock_partitions.test

raw
1 --source include/count_sessions.inc
2
3 # This is the essense of the scenario from the report in Bug#33724166.
4 # There are two rows:
5 # c0000 | c0003 | c0004 | partition
6 # ------+-------+-------+----------
7 # 123 | 12 | 0 | 5
8 # 122 | 13 | 0 | 2
9 #
10 # It's important that 5 is the last of 6 partitions.
11 # A buggy implementation, would keep the internal state after the loop
12 # over all partitions, and reuse this state when handling subsequent
13 # unlock request from Server layer - in other words, will use state
14 # of the locks of 5th partition 5, when reasoning about 2nd partition locks.
15 # The con1 is only used to create an X lock on row from 2nd partition, so that
16 # the con2 is forced to use semi-consistent read on it without a lock.
17 # The InnoDB should correctly track which row needed a lock and which didn't,
18 # during the initial phase of filling up the priority queue with one record from
19 # each partition, and then correctly handle unlock requests from Server layer,
20 # ignoring request for 2nd partition, and performing it for 5th partition only.
21
22 CREATE TABLE t (
23 `c0000` INT NOT NULL,
24 `c0003` TINYINT NOT NULL,
25 `c0004` INT NOT NULL,
26 PRIMARY KEY (`c0000`,`c0003`,`c0004`)
27 )
28 PARTITION BY KEY (c0003) PARTITIONS 6;
29
30 INSERT INTO t VALUES (123,12,0);
31 INSERT INTO t VALUES (122,13,0);
32
33 --connect (con1, localhost, root,,)
34 BEGIN;
35 SELECT * FROM t WHERE `c0000` = 122 AND `c0003` = 13 FOR UPDATE;
36
37 --connect (con2, localhost, root,,)
38 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
39 BEGIN;
40 # The WHERE condition intentionally doesn't match any record, but in a way
41 # which InnoDB can't determine (no condition push down) thus reporting the
42 # record to the Server layer
43 UPDATE t SET `c0003` = 16 WHERE `c0004`;
44 COMMIT;
45
46 --connection con1
47 COMMIT;
48
49 --connection default
50 --disconnect con1
51 --disconnect con2
52 DROP TABLE t;
53
54 --source include/wait_until_count_sessions.inc
55
56 # Here again we have the same two rows:
57 # c0000 | c0003 | c0004 | partition
58 # ------+-------+-------+----------
59 # 123 | 12 | 0 | 5
60 # 122 | 13 | 0 | 2
61 #
62 # The transaction first locks the record from 2nd partition, which can be reused
63 # in the subsequent UPDATE query. Such reused lock should not be released even
64 # if a row doesn't match the UPDATE's WHERE condition, because this lock was
65 # explicitly needed for a previous SELECT. InnoDB should correctly track that
66 # it reused a lock for record in 2nd partition, but has created a new lock for
67 # the record in 5th partition - and the latter can be released.
68
69 CREATE TABLE t (
70 `c0000` INT NOT NULL,
71 `c0003` TINYINT NOT NULL,
72 `c0004` INT NOT NULL,
73 PRIMARY KEY (`c0000`,`c0003`,`c0004`)
74 )
75 PARTITION BY KEY (c0003) PARTITIONS 6;
76
77 INSERT INTO t VALUES (121,13,0); # partition 2
78 INSERT INTO t VALUES (123,12,0); # partition 5
79
80 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
81 BEGIN;
82 # put an X REC_NOT_GAP lock on the first row in partition#2
83 SELECT * FROM t WHERE `c0000` = 121 AND `c0003` = 13 AND `c0004` = 0 FOR UPDATE;
84 SELECT LOCK_DATA
85 FROM performance_schema.data_locks
86 WHERE LOCK_TYPE = 'RECORD' AND OBJECT_NAME = 't';
87 # now try to iterate over rows from all partitions
88 UPDATE t SET `c0003` = 16 WHERE `c0004`;
89 SELECT LOCK_DATA
90 FROM performance_schema.data_locks
91 WHERE LOCK_TYPE = 'RECORD' AND OBJECT_NAME = 't';
92 COMMIT;
93
94 DROP TABLE t;
95
96 # Here we have one row in each of 6 partitions, except for the last one (5th).
97 # InnoDB should properly track that it has created a lock in 5 of 6 partitions,
98 # and then release them, as none of the records matches the WHERE condition.
99
100 CREATE TABLE t (
101 `c0000` INT NOT NULL,
102 `c0003` TINYINT NOT NULL,
103 `c0004` INT NOT NULL,
104 PRIMARY KEY (`c0000`,`c0003`,`c0004`)
105 )
106 PARTITION BY KEY (c0003) PARTITIONS 6;
107
108 INSERT INTO t VALUES (1,10,0);
109 INSERT INTO t VALUES (2,11,0);
110 #INSERT INTO t VALUES (3,12,0); this would be partition 5, but we omit it
111 INSERT INTO t VALUES (4,13,0);
112 INSERT INTO t VALUES (5,14,0);
113 INSERT INTO t VALUES (6,15,0);
114 INSERT INTO t VALUES (7,16,0);
115
116 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
117 BEGIN;
118 SELECT LOCK_DATA
119 FROM performance_schema.data_locks
120 WHERE LOCK_TYPE = 'RECORD' AND OBJECT_NAME = 't';
121 # now try to iterate over rows from all partitions
122 UPDATE t SET `c0003` = 16 WHERE `c0004`;
123 SELECT LOCK_DATA
124 FROM performance_schema.data_locks
125 WHERE LOCK_TYPE = 'RECORD' AND OBJECT_NAME = 't';
126 COMMIT;
127
128 DROP TABLE t;