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 
> <>.

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: 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 

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,

Reply via email to