1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
# 2005 January 19
#
# 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.
#
#***********************************************************************
#
# $Id: shared2.test,v 1.5 2007/08/23 02:47:54 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
db close
ifcapable !shared_cache {
finish_test
return
}
set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
# Test that if we delete all rows from a table any read-uncommitted
# cursors are correctly invalidated. Test on both table and index btrees.
do_test shared2-1.1 {
sqlite3 db1 test.db
sqlite3 db2 test.db
# Set up some data. Table "numbers" has 64 rows after this block
# is executed.
execsql {
BEGIN;
CREATE TABLE numbers(a PRIMARY KEY, b);
INSERT INTO numbers(oid) VALUES(NULL);
INSERT INTO numbers(oid) SELECT NULL FROM numbers;
INSERT INTO numbers(oid) SELECT NULL FROM numbers;
INSERT INTO numbers(oid) SELECT NULL FROM numbers;
INSERT INTO numbers(oid) SELECT NULL FROM numbers;
INSERT INTO numbers(oid) SELECT NULL FROM numbers;
INSERT INTO numbers(oid) SELECT NULL FROM numbers;
UPDATE numbers set a = oid, b = 'abcdefghijklmnopqrstuvwxyz0123456789';
COMMIT;
} db1
} {}
do_test shared2-1.2 {
# Put connection 2 in read-uncommitted mode and start a SELECT on table
# 'numbers'. Half way through the SELECT, use connection 1 to delete the
# contents of this table.
execsql {
pragma read_uncommitted = 1;
} db2
set count [execsql {SELECT count(*) FROM numbers} db2]
db2 eval {SELECT a FROM numbers ORDER BY oid} {
if {$a==32} {
execsql {
BEGIN;
DELETE FROM numbers;
} db1
}
}
list $a $count
} {32 64}
do_test shared2-1.3 {
# Same test as 1.2, except scan using the index this time.
execsql {
ROLLBACK;
} db1
set count [execsql {SELECT count(*) FROM numbers} db2]
db2 eval {SELECT a, b FROM numbers ORDER BY a} {
if {$a==32} {
execsql {
DELETE FROM numbers;
} db1
}
}
list $a $count
} {32 64}
#---------------------------------------------------------------------------
# These tests, shared2.2.*, test the outcome when data is added to or
# removed from a table due to a rollback while a read-uncommitted
# cursor is scanning it.
#
do_test shared2-2.1 {
execsql {
INSERT INTO numbers VALUES(1, 'Medium length text field');
INSERT INTO numbers VALUES(2, 'Medium length text field');
INSERT INTO numbers VALUES(3, 'Medium length text field');
INSERT INTO numbers VALUES(4, 'Medium length text field');
BEGIN;
DELETE FROM numbers WHERE (a%2)=0;
} db1
set res [list]
db2 eval {
SELECT a FROM numbers ORDER BY a;
} {
lappend res $a
if {$a==3} {
execsql {ROLLBACK} db1
}
}
set res
} {1 3 4}
do_test shared2-2.2 {
execsql {
BEGIN;
INSERT INTO numbers VALUES(5, 'Medium length text field');
INSERT INTO numbers VALUES(6, 'Medium length text field');
} db1
set res [list]
db2 eval {
SELECT a FROM numbers ORDER BY a;
} {
lappend res $a
if {$a==5} {
execsql {ROLLBACK} db1
}
}
set res
} {1 2 3 4 5}
db1 close
db2 close
do_test shared2-3.2 {
sqlite3_enable_shared_cache 1
} {1}
sqlite3_enable_shared_cache $::enable_shared_cache
finish_test
|
