Debian Codegraph @

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

raw
1 # Test of SKIP LOCKED and NOWAIT on partitioned tables.
2 # Must have same results as not partitioned tables.
3 --source include/have_innodb.inc
4 --source include/have_partition.inc
5
6 set @start_read_only= @@global.read_only;
7 set @start_autocommit= @@global.autocommit;
8 set default_storage_engine= innodb;
9 set @@global.autocommit= 0;
10
11 CREATE USER test@localhost;
12 grant CREATE, SELECT, UPDATE, INSERT on *.* to test@localhost;
13 CREATE USER test2@localhost;
14 grant CREATE, SELECT, UPDATE on *.* to test2@localhost;
15
16 CREATE TABLE t1 (
17 a char(2) NOT NULL,
18 b char(2) NOT NULL,
19 c int(10) unsigned NOT NULL,
20 d varchar(255) DEFAULT NULL,
21 e varchar(1000) DEFAULT NULL,
22 PRIMARY KEY (a, b, c),
23 KEY (a),
24 KEY (a, b)
25 ) charset latin1 PARTITION BY KEY (a) PARTITIONS 20;
26
27 INSERT INTO t1 (a, b, c, d, e) VALUES
28 ('07', '03', 343, '1', '07_03_343'),
29 ('01', '04', 343, '2', '01_04_343'),
30 ('01', '06', 343, '3', '01_06_343'),
31 ('01', '07', 343, '4', '01_07_343'),
32 ('01', '08', 343, '5', '01_08_343'),
33 ('01', '09', 343, '6', '01_09_343'),
34 ('03', '03', 343, '7', '03_03_343'),
35 ('03', '06', 343, '8', '03_06_343'),
36 ('03', '07', 343, '9', '03_07_343'),
37 ('04', '03', 343, '10', '04_03_343'),
38 ('04', '06', 343, '11', '04_06_343'),
39 ('05', '03', 343, '12', '05_03_343'),
40 ('11', '03', 343, '13', '11_03_343'),
41 ('11', '04', 343, '14', '11_04_343')
42 ;
43
44 CREATE TABLE t2 (a int, name VARCHAR(50), purchased DATE)
45 PARTITION BY RANGE (a)
46 (PARTITION p0 VALUES LESS THAN (3),
47 PARTITION p1 VALUES LESS THAN (7),
48 PARTITION p2 VALUES LESS THAN (9),
49 PARTITION p3 VALUES LESS THAN (11));
50 INSERT INTO t2 VALUES
51 (1, 'desk organiser', '2003-10-15'),
52 (2, 'CD player', '1993-11-05'),
53 (3, 'TV set', '1996-03-10'),
54 (4, 'bookcase', '1982-01-10'),
55 (5, 'exercise bike', '2004-05-09'),
56 (6, 'sofa', '1987-06-05'),
57 (7, 'popcorn maker', '2001-11-22'),
58 (8, 'acquarium', '1992-08-04'),
59 (9, 'study desk', '1984-09-16'),
60 (10, 'lava lamp', '1998-12-25');
61
62 CREATE TABLE t3 SELECT * FROM t1;
63 ALTER TABLE t3 ADD PRIMARY KEY (d);
64 ALTER TABLE t3 ADD KEY (a);
65 ALTER TABLE t3 ADD KEY (a, b);
66 ANALYZE TABLE t3;
67 #SELECT * FROM t3;
68
69 --echo ########################################################################
70 connect (con1,localhost,test,,test);
71 BEGIN;
72 SELECT d,a,b,c FROM t1 partition (p0);
73 SELECT d,a,b,c FROM t1 partition (p1);
74 SELECT d,a,b,c FROM t1 partition (p2);
75 SELECT d,a,b,c FROM t1 partition (p3);
76 SELECT d,a,b,c FROM t1 partition (p4);
77 SELECT d,a,b,c FROM t1 partition (p5);
78 SELECT d,a,b,c FROM t1 partition (p6);
79 SELECT d,a,b,c FROM t1 partition (p7);
80 SELECT d,a,b,c FROM t1 partition (p8);
81 SELECT d,a,b,c FROM t1 partition (p9);
82 SELECT d,a,b,c FROM t1 partition (p10);
83 SELECT d,a,b,c FROM t1 partition (p11);
84 SELECT d,a,b,c FROM t1 partition (p12);
85 SELECT d,a,b,c FROM t1 partition (p13);
86 SELECT d,a,b,c FROM t1 partition (p14);
87 SELECT d,a,b,c FROM t1 partition (p15);
88 SELECT d,a,b,c FROM t1 partition (p16);
89 SELECT d,a,b,c FROM t1 partition (p17);
90 SELECT d,a,b,c FROM t1 partition (p18);
91 SELECT d,a,b,c FROM t1 partition (p19);
92
93 SELECT * FROM t1 WHERE a='01' FOR UPDATE ;
94 SELECT * FROM t3 FORCE INDEX(a) WHERE a='01' FOR UPDATE ;
95
96 connect (con2,localhost,test,,test);
97 BEGIN;
98 SET SESSION innodb_lock_wait_timeout=1;
99
100 --echo #
101 --echo # SHARE ...
102 --error ER_LOCK_WAIT_TIMEOUT
103 SELECT * FROM t1 LOCK IN SHARE MODE;
104 --error ER_LOCK_WAIT_TIMEOUT
105 SELECT * FROM t1 LOCK IN SHARE MODE NOWAIT;
106 SELECT * FROM t1 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED;
107 SELECT a,count(b) FROM t1 force index (a) GROUP BY a ORDER BY a LOCK IN SHARE MODE SKIP LOCKED;
108 SELECT d,a,b,c FROM t1 partition (p1,p9,p11,p17) ORDER BY d
109 LOCK IN SHARE MODE SKIP LOCKED;
110 SELECT d,a,b,c FROM t1 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED;
111 ANALYZE TABLE t3;
112 SELECT d,a,b,c FROM t3 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED;
113 explain SELECT d,a,b,c FROM t3 ORDER BY d
114 LOCK IN SHARE MODE SKIP LOCKED;
115
116 --echo #
117 --echo # UPDATE ...
118 --error ER_LOCK_WAIT_TIMEOUT
119 SELECT * FROM t1 FOR UPDATE;
120 --error ER_LOCK_WAIT_TIMEOUT
121 SELECT * FROM t1 FOR UPDATE NOWAIT;
122 SELECT * FROM t1 ORDER BY a FOR UPDATE SKIP LOCKED;
123 SELECT a,count(b) FROM t1 GROUP BY a ORDER BY a FOR UPDATE SKIP LOCKED;
124 SELECT d,a,b,c FROM t1 partition (p1,p9,p11,p17) ORDER BY d
125 FOR UPDATE SKIP LOCKED;
126 SELECT d,a,b,c FROM t1 ORDER BY d FOR UPDATE SKIP LOCKED;
127 SELECT d,a,b,c FROM t3 ORDER BY d FOR UPDATE SKIP LOCKED;
128
129 connection con1;
130 COMMIT;
131
132 connection con1;
133 BEGIN;
134 SELECT * FROM t1 WHERE a='01' LOCK IN SHARE MODE ;
135 SELECT * FROM t3 FORCE INDEX(a) WHERE a='01' LOCK IN SHARE MODE ;
136
137 connection con2;
138 BEGIN;
139 SET SESSION innodb_lock_wait_timeout=1;
140
141 --echo #
142 --echo # SHARE ...
143 SELECT * FROM t1 LOCK IN SHARE MODE;
144 SELECT * FROM t1 LOCK IN SHARE MODE NOWAIT;
145 SELECT * FROM t1 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED;
146 SELECT a,count(b) FROM t1 GROUP BY a ORDER BY a LOCK IN SHARE MODE SKIP LOCKED;
147 SELECT d,a,b,c FROM t1 partition (p1,p9,p11,p17) ORDER BY d
148 LOCK IN SHARE MODE SKIP LOCKED;
149 SELECT d,a,b,c FROM t1 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED;
150 ANALYZE TABLE t3;
151 SELECT d,a,b,c FROM t3 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED;
152 explain SELECT d,a,b,c FROM t3 ORDER BY d
153 LOCK IN SHARE MODE SKIP LOCKED;
154
155 --echo #
156 --echo # UPDATE ...
157 --error ER_LOCK_WAIT_TIMEOUT
158 SELECT * FROM t1 FOR UPDATE;
159 --error ER_LOCK_WAIT_TIMEOUT
160 SELECT * FROM t1 FOR UPDATE NOWAIT;
161 SELECT * FROM t1 ORDER BY a FOR UPDATE SKIP LOCKED;
162 SELECT a,count(b) FROM t1 GROUP BY a ORDER BY a FOR UPDATE SKIP LOCKED;
163 SELECT d,a,b,c FROM t1 partition (p1,p9,p11,p17) ORDER BY d
164 FOR UPDATE SKIP LOCKED;
165 SELECT d,a,b,c FROM t1 ORDER BY d FOR UPDATE SKIP LOCKED;
166 SELECT d,a,b,c FROM t3 ORDER BY d FOR UPDATE SKIP LOCKED;
167
168 connection default;
169 disconnect con1;
170 disconnect con2;
171
172 DROP TABLE t1, t2, t3;
173
174 DROP USER test@localhost;
175 DROP USER test2@localhost;
176 set @@global.read_only= @start_read_only;
177 set @@global.autocommit= @start_autocommit;
178 set default_storage_engine= default;