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: [email protected] on behalf of liubin liu
Sent: Tue 5/11/2010 4:57 AM
To: [email protected]
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
