SQLite WAL mode with multiple transactions in multiple threads

Question

Can sqlite have multiple transactions on a same database at the same time with WAL mode?

Here is a sample application that spawns 500 threads and each thread creates a new sqlite connection. Inserting the data happens within a transaction.

In this sample app:

---

#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;
}

I get 'database is locked' running this.

My question is with WAL mode can we have multiple transactions at the same time? If so, what am I missing in the sample app?

Answer 1

SQLite, in its current versions anyway, does not support concurrent writes. There can be multiple reader processes simultaneously, but at most one writer. (See FAQ entry #5

With Write-Ahead Logging enabled, that fact does not change. WAL enables more concurrency:

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

But not write concurrency:

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.

(Above excerpts from the the documentation on WAL.)