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 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 # 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} {} 29do_test lock-1.1 { 30 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} 31} {} 32do_test lock-1.2 { 33 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} db2 34} {} 35do_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} 39do_test lock-1.5 { 40 catchsql { 41 SELECT name FROM sqlite_master WHERE type='table' ORDER BY name 42 } db2 43} {0 t1} 44 45do_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}} 53do_test lock-1.7.2 { 54 catchsql {SELECT * FROM t1} db2 55} {0 {1 2}} 56do_test lock-1.8 { 57 execsql {UPDATE t1 SET a=b, b=a} db2 58 execsql {SELECT * FROM t1} db2 59} {2 1} 60do_test lock-1.9 { 61 execsql {SELECT * FROM t1} 62} {2 1} 63do_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} 68do_test lock-1.11 { 69 catchsql {SELECT * FROM t1} db2 70} {0 {2 1}} 71do_test lock-1.12 { 72 execsql {ROLLBACK} 73 catchsql {SELECT * FROM t1} 74} {0 {2 1}} 75 76do_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} 81do_test lock-1.14.1 { 82 catchsql {SELECT * FROM t2} db2 83} {0 {8 9}} 84do_test lock-1.14.2 { 85 catchsql {SELECT * FROM t1} db2 86} {0 {2 1}} 87do_test lock-1.15 { 88 catchsql {SELECT * FROM t2} db2 89} {0 {8 9}} 90 91do_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} 97do_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# 121do_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 {}} 128do_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# 136do_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# 148if {$::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} 157integrity_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# 163do_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# 174do_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# 182do_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} 194do_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} {}} 202catch {execsql {ROLLBACK} db2} 203do_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}} 215do_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} {}} 228catch {execsql {ROLLBACK} db2} 229do_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} {}} 240execsql {ROLLBACK} 241 242# Test the built-in busy timeout handler 243# 244do_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}} 250do_test lock-2.9 { 251 db2 timeout 0 252 execsql COMMIT 253} {} 254integrity_check lock-2.10 255 256# Try to start two transactions in a row 257# 258do_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}} 264integrity_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# 270do_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}} 278do_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} {}} 283do_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}} 292execsql {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# 299proc tx_exec {sql} { 300 db2 eval $sql 301} 302do_test lock-5.1 { 303 execsql { 304 SELECT * FROM t1 305 } 306} {2 1} 307do_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 314ifcapable 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 353do_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 369do_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 381do_test lock-6.3 { 382 execsql { SELECT a FROM t4 ORDER BY a } db2 383} {1 2 3} 384 385do_test lock-6.4 { 386 execsql { PRAGMA integrity_check } db2 387} {ok} 388 389do_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# 400set temp_status unlocked 401if {$TEMP_STORE>=2} {set temp_status unknown} 402do_test lock-7.1 { 403 set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL] 404 sqlite3_step $STMT 405} {SQLITE_ROW} 406do_test lock-7.2 { 407 execsql { PRAGMA lock_status } 408} [list main shared temp $temp_status] 409do_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] 418do_test lock-7.4 { 419 sqlite3_finalize $STMT 420} {SQLITE_OK} 421 422do_test lock-999.1 { 423 rename db2 {} 424} {} 425 426finish_test 427