test/threadtest5.c

   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 */
  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 */
  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 */
  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 */
 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 */
 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 */
 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 */
 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 */
 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 }