On Fri, 02 Jan 2015 21:41:02 -0700
"Keith Medcalf" <kmedc...@dessus.com> wrote:
> On Friday, 2 January, 2015 16:26, James K. Lowden
> <jklow...@schemamania.org> said:
>
> >On Thu, 25 Dec 2014 05:32:45 -0700 (MST) Rick Kelly
> ><rpke...@gci.net> wrote:
>
> >> All SELECT type requests are wrapped with BEGIN TRANSACTION/COMMIT
>
> >That shouldn't be necessary and afaik isn't necessary. SELECT does
> >not modify the database. To "commit a select" is to apply the
> >nonchanges.
>
> It does not matter whether it modifies the database. "reading" the
> database requires a lock -- a shared lock. "updating" the database
> requires a "write" lock, which precludes obtaining a "shared" lock.
When I first read your answer my reaction was, "yes, yes, of course".
But I'm not sure where that leaves the OP. Are you suggesting select
statements work "better" in some sense with autocommit turned off?
In passing I suggest *requires* is not true in general. It may be that
SQLite and other implementations use locks to mediate access and
implement ACID semantics. Locks are just one popular way to accomplish
that, not the only one.
Your description of transaction implementation is illuminating. If I
understand correctly, you're describing an odd set of
design choices.
> BEGIN TRANSACTION does not acquire a lock -- BEGIN IMMEDIATE does
> that -- BEGIN TRANSACTION merely turns off autocommit, meaning that
> the lock will not be released magically, but rather by an explicit
> COMMIT (which itself does not do anything -- it merely turns
> autocommit back on so that the next statement will commit the
> transaction before magically acquiring a new lock).
I find this very peculiar. You aren't saying is that
begin transaction;
insert into T value (1);
commit;
[sqlite3_close]
leaves the database unchanged (because there's no "next statement")?
If not, and there's a power outage between (successful) commit and
closing the connection, what will be the state of the database on
restart? Is the transaction still open or, if not, is it rolled
forward and completed, or rolled back?
> However, execution of a SELECT statement does cause a lock to be
> obtained (a shared lock) and a COMMIT does cause that shared lock to
> be released.
Again, I find this surprising. I would expect SELECT to
1. establish a shared lock
2. select the data
3. release the lock
whether or not BEGIN is called. If I understand what commit does per
your description above, in a "transaction", the effect would be
1. BEGIN TRANSACTION (autocommit off)
2. SELECT (take shared lock)
3. data data data
4. [SELECT done] (release shared lock)
5. COMMIT (autocommit on)
which leaves steps #1 and #5 redundant.
> Executing an UPDATE after a SELECT -- in the same connection -- (or
> while a select is in progress) will escalate the SHARED lock to a
> WRITE lock.
OK, this is typical.
> COMMIT will release "the lock" -- "the lock" is now a WRITE lock, not
> a shared lock. Therefore the next _step() will be executing without
> any lock at all leading to apparently undefined results (really an
> error should be thrown "SQLITE_DATABASE_UNLOCKED_FROM_UNDER_ME" or a
> MISUSE error, but is not).
Good to know. I hope we agree this is unlovely, and intersects with
SQLite's unfortunate property of not isolating SELECT as atomic. (By
which I mean: if there are 10 rows in a table and connection 1 issues
"SELECT * FROM T" and between calls 4 & 5 to sqlite3_step another
connection 2 issues "DELETE FROM T", the first process may get 4 or 5
rows, or 10, depending on luck of the draw.)
The right behavior is not a misuse error. How is it "misuse" for two
connections to read and update the same table in overlapping time?
The right behavior is to isolate SELECT from UPDATE. Let the reader see
what was in the database at the time the SELECT was issued, unaffected
by UPDATE. The COMMIT associated with UPDATE should not affect the
SELECT's lock; rather it should pend until SELECT completes. Then its
own lock acquires rights to the resources it needs, and is released when
the work is done.
> This is inherent in how WAL works. Just because WAL is not in effect
> does not alter the fundamental workings of the transaction system.
Not sure how to parse that. I think you mean it's inherent in how
transactions work, whether or not WAL is used?
> I do not believe that there is a way to specify "COMMIT BUT MAINTAIN
> THE SHARED LOCK", (that is, to commit the changes only and
> un-escalate the lock back to a shared lock)
No such syntax is needed if ACID semantics are respected. In SQL we
we neither lock nor release anything, ever, explicitly. Locks are
implementation artifacts. COMMIT simply means the data are safely
stored.
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.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
