Multi processes, getting so many errores of SQLITE_BUSY and SQLITE_MISUSE...
And the system performance is very bad because of the three processes of insert/read/update database. How to improve the sqlite3's operations? _____my codes_______________________________________________________ ___proc_main.c_____________________ // create three processes of insert/read/update database. // proc_1 : insert; // proc_2 : select; // proc_3 : update; #include <stdio.h> #include <sys/types.h> // for fork(); #include <unistd.h> // for fork(); execv(); usleep(); #include <signal.h> // for kill(); #include <sqlite3.h> static int createdb (void); int main (void) { int ret = -1; ret = createdb (); usleep (50000); int i=0; while (1) { pid_t p1, p2, p3; p1 = fork(); if (0 == p1) { char *argv1[] = {"proc_1"}; ret = execv ("./proc_1", argv1); } p2 = fork(); if (0 == p2) { char *argv2[] = {"proc_2"}; ret = execv ("./proc_2", argv2); } p3 = fork(); if (0 == p3) { char *argv3[] = {"proc_3"}; ret = execv ("./proc_3", argv3); } usleep (1000000 * 100); while (1) { ret = kill (p1, SIGKILL); ret = kill (p2, SIGKILL); ret = kill (p3, SIGKILL); usleep (10 * 1000000); } } return 0; } static int createdb (void) { int ret = -1; sqlite3 *db = NULL; ret = sqlite3_open ("test.db", &db); ret = sqlite3_exec (db, "CREATE TABLE t1 (mp_no INTEGER, di INTEGER, data INT64, rec_time INTEGER, data_type CHAR(1) )", NULL,NULL,NULL); ret = sqlite3_exec (db, "CREATE UNIQUE INDEX i_t1 ON t1 (mp_no, di)", NULL,NULL,NULL); ret = sqlite3_close (db); return ret; } ___proc_1.c_____________________ #include <stdio.h> #include <time.h> // for time(); #include <stdlib.h> // for srand(); rand(); #include <unistd.h> // for usleep(); #include <sqlite3.h> #define DELAY_TIME 20000 // 20ms #define REDO_TIMES 60 int main (void) { int ret = -1; struct tm *tm = NULL; time_t t; char datee[30]; FILE *fp1; fp1 = fopen ("proc_1.log", "a+"); srand ((int) time(0)); sqlite3 *db = NULL; ret = sqlite3_open ("test.db", &db); char *sql_f = "INSERT OR REPLACE INTO t1 VALUES (%d, %d, %llu, %d, %d)"; char *sql = NULL; sqlite3_stmt *p_stmt = NULL; int i=0, n=5000; for (i=0; i<n; i++) { sql = sqlite3_mprintf (sql_f, i/255, i%255, 11223344556677889900ull, i, i%255); ret = sqlite3_prepare_v2 (db, sql, -1, &p_stmt, NULL); sqlite3_free (sql); if (SQLITE_OK != ret) { logging... } ret = sqlite3_step (p_stmt); if (SQLITE_BUSY == ret) { reiterating 20 times... } if (SQLITE_DONE != ret) { logging... } ret = sqlite3_finalize (p_stmt); if (SQLITE_OK != ret) { logging... } usleep (10 * (1 + (int) (10.0 * rand() / (RAND_MAX + 1.0))) ); } ret = fclose (fp1); ret = sqlite3_close (db); return 0; } ___proc_2.c_____________________ #include <stdio.h> #include <time.h> // for time(); #include <stdlib.h> // for srand(); rand(); #include <unistd.h> // for usleep(); #include <sqlite3.h> #define DELAY_TIME 20000 // 20ms #define REDO_TIMES 60 int main (void) { int ret = -1; struct tm *tm = NULL; time_t t; char datee[30]; FILE *fp2; fp2 = fopen ("proc_2.log", "a+"); srand ((int) time(0)); usleep (1000000); sqlite3 *db = NULL; ret = sqlite3_open ("test.db", &db); char *sql_f = "SELECT * FROM t1 WHERE mp_no=%d"; char *sql = NULL; sqlite3_stmt *p_stmt = NULL; int i=0, n=5000; for (i=0; i<n; i++) { sql = sqlite3_mprintf (sql_f, i/255); ret = sqlite3_prepare_v2 (db, sql, -1, &p_stmt, NULL); sqlite3_free (sql); if (SQLITE_OK != ret) { logging... } ret = sqlite3_step (p_stmt); if (SQLITE_BUSY == ret) { reiterating 20 times... } if ((SQLITE_ROW != ret) && (SQLITE_DONE != ret)) { logging... } ret = sqlite3_finalize (p_stmt); if (SQLITE_OK != ret) { logging... } usleep (10 * (1 + (int) (10.0 * rand() / (RAND_MAX + 1.0))) ); } ret = fclose (fp2); ret = sqlite3_close (db); return 0; } ___proc_3.c_____________________ #include <stdio.h> #include <time.h> // for time(); #include <stdlib.h> // for srand(); rand(); #include <unistd.h> // for usleep(); #include <sqlite3.h> #define DELAY_TIME 20000 // 20ms #define REDO_TIMES 60 int main (void) { usleep (1000000*5); struct tm *tm = NULL; time_t t; char datee[30]; int ret = -1; FILE *fp; fp = fopen ("proc_3.log", "a+"); srand ((int) time(0)); sqlite3 *db = NULL; ret = sqlite3_open ("test.db", &db); char *sql_f = "UPDATE t1 SET data=%llu, rec_time=%d, data_type=%d WHERE mp_no=%d AND di=%d"; char *sql = NULL; sqlite3_stmt *p_stmt = NULL; int i=0, n=5000; for (i=0; i<n; i++) { sql = sqlite3_mprintf (sql_f, 17887766554433221100ull, i+1010, i%256, i/256, i%256); ret = sqlite3_prepare_v2 (db, sql, -1, &p_stmt, NULL); sqlite3_free (sql); if (SQLITE_OK != ret) { logging... } ret = sqlite3_step (p_stmt); if (SQLITE_BUSY == ret) { reiterating 20 times... } if (SQLITE_DONE != ret) { logging... } ret = sqlite3_finalize (p_stmt); if (SQLITE_OK != ret) { logging } usleep (10 * (1 + (int) (10.0 * rand() / (RAND_MAX + 1.0))) ); } ret = fclose (fp); ret = sqlite3_close (db); return 0; } -- View this message in context: http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28522127.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users