多线程下的Sqlite(二)

默认为序列化的多线程模型, 本文将对多线程模式下的一些情况做一些测试

函数介绍

SQLITE_API int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

参数1为文件名, 参数2传入指向一个数据库句柄指针的指针; 打开数据库, 如果不存在会创建

typedef int (*sqlite3_callback)(void*,int,char**, char**);
SQLITE_API int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

执行一条SQL语句, 由sql该参数传入
参数1: 数据库句柄
参数2: sql语句
参数3: 回调函数(暂不做详细介绍)
参数4: 给回调函数传入的参数

两个线程使用同一个句柄进行操作

#include <stdio.h>
#include "sqlite3/sqlite3.h"
#include <thread>

void func1(sqlite3 *db)
{
    int i = 1;

    const char *insert_sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
                                "VALUES (1, 'zhangsan', 32, 'China', 100) "; 
    const char *update_sql = "UPDATE COMPANY set SALARY = 200 where ID = 2";

    char *errmsg = NULL;
    while(i < 100)
    {
        errmsg = NULL;
        sqlite3_exec(db, insert_sql, NULL, 0, &errmsg);
        if(errmsg)
        {
            printf("func1 1 %s\n", errmsg);
        }
        errmsg = NULL;
        sqlite3_exec(db, update_sql, NULL, 0, &errmsg);
        if(errmsg)
        {
            printf("func1 2 %s\n", errmsg);
        }
        i++;
    }   
}

void func2(sqlite3 *db)
{
    int i = 1;

    const char *insert_sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
                                "VALUES (2, 'zhangsan', 32, 'China', 100) "; 
    const char *update_sql = "UPDATE COMPANY set SALARY = 200 where ID = 1";

    char *errmsg = NULL;
    while(i < 100)
    {
        errmsg = NULL;
        sqlite3_exec(db, insert_sql, NULL, 0, &errmsg);
        if(errmsg)
        {
            printf("func2 1 %s\n", errmsg);
        }
        errmsg = NULL;
        sqlite3_exec(db, update_sql, NULL, 0, &errmsg);
        if(errmsg)
        {
            printf("func2 2 %s\n", errmsg);
        }
        i++;
    }   
}

int main()
{
    int mode = sqlite3_threadsafe();
    sqlite3_config(SQLITE_CONFIG_MULTITHREAD);

    sqlite3 *db; 
    sqlite3_open("E:/debian32.winsf/safe_sqlite/cmake-build-debug/test", &db);

    const char *create_sql = "CREATE TABLE COMPANY("
                                "ID             INT     NOT NULL,"
                                "NAME           TEXT,"
                                "AGE            INT,"
                                "ADDRESS        CHAR(50),"
                                "SALARY         INT);";
    sqlite3_exec(db, create_sql, NULL, 0, NULL);

    std::thread t1(func1, db);
    std::thread t2(func2, db);


    t1.join();
    t2.join();

    sqlite3_close(db);
    system("pause");
    return 0;
}

测试结果
无论Window或者Linux, 直接报出了磁盘错误disk I/O error
我们明确了一点在多线程模式下不应当在不同的线程中使用相同的句柄进行异步操作
当然如果加锁保证顺序依次执行SQL语句, 则不存在问题