3
# The author disclaims copyright to this source code. In place of
4
# a legal notice, here is a blessing:
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.
10
#***********************************************************************
11
# This file implements regression tests for SQLite library. The
12
# focus of this script is database locks.
14
# $Id: lock.test,v 1.33 2006/08/16 16:42:48 drh Exp $
17
set testdir [file dirname $argv0]
18
source $testdir/tester.tcl
20
# Create an alternative connection to the database
27
execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
30
execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} db2
33
execsql {CREATE TABLE t1(a int, b int)}
34
execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
38
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
43
execsql {INSERT INTO t1 VALUES(1,2)}
44
execsql {SELECT * FROM t1}
46
# Update: The schema is now brought up to date by test lock-1.5.
47
# do_test lock-1.7.1 {
48
# catchsql {SELECT * FROM t1} db2
49
# } {1 {no such table: t1}}
51
catchsql {SELECT * FROM t1} db2
54
execsql {UPDATE t1 SET a=b, b=a} db2
55
execsql {SELECT * FROM t1} db2
58
execsql {SELECT * FROM t1}
61
execsql {BEGIN TRANSACTION}
62
execsql {UPDATE t1 SET a = 0 WHERE 0}
63
execsql {SELECT * FROM t1}
66
catchsql {SELECT * FROM t1} db2
70
catchsql {SELECT * FROM t1}
74
execsql {CREATE TABLE t2(x int, y int)}
75
execsql {INSERT INTO t2 VALUES(8,9)}
76
execsql {SELECT * FROM t2}
79
catchsql {SELECT * FROM t2} db2
80
} {1 {no such table: t2}}
82
catchsql {SELECT * FROM t1} db2
85
catchsql {SELECT * FROM t2} db2
89
db eval {SELECT * FROM t1} qv {
90
set x [db eval {SELECT * FROM t1}]
95
db eval {SELECT * FROM t1} qv {
96
set x [db eval {SELECT * FROM t2}]
101
# You cannot UPDATE a table from within the callback of a SELECT
102
# on that same table because the SELECT has the table locked.
104
# 2006-08-16: Reads no longer block writes within the same
105
# database connection.
108
# db eval {SELECT * FROM t1} qv {
109
# set r [catch {db eval {UPDATE t1 SET a=b, b=a}} msg]
113
#} {1 {database table is locked}}
115
# But you can UPDATE a different table from the one that is used in
119
db eval {SELECT * FROM t1} qv {
120
set r [catch {db eval {UPDATE t2 SET x=y, y=x}} msg]
126
execsql {SELECT * FROM t2}
129
# It is possible to do a SELECT of the same table within the
130
# callback of another SELECT on that same table because two
131
# or more read-only cursors can be open at once.
134
db eval {SELECT * FROM t1} qv {
135
set r [catch {db eval {SELECT a FROM t1}} msg]
141
# Under UNIX you can do two SELECTs at once with different database
142
# connections, because UNIX supports reader/writer locks. Under windows,
143
# this is not possible.
145
if {$::tcl_platform(platform)=="unix"} {
147
db eval {SELECT * FROM t1} qv {
148
set r [catch {db2 eval {SELECT a FROM t1}} msg]
154
integrity_check lock-1.23
156
# If one thread has a transaction another thread cannot start
157
# a transaction. -> Not true in version 3.0. But if one thread
158
# as a RESERVED lock another thread cannot acquire one.
161
execsql {BEGIN TRANSACTION}
162
execsql {UPDATE t1 SET a = 0 WHERE 0}
163
execsql {BEGIN TRANSACTION} db2
164
set r [catch {execsql {UPDATE t1 SET a = 0 WHERE 0} db2} msg]
165
execsql {ROLLBACK} db2
167
} {1 {database is locked}}
169
# A thread can read when another has a RESERVED lock.
172
catchsql {SELECT * FROM t2} db2
175
# If the other thread (the one that does not hold the transaction with
176
# a RESERVED lock) tries to get a RESERVED lock, we do get a busy callback
177
# as long as we were not orginally holding a READ lock.
180
proc callback {count} {
181
set ::callback_value $count
184
set ::callback_value {}
186
# db2 does not hold a lock so we should get a busy callback here
187
set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
189
lappend r $::callback_value
190
} {1 {database is locked} 0}
192
set ::callback_value {}
193
execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2
194
# This time db2 does hold a read lock. No busy callback this time.
195
set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
197
lappend r $::callback_value
198
} {1 {database is locked} {}}
199
catch {execsql {ROLLBACK} db2}
201
proc callback {count} {
202
lappend ::callback_value $count
205
set ::callback_value {}
207
# We get a busy callback because db2 is not holding a lock
208
set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
210
lappend r $::callback_value
211
} {1 {database is locked} {0 1 2 3 4 5}}
213
proc callback {count} {
214
lappend ::callback_value $count
217
set ::callback_value {}
219
execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2
220
# No busy callback this time because we are holding a lock
221
set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
223
lappend r $::callback_value
224
} {1 {database is locked} {}}
225
catch {execsql {ROLLBACK} db2}
227
proc callback {count} {
228
lappend ::callback_value $count
231
set ::callback_value {}
233
set r [catch {execsql {SELECT * FROM t1} db2} msg]
235
lappend r $::callback_value
239
# Test the built-in busy timeout handler
244
execsql {UPDATE t1 SET a = 0 WHERE 0}
245
catchsql {BEGIN EXCLUSIVE;} db2
246
} {1 {database is locked}}
251
integrity_check lock-2.10
253
# Try to start two transactions in a row
256
execsql {BEGIN TRANSACTION}
257
set r [catch {execsql {BEGIN TRANSACTION}} msg]
260
} {1 {cannot start a transaction within a transaction}}
261
integrity_check lock-3.2
263
# Make sure the busy handler and error messages work when
264
# opening a new pointer to the database while another pointer
265
# has the database locked.
269
catch {db eval ROLLBACK}
271
db eval {UPDATE t1 SET a=0 WHERE 0}
272
sqlite3 db2 ./test.db
273
catchsql {UPDATE t1 SET a=0} db2
274
} {1 {database is locked}}
276
set ::callback_value {}
277
set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]
278
lappend rc $msg $::callback_value
279
} {1 {database is locked} {}}
281
proc callback {count} {
282
lappend ::callback_value $count
286
set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]
287
lappend rc $msg $::callback_value
288
} {1 {database is locked} {0 1 2 3 4 5}}
291
# When one thread is writing, other threads cannot read. Except if the
292
# writing thread is writing to its temporary tables, the other threads
293
# can still read. -> Not so in 3.0. One thread can read while another
294
# holds a RESERVED lock.
305
db function tx_exec tx_exec
307
INSERT INTO t1(a,b) SELECT 3, tx_exec('SELECT y FROM t2 LIMIT 1');
314
CREATE TEMP TABLE t3(x);
320
INSERT INTO t3 SELECT tx_exec('SELECT y FROM t2 LIMIT 1');
330
UPDATE t1 SET a=tx_exec('SELECT x FROM t2');
340
UPDATE t3 SET x=tx_exec('SELECT x FROM t2');