1 /*
2 ** 2014 December 9
3 **
4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
6 **
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
10 **
11 *************************************************************************
12 **
13 **
14 */
15
16
17 /*
18 ** Thread 1. CREATE and DROP a table.
19 */
stress_thread_1(int iTid,void * pArg)20 static char *stress_thread_1(int iTid, void *pArg){
21 Error err = {0}; /* Error code and message */
22 Sqlite db = {0}; /* SQLite database connection */
23
24 opendb(&err, &db, "test.db", 0);
25 while( !timetostop(&err) ){
26 sql_script(&err, &db, "CREATE TABLE IF NOT EXISTS t1(a PRIMARY KEY, b)");
27 clear_error(&err, SQLITE_LOCKED);
28 sql_script(&err, &db, "DROP TABLE IF EXISTS t1");
29 clear_error(&err, SQLITE_LOCKED);
30 }
31 closedb(&err, &db);
32 print_and_free_err(&err);
33 return sqlite3_mprintf("ok");
34 }
35
36 /*
37 ** Thread 2. Open and close database connections.
38 */
stress_thread_2(int iTid,void * pArg)39 static char *stress_thread_2(int iTid, void *pArg){
40 Error err = {0}; /* Error code and message */
41 Sqlite db = {0}; /* SQLite database connection */
42 while( !timetostop(&err) ){
43 opendb(&err, &db, "test.db", 0);
44 sql_script(&err, &db, "SELECT * FROM sqlite_schema;");
45 clear_error(&err, SQLITE_LOCKED);
46 closedb(&err, &db);
47 }
48 print_and_free_err(&err);
49 return sqlite3_mprintf("ok");
50 }
51
52 /*
53 ** Thread 3. Attempt many small SELECT statements.
54 */
stress_thread_3(int iTid,void * pArg)55 static char *stress_thread_3(int iTid, void *pArg){
56 Error err = {0}; /* Error code and message */
57 Sqlite db = {0}; /* SQLite database connection */
58
59 int i1 = 0;
60 int i2 = 0;
61
62 opendb(&err, &db, "test.db", 0);
63 while( !timetostop(&err) ){
64 sql_script(&err, &db, "SELECT * FROM t1 ORDER BY a;");
65 i1++;
66 if( err.rc ) i2++;
67 clear_error(&err, SQLITE_LOCKED);
68 clear_error(&err, SQLITE_ERROR);
69 }
70 closedb(&err, &db);
71 print_and_free_err(&err);
72 return sqlite3_mprintf("read t1 %d/%d attempts", i2, i1);
73 }
74
75 /*
76 ** Thread 5. Attempt INSERT statements.
77 */
stress_thread_4(int iTid,void * pArg)78 static char *stress_thread_4(int iTid, void *pArg){
79 Error err = {0}; /* Error code and message */
80 Sqlite db = {0}; /* SQLite database connection */
81 int i1 = 0;
82 int i2 = 0;
83 int iArg = PTR2INT(pArg);
84
85 opendb(&err, &db, "test.db", 0);
86 while( !timetostop(&err) ){
87 if( iArg ){
88 closedb(&err, &db);
89 opendb(&err, &db, "test.db", 0);
90 }
91 sql_script(&err, &db,
92 "WITH loop(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM loop LIMIT 200) "
93 "INSERT INTO t1 VALUES(randomblob(60), randomblob(60));"
94 );
95 i1++;
96 if( err.rc ) i2++;
97 clear_error(&err, SQLITE_LOCKED);
98 clear_error(&err, SQLITE_ERROR);
99 }
100 closedb(&err, &db);
101 print_and_free_err(&err);
102 return sqlite3_mprintf("wrote t1 %d/%d attempts", i2, i1);
103 }
104
105 /*
106 ** Thread 6. Attempt DELETE operations.
107 */
stress_thread_5(int iTid,void * pArg)108 static char *stress_thread_5(int iTid, void *pArg){
109 Error err = {0}; /* Error code and message */
110 Sqlite db = {0}; /* SQLite database connection */
111 int iArg = PTR2INT(pArg);
112
113 int i1 = 0;
114 int i2 = 0;
115
116 opendb(&err, &db, "test.db", 0);
117 while( !timetostop(&err) ){
118 i64 i = (i1 % 4);
119 if( iArg ){
120 closedb(&err, &db);
121 opendb(&err, &db, "test.db", 0);
122 }
123 execsql(&err, &db, "DELETE FROM t1 WHERE (rowid % 4)==:i", &i);
124 i1++;
125 if( err.rc ) i2++;
126 clear_error(&err, SQLITE_LOCKED);
127 }
128 closedb(&err, &db);
129 print_and_free_err(&err);
130 return sqlite3_mprintf("deleted from t1 %d/%d attempts", i2, i1);
131 }
132
133
stress1(int nMs)134 static void stress1(int nMs){
135 Error err = {0};
136 Threadset threads = {0};
137
138 setstoptime(&err, nMs);
139 sqlite3_enable_shared_cache(1);
140
141 launch_thread(&err, &threads, stress_thread_1, 0);
142 launch_thread(&err, &threads, stress_thread_1, 0);
143
144 launch_thread(&err, &threads, stress_thread_2, 0);
145 launch_thread(&err, &threads, stress_thread_2, 0);
146
147 launch_thread(&err, &threads, stress_thread_3, 0);
148 launch_thread(&err, &threads, stress_thread_3, 0);
149
150 launch_thread(&err, &threads, stress_thread_4, 0);
151 launch_thread(&err, &threads, stress_thread_4, 0);
152
153 launch_thread(&err, &threads, stress_thread_5, 0);
154 launch_thread(&err, &threads, stress_thread_5, (void*)1);
155
156 join_all_threads(&err, &threads);
157 sqlite3_enable_shared_cache(0);
158
159 print_and_free_err(&err);
160 }
161
162 /**************************************************************************
163 ***************************************************************************
164 ** Start of test case "stress2"
165 */
166
167
168
169 /*
170 ** 1. CREATE TABLE statements.
171 ** 2. DROP TABLE statements.
172 ** 3. Small SELECT statements.
173 ** 4. Big SELECT statements.
174 ** 5. Small INSERT statements.
175 ** 6. Big INSERT statements.
176 ** 7. Small UPDATE statements.
177 ** 8. Big UPDATE statements.
178 ** 9. Small DELETE statements.
179 ** 10. Big DELETE statements.
180 ** 11. VACUUM.
181 ** 14. Integrity-check.
182 ** 17. Switch the journal mode from delete to wal and back again.
183 ** 19. Open and close database connections rapidly.
184 */
185
186 #define STRESS2_TABCNT 5 /* count1 in SDS test */
187
188 #define STRESS2_COUNT2 200 /* count2 in SDS test */
189 #define STRESS2_COUNT3 57 /* count2 in SDS test */
190
stress2_workload1(Error * pErr,Sqlite * pDb,int i)191 static void stress2_workload1(Error *pErr, Sqlite *pDb, int i){
192 int iTab = (i % (STRESS2_TABCNT-1)) + 1;
193 sql_script_printf(pErr, pDb,
194 "CREATE TABLE IF NOT EXISTS t%d(x PRIMARY KEY, y, z);", iTab
195 );
196 }
197
stress2_workload2(Error * pErr,Sqlite * pDb,int i)198 static void stress2_workload2(Error *pErr, Sqlite *pDb, int i){
199 int iTab = (i % (STRESS2_TABCNT-1)) + 1;
200 sql_script_printf(pErr, pDb, "DROP TABLE IF EXISTS t%d;", iTab);
201 }
202
stress2_workload3(Error * pErr,Sqlite * pDb,int i)203 static void stress2_workload3(Error *pErr, Sqlite *pDb, int i){
204 sql_script(pErr, pDb, "SELECT * FROM t0 WHERE z = 'small'");
205 }
206
stress2_workload4(Error * pErr,Sqlite * pDb,int i)207 static void stress2_workload4(Error *pErr, Sqlite *pDb, int i){
208 sql_script(pErr, pDb, "SELECT * FROM t0 WHERE z = 'big'");
209 }
210
stress2_workload5(Error * pErr,Sqlite * pDb,int i)211 static void stress2_workload5(Error *pErr, Sqlite *pDb, int i){
212 sql_script(pErr, pDb,
213 "INSERT INTO t0 VALUES(hex(random()), hex(randomblob(200)), 'small');"
214 );
215 }
216
stress2_workload6(Error * pErr,Sqlite * pDb,int i)217 static void stress2_workload6(Error *pErr, Sqlite *pDb, int i){
218 sql_script(pErr, pDb,
219 "INSERT INTO t0 VALUES(hex(random()), hex(randomblob(57)), 'big');"
220 );
221 }
222
stress2_workload7(Error * pErr,Sqlite * pDb,int i)223 static void stress2_workload7(Error *pErr, Sqlite *pDb, int i){
224 sql_script_printf(pErr, pDb,
225 "UPDATE t0 SET y = hex(randomblob(200)) "
226 "WHERE x LIKE hex((%d %% 5)) AND z='small';"
227 ,i
228 );
229 }
stress2_workload8(Error * pErr,Sqlite * pDb,int i)230 static void stress2_workload8(Error *pErr, Sqlite *pDb, int i){
231 sql_script_printf(pErr, pDb,
232 "UPDATE t0 SET y = hex(randomblob(57)) "
233 "WHERE x LIKE hex(%d %% 5) AND z='big';"
234 ,i
235 );
236 }
237
stress2_workload9(Error * pErr,Sqlite * pDb,int i)238 static void stress2_workload9(Error *pErr, Sqlite *pDb, int i){
239 sql_script_printf(pErr, pDb,
240 "DELETE FROM t0 WHERE x LIKE hex(%d %% 5) AND z='small';", i
241 );
242 }
stress2_workload10(Error * pErr,Sqlite * pDb,int i)243 static void stress2_workload10(Error *pErr, Sqlite *pDb, int i){
244 sql_script_printf(pErr, pDb,
245 "DELETE FROM t0 WHERE x LIKE hex(%d %% 5) AND z='big';", i
246 );
247 }
248
stress2_workload11(Error * pErr,Sqlite * pDb,int i)249 static void stress2_workload11(Error *pErr, Sqlite *pDb, int i){
250 sql_script(pErr, pDb, "VACUUM");
251 }
252
stress2_workload14(Error * pErr,Sqlite * pDb,int i)253 static void stress2_workload14(Error *pErr, Sqlite *pDb, int i){
254 sql_script(pErr, pDb, "PRAGMA integrity_check");
255 }
256
stress2_workload17(Error * pErr,Sqlite * pDb,int i)257 static void stress2_workload17(Error *pErr, Sqlite *pDb, int i){
258 sql_script_printf(pErr, pDb,
259 "PRAGMA journal_mode = %q", (i%2) ? "delete" : "wal"
260 );
261 }
262
stress2_workload19(int iTid,void * pArg)263 static char *stress2_workload19(int iTid, void *pArg){
264 Error err = {0}; /* Error code and message */
265 Sqlite db = {0}; /* SQLite database connection */
266 const char *zDb = (const char*)pArg;
267 while( !timetostop(&err) ){
268 opendb(&err, &db, zDb, 0);
269 sql_script(&err, &db, "SELECT * FROM sqlite_schema;");
270 clear_error(&err, SQLITE_LOCKED);
271 closedb(&err, &db);
272 }
273 print_and_free_err(&err);
274 return sqlite3_mprintf("ok");
275 }
276
277
278 typedef struct Stress2Ctx Stress2Ctx;
279 struct Stress2Ctx {
280 const char *zDb;
281 void (*xProc)(Error*, Sqlite*, int);
282 };
283
stress2_thread_wrapper(int iTid,void * pArg)284 static char *stress2_thread_wrapper(int iTid, void *pArg){
285 Stress2Ctx *pCtx = (Stress2Ctx*)pArg;
286 Error err = {0}; /* Error code and message */
287 Sqlite db = {0}; /* SQLite database connection */
288 int i1 = 0;
289 int i2 = 0;
290
291 while( !timetostop(&err) ){
292 int cnt;
293 opendb(&err, &db, pCtx->zDb, 0);
294 for(cnt=0; err.rc==SQLITE_OK && cnt<STRESS2_TABCNT; cnt++){
295 pCtx->xProc(&err, &db, i1);
296 i2 += (err.rc==SQLITE_OK);
297 clear_error(&err, SQLITE_LOCKED);
298 i1++;
299 }
300 closedb(&err, &db);
301 }
302
303 print_and_free_err(&err);
304 return sqlite3_mprintf("ok %d/%d", i2, i1);
305 }
306
stress2_launch_thread_loop(Error * pErr,Threadset * pThreads,const char * zDb,void (* x)(Error *,Sqlite *,int))307 static void stress2_launch_thread_loop(
308 Error *pErr, /* IN/OUT: Error code */
309 Threadset *pThreads, /* Thread set */
310 const char *zDb, /* Database name */
311 void (*x)(Error*,Sqlite*,int) /* Run this until error or timeout */
312 ){
313 Stress2Ctx *pCtx = sqlite3_malloc(sizeof(Stress2Ctx));
314 pCtx->zDb = zDb;
315 pCtx->xProc = x;
316 launch_thread(pErr, pThreads, stress2_thread_wrapper, (void*)pCtx);
317 }
318
stress2(int nMs)319 static void stress2(int nMs){
320 struct Stress2Task {
321 void (*x)(Error*,Sqlite*,int);
322 } aTask[] = {
323 { stress2_workload1 },
324 { stress2_workload2 },
325 { stress2_workload3 },
326 { stress2_workload4 },
327 { stress2_workload5 },
328 { stress2_workload6 },
329 { stress2_workload7 },
330 { stress2_workload8 },
331 { stress2_workload9 },
332 { stress2_workload10 },
333 { stress2_workload11 },
334 { stress2_workload14 },
335 { stress2_workload17 },
336 };
337 const char *zDb = "test.db";
338
339 int i;
340 Error err = {0};
341 Sqlite db = {0};
342 Threadset threads = {0};
343
344 /* To make sure the db file is empty before commencing */
345 opendb(&err, &db, zDb, 1);
346 sql_script(&err, &db,
347 "CREATE TABLE IF NOT EXISTS t0(x PRIMARY KEY, y, z);"
348 "CREATE INDEX IF NOT EXISTS i0 ON t0(y);"
349 );
350 closedb(&err, &db);
351
352 setstoptime(&err, nMs);
353 sqlite3_enable_shared_cache(1);
354
355 for(i=0; i<sizeof(aTask)/sizeof(aTask[0]); i++){
356 stress2_launch_thread_loop(&err, &threads, zDb, aTask[i].x);
357 }
358 launch_thread(&err, &threads, stress2_workload19, (void*)zDb);
359 launch_thread(&err, &threads, stress2_workload19, (void*)zDb);
360
361 join_all_threads(&err, &threads);
362 sqlite3_enable_shared_cache(0);
363 print_and_free_err(&err);
364 }
365