1# 2009 April 01 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# 12# $Id: shared6.test,v 1.4 2009/06/05 17:09:12 drh Exp $ 13 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16ifcapable !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 close 25} {} 26do_test shared6-1.1.2 { 27 set ::enable_shared_cache [sqlite3_enable_shared_cache 1] 28 sqlite3_enable_shared_cache 29} {1} 30 31do_test shared6-1.1.3 { 32 sqlite3 db1 test.db 33 sqlite3 db2 test.db 34} {} 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 statement 44 execsql { BEGIN EXCLUSIVE } db1 45 catchsql { SELECT * FROM t1 } db2 ;# Execute the cached compiled statement 46} {1 {database table is locked}} 47do_test shared6-1.2.2 { 48 execsql { SELECT * FROM t1 } db1 49} {} 50do_test shared6-1.2.3 { 51 execsql { 52 COMMIT; 53 BEGIN; 54 INSERT INTO t2 VALUES(3, 4); 55 } db1 56 execsql { SELECT * FROM t1 } db2 57} {} 58do_test shared6-1.2.X { 59 execsql { COMMIT } db1 60} {} 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 } db1 76 execsql { SELECT * FROM t1 } db1 77} {1 2} 78do_test shared6-1.3.2 { 79 execsql { SELECT * FROM t2 } db2 80} {3 4} 81do_test shared6-1.3.3 { 82 catchsql { SELECT * FROM t1 } db2 83} {1 {database table is locked: t1}} 84do_test shared6-1.3.4 { 85 catchsql { INSERT INTO t2 VALUES(1, 2) } db2 86} {1 {database table is locked}} 87do_test shared6-1.3.5 { 88 execsql { 89 COMMIT; 90 BEGIN; 91 SELECT * FROM t1; 92 } db1 93 execsql { SELECT * FROM t1 } db2 94} {1 2} 95do_test shared6-1.3.5 { 96 catchsql { INSERT INTO t1 VALUES(5, 6) } db2 97} {1 {database table is locked: t1}} 98do_test shared6-1.3.X { 99 execsql { COMMIT } db1 100} {} 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 } db2 113 execsql { 114 BEGIN; 115 INSERT INTO t1 VALUES(5, 6); 116 } db1 117 execsql { SELECT * FROM t1 } db2 118} {1 2 5 6} 119do_test shared6-1.4.2 { 120 execsql { CREATE TABLE t4(a, b) } db1 121 catchsql { SELECT * FROM t1 } db2 122} {1 {database table is locked}} 123do_test shared6-1.4.3 { 124 execsql { 125 COMMIT; 126 BEGIN; 127 SELECT * FROM t1; 128 } db1 129 catchsql { INSERT INTO t1 VALUES(7, 8) } db2 130} {1 {database table is locked: t1}} 131 132do_test shared6-1.X { 133 db1 close 134 db2 close 135} {} 136 137#------------------------------------------------------------------------- 138# The following tests - shared6-2.* - test that two database connections 139# that connect to the same file using different VFS implementations do 140# not share a cache. 141# 142if {$::tcl_platform(platform) eq "unix"} { 143 do_test shared6-2.1 { 144 sqlite3 db1 test.db -vfs unix 145 sqlite3 db2 test.db -vfs unix 146 sqlite3 db3 test.db -vfs unix-none 147 sqlite3 db4 test.db -vfs unix-none 148 } {} 149 150 do_test shared6-2.2 { 151 execsql { BEGIN; INSERT INTO t1 VALUES(9, 10); } db1 152 catchsql { SELECT * FROM t1 } db2 153 } {1 {database table is locked: t1}} 154 do_test shared6-2.3 { 155 execsql { SELECT * FROM t1 } db3 156 } {1 2 5 6} 157 158 do_test shared6-2.3 { 159 execsql { COMMIT } db1 160 execsql { BEGIN; INSERT INTO t1 VALUES(11, 12); } db3 161 catchsql { SELECT * FROM t1 } db4 162 } {1 {database table is locked: t1}} 163 164 do_test shared6-2.4 { 165 execsql { SELECT * FROM t1 } db1 166 } {1 2 5 6 9 10} 167 168 do_test shared6-2.5 { 169 execsql { COMMIT } db3 170 } {} 171 172 do_test shared6-2.X { 173 db1 close 174 db2 close 175 db3 close 176 db4 close 177 } {} 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 is 183# not possible if some other connection holds such a lock. 184# 185do_test shared6-3.1 { 186 sqlite3 db1 test.db 187 sqlite3 db2 test.db 188 sqlite3 db3 test.db 189} {} 190db1 eval {SELECT * FROM t1} { 191 # Within this block [db1] is holding a read-lock on t1. Test that 192 # this means t1 cannot be written by [db2]. 193 # 194 do_test shared6-3.2 { 195 catchsql { INSERT INTO t1 VALUES(1, 2) } db2 196 } {1 {database table is locked: t1}} 197 198 do_test shared6-3.3 { 199 execsql { BEGIN EXCLUSIVE } db1 200 } {} 201 break 202} 203do_test shared6-3.4 { 204 catchsql { SELECT * FROM t1 } db2 205} {1 {database schema is locked: main}} 206do_test shared6-3.5 { 207 execsql COMMIT db1 208} {} 209db2 eval {SELECT * FROM t1} { 210 do_test shared6-3.6 { 211 catchsql { BEGIN EXCLUSIVE } db1 212 } {1 {database table is locked}} 213 break 214} 215do_test shared6-3.7 { 216 execsql { BEGIN } db1 217 execsql { BEGIN } db2 218} {} 219db2 eval {SELECT * FROM t1} { 220 do_test shared6-3.8 { 221 catchsql { INSERT INTO t1 VALUES(1, 2) } db1 222 } {1 {database table is locked: t1}} 223 break 224} 225do_test shared6-3.9 { 226 execsql { BEGIN ; ROLLBACK } db3 227} {} 228do_test shared6-3.10 { 229 catchsql { SELECT * FROM t1 } db3 230} {1 {database table is locked}} 231do_test shared6-3.X { 232 db1 close 233 db2 close 234 db3 close 235} {} 236 237do_test shared6-4.1 { 238 #forcedelete test.db test.db-journal 239 sqlite3 db1 test.db 240 sqlite3 db2 test.db 241 242 set ::STMT [sqlite3_prepare_v2 db1 "SELECT * FROM t1" -1 DUMMY] 243 execsql { CREATE TABLE t5(a, b) } db2 244} {} 245do_test shared6-4.2 { 246 sqlite3_finalize $::STMT 247} {SQLITE_OK} 248do_test shared6-4.X { 249 250 db1 close 251 db2 close 252} {} 253 254sqlite3_enable_shared_cache $::enable_shared_cache 255finish_test 256