# 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. # #*********************************************************************** # # Miscellaneous tests for transactions started with BEGIN CONCURRENT. # set testdir [file dirname $argv0] source $testdir/tester.tcl source $testdir/lock_common.tcl source $testdir/wal_common.tcl set ::testprefix concurrent2 ifcapable !concurrent { finish_test return } do_multiclient_test tn { do_test 1.$tn.1 { sql1 { PRAGMA journal_mode = wal; CREATE TABLE t1(x); CREATE TABLE t2(y); } } {wal} do_test 1.$tn.5 { sql3 { PRAGMA integrity_check } } {ok} # Test that an CONCURRENT transaction that allocates/frees no pages does # not conflict with a transaction that does allocate pages. do_test 1.$tn.2 { sql1 { BEGIN CONCURRENT; INSERT INTO t1 VALUES(4); } sql2 { INSERT INTO t2 VALUES(randomblob(1500)); } sql1 { COMMIT; } } {} do_test 1.$tn.5 { sql3 { PRAGMA integrity_check } } {ok} # But that an CONCURRENT transaction does conflict with a transaction # that modifies the db schema. do_test 1.$tn.3 { sql1 { BEGIN CONCURRENT; INSERT INTO t1 VALUES(5); } sql2 { CREATE TABLE t3(z); } list [catch { sql1 COMMIT } msg] $msg } {1 {database is locked}} do_test 1.$tn.5 { sql3 { PRAGMA integrity_check } } {ok} # Test that an CONCURRENT transaction that allocates at least one page # does not conflict with a transaction that allocates no pages. do_test 1.$tn.4 { sql1 { ROLLBACK; BEGIN CONCURRENT; INSERT INTO t1 VALUES(randomblob(1500)); } sql2 { INSERT INTO t2 VALUES(8); } sql1 { COMMIT; } } {} do_test 1.$tn.5 { sql3 { PRAGMA integrity_check } } {ok} } do_multiclient_test tn { do_test 2.$tn.1 { sql1 { PRAGMA journal_mode = wal; CREATE TABLE t1(x UNIQUE); CREATE TABLE t2(y UNIQUE); } } {wal} do_test 2.$tn.2 { sql1 { BEGIN CONCURRENT; INSERT INTO t1 VALUES(randomblob(1500)); } sql2 { INSERT INTO t2 VALUES(randomblob(1500)); } sql1 COMMIT } {} do_test 2.$tn.3 { sql3 { PRAGMA integrity_check } } {ok} do_test 2.$tn.4 { sql1 { BEGIN CONCURRENT; DELETE FROM t1; } sql2 { DELETE FROM t2; } sql1 COMMIT } {} do_test 2.$tn.5 { sql3 { PRAGMA integrity_check } } {ok} do_test 2.$tn.6 { sql1 { INSERT INTO t1 VALUES(randomblob(1500)); INSERT INTO t1 VALUES(randomblob(1500)); INSERT INTO t2 VALUES(randomblob(1500)); DELETE FROM t1 WHERE rowid=1; } sql1 { BEGIN CONCURRENT; DELETE FROM t1 WHERE rowid=2; } sql2 { DELETE FROM t2; } sql1 COMMIT } {} do_test 2.$tn.7 { sql3 { PRAGMA integrity_check } } {ok} } #------------------------------------------------------------------------- # When an CONCURRENT transaction is opened on a database, the nFree and # iTrunk header fields of the cached version of page 1 are both set # to 0. This allows an CONCURRENT transaction to use its own private # free-page-list, which is merged with the main database free-list when # the transaction is committed. # # The following tests check that nFree/iTrunk are correctly restored if # an CONCURRENT transaction is rolled back, and that savepoint rollbacks # that occur within CONCURRENT transactions do not incorrectly restore # these fields to their on-disk values. # reset_db do_execsql_test 3.0 { PRAGMA journal_mode = wal; CREATE TABLE t1(x, y); INSERT INTO t1 VALUES(randomblob(1500), randomblob(1500)); DELETE FROM t1; } {wal} do_execsql_test 3.1 { BEGIN CONCURRENT; INSERT INTO t1 VALUES(1, 2); ROLLBACK; } do_execsql_test 3.2 { PRAGMA integrity_check } {ok} do_execsql_test 3.3 { PRAGMA freelist_count } {2} do_execsql_test 3.4.1 { BEGIN CONCURRENT; PRAGMA freelist_count; } {2} do_execsql_test 3.4.2 { SAVEPOINT xyz; INSERT INTO t1 VALUES(randomblob(1500), NULL); PRAGMA freelist_count; } {0} do_execsql_test 3.4.3 { ROLLBACK TO xyz; } {} do_execsql_test 3.4.4 { PRAGMA freelist_count } {0} do_execsql_test 3.4.5 { COMMIT; PRAGMA freelist_count } {2} do_execsql_test 3.4.6 { PRAGMA integrity_check } {ok} do_execsql_test 3.5.1 { BEGIN CONCURRENT; UPDATE t1 SET x=randomblob(10) WHERE y=555; PRAGMA freelist_count; } {0} do_execsql_test 3.5.2 { ROLLBACK; PRAGMA freelist_count; } {2} do_execsql_test 3.5.3 { PRAGMA integrity_check } {ok} #------------------------------------------------------------------------- # Test that nothing goes wrong if an CONCURRENT transaction allocates a # page at the end of the file, frees it within the same transaction, and # then has to move the same page to avoid a conflict on COMMIT. # do_multiclient_test tn { do_test 4.$tn.1 { sql1 { PRAGMA journal_mode = wal; CREATE TABLE t1(x); CREATE TABLE t2(x); } } {wal} do_test 4.$tn.2 { sql1 { BEGIN CONCURRENT; INSERT INTO t1 VALUES(randomblob(1500)); INSERT INTO t1 VALUES(randomblob(1500)); DELETE FROM t1 WHERE rowid = 1; } sql2 { INSERT INTO t2 VALUES(randomblob(1500)); INSERT INTO t2 VALUES(randomblob(1500)); INSERT INTO t2 VALUES(randomblob(1500)); INSERT INTO t2 VALUES(randomblob(1500)); DELETE FROM t2 WHERE rowid IN (1, 2); } sql1 COMMIT } {} } #------------------------------------------------------------------------- # do_multiclient_test tn { do_test 5.$tn.1 { sql1 { PRAGMA journal_mode = wal; CREATE TABLE t1(x); CREATE TABLE t2(x); INSERT INTO t1 VALUES(randomblob(1500)); PRAGMA page_count; } } {wal 4} do_test 5.$tn.2 { sql1 { BEGIN CONCURRENT; INSERT INTO t2 VALUES(randomblob(1500)); PRAGMA page_count; } } {5} do_test 5.$tn.3 { sql2 { DELETE FROM t1; PRAGMA freelist_count; PRAGMA page_count; } } {1 4} do_test 5.$tn.4 { sql1 COMMIT } {} do_test 5.$tn.5 { sql3 { PRAGMA integrity_check } } {ok} } #------------------------------------------------------------------------- # do_multiclient_test tn { do_test 6.$tn.1 { sql1 { PRAGMA journal_mode = wal; CREATE TABLE t1(x); INSERT INTO t1 VALUES(randomblob(1500)); PRAGMA wal_checkpoint; } } {wal 0 5 5} do_test 6.$tn.2 { sql1 { BEGIN CONCURRENT; INSERT INTO t1 VALUES(randomblob(1500)); INSERT INTO t1 VALUES(randomblob(1500)); } } {} do_test 6.$tn.3 { sql2 { BEGIN; INSERT INTO t1 VALUES(randomblob(1500)); INSERT INTO t1 VALUES(randomblob(1500)); COMMIT; } } {} do_test 6.$tn.4 { list [catch { sql1 COMMIT } msg] $msg } {1 {database is locked}} do_test 6.$tn.5 { sql3 { PRAGMA integrity_check } } {ok} do_test 6.$tn.5 { sql3 { SELECT count(*) from t1 } } {3} } #------------------------------------------------------------------------- # Test that if a corrupt wal-index-header is encountered when attempting # to commit a CONCURRENT transaction, the transaction is not committed # (or rolled back) and that SQLITE_BUSY_SNAPSHOT is returned to the user. # catch { db close } forcedelete test.db testvfs tvfs sqlite3 db test.db -vfs tvfs do_execsql_test 7.1 { PRAGMA journal_mode = wal; BEGIN; CREATE TABLE t1(a, b, PRIMARY KEY(a)); INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 VALUES(3, 4); COMMIT; BEGIN CONCURRENT; INSERT INTO t1 VALUES(5, 6); INSERT INTO t1 VALUES(7, 8); SELECT * FROM t1; } {wal 1 2 3 4 5 6 7 8} # Corrupt the wal-index header incr_tvfs_hdr test.db 11 1 do_catchsql_test 7.2.1 { COMMIT } {1 {database is locked}} do_test 7.2.2 { sqlite3_extended_errcode db } SQLITE_BUSY_SNAPSHOT do_execsql_test 7.3.1 { SELECT * FROM t1; ROLLBACK; } {1 2 3 4 5 6 7 8} do_execsql_test 7.3.2 { SELECT * FROM t1; } {1 2 3 4} #------------------------------------------------------------------------- # Test that "PRAGMA integrity_check" works within a concurrent # transaction. Within a concurrent transaction, "PRAGMA integrity_check" # is unable to detect unused database pages, but can detect other types # of corruption. # reset_db do_execsql_test 8.1 { PRAGMA journal_mode = wal; CREATE TABLE kv(k INTEGER PRIMARY KEY, v UNIQUE); INSERT INTO kv VALUES(NULL, randomblob(750)); INSERT INTO kv SELECT NULL, randomblob(750) FROM kv; INSERT INTO kv SELECT NULL, randomblob(750) FROM kv; INSERT INTO kv SELECT NULL, randomblob(750) FROM kv; INSERT INTO kv SELECT NULL, randomblob(750) FROM kv; INSERT INTO kv SELECT NULL, randomblob(750) FROM kv; DELETE FROM kv WHERE rowid%2; PRAGMA freelist_count; } {wal 34} do_execsql_test 8.2 { PRAGMA integrity_check } ok do_execsql_test 8.3 { BEGIN CONCURRENT; PRAGMA integrity_check; } {ok} do_execsql_test 8.4 { INSERT INTO kv VALUES(1100, 1100); PRAGMA integrity_check; } {ok} do_execsql_test 8.5 { COMMIT; PRAGMA integrity_check; } {ok} #------------------------------------------------------------------------- # Test that concurrent transactions do not allow foreign-key constraints # to be bypassed. # do_multiclient_test tn { do_test 9.$tn.1 { sql1 { PRAGMA journal_mode = wal; CREATE TABLE pp(i INTEGER PRIMARY KEY, j); CREATE TABLE cc(a, b REFERENCES pp); WITH seq(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM seq WHERE i<100) INSERT INTO pp SELECT i, randomblob(1000) FROM seq; PRAGMA foreign_keys = 1; } } {wal} do_test 9.$tn.2.1 { sql1 { BEGIN CONCURRENT; INSERT INTO cc VALUES(42, 42); } } {} do_test 9.$tn.2.2 { sql2 { DELETE FROM pp WHERE i=42 } list [catch { sql1 COMMIT } msg] $msg } {1 {database is locked}} do_test 9.$tn.2.3 { sql1 ROLLBACK } {} do_test 9.$tn.3.1 { sql1 { PRAGMA foreign_keys = 0; BEGIN CONCURRENT; INSERT INTO cc VALUES(43, 43); } } {} do_test 9.$tn.3.2 { sql2 { DELETE FROM pp WHERE i=43 } list [catch { sql1 COMMIT } msg] $msg } {0 {}} do_test 9.$tn.4.1 { sql1 { PRAGMA foreign_keys = on; BEGIN CONCURRENT; INSERT INTO cc VALUES(44, 44); } } {} do_test 9.$tn.4.2 { sql2 { DELETE FROM pp WHERE i=1 } list [catch { sql1 COMMIT } msg] $msg } {0 {}} } #------------------------------------------------------------------------- # Test that even if a SELECT statement appears before all writes within # a CONCURRENT transaction, the pages it reads are still considered when # considering whether or not the transaction may be committed. # do_multiclient_test tn { do_test 10.$tn.1.1 { sql1 { PRAGMA journal_mode = wal; CREATE TABLE t1(a); CREATE TABLE t2(b); CREATE TABLE t3(c); INSERT INTO t1 VALUES(1), (2), (3); INSERT INTO t2 VALUES(1), (2), (3); INSERT INTO t3 VALUES(1), (2), (3); } } {wal} do_test 10.$tn.1.2 { sql1 { BEGIN CONCURRENT; SELECT * FROM t1; INSERT INTO t2 VALUES(4); } } {1 2 3} do_test 10.$tn.1.3 { sql2 { INSERT INTO t1 VALUES(4) } list [catch {sql1 COMMIT} msg] $msg } {1 {database is locked}} sql1 ROLLBACK # In this case, because the "SELECT * FROM t1" is first stepped before # the "BEGIN CONCURRENT", the pages it reads are not recorded by the # pager object. And so the transaction can be committed. Technically # this behaviour (the effect of an ongoing SELECT on a BEGIN CONCURRENT # transacation) is undefined. # do_test 10.$tn.2.1 { code1 { set ::stmt [sqlite3_prepare db "SELECT * FROM t1" -1 dummy] sqlite3_step $::stmt } } {SQLITE_ROW} do_test 10.$tn.2.2 { sql1 { BEGIN CONCURRENT; INSERT INTO t2 VALUES(4); } code1 { set res [list] lappend res [sqlite3_column_int $::stmt 0] while {[sqlite3_step $::stmt]=="SQLITE_ROW"} { lappend res [sqlite3_column_int $::stmt 0] } sqlite3_finalize $::stmt set res } } {1 2 3 4} do_test 10.$tn.2.3 { sql2 { INSERT INTO t1 VALUES(5) } sql1 COMMIT } {} # More tests surrounding long-lived prepared statements and concurrent # transactions. do_test 10.$tn.3.1 { sql1 { BEGIN CONCURRENT; SELECT * FROM t1; COMMIT; } sql1 { BEGIN CONCURRENT; INSERT INTO t2 VALUES(5); } sql2 { INSERT INTO t1 VALUES(5); } sql1 COMMIT sql3 { SELECT * FROM t2; } } {1 2 3 4 5} do_test 10.$tn.3.2 { sql1 { BEGIN CONCURRENT; SELECT * FROM t1; ROLLBACK; } sql1 { BEGIN CONCURRENT; INSERT INTO t2 VALUES(6); } sql2 { INSERT INTO t1 VALUES(6); } sql1 COMMIT sql3 { SELECT * FROM t2 } } {1 2 3 4 5 6} do_test 10.$tn.3.3 { sql1 { BEGIN CONCURRENT } code1 { set ::stmt [sqlite3_prepare db "SELECT * FROM t1" -1 dummy] sqlite3_step $::stmt } sql1 { INSERT INTO t2 VALUES(7); SELECT * FROM t3; ROLLBACK; BEGIN CONCURRENT; } sql2 { INSERT INTO t3 VALUES(5) } code1 { sqlite3_finalize $::stmt } sql1 { INSERT INTO t2 VALUES(8); COMMIT; } } {} } do_multiclient_test tn { do_test 11.$tn.1 { sql1 { PRAGMA journal_mode = wal; CREATE TABLE t1(a); } } {wal} do_test 11.$tn.2 { code1 { sqlite3_wal_info db main } } {0 2} do_test 11.$tn.3 { sql1 { INSERT INTO t1 VALUES(1) } code1 { sqlite3_wal_info db main } } {2 3} do_test 11.$tn.4 { sql2 { INSERT INTO t1 VALUES(2) } code2 { sqlite3_wal_info db2 main } } {3 4} do_test 11.$tn.5 { sql1 { PRAGMA wal_checkpoint } sql2 { INSERT INTO t1 VALUES(3) } code2 { sqlite3_wal_info db2 main } } {0 1} } reset_db do_execsql_test 12.0 { PRAGMA journal_mode = wal; CREATE TABLE tx(a INTEGER PRIMARY KEY, b); } {wal} do_test 12.1 { for {set i 0} {$i < 50} {incr i} { execsql { BEGIN CONCURRENT; INSERT INTO tx(b) VALUES( randomblob( 1200 ) ); COMMIT; } } execsql { PRAGMA page_size } } {1024} do_execsql_test 12.2 { DELETE FROM tx; } do_test 12.3 { for {set i 0} {$i < 50} {incr i} { execsql { BEGIN CONCURRENT; INSERT INTO tx(b) VALUES( randomblob( 1200 ) ); COMMIT; } } execsql { PRAGMA page_size } } {1024} do_execsql_test 12.4 { DELETE FROM tx; } do_test 12.5 { execsql { BEGIN CONCURRENT } for {set i 0} {$i < 5000} {incr i} { execsql { INSERT INTO tx(b) VALUES( randomblob( 1200 ) ); } } execsql { COMMIT } execsql { PRAGMA page_size } } {1024} finish_test