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
