On 9/20/16, Kevin O'Gorman <kevinogorm...@gmail.com> wrote:
> Surely, Mr. Hipp is an authority, but I'm slightly puzzled by this answer.
> And it doesn't answer the part of the question about what happens if I do
> it wrong, and transactions conflict.  Based on what I now think is true, if
> I don't do something, transactions begin with the first modification.  They
> may be prevented from executing UPDATEs simultaneously, but could still
> result in non-serializable execution of the whole, and inconsistent or
> erroneous data in the database, because each would be based on the SELECT
> statements before either had written.  Or do they result in exceptions?

If you do a "BEGIN;" followed by a "SELECT..." then the transaction
starts before the SELECT is run.  So it is serializable.

But if you do just a "BEGIN", then some other process might jump in
line ahead of you and make some other changes to the database.  Your
transaction will not be able to see those changes, due to isolation.
But they will be in the database file. Then when you got to COMMIT,
SQLite will see that your transaction is based on an historical and
out-of-date version of the database and hence will refuse to do the
commit.  You'll have to ROLLBACK and try again.

When you do "BEGIN IMMEDIATE" that reserves your spot in line and
ensures that no other transactions will commit in front of you.

D. Richard Hipp
sqlite-users mailing list

Reply via email to