Hi Darren, Thanks for explaining the internals.
I already assumed by my 2 small experiments that this was the case but it's nice to have it confirmed. With kind regards, Koen 2014-11-11 12:05 GMT+01:00 Darren Duncan <dar...@darrenduncan.net>: > On 2014-11-11 2:41 AM, Koen Van Exem wrote: > >> I find it a bit confusing because when you create >> a PRIMARY KEY AUTOINCREMENT then a >> table named sqlite_sequence is created. >> >> According to the SQL (2003) standard multiple sessions are >> guaranteed to allocate distinct sequence values. >> (even when rollbacks are involved) >> > > See, this is the source of your confusion. I will explain. > > 1. Conceptually a sequence generator is just a database table with a > single row and single column whose value is the integer. When the > generator produces the next value, it is like these 2 statements being done > as an atomic unit: "update seqgentbl set theint = theint + 1" and "select > theint from seqgentbl". > > 2. The semantics that the SQL standard defines, and is commonplace with > other SQL DBMSs, is that the aforementioned read+update of seqgentbl > happens in its own autonomous database transaction that commits > immediately, and serially prior to the main transaction that called upon > the sequence generator. This is why in those cases a rollback of the main > transaction doesn't rollback the sequence generator, because semantically > that happened prior to the current transaction and successfully committed. > > 3. SQLite is different such that its read_update of seqgentbl happens > within the current main transaction rather than a separate one, and > therefore its actions rollback like anything else. > > So SQLite is purposefully being different than the SQL standard. Partly > this is because supporting the standard means having to support multiple > concurrent transactions trying to write the database, in contrast to what > SQLite actually does which is only supporting one writing transaction at a > time. > > If you want to use SQLite like the SQL standard, then invoke the sequence > generator first in its own transaction and remember the value, then use > that remembered value in your main transaction that you explicitly do > afterwards. > > Do you understand what's going on now? > > -- Darren Duncan > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Koen Van Exem +32 3301 3301 +32 498 519999 k...@allors.com http://www.allors.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
