SQLite Forum

Standalone PRAGMA vs SELECT pragma_...() across threads
Login

Standalone PRAGMA vs SELECT pragma_...() across threads

(1) By anonymous on 2020-07-03 14:25:30 [link] [source]

When opening the same database multiple times in different threads, writing changes to the schema from one thread does not immediately have an effect on the other thread in all cases.

Steps to reproduce:

  1. Open DB in thread 1
  2. Open DB in thread 2
  3. Create a table in thread 2
  4. From thread 2, signal thread 1
  5. In thread 1, wait for the signal and read from the schema.

In WAL mode, this seems to work when you use the "SELECT * FROM pragma_table_info(...)" syntax, but not if you use "PRAGMA table_info(...)".

A minimal example (C++):

#include <iostream>
#include <thread>
#include <condition_variable>

int main()
{
        sqlite3* db;
        assert(sqlite3_open("test.sqlite3", &db) == SQLITE_OK);
        assert(sqlite3_exec(db, "PRAGMA journal_mode = WAL;", 0, 0, 0) == SQLITE_OK);
        assert(sqlite3_exec(db, "PRAGMA synchronous = NORMAL", 0, 0, 0) == SQLITE_OK);

        std::mutex m;
        std::condition_variable cv;

        auto thread = std::thread([&cv, &m]() {
                sqlite3* db2;
                assert(sqlite3_open("test.sqlite3", &db2) == SQLITE_OK);
                assert(sqlite3_exec(db2, "CREATE TABLE sometable(firstcolumn INTEGER)", 0, 0, 0) == SQLITE_OK);

                std::unique_lock<std::mutex> lk(m);
                cv.notify_one();
        });

        {
                std::unique_lock<std::mutex> lk(m);
                cv.wait(lk);
        }

        sqlite3_stmt* stmt;

        // This works:
        //assert(sqlite3_prepare_v2(db, "SELECT * FROM pragma_table_info('sometable');", -1, &stmt, static_cast<const char**>(nullptr)) == SQLITE_OK);
        // This doesn't work:
        assert(sqlite3_prepare_v2(db, "PRAGMA table_info('sometable');", -1, &stmt, static_cast<const char**>(nullptr)) == SQLITE_OK);

        assert(sqlite3_step(stmt) == SQLITE_ROW);
}

(2) By anonymous on 2020-07-04 07:18:27 in reply to 1 [link] [source]

It seems like threads are not even necessary, just two connections to the same database in WAL mode (journal mode works as expected).

The problem was also reported on the mailing list in 2017.

New minimal example (C++):

#include <iostream>

int main()
{       
        sqlite3* db;
        assert(sqlite3_open("test.sqlite3", &db) == SQLITE_OK);
        assert(sqlite3_exec(db, "PRAGMA journal_mode = WAL;", 0, 0, 0) == SQLITE_OK);
        assert(sqlite3_exec(db, "PRAGMA synchronous = NORMAL", 0, 0, 0) == SQLITE_OK);
        
        sqlite3* db2;
        assert(sqlite3_open("test.sqlite3", &db2) == SQLITE_OK);
        assert(sqlite3_exec(db2, "CREATE TABLE sometable(firstcolumn INTEGER)", 0, 0, 0) == SQLITE_OK);

        sqlite3_stmt* stmt;

        // This works:
        //assert(sqlite3_prepare_v2(db, "SELECT * FROM pragma_table_info('sometable');", -1, &stmt, static_cast<const char**>(nullptr)) == SQLITE_OK);
        // This doesn't work:
        assert(sqlite3_prepare_v2(db, "PRAGMA table_info('sometable');", -1, &stmt, static_cast<const char**>(nullptr)) == SQLITE_OK);

        assert(sqlite3_step(stmt) == SQLITE_ROW);
}

(3) By Richard Hipp (drh) on 2020-07-04 16:13:52 in reply to 2 [link] [source]

First: Never put code with side effects inside of an assert(). Never. Even for a demo or proof-of-concept. That is such an egregious violation of original intent of assert() that I can't get past it. If you do not understand why they way you are using assert() in your example is a problem, then I implore you to never use assert() for anything. Please.

Regarding the POC:

I translated your C++ code into C code as shown below. I get consistent output like this:

   first query rc = 0
   0,firstcolumn,INTEGER,0,(null),0
   second query rc = 0
   0,firstcolumn,INTEGER,0,(null),0

Please explain why you think this output is incorrect. What output where you expecting?


The C code:

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

int main(int argc, char **argv){
  sqlite3* db;
  sqlite3* db2;
  sqlite3_stmt* stmt;
  int rc, i;
  sqlite3_open("test.sqlite3", &db);
  sqlite3_exec(db, "PRAGMA journal_mode = WAL;", 0, 0, 0);
  sqlite3_exec(db, "PRAGMA synchronous = NORMAL", 0, 0, 0);
        
  sqlite3_open("test.sqlite3", &db2);
  sqlite3_exec(db2, "CREATE TABLE sometable(firstcolumn INTEGER)", 0, 0, 0);

  rc = sqlite3_prepare_v2(db, "SELECT * FROM pragma_table_info('sometable');", -1, &stmt, 0);
  printf("first query rc = %d\n", rc);
  while( sqlite3_step(stmt)==SQLITE_ROW ){
    for(i=0; i<sqlite3_column_count(stmt); i++){
      if( i ) printf(",");
      printf("%s", sqlite3_column_text(stmt,i));
    }
    printf("\n");
  }
  sqlite3_finalize(stmt);
  rc = sqlite3_prepare_v2(db, "PRAGMA table_info('sometable');", -1, &stmt, 0);
  printf("second query rc = %d\n", rc);
  while( sqlite3_step(stmt)==SQLITE_ROW ){
    for(i=0; i<sqlite3_column_count(stmt); i++){
      if( i ) printf(",");
      printf("%s", sqlite3_column_text(stmt,i));
    }
    printf("\n");
  }
}

(4.2) By Keith Medcalf (kmedcalf) on 2020-07-04 19:48:25 edited from 4.1 in reply to 3 [source]

Unfortunately Richard, you are doing it wrong. Try the following code:

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

int main(int argc, char **argv){
  sqlite3* db;
  sqlite3* db2;
  sqlite3_stmt* stmt;
  int rc, i;
  sqlite3_open("test.sqlite3", &db);
  sqlite3_exec(db, "PRAGMA journal_mode = WAL;", 0, 0, 0);
  sqlite3_exec(db, "PRAGMA synchronous = NORMAL", 0, 0, 0);

  sqlite3_open("test.sqlite3", &db2);
  sqlite3_exec(db2, "CREATE TABLE sometable(firstcolumn INTEGER)", 0, 0, 0);


  rc = sqlite3_prepare_v2(db, "PRAGMA table_info('sometable');", -1, &stmt, 0);
  printf("second query rc = %d\n", rc);
  while( sqlite3_step(stmt)==SQLITE_ROW ){
    for(i=0; i<sqlite3_column_count(stmt); i++){
      if( i ) printf(",");
      printf("%s", sqlite3_column_text(stmt,i));
    }
    printf("\n");
  }
  sqlite3_finalize(stmt);

  rc = sqlite3_prepare_v2(db, "SELECT * FROM pragma_table_info('sometable');", -1, &stmt, 0);
  printf("first query rc = %d\n", rc);
  while( sqlite3_step(stmt)==SQLITE_ROW ){
    for(i=0; i<sqlite3_column_count(stmt); i++){
      if( i ) printf(",");
      printf("%s", sqlite3_column_text(stmt,i));
    }
    printf("\n");
  }
  sqlite3_finalize(stmt);
}

Which adds a finalize call and switches the order of the statements. On current tip of trunk I get the following output (assuming I delete the test.sqlite3 file first):

second query rc = 0
first query rc = 0
0,firstcolumn,INTEGER,INTEGER,(null),0,(null),0,0,0

Fascinatingly the CLI does not exhibit the same behaviour for some reason.

** SKIP THIS -- The CLI is using only one connection, of course **

SQLite version 3.33.0 2020-07-03 21:44:20
Enter ".help" for usage hints.
sqlite> pragma journal_mode=wal;
┌──────────────┐
│ journal_mode │
├──────────────┤
│ wal          │
└──────────────┘
sqlite> pragma synchronous=normal;
sqlite> create table t(x);
sqlite> pragma table_info('t');
┌─────┬──────┬──────┬──────┬──────┬─────────┬────────────┬────┬───────┬─────────┐
│ cid │ name │ type │ aff  │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │
├─────┼──────┼──────┼──────┼──────┼─────────┼────────────┼────┼───────┼─────────┤
│ 0   │ x    │      │ BLOB │      │ 0       │            │ 0  │ 0     │ 0       │
└─────┴──────┴──────┴──────┴──────┴─────────┴────────────┴────┴───────┴─────────┘

(5) By Richard Hipp (drh) on 2020-07-04 20:32:22 in reply to 4.2 [link] [source]

Thank you for the test case. The latest prerelease snapshot should fix the problem.