> On 14 Sep 2016, at 5:47pm, Alex Ward <cov...@yahoo.com> wrote:
>
>> Behalf Of Simon Slavin
>
>> It is not enough to lock the _prepare, lock the _step()s, and lock the
>> _finalize.
>> If they're sharing a connection with other threads then the lock has to be
>> placed at the start of the prepare and be released at the end of the
>> finalize.
>> Don't forget that they're all part of the same transaction.
>
> Got it, it's the set of the 3 operations that should be serialized. Thanks.
>
> The finalize is the end of the implicit transaction, correct?
Right.
> Assuming that it is the finalize for the only statement being executed on
> that connection. Then if the same thread did another prepare/step/finalize
> on that same connection it would be in a new implicit transaction. Do I have
> that right?
The parameter you pass to _step() and _finalize() is a statement handle (and
the statement knows which connection it should be using). So yes, it's
possible to maintain multiple statements for one connection, and SQLite will
know which statement you're finalizing.
The SQL standard says that if you try doing SELECT without a BEGIN it should
return an error message. Because you shouldn't be doing database stuff outside
a transaction.
Instead of returning an error SQLite automatically wraps BEGIN/COMMIT around
any statement (even if it just reads) if you haven't already done your own
BEGIN. So what you're getting from prepare/step/finalize is actually (I might
have this wrong I haven't read the source code)
_prepare()
first _step()
BEGIN
SELECT result (first row)
more _step()
SELECT results (more rows)
eventually a SELECT results in SQLITE_DONE
_finalize()
COMMIT
So think about what would happen if you had two of these running
simultaneously. The BEGIN on one query would not lock out the BEGIN on another
query, but it could prevent a INSERT/UPDATE from writing to the database. This
is what you're trying to simulate when you do your own locking/mutex, and
that's why it's so easy to get it wrong.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
