sqlite3_stmt: binding from multiple threads possible ?

(1) By anonymous on 2022-01-30 08:23:22 [link] [source]

Hi,

I wonder if it's possible to use the same sqlite3_stmt from different threads at the same time, where each binds different values to parameters.

Thinking about it, I am nearly sure that this is not a good idea, and every thread would need an own sqlite3_stmt instance,

but I thought it might not harm to ask, since the docs to sqlite3_stmt does not mention that topic

(2) By Warren Young (wyoung) on 2022-01-30 10:18:48 in reply to 1 [source]

use the same sqlite3_stmt from different threads at the same time

No, because:

Prepared statements in SQLite are per-connection; and
The only safe ways to use SQLite in a multithreaded application are:
- Serialized, which doesn't get you what you want.
- Conn-per-thread, which runs you into limitation #1.

To violate this would be to try to use one connection's prepared statement with another conn.

(3) By Gunter Hick (gunter_hick) on 2022-01-31 07:29:58 in reply to 1 [link] [source]

The set of bound paramters is an attribute of the statement.

Attempting to bind different values from different threads is a "negative race condition" (last one wins).

The first thread to step the statement kicks out all threads that have not finished binding their values (BIND after STEP returns MISUSE), and the remaining threads then compete for the rows that match the (probably inconsistent) set of bound values.

The next race condition occurs at the end of the row set, when the thread that ran out of rows calls RESET and BIND, while the other threads implicitly re-run the statement (with possibly changed parameter values).

Running multiple threads against one connection requires that threads wait their turn until the current thread has finished using the connection and return it to a defined starting state (no pending transaction, no running statements).

Giving each thread its own connection allows it do do whatever it wants without interference.

(4) By Stephan (stephancb) on 2022-01-31 12:13:29 in reply to 1 [link] [source]

It depends on what "at the same time" means?

After

sqlite3_bind_xxx(stmt, ...)
sqlite3_step(stmt)
.
.
.
sqlite3_reset(stmt)

is completed, other threads are free to rebind, step, and reset, and so on, without having to "reprepare". Thus the sequence above must be protected, by a mutex. According to https://www.sqlite.org/c3ref/open.html, if the database has been opened SQLITE_OPEN_FULLMUTEX then "multiple threads can safely attempt to use the same database connection at the same time. (Mutexes will block any actual concurrency, but in this mode there is no harm in trying.)" My interpretation is that SQLite will exactly handle the mutex in this case (but I have not tried it). So you could try whether it works?

(5) By cj (sqlitening) on 2022-01-31 20:34:00 in reply to 1 [link] [source]

1) Begin immediate or Begin Exclusive must be used with threads
2) Unique global string element is thread safe if used only by its thread
3) If over 64 threads waitformultipleobjects must wait in groups of 64
4) Use SLEEP 10 after THREAD CREATE is highly suggested with many threads

#INCLUDE "sqlitening.inc"
MACRO NewRow(sTable,sColumn)=slselstr("select COALESCE(max("+sColumn + "),0)+1 from "+sTable)
MACRO BindText(str)= MKDWD$(LEN(str) + 1) + "T" +  str

FUNCTION PBMAIN
 LOCAL x AS LONG
 LOCAL threads AS LONG
 threads = 39
 DIM hThread(1 TO threads) AS LONG
 DIM gs     (1 TO threads) AS GLOBAL STRING
 slOpen "junk.db3","C"
 slExe  "create table if not exists t1(c1 integer primary key,c2 text)"
 FOR x = 1 TO threads
  THREAD CREATE Test(x) TO hThread(x)
  THREAD CLOSE  hThread(x) TO hThread(x)
 NEXT
 DO:SLEEP 50:LOOP UNTIL THREADCOUNT=1
 MSGBOX JOIN$(gs(),$CR) 'results of all threads
END FUNCTION

THREAD FUNCTION Test(BYVAL threadnum AS LONG) AS LONG
 LOCAL sNewRow AS STRING
 slOpen "junk.db3","C"
 slexe  "begin immediate"
  sNewRow = NewRow("T1","C1")
  slexebind "insert into t1 values(null,?)",BindText("Insert record " + sNewRow + " thread" + STR$(threadnum))
 slexe "end"

 'fill unique global string element for this thread
 LOCAL sArray() AS STRING
 slSelAry "select c1,c2 from t1 where rowid="+sNewRow,sArray(),"Q9c"
 gs(threadnum) = JOIN$(sArray(),"") 'add to unique element

END FUNCTION