3 ms·

sqlite_db_name docs say: "Applications that need to remember the string long-term should make their own copy. Applications that are accessing the same database

sqlite_db_name docs say: "Applications that need to remember the string long-term should make their own copy. Applications that are accessing the same database connection simultaneously on multiple threads should mutex-protect calls to this API and should make their own private copy of the result prior to releasing the mutex." But it sounds like they should advise to mutex-protect all operations that can change the schema then, not just calls to that particular function.

masklinn 4y ago
> But it sounds like they should advise to mutex-protect all operations that can change the schema then, not just calls to that particular function. No? Do note the problem condition: > Applications that are accessing the same database connection simultaneously on multiple threads So the issue is likely the value returned by the function is borrowed from a per-connection datastructure (a mapping of some sort). The consequence is obvious when said connection is shared between threads, while the schema update is thread-safe, retaining data owned by the connection is not. And do note that connection thread-safety is already shaky: while sqlite compiles with thread-safety enabled by default, lots of functions from the C API can return utter nonsense for shared connections e.g. > If a separate thread performs a new INSERT on the same database connection while the sqlite3_last_insert_rowid() function is running and thus changes the last insert rowid, then the value returned by sqlite3_last_insert_rowid() is unpredictable and might not equal either the old or the new last insert rowid. One of the reasons why the addition of RETURNING was a great boon. Personally, I'd strongly recommend not sharing sqlite connections between threads anyway for those reasons. Either create one connection per thread, or use a pool to move connections between threads as needed. And downgrade thread safety to SQLITE_CONFIG_MULTITHREAD if you have the opportunity.
- Groxx 4y ago
  Performing a separate request for the last-inserted row has always struck me as a literally insane idea. Of course it's incredibly error prone! How could it be anything else? How the heck did it come into existence?
  - cryptonector 4y ago
    Yes, that was always kinda nonsense. You really need RETURNING.
  - masklinn 4y ago
    > How the heck did it come into existence? Probably implementation simplicity: sqlite_last_insert_rowid() dates back to the "dark ages" of mysql (it was added in 2.2.3, released 2002-01-16). The alternative is to add RETURNING, which for that sole purpose is rather complicated. sqlite only added RETURNING in 3.35.0 (2021-03-12), and that was also motivated by the addition of upserts (ON CONFLICT clauses), for which last_insert_rowid really doesn't work. Furthermore, sqlite really only added multithreading support reluctantly, per its FAQ: > (6) Is SQLite threadsafe? > Threads are evil. Avoid them. Multithreading support was added in 2.2.3 (2002-01-16), but it wasn't even possible to move connections between threads until 3.3.3 (2006-01-31), and using the same connection from multiple threads concurrently was not supported until 3.5.1 (2007-10-04).
- krylon 4y ago
  > Personally, I'd strongly recommend not sharing sqlite connections between threads anyway for those reasons. I have to admit I am surprised this would work at all without blowing up in your face in surprising and horrifying ways.

masklinn 4y ago

> But it sounds like they should advise to mutex-protect all operations that can change the schema then, not just calls to that particular function. No? Do note the problem condition: > Applications that are accessing the same database connection simultaneously on multiple threads So the issue is likely the value returned by the function is borrowed from a per-connection datastructure (a mapping of some sort). The consequence is obvious when said connection is shared between threads, while the schema update is thread-safe, retaining data owned by the connection is not. And do note that connection thread-safety is already shaky: while sqlite compiles with thread-safety enabled by default, lots of functions from the C API can return utter nonsense for shared connections e.g. > If a separate thread performs a new INSERT on the same database connection while the sqlite3_last_insert_rowid() function is running and thus changes the last insert rowid, then the value returned by sqlite3_last_insert_rowid() is unpredictable and might not equal either the old or the new last insert rowid. One of the reasons why the addition of RETURNING was a great boon. Personally, I'd strongly recommend not sharing sqlite connections between threads anyway for those reasons. Either create one connection per thread, or use a pool to move connections between threads as needed. And downgrade thread safety to SQLITE_CONFIG_MULTITHREAD if you have the opportunity.

Groxx 4y ago
Performing a separate request for the last-inserted row has always struck me as a literally insane idea. Of course it's incredibly error prone! How could it be anything else? How the heck did it come into existence?
- cryptonector 4y ago
  Yes, that was always kinda nonsense. You really need RETURNING.
- masklinn 4y ago
  > How the heck did it come into existence? Probably implementation simplicity: sqlite_last_insert_rowid() dates back to the "dark ages" of mysql (it was added in 2.2.3, released 2002-01-16). The alternative is to add RETURNING, which for that sole purpose is rather complicated. sqlite only added RETURNING in 3.35.0 (2021-03-12), and that was also motivated by the addition of upserts (ON CONFLICT clauses), for which last_insert_rowid really doesn't work. Furthermore, sqlite really only added multithreading support reluctantly, per its FAQ: > (6) Is SQLite threadsafe? > Threads are evil. Avoid them. Multithreading support was added in 2.2.3 (2002-01-16), but it wasn't even possible to move connections between threads until 3.3.3 (2006-01-31), and using the same connection from multiple threads concurrently was not supported until 3.5.1 (2007-10-04).
krylon 4y ago
> Personally, I'd strongly recommend not sharing sqlite connections between threads anyway for those reasons. I have to admit I am surprised this would work at all without blowing up in your face in surprising and horrifying ways.