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. :) 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. > 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. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om
