On Sunday, 13 December, 2015 17:04, Olivier Mascia <om at integral.be> wrote:
> Thanks. I'm reading you with attention. > > Clemens: > > Please note that transactions work on the connection level. > That was clear. > > Simon: > > Which, of course, decreases the point of you having competing threads in > the first place. Which is related to the FAQ pointing at > <http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf>. > > Those threads which I deal with are there for other purposes than > accessing the DB, but they happen to have. Besides, the issue is not at > first about those threads but about the SQLite API which quite clearly > *could* easily know and keep within the statement state information, the > count of changes and the last inserted rowid, which could then in turn be > happily queried by the application. Keeping them at the connection level > is essentially useless (since they could be wrong or undefined) except in > very restrictive conditions. I just would not like to fix holes in the > road by asking people to stop driving cars. :) The documentation for sqlite3_last_insert_rowid clearly states that it returns the rowid last inserted on the connection, as does the documentation for sqlite3_changes. While I agree that it may very well be possible to maintain the data by statement, that is not what the current functions as documented do. Perhaps you can/might want to request an enhancement? > Reading: https://www.sqlite.org/threadsafe.html the default for SQLite, > unless compiled differently is serialized which means that "SQLite can be > safely used by multiple threads with no restriction". It obviously implies > more contention than transaction isolation should dictate, but it states a > clear contract for the application developer. Contract which is very > quickly violated by API like last_insert_rowid() and changes() reporting > on the connection level rather than statement level to the extent of > having to document their inability to report any useful result in a multi- > threaded configuration. You are misconstruing the serialization and what threadsafe means. "Threadsafe" means there is no distinction between threads and no per-thread state information maintained between calls into the engine. Thus you can execute a select on one thread, and "step" on 10,000 different threads to each retrieve one row of the result set -- there is no isolation between threads -- only between connections. "Serialized" means "side-by-each" (to use newfy-speak). In other words, as if made from THE SAME THREAD. The calls are interleaved so that only one occurs at a time, but that is it. Serialized data access requires transactions. Transactions are per connection. Hence, multiple threads performing operations on the same connection (and thus are part of the same transaction) which change database state are not isolated from each other in any way. > > Keith: > > INSERT INTO table VALUES ('somedata'); > > SELECT rowid FROM table WHERE data='somedata'; > > My question cited last_insert_rowid and changes. Regarding > last_insert_rowid() the whole purpose was indeed to know what rowid got > assigned by an insert, without having to run yet another query for that, > not to mention that in a not fully normalized world, there might not be > other unique combination of values to uniquely select the just inserted > row, except its rowid (or whatever else integer primary key it uses). > Other SQLs have INSERT ... RETURNING ..., solving that case very > effectively (as the answer is known and does not need to be looked up > again after the facts). > As I wrote, UPDATE OR INSERT has value when needing to update some > attributes of a row, if it exist (WHERE clause) and INSERT a new row, when > not found. The behavior cannot be mimicked by INSERT OR REPLACE which, if > it happens to have to REPLACE does just that: substitute the whole row > (DELETing it, INSERTing it). Doesn't allow ? and that's expected for that > statement ? for partial update of a row. Then you will have to create your own mutex to control the contention you are creating on the database connection/transaction.
