Artifact
854f3f428bb2e0ae91cb2400596a8f1ab7eaa409:
- File
test/unlocked.test
— part of check-in
[6da0e962]
at
2015-07-28 16:46:49
on branch begin-concurrent
— Add some test cases and fix some small problems with BEGIN UNLOCKED transactions.
(user:
dan
size: 8700)
# 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