1 /*
2 ** 2021-05-12
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 ** Testing threading behavior when multiple database connections in separate
14 ** threads of the same process are all talking to the same database file.
15 **
16 ** For best results, ensure that SQLite is compiled with HAVE_USLEEP=1
17 **
18 ** Only works on unix platforms.
19 **
20 ** Usage:
21 **
22 ** ./threadtest5 ?DATABASE?
23 **
24 ** If DATABASE is omitted, it defaults to using file:/mem?vfs=memdb.
25 */
26 #include "sqlite3.h"
27 #include <pthread.h>
28 #include <stdio.h>
29 #include <unistd.h>
30 #include <stdlib.h>
31 #include <string.h>
32 #include <stdarg.h>
33
34 /* Name of the in-memory database */
35 static char *zDbName = 0;
36
37 /* True for debugging */
38 static int eVerbose = 0;
39
40 /* If rc is not SQLITE_OK, then print an error message and stop
41 ** the test.
42 */
error_out(int rc,const char * zCtx,int lineno)43 static void error_out(int rc, const char *zCtx, int lineno){
44 if( rc!=SQLITE_OK ){
45 fprintf(stderr, "error %d at %d in \"%s\"\n", rc, lineno, zCtx);
46 exit(-1);
47 }
48 }
49
50 #if 0
51 /* Return the number of milliseconds since the Julian epoch (-4714-11-24).
52 */
53 static sqlite3_int64 gettime(void){
54 sqlite3_int64 tm;
55 sqlite3_vfs *pVfs = sqlite3_vfs_find(0);
56 pVfs->xCurrentTimeInt64(pVfs, &tm);
57 return tm;
58 }
59 #endif
60
61 /* Run the SQL in the second argument.
62 */
exec(sqlite3 * db,const char * zId,int lineno,const char * zFormat,...)63 static int exec(
64 sqlite3 *db,
65 const char *zId,
66 int lineno,
67 const char *zFormat,
68 ...
69 ){
70 int rc;
71 va_list ap;
72 char *zSql;
73 va_start(ap, zFormat);
74 zSql = sqlite3_vmprintf(zFormat, ap);
75 va_end(ap);
76 if( eVerbose){
77 printf("%s:%d: [%s]\n", zId, lineno, zSql);
78 fflush(stdout);
79 }
80 rc = sqlite3_exec(db, zSql, 0, 0, 0);
81 if( rc && eVerbose ){
82 printf("%s:%d: return-code %d\n", zId, lineno, rc);
83 fflush(stdout);
84 }
85 sqlite3_free(zSql);
86 return rc;
87 }
88
89 /* Generate a perpared statement from the input SQL
90 */
prepare(sqlite3 * db,const char * zId,int lineno,const char * zFormat,...)91 static sqlite3_stmt *prepare(
92 sqlite3 *db,
93 const char *zId,
94 int lineno,
95 const char *zFormat,
96 ...
97 ){
98 int rc;
99 va_list ap;
100 char *zSql;
101 sqlite3_stmt *pStmt = 0;
102 va_start(ap, zFormat);
103 zSql = sqlite3_vmprintf(zFormat, ap);
104 va_end(ap);
105 if( eVerbose){
106 printf("%s:%d: [%s]\n", zId, lineno, zSql);
107 fflush(stdout);
108 }
109
110 rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
111 if( rc ){
112 printf("%s:%d: ERROR - %s\n", zId, lineno, sqlite3_errmsg(db));
113 exit(-1);
114 }
115 sqlite3_free(zSql);
116 return pStmt;
117 }
118
119 /*
120 ** Wait for table zTable to exist in the schema.
121 */
waitOnTable(sqlite3 * db,const char * zWorker,const char * zTable)122 static void waitOnTable(sqlite3 *db, const char *zWorker, const char *zTable){
123 while(1){
124 int eFound = 0;
125 sqlite3_stmt *q = prepare(db, zWorker, __LINE__,
126 "SELECT 1 FROM sqlite_schema WHERE name=%Q", zTable);
127 if( sqlite3_step(q)==SQLITE_ROW && sqlite3_column_int(q,0)!=0 ){
128 eFound = 1;
129 }
130 sqlite3_finalize(q);
131 if( eFound ) return;
132 sqlite3_sleep(1);
133 }
134 }
135
136 /*
137 ** Return true if x is a prime number
138 */
isPrime(int x)139 static int isPrime(int x){
140 int i;
141 if( x<2 ) return 1;
142 for(i=2; i*i<=x; i++){
143 if( (x%i)==0 ) return 0;
144 }
145 return 1;
146 }
147
148 /* Each worker thread runs an instance of the following */
worker(void * pArg)149 static void *worker(void *pArg){
150 int rc;
151 const char *zName = (const char*)pArg;
152 sqlite3 *db = 0;
153
154 if( eVerbose ){
155 printf("%s: startup\n", zName);
156 fflush(stdout);
157 }
158
159 rc = sqlite3_open(zDbName, &db);
160 error_out(rc, "sqlite3_open", __LINE__);
161 sqlite3_busy_timeout(db, 2000);
162
163 while( 1 ){
164 sqlite3_stmt *q1;
165 int tid = -1;
166 q1 = prepare(db, zName, __LINE__,
167 "UPDATE task SET doneby=%Q"
168 " WHERE tid=(SELECT tid FROM task WHERE doneby IS NULL LIMIT 1)"
169 "RETURNING tid", zName
170 );
171 if( sqlite3_step(q1)==SQLITE_ROW ){
172 tid = sqlite3_column_int(q1,0);
173 }
174 sqlite3_finalize(q1);
175 if( tid<0 ) break;
176 if( eVerbose ){
177 printf("%s: starting task %d\n", zName, tid);
178 fflush(stdout);
179 }
180 if( tid==1 ){
181 exec(db, zName, __LINE__,
182 "CREATE TABLE IF NOT EXISTS p1(x INTEGER PRIMARY KEY);"
183 );
184 }else if( tid>=2 && tid<=51 ){
185 int a, b, i;
186 waitOnTable(db, zName, "p1");
187 a = (tid-2)*200 + 1;
188 b = a+200;
189 for(i=a; i<b; i++){
190 if( isPrime(i) ){
191 exec(db, zName, __LINE__,
192 "INSERT INTO p1(x) VALUES(%d)", i);
193 }
194 }
195 }else if( tid==52 ){
196 exec(db, zName, __LINE__,
197 "CREATE TABLE IF NOT EXISTS p2(x INTEGER PRIMARY KEY);"
198 "WITH RECURSIVE"
199 " c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10000)"
200 "INSERT INTO p2(x) SELECT x FROM c;"
201 );
202 }else if( tid>=53 && tid<=62 ){
203 int a, b, i;
204 waitOnTable(db, zName, "p2");
205 a = (tid-53)*10 + 2;
206 b = a+9;
207 for(i=a; i<=b; i++){
208 exec(db, zName, __LINE__,
209 "DELETE FROM p2 WHERE x>%d AND (x %% %d)==0", i, i);
210 }
211 }
212 if( eVerbose ){
213 printf("%s: completed task %d\n", zName, tid);
214 fflush(stdout);
215 }
216 sqlite3_sleep(1);
217 }
218
219 sqlite3_close(db);
220
221 if( eVerbose ){
222 printf("%s: exit\n", zName);
223 fflush(stdout);
224 }
225 return 0;
226 }
227
228 /* Print a usage comment and die */
usage(const char * argv0)229 static void usage(const char *argv0){
230 printf("Usage: %s [options]\n", argv0);
231 printf(
232 " -num-workers N Run N worker threads\n"
233 " -v Debugging output\n"
234 );
235 exit(1);
236 }
237
238 /* Maximum number of threads */
239 #define MX_WORKER 100
240
241 /*
242 ** Main routine
243 */
main(int argc,char ** argv)244 int main(int argc, char **argv){
245 int i;
246 int nWorker = 4;
247 int rc;
248 sqlite3 *db = 0;
249 sqlite3_stmt *q;
250 pthread_t aWorker[MX_WORKER];
251 char aWorkerName[MX_WORKER][8];
252
253 for(i=1; i<argc; i++){
254 const char *zArg = argv[i];
255 if( zArg[0]!='-' ){
256 if( zDbName==0 ){
257 zDbName = argv[i];
258 continue;
259 }
260 printf("unknown argument: %s\n", zArg);
261 usage(argv[0]);
262 }
263 if( zArg[1]=='-' ) zArg++;
264 if( strcmp(zArg, "-v")==0 ){
265 eVerbose = 1;
266 continue;
267 }
268 if( strcmp(zArg, "-num-workers")==0 && i+1<argc ){
269 nWorker = atoi(argv[++i]);
270 if( nWorker<1 || nWorker>MX_WORKER ){
271 printf("number of threads must be between 1 and %d\n", MX_WORKER);
272 exit(1);
273 }
274 continue;
275 }
276 printf("unknown option: %s\n", argv[i]);
277 usage(argv[0]);
278 }
279 if( zDbName==0 ) zDbName = "file:/mem?vfs=memdb";
280
281 sqlite3_config(SQLITE_CONFIG_URI, (int)1);
282 rc = sqlite3_open(zDbName, &db);
283 error_out(rc, "sqlite3_open", __LINE__);
284
285 rc = exec(db, "SETUP", __LINE__,
286 "DROP TABLE IF EXISTS task;\n"
287 "DROP TABLE IF EXISTS p1;\n"
288 "DROP TABLE IF EXISTS p2;\n"
289 "DROP TABLE IF EXISTS verify;\n"
290 "CREATE TABLE IF NOT EXISTS task(\n"
291 " tid INTEGER PRIMARY KEY,\n"
292 " doneby TEXT\n"
293 ");\n"
294 "WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)"
295 "INSERT INTO task(tid) SELECT x FROM c;\n"
296 );
297 error_out(rc, "sqlite3_exec", __LINE__);
298
299 for(i=0; i<nWorker; i++){
300 sqlite3_snprintf(sizeof(aWorkerName[i]), aWorkerName[i],
301 "W%02d", i);
302 pthread_create(&aWorker[i], 0, worker, aWorkerName[i]);
303 }
304 for(i=0; i<nWorker; i++){
305 pthread_join(aWorker[i], 0);
306 }
307
308 for(i=0; i<nWorker; i++){
309 q = prepare(db, "MAIN", __LINE__,
310 "SELECT group_concat(tid,',') FROM task WHERE doneby=%Q",
311 aWorkerName[i]);
312 if( sqlite3_step(q)==SQLITE_ROW ){
313 printf("%s: %s\n", aWorkerName[i], sqlite3_column_text(q,0));
314 }
315 sqlite3_finalize(q);
316 }
317 q = prepare(db, "MAIN", __LINE__, "SELECT count(*) FROM p2");
318 if( sqlite3_step(q)!=SQLITE_ROW || sqlite3_column_int(q,0)<10 ){
319 printf("incorrect result\n");
320 exit(-1);
321 }
322 sqlite3_finalize(q);
323 q = prepare(db, "MAIN", __LINE__, "SELECT x FROM p1 EXCEPT SELECT x FROM p2");
324 if( sqlite3_step(q)==SQLITE_ROW ){
325 printf("incorrect result\n");
326 exit(-1);
327 }
328 sqlite3_finalize(q);
329 q = prepare(db, "MAIN", __LINE__, "SELECT x FROM p2 EXCEPT SELECT x FROM p1");
330 if( sqlite3_step(q)==SQLITE_ROW ){
331 printf("incorrect result\n");
332 exit(-1);
333 }
334 sqlite3_finalize(q);
335 printf("OK\n");
336
337 sqlite3_close(db);
338 return 0;
339 }
340