AryaWu/sqlite
0
1/*2** 2021-05-123**4** The author disclaims copyright to this source code. In place of5** 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 separate14** 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=117**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 */35static char *zDbName = 0;36 37/* True for debugging */38static int eVerbose = 0;39 40/* If rc is not SQLITE_OK, then print an error message and stop41** the test.42*/43static 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 051/* Return the number of milliseconds since the Julian epoch (-4714-11-24).52*/53static 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#endif60 61/* Run the SQL in the second argument.62*/63static 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 SQL90*/91static 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*/122static 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 number138*/139static 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 */149static 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", zName170 );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 */229static 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 100240 241/*242** Main routine243*/244int 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