2

After some study, I saw that SQLite will handle concurrent read . But after I try to create multi-thread and read the SQLite simultaneously, it will crash on sqlite3_get_table. But if I use serialized mode, it will be totally fine. Why this happened? Did I misunderstand something?

And here is how I read the data:

ret = sqlite3_get_table(db, sql, &results, &rows, &columns, &err_msg);
if (ret != SQLITE_OK) {
    // error handling
}
if (rows < 1) {
    // error handling
}
else {
    // reading data
}
sqlite3_free_table(results);

I could also add lock/unlock around sqlite3_get_table to solve the problem. But why I can't just call this function without locking?

Emma
  • 29
  • 3
  • What exactly do you mean by crashing? _Inside_ the `sqlite3_get_table()` call, or within the rest of your program? While not impossible, it's _very_ unlikely to be within the call itself, in which case what is the return code from the call? Are you using WAL mode? Finally, while I think `sqlite3_get_table()` still works, I have a feeling it's not the encouraged way of doing things. – TripeHound Apr 19 '18 at 12:09
  • Hi @TripeHound, thanks for your reply. It crashed inside sqlite3_get_table(access violation). And I try to use WAL or without WAL, but the result is the same. Sure, sqlite3_get_table should work fine, so I think there must be some misunderstanding. – Emma Apr 20 '18 at 02:14

1 Answers1

1

The linked answer talks about concurrent accesses from multiple processes.

Concurrent accesses are perfectly fine as long as each access goes through its own connection. (Reads are allowed, writes are properly locked.)

Your program should use one connection per thread.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks for your reply. I am sorry that I am the newbie in this area. What do you mean "use one connection per thread", do you mean that I should have their own sqlite db for each thread, or could I share the same sqlite db and use the different connection? Thanks! – Emma Apr 20 '18 at 02:23
  • I did not say "one database per thread" but "one connection per thread". – CL. Apr 20 '18 at 07:11
  • 1
    @Emma See [Using SQLite In Multi-Threaded Applications](https://sqlite.org/threadsafe.html), in particular "_provided that no single database **connection** is used simultaneously in two or more threads_". Each thread must open its _own_ connection to the (same) database. – TripeHound Apr 20 '18 at 07:58