gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/threadtest5.c (about) 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 }