# 2015 July 26 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # set testdir [file dirname $argv0] source $testdir/tester.tcl source $testdir/lock_common.tcl set ::testprefix unlocked do_execsql_test 1.0 { PRAGMA journal_mode = wal; } {wal} do_execsql_test 1.1 { CREATE TABLE t1(k INTEGER PRIMARY KEY, v); BEGIN UNLOCKED; INSERT INTO t1 VALUES(1, 'abcd'); COMMIT; } do_execsql_test 1.2 { SELECT * FROM t1; } {1 abcd} do_execsql_test 1.3 { BEGIN UNLOCKED; INSERT INTO t1 VALUES(2, 'efgh'); ROLLBACK; } do_execsql_test 1.4 { SELECT * FROM t1; } {1 abcd} #------------------------------------------------------------------------- # UNLOCKED transactions cannot do cache spills. # foreach {tn trans spill} { 1 {BEGIN UNLOCKED} 0 2 {BEGIN} 1 } { do_test 1.5.$tn { sqlite3 db2 test.db set walsz [file size test.db-wal] execsql { PRAGMA cache_size = 10 } db2 execsql $trans db2 execsql { WITH cnt(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM cnt WHERE i<50) INSERT INTO t1(v) SELECT randomblob(900) FROM cnt; } db2 expr {[file size test.db-wal]==$walsz} } [expr !$spill] execsql ROLLBACK db2 db2 close } #------------------------------------------------------------------------- # UNLOCKED transactions man not be committed while there are active # readers. do_execsql_test 1.6.setup { DROP TABLE t1; CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 VALUES(3, 4); INSERT INTO t1 VALUES(5, 6); } foreach {tn trans commit_ok} { 1 {BEGIN UNLOCKED} 0 2 {BEGIN} 1 } { do_test 1.6.$tn.1 { set stmt [sqlite3_prepare db "SELECT * FROM t1" -1 dummy] sqlite3_step $stmt } SQLITE_ROW do_test 1.6.$tn.2 { execsql $trans execsql { INSERT INTO t1 VALUES(7, 8) } } {} if { $commit_ok } { do_test 1.6.$tn.3 { catchsql COMMIT } {0 {}} } else { do_test 1.6.$tn.4 { catchsql COMMIT } {/1 {cannot commit transaction .*}/} } sqlite3_finalize $stmt catchsql ROLLBACK } #------------------------------------------------------------------------- # UNLOCKED transactions may not modify the db schema. # foreach {tn sql} { 1 { CREATE TABLE xx(a, b) } 2 { DROP TABLE t1 } } { do_catchsql_test 1.7.$tn.1 " BEGIN UNLOCKED; $sql " {1 {cannot modify database schema - UNLOCKED transaction}} do_execsql_test 1.7.$tn.2 ROLLBACK } do_multiclient_test tn { #----------------------------------------------------------------------- # 1. Start an UNLOCKED transaction using [db1]. # # 2. Start and then rollback a regular transaction using [db2]. This # can be done as the ongoing [db1] transaction is UNLOCKED. # # 3. The [db1] transaction can now be committed, as [db2] has relinquished # the write lock. # do_test 2.$tn.1.1 { sql1 { PRAGMA journal_mode = wal; CREATE TABLE t1(k INTEGER PRIMARY KEY, v); INSERT INTO t1 VALUES(1, 'one'); } sql1 { BEGIN UNLOCKED; INSERT INTO t1 VALUES(2, 'two'); } code1 { sqlite3_get_autocommit db } } 0 do_test 2.$tn.1.2 { sql2 { BEGIN; INSERT INTO t1 VALUES(3, 'three'); ROLLBACK; } } {} do_test 2.$tn.1.3 { sql1 COMMIT sql2 { SELECT * FROM t1 } } {1 one 2 two} #----------------------------------------------------------------------- # 1. Start an UNLOCKED transaction using [db1]. # # 2. Commit a transaction using [db2]. # # 3. Try to commit with [db1]. Check that SQLITE_BUSY_SNAPSHOT is returned, # and the transaction is not rolled back. # do_test 2.$tn.2.1 { sql1 { BEGIN UNLOCKED; INSERT INTO t1 VALUES(-1, 'hello world'); } } {} do_test 2.$tn.2.2 { sql2 { INSERT INTO t1 VALUES(3, 'three'); } } {} do_test 2.$tn.2.3.1 { set rc [catch { sql1 COMMIT } msg] list $rc $msg } {1 {database is locked}} do_test 2.$tn.2.3.2 { code1 { list [sqlite3_extended_errcode db] [sqlite3_get_autocommit db] } } {SQLITE_BUSY_SNAPSHOT 0} do_test 2.$tn.2.3.3 { sql1 { SELECT * FROM t1; ROLLBACK; } } {-1 {hello world} 1 one 2 two} #----------------------------------------------------------------------- # 1. Start an UNLOCKED transaction using [db1]. # # 2. Open a transaction using [db2]. # # 3. Try to commit with [db1]. Check that SQLITE_BUSY is returned, # and the transaction is not rolled back. # # 4. Have [db2] roll its transaction back. Then check that [db1] can # commit. # do_test 2.$tn.3.1 { sql1 { BEGIN UNLOCKED; INSERT INTO t1 VALUES(4, 'four'); } } {} do_test 2.$tn.3.2 { sql2 { BEGIN; INSERT INTO t1 VALUES(-1, 'xyz'); } } {} do_test 2.$tn.3.3.1 { set rc [catch { sql1 COMMIT } msg] list $rc $msg } {1 {database is locked}} do_test 2.$tn.3.3.2 { code1 { list [sqlite3_extended_errcode db] [sqlite3_get_autocommit db] } } {SQLITE_BUSY 0} do_test 2.$tn.3.3.3 { sql1 { SELECT * FROM t1; } } {1 one 2 two 3 three 4 four} do_test 2.$tn.3.4 { sql2 ROLLBACK sql1 COMMIT sql1 { SELECT * FROM t1; } } {1 one 2 two 3 three 4 four} #----------------------------------------------------------------------- # 1. Create a second table - t2. # # 2. Write to t1 with [db] and t2 with [db2]. # # 3. See if it worked. # do_test 2.$tn.4.1 { sql1 { CREATE TABLE t2(a, b) } } {} do_test 2.$tn.4.2 { sql2 { BEGIN UNLOCKED; INSERT INTO t2 VALUES('i', 'n'); } sql1 { BEGIN UNLOCKED; INSERT INTO t1 VALUES(5, 'five'); COMMIT; } sql2 COMMIT } {} do_test 2.$tn.4.3.1 { sql2 {SELECT * FROM t1} } {1 one 2 two 3 three 4 four 5 five} do_test 2.$tn.4.3.2 { sql1 {SELECT * FROM t1} } {1 one 2 two 3 three 4 four 5 five} do_test 2.$tn.4.3.3 { sql2 {SELECT * FROM t2} } {i n} do_test 2.$tn.4.3.4 { sql1 {SELECT * FROM t2} } {i n} #----------------------------------------------------------------------- # The "schema cookie" issue. # # 1. Begin and UNLOCKED write to "t1" using [db] # # 2. Create an index on t1 using [db2]. # # 3. Attempt to commit the UNLOCKED write. This is an SQLITE_BUSY_SNAPSHOT, # even though there is no page collision. # do_test 2.$tn.5.1 { sql1 { BEGIN UNLOCKED; INSERT INTO t1 VALUES(6, 'six'); } } {} do_test 2.$tn.5.2 { sql2 { CREATE INDEX i1 ON t1(v); } } {} do_test 2.$tn.5.3 { list [catch { sql1 { COMMIT } } msg] $msg [sqlite3_errcode db] } {1 {database is locked} SQLITE_BUSY_SNAPSHOT} do_test 2.$tn.5.4 { sql2 { PRAGMA integrity_check } } {ok} catch { sql1 ROLLBACK } #----------------------------------------------------------------------- # The "schema cookie" issue. # # 1. Begin an UNLOCKED write to "t1" using [db] # # 2. Lots of inserts into t2. Enough to grow the db file. # # 3. Check that the UNLOCKED transaction can still be committed. # do_test 2.$tn.6.1 { sql1 { BEGIN UNLOCKED; INSERT INTO t1 VALUES(6, 'six'); } } {} do_test 2.$tn.6.2 { sql2 { WITH src(a,b) AS ( VALUES(1,1) UNION ALL SELECT a+1,b+1 FROM src WHERE a<10000 ) INSERT INTO t2 SELECT * FROM src; } } {} do_test 2.$tn.6.3 { sql1 { SELECT count(*) FROM t2; COMMIT; SELECT count(*) FROM t2; } } {1 10001} #----------------------------------------------------------------------- # # 1. Begin an big UNLOCKED write to "t1" using [db] - large enough to # grow the db file. # # 2. Lots of inserts into t2. Also enough to grow the db file. # # 3. Check that the UNLOCKED transaction cannot be committed (due to a clash # on page 1 - the db size field). # do_test 2.$tn.7.1 { sql1 { BEGIN UNLOCKED; WITH src(a,b) AS ( VALUES(10000,10000) UNION ALL SELECT a+1,b+1 FROM src WHERE a<20000 ) INSERT INTO t1 SELECT * FROM src; } } {} do_test 2.$tn.7.2 { sql2 { WITH src(a,b) AS ( VALUES(1,1) UNION ALL SELECT a+1,b+1 FROM src WHERE a<10000 ) INSERT INTO t2 SELECT * FROM src; } } {} do_test 2.$tn.7.3 { list [catch { sql1 { COMMIT } } msg] $msg [sqlite3_errcode db] } {1 {database is locked} SQLITE_BUSY_SNAPSHOT} sql1 ROLLBACK } finish_test