5 ms·

I just dealt with a lot of sqlite concurrency stuff recently. When I first got started, a lot of recommendations were that apps should create a connection and h

I just dealt with a lot of sqlite concurrency stuff recently. When I first got started, a lot of recommendations were that apps should create a connection and hold that connection its the duration. This works great but I feel like it's worth noting that this can get dangerous when you're working in a multi-threaded context. You can have one thread in the middle of stepping through the results of a SELECT prepared statement and if another thread resets the statement and runs it again, that original thread will start stepping through the results from the other thread's call. Or if both threads begin a transaction, you'll get nested transaction errors. Things get really confusing, especially because you might not notice the problems until the app is running on a slower machine.

deleted 2y ago
[deleted]
epcoa 2y ago
The particulars of using SQLite are clearly documented: https://www.sqlite.org/threadsafe.html https://www.sqlite.org/threadsafe.html You must use serialized mode or provide your own concurrency control.
- fnordlord 2y ago
  In my case, it's using default serialized mode. I would assume I was safe judging by "In serialized mode, API calls to affect or use any SQLite database connection or any object derived from such a database connection can be made safely from multiple threads" But in the unit tests I have setup, it hasn't been the case, at least with prepared statements being shared between threads.
  - epcoa 2y ago
    Yes that's exactly how it works in serialized build/mode. Are you sure not ultimately compiling with -DSQLITE_THREADSAFE=0 or 2? Also the serialized setting is distinct from the isolation level. sqlite has a famously extensive test suite and is effectively one of the most heavily tested libraries ever, and there are multithread tests in the suite. If your tests are triggering some other kind of race/undefined behavior there is also that to consider.
jpc0 2y ago
Just for clarification, did you have one connection per thread or one global connection? If it's the latter your error would make sense since you created a race condition, databases connections aren't magic, they are still just file descriptors without synchronisation, most ORMs/frameworks put the magic in there for you. If it's the former then yeah that makes no sense.
- epcoa 2y ago
  SQLite database connection objects are not "just file descriptors" and with the default serialization build they are appropriately locked to allow multi-threaded use. In that sense, they are magic. https://www.sqlite.org/threadsafe.html https://www.sqlite.org/threadsafe.html
  - jpc0 2y ago
    So in this you assume this would make sense: THREAD 1: Start transaction on Dbconnection - Success THREAD 2: Start transaction on Dbconnection - Success And there is no confusion about who owns what? I don't see any way in the SQLite C api for that to not be a race condition and would require two connections for one of them not to throw an error.
    - deleted 2y ago
      [deleted]
    - epcoa 2y ago
      I think we are talking past each other and see what you’re saying. Also rereading the initial comment it is now obvious what is going on. For some reason I thought they were claiming weird interactions between statement objects (which would occur if serialization was not used). It appears they are just sharing a single statement object between threads and expecting that to work. I read past that because it never occurred someone working with the API would think that would work. It would imply some kind of thread local storage, and as you are aware that is definitely not something commonly done on APIs of this type. > And there is no confusion about who owns what? What you are describing is a higher level of race condition of course, but not one where the internal state of SQLite is inconsistent or breaking invariants. Specifically there is no confusion about ownership: there is one tx (stack) per connection and it is owned by the connection, to which access is serialized by the library. In SQLite you implicitly open a transaction with any statement so it’s not necessarily a problem to be “starting a transaction” since the implicit start will be idempotent. And you can certainly use this behavior to run concurrent reads and writers across multiple threads on a single connection without issue. Yes if you try to run multiple BEGIN statements that will error out. You’re right some ORMs do some fancy magic over a connection to serialize those. Sorry that was not the initial problem I was addressing (though obviously the GP had confusion in this area too) Commenting too late at night.

deleted 2y ago

[deleted]

epcoa 2y ago

The particulars of using SQLite are clearly documented: https://www.sqlite.org/threadsafe.html https://www.sqlite.org/threadsafe.html You must use serialized mode or provide your own concurrency control.

fnordlord 2y ago
In my case, it's using default serialized mode. I would assume I was safe judging by "In serialized mode, API calls to affect or use any SQLite database connection or any object derived from such a database connection can be made safely from multiple threads" But in the unit tests I have setup, it hasn't been the case, at least with prepared statements being shared between threads.
- epcoa 2y ago
  Yes that's exactly how it works in serialized build/mode. Are you sure not ultimately compiling with -DSQLITE_THREADSAFE=0 or 2? Also the serialized setting is distinct from the isolation level. sqlite has a famously extensive test suite and is effectively one of the most heavily tested libraries ever, and there are multithread tests in the suite. If your tests are triggering some other kind of race/undefined behavior there is also that to consider.

jpc0 2y ago

Just for clarification, did you have one connection per thread or one global connection? If it's the latter your error would make sense since you created a race condition, databases connections aren't magic, they are still just file descriptors without synchronisation, most ORMs/frameworks put the magic in there for you. If it's the former then yeah that makes no sense.

epcoa 2y ago
SQLite database connection objects are not "just file descriptors" and with the default serialization build they are appropriately locked to allow multi-threaded use. In that sense, they are magic. https://www.sqlite.org/threadsafe.html https://www.sqlite.org/threadsafe.html
- jpc0 2y ago
  So in this you assume this would make sense: THREAD 1: Start transaction on Dbconnection - Success THREAD 2: Start transaction on Dbconnection - Success And there is no confusion about who owns what? I don't see any way in the SQLite C api for that to not be a race condition and would require two connections for one of them not to throw an error.
  - deleted 2y ago
    [deleted]
  - epcoa 2y ago
    I think we are talking past each other and see what you’re saying. Also rereading the initial comment it is now obvious what is going on. For some reason I thought they were claiming weird interactions between statement objects (which would occur if serialization was not used). It appears they are just sharing a single statement object between threads and expecting that to work. I read past that because it never occurred someone working with the API would think that would work. It would imply some kind of thread local storage, and as you are aware that is definitely not something commonly done on APIs of this type. > And there is no confusion about who owns what? What you are describing is a higher level of race condition of course, but not one where the internal state of SQLite is inconsistent or breaking invariants. Specifically there is no confusion about ownership: there is one tx (stack) per connection and it is owned by the connection, to which access is serialized by the library. In SQLite you implicitly open a transaction with any statement so it’s not necessarily a problem to be “starting a transaction” since the implicit start will be idempotent. And you can certainly use this behavior to run concurrent reads and writers across multiple threads on a single connection without issue. Yes if you try to run multiple BEGIN statements that will error out. You’re right some ORMs do some fancy magic over a connection to serialize those. Sorry that was not the initial problem I was addressing (though obviously the GP had confusion in this area too) Commenting too late at night.