Debian Codegraph @

mysql-test/suite/x/t/find_row_locking.test

raw
1 ## Tests for row locking in SQL find.
2
3 ## Preamble
4 --source include/xplugin_preamble.inc
5 --source include/xplugin_create_user.inc
6
7 let $old_innodb_lock_wait_timeout= `SELECT @@innodb_lock_wait_timeout`;
8 create schema xtest default charset 'utf8mb4';
9
10 # Tests
11 --write_file $MYSQL_TMP_DIR/find_row_locking.xpl
12 -->quiet
13
14 -->macro Check_data_locks_table
15 -->noquery_result
16 -->stmtsql SELECT COUNT(*) FROM performance_schema.data_locks WHERE OBJECT_NAME='test_coll';
17 -->recvtovar %NUM%
18 -->endmacro
19
20 -->macro Expect_data_locks_table_is_not_empty
21 -->echo Verify [Expecting performance_schema.data_locks to be not empty]
22 -->callmacro Check_data_locks_table
23 -->assert_gt %NUM% 0
24 -->query_result
25 -->endmacro
26
27 -->macro Expect_data_locks_table_is_empty
28 -->echo Verify [Expecting performance_schema.data_locks to be empty]
29 -->callmacro Check_data_locks_table
30 -->assert_eq 0 %NUM%
31 -->query_result
32 -->endmacro
33
34 -->macro Rollback_sessions
35 -->callmacro Expect_data_locks_table_is_not_empty
36 -->echo switched to session session1
37 -->setsession session1
38 -->stmtsql ROLLBACK;
39 -->recvresult
40 -->echo switched to session session2
41 -->setsession session2
42 -->stmtsql ROLLBACK;
43 -->recvresult
44 -->callmacro Expect_data_locks_table_is_empty
45 -->endmacro
46
47 -->macro Use_first_transation
48 -->echo switched to session session1
49 -->setsession session1
50 -->callmacro Expect_data_locks_table_is_empty
51 -->stmtsql START TRANSACTION;
52 -->recvresult
53 -->endmacro
54
55 -->macro Switch_to_second_transaction
56 -->callmacro Expect_data_locks_table_is_not_empty
57 -->echo switched to session session2
58 -->setsession session2
59 -->stmtsql START TRANSACTION;
60 -->recvresult
61 -->endmacro
62
63 -->echo ######### preparation #########
64
65 -->stmtadmin create_collection {"schema":"xtest","name":"test_coll"}
66 -->recvresult
67
68 -->sql
69 use xtest;
70 insert into test_coll (doc) values ('{"_id": "1", "a": 1}');
71 insert into test_coll (doc) values ('{"_id": "2", "a": 2}');
72 insert into test_coll (doc) values ('{"_id": "3", "a": 30}');
73 insert into test_coll (doc) values ('{"_id": "4", "a": 30}');
74 set global innodb_lock_wait_timeout=3;
75 -->endsql
76
77 -->newsession session1 x_root
78 -->newsession session2 x_root
79
80 -->macro Send_simple_find_with_lock %ID% %VALUE% %LOCKING%
81 Mysqlx.Crud.Find {
82 collection {
83 name: "test_coll"
84 schema: "xtest"
85 }
86 data_model: DOCUMENT
87 criteria {
88 type: OPERATOR
89 operator {
90 name: "=="
91 param {
92 type: IDENT identifier {
93 name: "%ID%"
94 }
95 }
96 param {
97 type: LITERAL
98 literal {
99 type: V_STRING
100 v_string {
101 value: "%VALUE%"
102 }
103 }
104 }
105 }
106 }
107 locking: %LOCKING%
108 }
109 -->endmacro
110
111 -->echo ######### Shared lock after shared lock #########
112 -->callmacro Use_first_transation
113 -->callmacro Send_simple_find_with_lock _id 1 SHARED_LOCK
114 -->recvresult
115 -->callmacro Switch_to_second_transaction
116 -->callmacro Send_simple_find_with_lock _id 2 SHARED_LOCK
117 -->recvresult
118 -->callmacro Send_simple_find_with_lock _id 1 SHARED_LOCK
119 -->recvresult
120 -->callmacro Rollback_sessions
121
122 -->echo ######### Shared lock after exclusive lock #########
123 -->callmacro Use_first_transation
124 -->callmacro Send_simple_find_with_lock _id 1 EXCLUSIVE_LOCK
125 -->recvresult
126 -->callmacro Switch_to_second_transaction
127 -->callmacro Send_simple_find_with_lock _id 2 EXCLUSIVE_LOCK
128 -->recvresult
129 -->callmacro Send_simple_find_with_lock _id 1 EXCLUSIVE_LOCK
130 -->expecterror ER_LOCK_WAIT_TIMEOUT
131 -->recvresult
132 -->callmacro Rollback_sessions
133
134 -->echo ######### Exclusive lock after shared lock #########
135 -->callmacro Use_first_transation
136 -->callmacro Send_simple_find_with_lock _id 1 SHARED_LOCK
137 -->recvresult
138 -->callmacro Switch_to_second_transaction
139 -->callmacro Send_simple_find_with_lock _id 2 EXCLUSIVE_LOCK
140 -->recvresult
141 -->callmacro Send_simple_find_with_lock _id 3 EXCLUSIVE_LOCK
142 -->recvresult
143 -->callmacro Send_simple_find_with_lock _id 1 EXCLUSIVE_LOCK
144 -->expecterror ER_LOCK_WAIT_TIMEOUT
145 -->recvresult
146 -->callmacro Rollback_sessions
147
148 -->echo ######### Exclusive lock after exclusive lock #########
149 -->callmacro Use_first_transation
150 -->callmacro Send_simple_find_with_lock _id 1 EXCLUSIVE_LOCK
151 -->recvresult
152 -->callmacro Switch_to_second_transaction
153 -->callmacro Send_simple_find_with_lock _id 2 EXCLUSIVE_LOCK
154 -->recvresult
155 -->callmacro Send_simple_find_with_lock _id 1 EXCLUSIVE_LOCK
156 -->expecterror ER_LOCK_WAIT_TIMEOUT
157 -->recvresult
158 -->callmacro Rollback_sessions
159
160
161 -->echo ######### Locking with aggregate #########
162
163 -->macro Check_locking_with_aggregate %LOCK_TYPE%
164 -->callmacro Use_first_transation
165 -->callmacro Send_simple_find_with_lock _id 1 EXCLUSIVE_LOCK
166 -->recvresult
167 -->callmacro Switch_to_second_transaction
168
169 Mysqlx.Crud.Find {
170 collection {
171 name: "test_coll"
172 schema: "xtest"
173 }
174 data_model: DOCUMENT
175 projection {
176 alias: "a"
177 source {
178 type: IDENT
179 identifier {
180 document_path {
181 type: MEMBER
182 value: "a"
183 }
184 }
185 }
186 }
187 grouping {
188 type: IDENT
189 identifier {
190 document_path {
191 type: MEMBER
192 value: "a"
193 }
194 }
195 }
196 locking: %LOCK_TYPE%
197 }
198 -->recvresult
199
200 -->callmacro Rollback_sessions
201 -->endmacro
202
203 -->echo ######### Shared lock with grouping #########
204 -->callmacro Check_locking_with_aggregate SHARED_LOCK
205 -->echo ######### Exclusive lock with grouping #########
206 -->callmacro Check_locking_with_aggregate EXCLUSIVE_LOCK
207
208 -->echo ######### Locking options #########
209
210 -->macro Send_find_with_locking_option %LOCK_TYPE% %OPTION_TYPE%
211 -->callmacro Use_first_transation
212 -->callmacro Send_simple_find_with_lock _id 1 EXCLUSIVE_LOCK
213 -->recvresult
214
215 -->callmacro Switch_to_second_transaction
216
217 Mysqlx.Crud.Find {
218 collection {
219 name: "test_coll"
220 schema: "xtest"
221 }
222 data_model: DOCUMENT
223 locking: %LOCK_TYPE%
224 locking_options: %OPTION_TYPE%
225 }
226 -->endmacro
227
228 -->echo ######### Shared lock with NOWAIT #########
229 -->callmacro Send_find_with_locking_option SHARED_LOCK NOWAIT
230 -->expecterror ER_LOCK_NOWAIT
231 -->recvresult
232 -->callmacro Rollback_sessions
233
234 -->echo ######### Shared lock with SKIP LOCKED #########
235 -->callmacro Send_find_with_locking_option SHARED_LOCK SKIP_LOCKED
236 -->recvresult
237 -->callmacro Rollback_sessions
238
239 -->echo ######### Exclusive lock with NOWAIT #########
240 -->callmacro Send_find_with_locking_option EXCLUSIVE_LOCK NOWAIT
241 -->expecterror ER_LOCK_NOWAIT
242 -->recvresult
243 -->callmacro Rollback_sessions
244
245 -->echo ######### Exclusive lock with SKIP LOCKED #########
246 -->callmacro Send_find_with_locking_option EXCLUSIVE_LOCK SKIP_LOCKED
247 -->recvresult
248 -->callmacro Rollback_sessions
249
250 -->macro Expect_error_when_locking_option_is_set_and_no_locking %OPTION%
251 Mysqlx.Crud.Find {
252 collection {
253 name: "test_coll"
254 schema: "xtest"
255 }
256 data_model: DOCUMENT
257 locking_options: %OPTION%
258 }
259 -->recvtype Mysqlx.Error
260 -->endmacro
261
262 -->echo ######### NOWAIT set but no locking #########
263 -->callmacro Expect_error_when_locking_option_is_set_and_no_locking NOWAIT
264
265 -->echo ######### SKIP LOCKED set but no locking #########
266 -->callmacro Expect_error_when_locking_option_is_set_and_no_locking SKIP_LOCKED
267
268 EOF
269
270 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/find_row_locking.xpl 2>&1
271
272 ## Cleanup
273 --source ../include/xplugin_cleanup.inc
274
275 --disable_query_log
276 eval SET GLOBAL innodb_lock_wait_timeout=$old_innodb_lock_wait_timeout;
277 --enable_query_log