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
