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 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 ifcapable !shared_cache { finish_test ; return } 17 18 do_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 } {} 26 do_test shared6-1.1.2 { 27 set ::enable_shared_cache [sqlite3_enable_shared_cache 1] 28 sqlite3_enable_shared_cache 29 } {1} 30 31 do_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 # 42 do_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}} 47 do_test shared6-1.2.2 { 48 execsql { SELECT * FROM t1 } db1 49 } {} 50 do_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 } {} 58 do_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 # 71 do_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} 78 do_test shared6-1.3.2 { 79 execsql { SELECT * FROM t2 } db2 80 } {3 4} 81 do_test shared6-1.3.3 { 82 catchsql { SELECT * FROM t1 } db2 83 } {1 {database table is locked: t1}} 84 do_test shared6-1.3.4 { 85 catchsql { INSERT INTO t2 VALUES(1, 2) } db2 86 } {1 {database table is locked}} 87 do_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} 95 do_test shared6-1.3.5 { 96 catchsql { INSERT INTO t1 VALUES(5, 6) } db2 97 } {1 {database table is locked: t1}} 98 do_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 # 111 do_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} 119 do_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}} 123 do_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 132 do_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 # 142 if {$::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 # 185 do_test shared6-3.1 { 186 sqlite3 db1 test.db 187 sqlite3 db2 test.db 188 sqlite3 db3 test.db 189 } {} 190 db1 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 } 203 do_test shared6-3.4 { 204 catchsql { SELECT * FROM t1 } db2 205 } {1 {database schema is locked: main}} 206 do_test shared6-3.5 { 207 execsql COMMIT db1 208 } {} 209 db2 eval {SELECT * FROM t1} { 210 do_test shared6-3.6 { 211 catchsql { BEGIN EXCLUSIVE } db1 212 } {1 {database table is locked}} 213 break 214 } 215 do_test shared6-3.7 { 216 execsql { BEGIN } db1 217 execsql { BEGIN } db2 218 } {} 219 db2 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 } 225 do_test shared6-3.9 { 226 execsql { BEGIN ; ROLLBACK } db3 227 } {} 228 do_test shared6-3.10 { 229 catchsql { SELECT * FROM t1 } db3 230 } {1 {database table is locked}} 231 do_test shared6-3.X { 232 db1 close 233 db2 close 234 db3 close 235 } {} 236 237 do_test shared6-4.1 { 238 #file delete -force 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 } {} 245 do_test shared6-4.2 { 246 sqlite3_finalize $::STMT 247 } {SQLITE_OK} 248 do_test shared6-4.X { 249 250 db1 close 251 db2 close 252 } {} 253 254 sqlite3_enable_shared_cache $::enable_shared_cache 255 finish_test 256
