On Wednesday, 7 January, 2015 20:01, James K. Lowden <jklow...@schemamania.org>
said:
>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?
No. However, when you perform an update in the same transaction (and
connection) as you are performing the select, when the select is not completed
yet, and you commit on the connection, it is to be expected that AHWBL.
>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?
I am not certain exactly where the COMMIT performs the commit. It could very
likely commit and then turn autocommit back on. Whatever so, it makes no
difference really.
>> 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
Except that this is not what the OP is doing. The OP is performing a COMMIT in
the middle of the select running. He is doing this:
1. establish the lock
2. select the first of one of many rows of data
3. release the lock forcibly (COMMIT)
4. read the next row of data from the select
5. release the lock forcibly (COMMIT)
... lather rinse repeat steps 4 and 5 until you reach the end of the selected
rows
>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.)
No, it was the other persons choice to do this. He could have run the select
entirely, then done the updates. Or he could have chosen to do the updates on
another connection (which would be isolated from the connection running the
select) using WAL.
>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?
It is not. There IS NOT TWO CONNECTIONS. There is only one connection. The
same connection is interspersing updates while running the select, and
attempting to commit the single connection in the middle of the select, but
still expecting that the select will "carry on".
>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.
You are correct. The way to do this is with journal_mode=WAL and two separate
connections, one for the select, and one for the update.
In the posited code, the COMMIT is not associated with the UPDATE, it is
associated with BOTH the SELECT and the UPDATE because they both occur on the
same connection.
>> 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?
Without WAL, readers block writers and writers block readers. With WAL,
readers do not block writers -- they have ADDITIONAL processing that isolates
their view of the database to a point in time before the writer obtained its
lock. The implementation does not vary -- the basic processing is the same --
WAL adds additional things, but does not change the fact that a SELECT requires
a shared lock -- it merely adds some lookaside semantics.
>> 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.
And that the "view state of the database is released". That is, 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 fact I know that they do, because you can
perform steps like the above to do master/detail processing and ensure
consistent results, even though some other process makes changes (or even
deletes the whole master/detail shebang while query 2 is executing. It is only
if you try to update what is now non-existant or changed rows from your view do
you get an error that your data is stale.
>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.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
