AryaWu/sqlite
0
1# 2001 September 152#3# The author disclaims copyright to this source code. In place of4# 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. The12# 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.tcl19 20# Create an alternative connection to the database21#22do_test lock-1.0 {23 # Give a complex pathname to stress the path simplification logic in24 # the vxworks driver.25 file mkdir tempdir/t1/t226 sqlite3 db2 ./tempdir/../tempdir/t1/.//t2/../../..//test.db27 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} db234} {}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 name42 } db243} {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} db252# } {1 {no such table: t1}}53do_test lock-1.7.2 {54 catchsql {SELECT * FROM t1} db255} {0 {1 2}}56do_test lock-1.8 {57 execsql {UPDATE t1 SET a=b, b=a} db258 execsql {SELECT * FROM t1} db259} {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} db270} {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} db283} {0 {8 9}}84do_test lock-1.14.2 {85 catchsql {SELECT * FROM t1} db286} {0 {2 1}}87do_test lock-1.15 {88 catchsql {SELECT * FROM t2} db289} {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 x96} {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 x102} {8 9}103 104# You cannot UPDATE a table from within the callback of a SELECT105# on that same table because the SELECT has the table locked.106#107# 2006-08-16: Reads no longer block writes within the same108# 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 $msg114# }115# set r116#} {1 {database table is locked}}117 118# But you can UPDATE a different table from the one that is used in119# 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 $msg125 }126 set r127} {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 the133# callback of another SELECT on that same table because two134# 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 $msg140 }141 set r142} {0 2}143 144# Under UNIX you can do two SELECTs at once with different database145# connections, because UNIX supports reader/writer locks. Under windows,146# this is not possible.147#148if {$::tcl_platform(platform) eq "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 $msg153 }154 set r155 } {0 2}156}157integrity_check lock-1.23158 159# If one thread has a transaction another thread cannot start160# a transaction. -> Not true in version 3.0. But if one thread161# 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} db2167 set r [catch {execsql {UPDATE t1 SET a = 0 WHERE 0} db2} msg]168 execsql {ROLLBACK} db2169 lappend r $msg170} {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} db2176} {0 {9 8}}177 178# If the other thread (the one that does not hold the transaction with179# a RESERVED lock) tries to get a RESERVED lock, we do get a busy callback180# 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 $count185 break186 }187 set ::callback_value {}188 db2 busy callback189 # db2 does not hold a lock so we should get a busy callback here190 set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]191 lappend r $msg192 lappend r $::callback_value193} {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} db2197 # 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 $msg200 lappend r $::callback_value201} {1 {database is locked} {}}202catch {execsql {ROLLBACK} db2}203do_test lock-2.4.1 {204 proc callback {count} {205 lappend ::callback_value $count206 if {$count>4} break207 }208 set ::callback_value {}209 db2 busy callback210 # We get a busy callback because db2 is not holding a lock211 set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]212 lappend r $msg213 lappend r $::callback_value214} {1 {database is locked} {0 1 2 3 4 5}}215do_test lock-2.4.2 {216 proc callback {count} {217 lappend ::callback_value $count218 if {$count>4} break219 }220 set ::callback_value {}221 db2 busy callback222 execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2223 # No busy callback this time because we are holding a lock224 set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]225 lappend r $msg226 lappend r $::callback_value227} {1 {database is locked} {}}228catch {execsql {ROLLBACK} db2}229do_test lock-2.5 {230 proc callback {count} {231 lappend ::callback_value $count232 if {$count>4} break233 }234 set ::callback_value {}235 db2 busy callback236 set r [catch {execsql {SELECT * FROM t1} db2} msg]237 lappend r $msg238 lappend r $::callback_value239} {0 {2 1} {}}240execsql {ROLLBACK}241 242# Test the built-in busy timeout handler243#244# EVIDENCE-OF: R-23579-05241 PRAGMA busy_timeout; PRAGMA busy_timeout =245# milliseconds; Query or change the setting of the busy timeout.246#247do_test lock-2.8 {248 db2 timeout 400249 execsql BEGIN250 execsql {UPDATE t1 SET a = 0 WHERE 0}251 catchsql {BEGIN EXCLUSIVE;} db2252} {1 {database is locked}}253do_test lock-2.8b {254 db2 eval {PRAGMA busy_timeout}255} {400}256do_test lock-2.9 {257 db2 timeout 0258 execsql COMMIT259} {}260do_test lock-2.9b {261 db2 eval {PRAGMA busy_timeout}262} {0}263integrity_check lock-2.10264do_test lock-2.11 {265 db2 eval {PRAGMA busy_timeout(400)}266 execsql BEGIN267 execsql {UPDATE t1 SET a = 0 WHERE 0}268 catchsql {BEGIN EXCLUSIVE;} db2269} {1 {database is locked}}270do_test lock-2.11b {271 db2 eval {PRAGMA busy_timeout}272} {400}273do_test lock-2.12 {274 db2 eval {PRAGMA busy_timeout(0)}275 execsql COMMIT276} {}277do_test lock-2.12b {278 db2 eval {PRAGMA busy_timeout}279} {0}280integrity_check lock-2.13281 282# Try to start two transactions in a row283#284do_test lock-3.1 {285 execsql {BEGIN TRANSACTION}286 set r [catch {execsql {BEGIN TRANSACTION}} msg]287 execsql {ROLLBACK}288 lappend r $msg289} {1 {cannot start a transaction within a transaction}}290integrity_check lock-3.2291 292# Make sure the busy handler and error messages work when293# opening a new pointer to the database while another pointer294# has the database locked.295#296do_test lock-4.1 {297 db2 close298 catch {db eval ROLLBACK}299 db eval BEGIN300 db eval {UPDATE t1 SET a=0 WHERE 0}301 sqlite3 db2 ./test.db302 catchsql {UPDATE t1 SET a=0} db2303} {1 {database is locked}}304do_test lock-4.2 {305 set ::callback_value {}306 set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]307 lappend rc $msg $::callback_value308} {1 {database is locked} {}}309do_test lock-4.3 {310 proc callback {count} {311 lappend ::callback_value $count312 if {$count>4} break313 }314 db2 busy callback315 set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]316 lappend rc $msg $::callback_value317} {1 {database is locked} {0 1 2 3 4 5}}318execsql {ROLLBACK}319 320# When one thread is writing, other threads cannot read. Except if the321# writing thread is writing to its temporary tables, the other threads322# can still read. -> Not so in 3.0. One thread can read while another323# holds a RESERVED lock.324#325proc tx_exec {sql} {326 db2 eval $sql327}328do_test lock-5.1 {329 execsql {330 SELECT * FROM t1331 }332} {2 1}333do_test lock-5.2 {334 db function tx_exec tx_exec335 catchsql {336 INSERT INTO t1(a,b) SELECT 3, tx_exec('SELECT y FROM t2 LIMIT 1');337 }338} {0 {}}339 340ifcapable tempdb {341 do_test lock-5.3 {342 execsql {343 CREATE TEMP TABLE t3(x);344 SELECT * FROM t3;345 }346 } {}347 do_test lock-5.4 {348 catchsql {349 INSERT INTO t3 SELECT tx_exec('SELECT y FROM t2 LIMIT 1');350 }351 } {0 {}}352 do_test lock-5.5 {353 execsql {354 SELECT * FROM t3;355 }356 } {8}357 do_test lock-5.6 {358 catchsql {359 UPDATE t1 SET a=tx_exec('SELECT x FROM t2');360 }361 } {0 {}}362 do_test lock-5.7 {363 execsql {364 SELECT * FROM t1;365 }366 } {9 1 9 8}367 do_test lock-5.8 {368 catchsql {369 UPDATE t3 SET x=tx_exec('SELECT x FROM t2');370 }371 } {0 {}}372 do_test lock-5.9 {373 execsql {374 SELECT * FROM t3;375 }376 } {9}377}378 379do_test lock-6.1 {380 execsql {381 CREATE TABLE t4(a PRIMARY KEY, b);382 INSERT INTO t4 VALUES(1, 'one');383 INSERT INTO t4 VALUES(2, 'two');384 INSERT INTO t4 VALUES(3, 'three');385 }386 387 set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL]388 sqlite3_step $STMT389 390 execsql { DELETE FROM t4 }391 execsql { SELECT * FROM sqlite_master } db2392 execsql { SELECT * FROM t4 } db2393} {}394 395do_test lock-6.2 {396 execsql { 397 BEGIN;398 INSERT INTO t4 VALUES(1, 'one');399 INSERT INTO t4 VALUES(2, 'two');400 INSERT INTO t4 VALUES(3, 'three');401 COMMIT;402 }403 404 execsql { SELECT * FROM t4 } db2405} {1 one 2 two 3 three}406 407do_test lock-6.3 {408 execsql { SELECT a FROM t4 ORDER BY a } db2409} {1 2 3}410 411do_test lock-6.4 {412 execsql { PRAGMA integrity_check } db2413} {ok}414 415do_test lock-6.5 {416 sqlite3_finalize $STMT417} {SQLITE_OK}418 419# At one point the following set of conditions would cause SQLite to 420# retain a RESERVED or EXCLUSIVE lock after the transaction was committed:421# 422# * The journal-mode is set to something other than 'delete', and423# * there exists one or more active read-only statements, and424# * a transaction that modified zero database pages is committed.425# 426#set temp_status unlocked427#if {$TEMP_STORE>=2} {set temp_status unknown}428set temp_status unknown429do_test lock-7.1 {430 set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL]431 sqlite3_step $STMT432} {SQLITE_ROW}433do_test lock-7.2 {434 execsql { PRAGMA lock_status }435} [list main shared temp $temp_status]436do_test lock-7.3 {437 execsql {438 PRAGMA journal_mode = truncate;439 BEGIN;440 UPDATE t4 SET a = 10 WHERE 0;441 COMMIT;442 }443 execsql { PRAGMA lock_status }444} [list main shared temp $temp_status]445do_test lock-7.4 {446 sqlite3_finalize $STMT447} {SQLITE_OK}448 449do_test lock-999.1 {450 rename db2 {}451} {}452 453finish_test454