On Wed, 07 Jan 2015 21:47:24 -0700
"Keith Medcalf" <kmedc...@dessus.com> wrote:
> >As I said, your description (which I trust is accurate) is very
> >helpful to someone who wants to understand how SQLite will act on
> >the SQL provided to it. But it also protrays problematic choices
> >that stray from SQL's defined behavior.
>
> Not really. It only shows that updating the database on the same
> connection as is being used to run a select, and attempting to commit
> the update before the select is complete results in chaos. I think
> you would agree that the sequence:
>
> _prepare('select ...')
> do while _step() != DONE
> _step(_prepare('BEGIN'))
> _step(_prepare('UPDATE ...'))
> _step(_prepare('COMMIT'))
> continue
>
> should not be valid. The BEGIN COMMIT should be moved outside the
> loop unless the connection on which the select is processed is
> separate from that on which the BEGIN/UPDATE/COMMIT is performed.
Yes, I agree that shouldn't be valid. Or, if it is, then it should
work correctly! ;-)
Thanks for the clarification. The salient point, as you emphasized, is
> There is only one connection.
I tend to forget that SQLite behaves oddly sometimes when misused. We
had a discussion months ago about approximately the same thing, where
threads were sharing a connection.
Sybase works similarly. The server insists that each query be handled
in its entirety before it accepts the next. Attempting to issue an
UPDATE per your example above would result in the dread "pending
results" error.
Along the same lines, since you mentioned it,
> in an SQLite database you can do:
>
> BEGIN
> SELECT ...
> SELECT ...
> SELECT ...
> SELECT ...
> COMMIT
>
> and the view of the database seen by this connection will be
> consistent even though "some other process" modified the tables used
> in query 3 while query 2 was executing. Even if those changes are
> commited by the writer process, the above process will not see them
> until the COMMIT releases the locks. I expect other databases do
> this as well.
In other DBMSs that I know of -- and afaik standard SQL -- BEGIN
TRANSACTION has no effect on SELECT. Each SELECT is atomic, of course,
but there's no way to hook two SELECTs together to make them see one
database state. That's what JOIN is for. :-)
A common error among inexperienced programmers is to assume that BEGIN
TRANSACTION works as you describe above: that it isolates SELECT from
competing updates. But a transaction is not a lock! A better mental
model is the one defined in the standard: BEGIN TRANSACTION defines a
unit of work that either will or will not be applied to the database in
its entirety (depending on COMMIT or ROLLBACK, of course). It has a
role to play in maintaining database integrity. It really has nothing
to do with isolation.
Interesting discussion, Keith. Thanks for the clarification.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
