[Devops] Compile Sqlite and enable multithread mode to prepare for premain mainnet

Proposal Details

Sqlite version

To ensure the consistency of local and server data import, use the same Sqlite version 3.46.1

sqlite3 --version
### 3.46.1 2024-08-13 09:16:08 c9c2ab54ba1f5f46360f1b4f35d849cd3f080e6fc2b6c60e91b16c63f69a1e33 (64-bit)

Corresponding source code

https://www.sqlite.org/2024/sqlite-autoconf-3460100.tar.gz

Enable Sqlite multithread mode

Sqlite must be compiled first and then enable multithread mode.

Configure the Build with SQLITE_THREADSAFE=2

curl -O https://www.sqlite.org/2024/sqlite-autoconf-3460100.tar.gz
tar xzf sqlite-autoconf-3460100.tar.gz
cd sqlite-autoconf-3460100

./configure CFLAGS="-DSQLITE_THREADSAFE=2"
make
sudo make install

./sqlite3 --version
3.46.1 2024-08-13 09:16:08 c9c2ab54ba1f5f46360f1b4f35d849cd3f080e6fc2b6c60e91b16c63f69a1e33 (64-bit)

How to Check SQLITE_THREADSAFE:

Run a Small C Program check_threadsafe.c

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

int main(void) {
    printf("SQLITE_THREADSAFE = %d\n", sqlite3_threadsafe());
    return 0;
}

Compile and run this program:

gcc -o check_threadsafe check_threadsafe.c -lsqlite3
./check_threadsafe

Expect output

SQLITE_THREADSAFE = 2

The output will show 1, 2, or 0, depending on how SQLite was compiled.

1 means SQLite is compiled in Serialized mode (SQLITE_THREADSAFE=1). Default
2 means SQLite is compiled in Multi-thread mode (SQLITE_THREADSAFE=2).
0 means SQLite is compiled in Single-thread mode (SQLITE_THREADSAFE=0).

If use SQLITE_THREADSAFE=2 (multithreaded) you have to make sure that each thread uses its own database connection. Sharing a single database connecgtion amongst multiple threads isn't safe. If use SQLITE_THREADSAFE=1 (serialized) you can even safely reuse a single databse connection amongst multiple threads.

rooch-network / rooch