AryaWu/sqlite
0
1# 2009 April 012#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#12# $Id: shared6.test,v 1.4 2009/06/05 17:09:12 drh Exp $13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16ifcapable !shared_cache { finish_test ; return }17 18do_test shared6-1.1.1 {19 execsql {20 CREATE TABLE t1(a, b);21 CREATE TABLE t2(c, d);22 CREATE TABLE t3(e, f);23 }24 db close25} {}26do_test shared6-1.1.2 {27 set ::enable_shared_cache [sqlite3_enable_shared_cache 1]28 sqlite3_enable_shared_cache29} {1}30 31do_test shared6-1.1.3 {32 sqlite3 db1 test.db33 sqlite3 db2 test.db34} {}35 36# Exclusive shared-cache locks. Test the following:37#38# 1.2.1: If [db1] has an exclusive lock, [db2] cannot read.39# 1.2.2: If [db1] has an exclusive lock, [db1] can read.40# 1.2.3: If [db1] has a non-exclusive write-lock, [db2] can read.41# 42do_test shared6-1.2.1 {43 execsql { SELECT * FROM t1 } db2 ;# Cache a compiled statement44 execsql { BEGIN EXCLUSIVE } db145 catchsql { SELECT * FROM t1 } db2 ;# Execute the cached compiled statement46} {1 {database table is locked}}47do_test shared6-1.2.2 {48 execsql { SELECT * FROM t1 } db149} {}50do_test shared6-1.2.3 {51 execsql {52 COMMIT;53 BEGIN;54 INSERT INTO t2 VALUES(3, 4);55 } db156 execsql { SELECT * FROM t1 } db257} {}58do_test shared6-1.2.X {59 execsql { COMMIT } db160} {}61 62# Regular shared-cache locks. Verify the following:63#64# 1.3.1: If [db1] has a write-lock on t1, [db1] can read from t1.65# 1.3.2: If [db1] has a write-lock on t1, [db2] can read from t2.66# 1.3.3: If [db1] has a write-lock on t1, [db2] cannot read from t1.67# 1.3.4: If [db1] has a write-lock on t1, [db2] cannot write to t1.68# 1.3.5: If [db1] has a read-lock on t1, [db2] can read from t1.69# 1.3.6: If [db1] has a read-lock on t1, [db2] cannot write to t1.70#71do_test shared6-1.3.1 {72 execsql {73 BEGIN;74 INSERT INTO t1 VALUES(1, 2);75 } db176 execsql { SELECT * FROM t1 } db177} {1 2}78do_test shared6-1.3.2 {79 execsql { SELECT * FROM t2 } db280} {3 4}81do_test shared6-1.3.3 {82 catchsql { SELECT * FROM t1 } db283} {1 {database table is locked: t1}}84do_test shared6-1.3.4 {85 catchsql { INSERT INTO t2 VALUES(1, 2) } db286} {1 {database table is locked}}87do_test shared6-1.3.5 {88 execsql {89 COMMIT;90 BEGIN;91 SELECT * FROM t1;92 } db193 execsql { SELECT * FROM t1 } db294} {1 2}95do_test shared6-1.3.5 {96 catchsql { INSERT INTO t1 VALUES(5, 6) } db297} {1 {database table is locked: t1}}98do_test shared6-1.3.X {99 execsql { COMMIT } db1100} {}101 102# Read-uncommitted mode.103#104# For these tests, connection [db2] is in read-uncommitted mode.105#106# 1.4.1: If [db1] has a write-lock on t1, [db2] can still read from t1.107# 1.4.2: If [db1] has a write-lock on the db schema (sqlite_master table), 108# [db2] cannot read from the schema.109# 1.4.3: If [db1] has a read-lock on t1, [db2] cannot write to t1.110#111do_test shared6-1.4.1 {112 execsql { PRAGMA read_uncommitted = 1 } db2113 execsql {114 BEGIN;115 INSERT INTO t1 VALUES(5, 6);116 } db1117 execsql { SELECT * FROM t1 } db2118} {1 2 5 6}119do_test shared6-1.4.2 {120 execsql { CREATE TABLE t4(a, b) } db1121 catchsql { SELECT * FROM t1 } db2122} {1 {database table is locked}}123do_test shared6-1.4.3 {124 execsql {125 COMMIT;126 BEGIN;127 SELECT * FROM t1;128 } db1129 catchsql { INSERT INTO t1 VALUES(7, 8) } db2130} {1 {database table is locked: t1}}131 132do_test shared6-1.X {133 db1 close134 db2 close135} {}136 137#-------------------------------------------------------------------------138# The following tests - shared6-2.* - test that two database connections139# that connect to the same file using different VFS implementations do140# not share a cache.141#142if {$::tcl_platform(os) ne "Windows NT"} {143 do_test shared6-2.1 {144 sqlite3 db1 test.db -vfs unix145 sqlite3 db2 test.db -vfs unix146 sqlite3 db3 test.db -vfs unix-none147 sqlite3 db4 test.db -vfs unix-none148 } {}149 150 do_test shared6-2.2 {151 execsql { BEGIN; INSERT INTO t1 VALUES(9, 10); } db1152 catchsql { SELECT * FROM t1 } db2153 } {1 {database table is locked: t1}}154 do_test shared6-2.3 {155 execsql { SELECT * FROM t1 } db3156 } {1 2 5 6}157 158 do_test shared6-2.3 {159 execsql { COMMIT } db1160 execsql { BEGIN; INSERT INTO t1 VALUES(11, 12); } db3161 catchsql { SELECT * FROM t1 } db4162 } {1 {database table is locked: t1}}163 164 do_test shared6-2.4 {165 execsql { SELECT * FROM t1 } db1166 } {1 2 5 6 9 10}167 168 do_test shared6-2.5 {169 execsql { COMMIT } db3170 } {}171 172 do_test shared6-2.X {173 db1 close174 db2 close175 db3 close176 db4 close177 } {}178}179 180#-------------------------------------------------------------------------181# Test that it is possible to open an exclusive transaction while 182# already holding a read-lock on the database file. And that it is183# not possible if some other connection holds such a lock.184#185do_test shared6-3.1 {186 sqlite3 db1 test.db187 sqlite3 db2 test.db188 sqlite3 db3 test.db189} {}190db1 eval {SELECT * FROM t1} {191 # Within this block [db1] is holding a read-lock on t1. Test that192 # this means t1 cannot be written by [db2].193 #194 do_test shared6-3.2 {195 catchsql { INSERT INTO t1 VALUES(1, 2) } db2196 } {1 {database table is locked: t1}}197 198 do_test shared6-3.3 {199 execsql { BEGIN EXCLUSIVE } db1200 } {}201 break202}203do_test shared6-3.4 {204 catchsql { SELECT * FROM t1 } db2205} {1 {database schema is locked: main}}206do_test shared6-3.5 {207 execsql COMMIT db1208} {}209db2 eval {SELECT * FROM t1} {210 do_test shared6-3.6 {211 catchsql { BEGIN EXCLUSIVE } db1212 } {1 {database table is locked}}213 break214}215do_test shared6-3.7 {216 execsql { BEGIN } db1217 execsql { BEGIN } db2218} {}219db2 eval {SELECT * FROM t1} {220 do_test shared6-3.8 {221 catchsql { INSERT INTO t1 VALUES(1, 2) } db1222 } {1 {database table is locked: t1}}223 break224}225do_test shared6-3.9 {226 execsql { BEGIN ; ROLLBACK } db3227} {}228do_test shared6-3.10 {229 catchsql { SELECT * FROM t1 } db3230} {1 {database table is locked}}231do_test shared6-3.X {232 db1 close233 db2 close234 db3 close235} {}236 237do_test shared6-4.1 {238 #forcedelete test.db test.db-journal239 sqlite3 db1 test.db240 sqlite3 db2 test.db241 242 set ::STMT [sqlite3_prepare_v2 db1 "SELECT * FROM t1" -1 DUMMY]243 execsql { CREATE TABLE t5(a, b) } db2244} {}245do_test shared6-4.2 {246 sqlite3_finalize $::STMT247} {SQLITE_OK}248do_test shared6-4.X {249 250 db1 close251 db2 close252} {}253 254sqlite3_enable_shared_cache $::enable_shared_cache255finish_test256