c - 在多个线程中具有多个事务的 SQLite WAL 模式

Question

sqlite可以在WAL模式下同时在同一个数据库上有多个事务吗？

这是一个生成 500 个线程的示例应用程序，每个线程创建一个新的 sqlite 连接。插入数据发生在事务中。

在此示例应用中:

---

#include "sqlite3.h"
#include "nspr\prthread.h"

void CreateThreads();
static void StartThread(void *Arg);

int main()
{
    CreateThreads();
}

void CreateThreads()
{
    for(int i = 0; i<500;i++)
    {
        PR_CreateThread(PR_USER_THREAD, StartThread, NULL, PR_PRIORITY_NORMAL, PR_GLOBAL_THREAD, PR_UNJOINABLE_THREAD, 0);
    }
    Sleep(10000);
}

void StartThread(void *Arg)
{
    sqlite3 *sqlite;
    char *errmsg;
    const char *err;
    const char *unused;
    int ret;
    const unsigned char * journal;
    const char *dir = "D:\\Samples\\test.sqlite";
    ret = sqlite3_open(dir, &sqlite);
    if(ret != SQLITE_OK)
    {
       err = sqlite3_errmsg(sqlite);
         return;
    }

    char query[100];
    strcpy(query, "Begin transaction");
    if(sqlite3_exec(sqlite, query, NULL,0, &errmsg) != SQLITE_OK )
    {
        printf("%s", errmsg);
        return;
    }

    strcpy(query, "insert into test values(1,2,3,4,5,6)");
    for(int i = 0; i<10;i++)
    {
        if(sqlite3_exec(sqlite, query, NULL,0, &errmsg) != SQLITE_OK )
        {
         printf("%s", errmsg);
            return;
        }
    }

    strcpy(query, "End Transaction");
    if(sqlite3_exec(sqlite, query, NULL,0, &errmsg) != SQLITE_OK )
    {
        printf("%s", errmsg);
        return;
    }
    return;
}

我得到'数据库被锁定'运行这个。

我的问题是使用 WAL 模式我们可以同时进行多个交易吗？如果是这样，我在示例应用程序中缺少什么？

Answer 1

无论如何，SQLite 在其当前版本中不支持并发写入。可以同时有多个读取进程，但最多只有一个写入进程。 (参见 FAQ entry #5

启用预写日志记录后，这一事实不会改变。 WAL 支持更多并发:

WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

但不写并发:

Writers merely append new content to the end of the WAL file. Because writers do nothing that would interfere with the actions of readers, writers and readers can run at the same time. However, since there is only one WAL file, there can only be one writer at a time.

(以上摘自 the documentation on WAL .)