wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2001 September 15 |
2 | # |
||
3 | # The author disclaims copyright to this source code. In place of |
||
4 | # a legal notice, here is a blessing: |
||
5 | # |
||
6 | # May you do good and not evil. |
||
7 | # May you find forgiveness for yourself and forgive others. |
||
8 | # May you share freely, never taking more than you give. |
||
9 | # |
||
10 | #*********************************************************************** |
||
11 | # This file implements regression tests for SQLite library. The |
||
12 | # focus of this script is database locks. |
||
13 | # |
||
14 | # $Id: lock.test,v 1.40 2009/06/16 17:49:36 drh Exp $ |
||
15 | |||
16 | |||
17 | set testdir [file dirname $argv0] |
||
18 | source $testdir/tester.tcl |
||
19 | |||
20 | # Create an alternative connection to the database |
||
21 | # |
||
22 | do_test lock-1.0 { |
||
23 | # Give a complex pathname to stress the path simplification logic in |
||
24 | # the vxworks driver and in test_async. |
||
25 | file mkdir tempdir/t1/t2 |
||
26 | sqlite3 db2 ./tempdir/../tempdir/t1/.//t2/../../..//test.db |
||
27 | set dummy {} |
||
28 | } {} |
||
29 | do_test lock-1.1 { |
||
30 | execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} |
||
31 | } {} |
||
32 | do_test lock-1.2 { |
||
33 | execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} db2 |
||
34 | } {} |
||
35 | do_test lock-1.3 { |
||
36 | execsql {CREATE TABLE t1(a int, b int)} |
||
37 | execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} |
||
38 | } {t1} |
||
39 | do_test lock-1.5 { |
||
40 | catchsql { |
||
41 | SELECT name FROM sqlite_master WHERE type='table' ORDER BY name |
||
42 | } db2 |
||
43 | } {0 t1} |
||
44 | |||
45 | do_test lock-1.6 { |
||
46 | execsql {INSERT INTO t1 VALUES(1,2)} |
||
47 | execsql {SELECT * FROM t1} |
||
48 | } {1 2} |
||
49 | # Update: The schema is now brought up to date by test lock-1.5. |
||
50 | # do_test lock-1.7.1 { |
||
51 | # catchsql {SELECT * FROM t1} db2 |
||
52 | # } {1 {no such table: t1}} |
||
53 | do_test lock-1.7.2 { |
||
54 | catchsql {SELECT * FROM t1} db2 |
||
55 | } {0 {1 2}} |
||
56 | do_test lock-1.8 { |
||
57 | execsql {UPDATE t1 SET a=b, b=a} db2 |
||
58 | execsql {SELECT * FROM t1} db2 |
||
59 | } {2 1} |
||
60 | do_test lock-1.9 { |
||
61 | execsql {SELECT * FROM t1} |
||
62 | } {2 1} |
||
63 | do_test lock-1.10 { |
||
64 | execsql {BEGIN TRANSACTION} |
||
65 | execsql {UPDATE t1 SET a = 0 WHERE 0} |
||
66 | execsql {SELECT * FROM t1} |
||
67 | } {2 1} |
||
68 | do_test lock-1.11 { |
||
69 | catchsql {SELECT * FROM t1} db2 |
||
70 | } {0 {2 1}} |
||
71 | do_test lock-1.12 { |
||
72 | execsql {ROLLBACK} |
||
73 | catchsql {SELECT * FROM t1} |
||
74 | } {0 {2 1}} |
||
75 | |||
76 | do_test lock-1.13 { |
||
77 | execsql {CREATE TABLE t2(x int, y int)} |
||
78 | execsql {INSERT INTO t2 VALUES(8,9)} |
||
79 | execsql {SELECT * FROM t2} |
||
80 | } {8 9} |
||
81 | do_test lock-1.14.1 { |
||
82 | catchsql {SELECT * FROM t2} db2 |
||
83 | } {0 {8 9}} |
||
84 | do_test lock-1.14.2 { |
||
85 | catchsql {SELECT * FROM t1} db2 |
||
86 | } {0 {2 1}} |
||
87 | do_test lock-1.15 { |
||
88 | catchsql {SELECT * FROM t2} db2 |
||
89 | } {0 {8 9}} |
||
90 | |||
91 | do_test lock-1.16 { |
||
92 | db eval {SELECT * FROM t1} qv { |
||
93 | set x [db eval {SELECT * FROM t1}] |
||
94 | } |
||
95 | set x |
||
96 | } {2 1} |
||
97 | do_test lock-1.17 { |
||
98 | db eval {SELECT * FROM t1} qv { |
||
99 | set x [db eval {SELECT * FROM t2}] |
||
100 | } |
||
101 | set x |
||
102 | } {8 9} |
||
103 | |||
104 | # You cannot UPDATE a table from within the callback of a SELECT |
||
105 | # on that same table because the SELECT has the table locked. |
||
106 | # |
||
107 | # 2006-08-16: Reads no longer block writes within the same |
||
108 | # database connection. |
||
109 | # |
||
110 | #do_test lock-1.18 { |
||
111 | # db eval {SELECT * FROM t1} qv { |
||
112 | # set r [catch {db eval {UPDATE t1 SET a=b, b=a}} msg] |
||
113 | # lappend r $msg |
||
114 | # } |
||
115 | # set r |
||
116 | #} {1 {database table is locked}} |
||
117 | |||
118 | # But you can UPDATE a different table from the one that is used in |
||
119 | # the SELECT. |
||
120 | # |
||
121 | do_test lock-1.19 { |
||
122 | db eval {SELECT * FROM t1} qv { |
||
123 | set r [catch {db eval {UPDATE t2 SET x=y, y=x}} msg] |
||
124 | lappend r $msg |
||
125 | } |
||
126 | set r |
||
127 | } {0 {}} |
||
128 | do_test lock-1.20 { |
||
129 | execsql {SELECT * FROM t2} |
||
130 | } {9 8} |
||
131 | |||
132 | # It is possible to do a SELECT of the same table within the |
||
133 | # callback of another SELECT on that same table because two |
||
134 | # or more read-only cursors can be open at once. |
||
135 | # |
||
136 | do_test lock-1.21 { |
||
137 | db eval {SELECT * FROM t1} qv { |
||
138 | set r [catch {db eval {SELECT a FROM t1}} msg] |
||
139 | lappend r $msg |
||
140 | } |
||
141 | set r |
||
142 | } {0 2} |
||
143 | |||
144 | # Under UNIX you can do two SELECTs at once with different database |
||
145 | # connections, because UNIX supports reader/writer locks. Under windows, |
||
146 | # this is not possible. |
||
147 | # |
||
148 | if {$::tcl_platform(platform)=="unix"} { |
||
149 | do_test lock-1.22 { |
||
150 | db eval {SELECT * FROM t1} qv { |
||
151 | set r [catch {db2 eval {SELECT a FROM t1}} msg] |
||
152 | lappend r $msg |
||
153 | } |
||
154 | set r |
||
155 | } {0 2} |
||
156 | } |
||
157 | integrity_check lock-1.23 |
||
158 | |||
159 | # If one thread has a transaction another thread cannot start |
||
160 | # a transaction. -> Not true in version 3.0. But if one thread |
||
161 | # as a RESERVED lock another thread cannot acquire one. |
||
162 | # |
||
163 | do_test lock-2.1 { |
||
164 | execsql {BEGIN TRANSACTION} |
||
165 | execsql {UPDATE t1 SET a = 0 WHERE 0} |
||
166 | execsql {BEGIN TRANSACTION} db2 |
||
167 | set r [catch {execsql {UPDATE t1 SET a = 0 WHERE 0} db2} msg] |
||
168 | execsql {ROLLBACK} db2 |
||
169 | lappend r $msg |
||
170 | } {1 {database is locked}} |
||
171 | |||
172 | # A thread can read when another has a RESERVED lock. |
||
173 | # |
||
174 | do_test lock-2.2 { |
||
175 | catchsql {SELECT * FROM t2} db2 |
||
176 | } {0 {9 8}} |
||
177 | |||
178 | # If the other thread (the one that does not hold the transaction with |
||
179 | # a RESERVED lock) tries to get a RESERVED lock, we do get a busy callback |
||
180 | # as long as we were not orginally holding a READ lock. |
||
181 | # |
||
182 | do_test lock-2.3.1 { |
||
183 | proc callback {count} { |
||
184 | set ::callback_value $count |
||
185 | break |
||
186 | } |
||
187 | set ::callback_value {} |
||
188 | db2 busy callback |
||
189 | # db2 does not hold a lock so we should get a busy callback here |
||
190 | set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg] |
||
191 | lappend r $msg |
||
192 | lappend r $::callback_value |
||
193 | } {1 {database is locked} 0} |
||
194 | do_test lock-2.3.2 { |
||
195 | set ::callback_value {} |
||
196 | execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2 |
||
197 | # This time db2 does hold a read lock. No busy callback this time. |
||
198 | set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg] |
||
199 | lappend r $msg |
||
200 | lappend r $::callback_value |
||
201 | } {1 {database is locked} {}} |
||
202 | catch {execsql {ROLLBACK} db2} |
||
203 | do_test lock-2.4.1 { |
||
204 | proc callback {count} { |
||
205 | lappend ::callback_value $count |
||
206 | if {$count>4} break |
||
207 | } |
||
208 | set ::callback_value {} |
||
209 | db2 busy callback |
||
210 | # We get a busy callback because db2 is not holding a lock |
||
211 | set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg] |
||
212 | lappend r $msg |
||
213 | lappend r $::callback_value |
||
214 | } {1 {database is locked} {0 1 2 3 4 5}} |
||
215 | do_test lock-2.4.2 { |
||
216 | proc callback {count} { |
||
217 | lappend ::callback_value $count |
||
218 | if {$count>4} break |
||
219 | } |
||
220 | set ::callback_value {} |
||
221 | db2 busy callback |
||
222 | execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2 |
||
223 | # No busy callback this time because we are holding a lock |
||
224 | set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg] |
||
225 | lappend r $msg |
||
226 | lappend r $::callback_value |
||
227 | } {1 {database is locked} {}} |
||
228 | catch {execsql {ROLLBACK} db2} |
||
229 | do_test lock-2.5 { |
||
230 | proc callback {count} { |
||
231 | lappend ::callback_value $count |
||
232 | if {$count>4} break |
||
233 | } |
||
234 | set ::callback_value {} |
||
235 | db2 busy callback |
||
236 | set r [catch {execsql {SELECT * FROM t1} db2} msg] |
||
237 | lappend r $msg |
||
238 | lappend r $::callback_value |
||
239 | } {0 {2 1} {}} |
||
240 | execsql {ROLLBACK} |
||
241 | |||
242 | # Test the built-in busy timeout handler |
||
243 | # |
||
244 | do_test lock-2.8 { |
||
245 | db2 timeout 400 |
||
246 | execsql BEGIN |
||
247 | execsql {UPDATE t1 SET a = 0 WHERE 0} |
||
248 | catchsql {BEGIN EXCLUSIVE;} db2 |
||
249 | } {1 {database is locked}} |
||
250 | do_test lock-2.9 { |
||
251 | db2 timeout 0 |
||
252 | execsql COMMIT |
||
253 | } {} |
||
254 | integrity_check lock-2.10 |
||
255 | |||
256 | # Try to start two transactions in a row |
||
257 | # |
||
258 | do_test lock-3.1 { |
||
259 | execsql {BEGIN TRANSACTION} |
||
260 | set r [catch {execsql {BEGIN TRANSACTION}} msg] |
||
261 | execsql {ROLLBACK} |
||
262 | lappend r $msg |
||
263 | } {1 {cannot start a transaction within a transaction}} |
||
264 | integrity_check lock-3.2 |
||
265 | |||
266 | # Make sure the busy handler and error messages work when |
||
267 | # opening a new pointer to the database while another pointer |
||
268 | # has the database locked. |
||
269 | # |
||
270 | do_test lock-4.1 { |
||
271 | db2 close |
||
272 | catch {db eval ROLLBACK} |
||
273 | db eval BEGIN |
||
274 | db eval {UPDATE t1 SET a=0 WHERE 0} |
||
275 | sqlite3 db2 ./test.db |
||
276 | catchsql {UPDATE t1 SET a=0} db2 |
||
277 | } {1 {database is locked}} |
||
278 | do_test lock-4.2 { |
||
279 | set ::callback_value {} |
||
280 | set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg] |
||
281 | lappend rc $msg $::callback_value |
||
282 | } {1 {database is locked} {}} |
||
283 | do_test lock-4.3 { |
||
284 | proc callback {count} { |
||
285 | lappend ::callback_value $count |
||
286 | if {$count>4} break |
||
287 | } |
||
288 | db2 busy callback |
||
289 | set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg] |
||
290 | lappend rc $msg $::callback_value |
||
291 | } {1 {database is locked} {0 1 2 3 4 5}} |
||
292 | execsql {ROLLBACK} |
||
293 | |||
294 | # When one thread is writing, other threads cannot read. Except if the |
||
295 | # writing thread is writing to its temporary tables, the other threads |
||
296 | # can still read. -> Not so in 3.0. One thread can read while another |
||
297 | # holds a RESERVED lock. |
||
298 | # |
||
299 | proc tx_exec {sql} { |
||
300 | db2 eval $sql |
||
301 | } |
||
302 | do_test lock-5.1 { |
||
303 | execsql { |
||
304 | SELECT * FROM t1 |
||
305 | } |
||
306 | } {2 1} |
||
307 | do_test lock-5.2 { |
||
308 | db function tx_exec tx_exec |
||
309 | catchsql { |
||
310 | INSERT INTO t1(a,b) SELECT 3, tx_exec('SELECT y FROM t2 LIMIT 1'); |
||
311 | } |
||
312 | } {0 {}} |
||
313 | |||
314 | ifcapable tempdb { |
||
315 | do_test lock-5.3 { |
||
316 | execsql { |
||
317 | CREATE TEMP TABLE t3(x); |
||
318 | SELECT * FROM t3; |
||
319 | } |
||
320 | } {} |
||
321 | do_test lock-5.4 { |
||
322 | catchsql { |
||
323 | INSERT INTO t3 SELECT tx_exec('SELECT y FROM t2 LIMIT 1'); |
||
324 | } |
||
325 | } {0 {}} |
||
326 | do_test lock-5.5 { |
||
327 | execsql { |
||
328 | SELECT * FROM t3; |
||
329 | } |
||
330 | } {8} |
||
331 | do_test lock-5.6 { |
||
332 | catchsql { |
||
333 | UPDATE t1 SET a=tx_exec('SELECT x FROM t2'); |
||
334 | } |
||
335 | } {0 {}} |
||
336 | do_test lock-5.7 { |
||
337 | execsql { |
||
338 | SELECT * FROM t1; |
||
339 | } |
||
340 | } {9 1 9 8} |
||
341 | do_test lock-5.8 { |
||
342 | catchsql { |
||
343 | UPDATE t3 SET x=tx_exec('SELECT x FROM t2'); |
||
344 | } |
||
345 | } {0 {}} |
||
346 | do_test lock-5.9 { |
||
347 | execsql { |
||
348 | SELECT * FROM t3; |
||
349 | } |
||
350 | } {9} |
||
351 | } |
||
352 | |||
353 | do_test lock-6.1 { |
||
354 | execsql { |
||
355 | CREATE TABLE t4(a PRIMARY KEY, b); |
||
356 | INSERT INTO t4 VALUES(1, 'one'); |
||
357 | INSERT INTO t4 VALUES(2, 'two'); |
||
358 | INSERT INTO t4 VALUES(3, 'three'); |
||
359 | } |
||
360 | |||
361 | set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL] |
||
362 | sqlite3_step $STMT |
||
363 | |||
364 | execsql { DELETE FROM t4 } |
||
365 | execsql { SELECT * FROM sqlite_master } db2 |
||
366 | execsql { SELECT * FROM t4 } db2 |
||
367 | } {} |
||
368 | |||
369 | do_test lock-6.2 { |
||
370 | execsql { |
||
371 | BEGIN; |
||
372 | INSERT INTO t4 VALUES(1, 'one'); |
||
373 | INSERT INTO t4 VALUES(2, 'two'); |
||
374 | INSERT INTO t4 VALUES(3, 'three'); |
||
375 | COMMIT; |
||
376 | } |
||
377 | |||
378 | execsql { SELECT * FROM t4 } db2 |
||
379 | } {1 one 2 two 3 three} |
||
380 | |||
381 | do_test lock-6.3 { |
||
382 | execsql { SELECT a FROM t4 ORDER BY a } db2 |
||
383 | } {1 2 3} |
||
384 | |||
385 | do_test lock-6.4 { |
||
386 | execsql { PRAGMA integrity_check } db2 |
||
387 | } {ok} |
||
388 | |||
389 | do_test lock-6.5 { |
||
390 | sqlite3_finalize $STMT |
||
391 | } {SQLITE_OK} |
||
392 | |||
393 | # At one point the following set of conditions would cause SQLite to |
||
394 | # retain a RESERVED or EXCLUSIVE lock after the transaction was committed: |
||
395 | # |
||
396 | # * The journal-mode is set to something other than 'delete', and |
||
397 | # * there exists one or more active read-only statements, and |
||
398 | # * a transaction that modified zero database pages is committed. |
||
399 | # |
||
400 | set temp_status unlocked |
||
401 | if {$TEMP_STORE>=2} {set temp_status unknown} |
||
402 | do_test lock-7.1 { |
||
403 | set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL] |
||
404 | sqlite3_step $STMT |
||
405 | } {SQLITE_ROW} |
||
406 | do_test lock-7.2 { |
||
407 | execsql { PRAGMA lock_status } |
||
408 | } [list main shared temp $temp_status] |
||
409 | do_test lock-7.3 { |
||
410 | execsql { |
||
411 | PRAGMA journal_mode = truncate; |
||
412 | BEGIN; |
||
413 | UPDATE t4 SET a = 10 WHERE 0; |
||
414 | COMMIT; |
||
415 | } |
||
416 | execsql { PRAGMA lock_status } |
||
417 | } [list main shared temp $temp_status] |
||
418 | do_test lock-7.4 { |
||
419 | sqlite3_finalize $STMT |
||
420 | } {SQLITE_OK} |
||
421 | |||
422 | do_test lock-999.1 { |
||
423 | rename db2 {} |
||
424 | } {} |
||
425 | |||
426 | finish_test |