# 2017 May 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 concurrent4 ifcapable !concurrent { finish_test return } do_execsql_test 1.0 { PRAGMA journal_mode = wal; CREATE TABLE t1(x PRIMARY KEY, y UNIQUE); WITH s(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100) INSERT INTO t1 SELECT randomblob(400), randomblob(400) FROM s; DELETE FROM t1 WHERE rowid<2; } {wal} do_execsql_test 1.1 { BEGIN CONCURRENT; INSERT INTO t1(rowid, x, y) VALUES(1000, randomblob(3000), randomblob(3000)); SAVEPOINT abc; DELETE FROM t1 WHERE rowid = 1000; } do_execsql_test 1.2 { ROLLBACK TO abc } do_execsql_test 1.3 { COMMIT } do_execsql_test 1.4 { PRAGMA integrity_check } {ok} do_multiclient_test tn { do_test 2.$tn.1 { sql1 { PRAGMA journal_mode = wal; CREATE TABLE t1(a, b); WITH s(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100) INSERT INTO t1 SELECT randomblob(400), randomblob(400) FROM s; CREATE TABLE t2(a, b); WITH s(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100) INSERT INTO t2 SELECT randomblob(400), randomblob(400) FROM s; } sql1 { BEGIN CONCURRENT; INSERT INTO t1 VALUES(randomblob(3000), randomblob(3000)); } } {} do_test 2.$tn.2 { sql2 { WITH s(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<10) INSERT INTO t2 SELECT randomblob(400), randomblob(400) FROM s; } sql2 { DELETE FROM t2 WHERE rowid<10; } } {} do_test 2.$tn.3 { sql1 { COMMIT; PRAGMA integrity_check; } } {ok} do_test 2.$tn.4 { sql2 { PRAGMA integrity_check; } } {ok} } reset_db do_execsql_test 3.1 { PRAGMA page_size = 1024; PRAGMA journal_mode = wal; CREATE TABLE t2(x); INSERT INTO t2 VALUES(randomblob(5000)); CREATE TABLE t1(a INTEGER PRIMARY KEY, b); INSERT INTO t1 VALUES(25, randomblob(104)); DELETE FROM t2; } {wal} do_execsql_test 3.2 { BEGIN CONCURRENT; REPLACE INTO t1 VALUES(25, randomblob(1117)); COMMIT; } {} #------------------------------------------------------------------------- # Test the effect of BEGIN CONCURRENT transactions that consist entirely # of read-only statements. # reset_db do_execsql_test 4.0 { PRAGMA page_size = 1024; PRAGMA journal_mode = wal; CREATE TABLE t4(a, b); INSERT INTO t4 VALUES(1, 2); INSERT INTO t4 VALUES(3, 4); } {wal} sqlite3 db2 test.db do_test 4.1.1 { db eval { BEGIN CONCURRENT; INSERT INTO t4 VALUES(5, 6); } db2 eval { BEGIN CONCURRENT; SELECT * FROM t4; ROLLBACK; } } {1 2 3 4} do_test 4.1.2 { db eval { COMMIT } db2 eval { SELECT * FROM t4 } } {1 2 3 4 5 6} do_test 4.2.1 { db eval { BEGIN CONCURRENT; INSERT INTO t4 VALUES(7, 8); } db2 eval { BEGIN CONCURRENT; SELECT * FROM t4; COMMIT; } } {1 2 3 4 5 6} do_test 4.2.2 { db eval { COMMIT } db2 eval { SELECT * FROM t4 } } {1 2 3 4 5 6 7 8} do_test 4.3 { db2 eval { BEGIN CONCURRENT; SELECT * FROM t4; } db eval { BEGIN CONCURRENT; INSERT INTO t4 VALUES(9, 10); COMMIT; } db2 eval { SELECT * FROM t4; COMMIT; } } {1 2 3 4 5 6 7 8} set sz [file size test.db-wal] do_test 4.4.1 { db eval { BEGIN CONCURRENT; SELECT * FROM t4; SELECT * FROM sqlite_master; } db eval COMMIT file size test.db-wal } $sz do_test 4.4.2 { db eval { BEGIN CONCURRENT; SELECT * FROM t4; SELECT * FROM sqlite_master; ROLLBACK; } file size test.db-wal } $sz finish_test