On Wed, Jan 7, 2015 at 11:47 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
> 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 OP wrote: On Thu, Dec 25, 2014 at 7:32 AM, Rick Kelly <rpke...@gci.net> wrote: >*SNIP* > The database file is located in the same folder as the server. The server is > multi-threaded. I'm using library version 3.8.7.4 > The database file is opened as follows: > 1. sqlite3_enable_shared_cache(%SQLITE_OPEN_SHAREDCACHE) -- *Presumably* enabling SQLite Shared-Cache Mode likely signals his intention to HAVE two or more connections, right? If so, with that comes the Shared-Cache Locking Model at https://www.sqlite.org/sharedcache.html -- -- -- --Ô¿Ô-- K e V i N > > >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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
