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

Reply via email to