wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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