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