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.1.1.1 2012/02/21 17:04:16 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to lock.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test