database/sqlite
1
0
Fork 0
mirror of https://github.com/sqlite/sqlite.git synced 2025-08-01 06:27:03 +03:00
Code Activity
Files
bedrock
sqlite/test/concurrent2.test
dan 5a19f287dc Merge changes from begin-concurrent-wal2. Also modify the unix-excl shm-locks 
on this branch so that it is not an error to attempt to release an EXCLUSIVE
lock that is not held.

FossilOrigin-Name: 86685679b4cff94704fe4b52364f25f579d98ee725407cfe3bd2bbf7ecd651a8
2019-01-02 19:46:46 +00:00

631 lines
15 KiB
Plaintext
Raw Permalink Blame History

# 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_test 0.1 {
llength [sqlite3_wal_info db main]
} {2}
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_test 8.1 {
execsql {
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;
}
set v [db one {PRAGMA freelist_count}]
expr $v==33 || $v==34
} {1}
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
View Git Blame Copy Permalink