Your "reiterating 20 times" is not using a usleep so you'll blow by this most every time it's busy. Do this instead in all your proc's ret = sqlite3_step (p_stmt); if (SQLITE_BUSY == ret) { int n=0; usleep(100000); // try one more time before error while ((ret=sqlite3_step(p_stmt))==SQLITE_BUSY) { printf("proc1 ret==BUSY %d\n",++n); usleep(100000); } }
And you'll also need to handle "database is locked" coming from your prepare statements. I saw that error too. You'll need to loop there too. The more you drop the usleep time the more times it will show as busy. 1/10th or 1/100th of second is about all you want I would think. And get rid of the usleep at the bottom of each proc -- it's pretty useless at 100 microseconds. You don't need to sleep unless you're busy. I tested your code with this and got no errors at all -- just a bunch of BUSY messages. Not sure what your purpose is in sqlrun.c with looping and killing. Looks pretty squirrely to me. You're not waiting for the forks to finish so what is your logic here? Michael D. Black Senior Scientist Northrop Grumman Mission Systems ________________________________ From: sqlite-users-boun...@sqlite.org on behalf of liubin liu Sent: Tue 5/11/2010 4:57 AM To: sqlite-users@sqlite.org Subject: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE 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
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users