1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test basic transaction functionality", "[transactions]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db), con2(db);
11 con.EnableQueryVerification();
12
13 // cannot commit or rollback in auto commit mode
14 REQUIRE_FAIL(con.Query("COMMIT"));
15 REQUIRE_FAIL(con.Query("ROLLBACK"));
16 // we can start a transaction
17 REQUIRE_NO_FAIL(con.Query("START TRANSACTION"));
18 // but we cannot start a transaction within a transaction!
19 REQUIRE_FAIL(con.Query("START TRANSACTION"));
20 // now we can rollback
21 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
22}
23
24TEST_CASE("Test operations on transaction local data", "[transactions]") {
25 unique_ptr<QueryResult> result;
26 DuckDB db(nullptr);
27 Connection con(db);
28 con.EnableQueryVerification();
29
30 // perform different operations on the same data within one transaction
31 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
32 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER, j INTEGER)"));
33
34 // append
35 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 3), (2, 3)"));
36
37 result = con.Query("SELECT * FROM integers ORDER BY 1");
38 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
39 REQUIRE(CHECK_COLUMN(result, 1, {3, 3}));
40
41 // update
42 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET j=5 WHERE i=2"));
43
44 result = con.Query("SELECT * FROM integers ORDER BY 1");
45 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
46 REQUIRE(CHECK_COLUMN(result, 1, {3, 5}));
47
48 // delete
49 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=2"));
50
51 result = con.Query("SELECT * FROM integers ORDER BY 1");
52 REQUIRE(CHECK_COLUMN(result, 0, {1}));
53 REQUIRE(CHECK_COLUMN(result, 1, {3}));
54
55 // commit
56 REQUIRE_NO_FAIL(con.Query("COMMIT"));
57
58 // we can still read the table now
59 result = con.Query("SELECT * FROM integers ORDER BY 1");
60 REQUIRE(CHECK_COLUMN(result, 0, {1}));
61 REQUIRE(CHECK_COLUMN(result, 1, {3}));
62}
63
64TEST_CASE("Test appends on transaction local data with unique indices", "[transactions]") {
65 unique_ptr<QueryResult> result;
66 DuckDB db(nullptr);
67 Connection con(db);
68 con.EnableQueryVerification();
69
70 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY, j INTEGER)"));
71
72 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 3)"));
73
74 // append only
75 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
76 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (1, 2)"));
77 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
78
79 // if we delete we can insert that value again
80 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers"));
81 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 2)"));
82}
83
84TEST_CASE("Test appends with multiple transactions", "[transactions]") {
85 unique_ptr<QueryResult> result;
86 DuckDB db(nullptr);
87 Connection con(db), con2(db);
88 con.EnableQueryVerification();
89
90 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER, j INTEGER)"));
91
92 // begin two transactions
93 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
94 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
95
96 // append a tuple, con2 cannot see this tuple yet
97 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 3)"));
98 result = con2.Query("SELECT COUNT(*) FROM integers");
99 REQUIRE(CHECK_COLUMN(result, 0, {0}));
100 result = con2.Query("SELECT COUNT(*) FROM integers WHERE i=1");
101 REQUIRE(CHECK_COLUMN(result, 0, {0}));
102
103 // after committing, con2 still cannot see this tuple
104 REQUIRE_NO_FAIL(con.Query("COMMIT"));
105 result = con2.Query("SELECT COUNT(*) FROM integers");
106 REQUIRE(CHECK_COLUMN(result, 0, {0}));
107 result = con2.Query("SELECT COUNT(*) FROM integers WHERE i=1");
108 REQUIRE(CHECK_COLUMN(result, 0, {0}));
109
110 // after con2 commits, it can see this tuple
111 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
112 result = con2.Query("SELECT COUNT(*) FROM integers");
113 REQUIRE(CHECK_COLUMN(result, 0, {1}));
114 result = con2.Query("SELECT COUNT(*) FROM integers WHERE i=1");
115 REQUIRE(CHECK_COLUMN(result, 0, {1}));
116
117 // now both transactions append one tuple
118 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
119 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
120
121 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 3)"));
122 REQUIRE_NO_FAIL(con2.Query("INSERT INTO integers VALUES (1, 3)"));
123
124 // they cannot see each others tuple yet
125 result = con.Query("SELECT COUNT(*) FROM integers");
126 REQUIRE(CHECK_COLUMN(result, 0, {2}));
127 result = con2.Query("SELECT COUNT(*) FROM integers");
128 REQUIRE(CHECK_COLUMN(result, 0, {2}));
129
130 // until they both commit
131 REQUIRE_NO_FAIL(con.Query("COMMIT"));
132 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
133
134 result = con.Query("SELECT COUNT(*) FROM integers");
135 REQUIRE(CHECK_COLUMN(result, 0, {3}));
136 result = con2.Query("SELECT COUNT(*) FROM integers");
137 REQUIRE(CHECK_COLUMN(result, 0, {3}));
138
139 result = con.Query("SELECT * FROM integers");
140 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1}));
141 REQUIRE(CHECK_COLUMN(result, 1, {3, 3, 3}));
142}
143
144TEST_CASE("Test operations on transaction local data with unique indices", "[transactions]") {
145 unique_ptr<QueryResult> result;
146 DuckDB db(nullptr);
147 Connection con(db);
148 con.EnableQueryVerification();
149
150 // perform different operations on the same data within one transaction
151 for (idx_t i = 0; i < 3; i++) {
152 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
153 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY, j INTEGER)"));
154 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 3), (2, 3)"));
155
156 result = con.Query("SELECT * FROM integers ORDER BY 1");
157 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
158 REQUIRE(CHECK_COLUMN(result, 1, {3, 3}));
159
160 switch (i) {
161 case 0:
162 // appending the same value again fails
163 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (1, 2)"));
164 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
165 break;
166 case 1:
167 // updating also fails if there is a conflict
168 REQUIRE_FAIL(con.Query("UPDATE integers SET i=1 WHERE i=2"));
169 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
170 break;
171 default:
172 // but not if there is no conflict
173 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=3 WHERE i=2"));
174 REQUIRE_NO_FAIL(con.Query("COMMIT"));
175 break;
176 }
177 }
178
179 result = con.Query("SELECT * FROM integers ORDER BY 1");
180 REQUIRE(CHECK_COLUMN(result, 0, {1, 3}));
181 REQUIRE(CHECK_COLUMN(result, 1, {3, 3}));
182
183 // if we delete, we can insert the value again
184 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=1"));
185 result = con.Query("SELECT * FROM integers ORDER BY 1");
186 REQUIRE(CHECK_COLUMN(result, 0, {3}));
187 REQUIRE(CHECK_COLUMN(result, 1, {3}));
188
189 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 3)"));
190
191 result = con.Query("SELECT * FROM integers ORDER BY 1");
192 REQUIRE(CHECK_COLUMN(result, 0, {1, 3}));
193 REQUIRE(CHECK_COLUMN(result, 1, {3, 3}));
194}
195
196TEST_CASE("Test transaction aborts after failures", "[transactions]") {
197 unique_ptr<QueryResult> result;
198 DuckDB db(nullptr);
199 Connection con(db);
200 con.EnableQueryVerification();
201
202 // set up a table
203 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY)"));
204 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2)"));
205 // start a transaction
206 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
207 // parser errors do not invalidate the current transaction
208 REQUIRE_FAIL(con.Query("SELEC 42"));
209 REQUIRE_NO_FAIL(con.Query("SELECT 42"));
210 // neither do binder errors
211 REQUIRE_FAIL(con.Query("SELECT * FROM nonexistanttable"));
212 REQUIRE_NO_FAIL(con.Query("SELECT 42"));
213 // however primary key conflicts do invalidate it
214 REQUIRE_FAIL(con.Query("UPDATE integers SET i=2"));
215 REQUIRE_FAIL(con.Query("SELECT 42"));
216 // now we need to rollback
217 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
218
219 result = con.Query("SELECT * FROM integers ORDER BY 1");
220 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
221}
222