On Friday, 2 January, 2015 16:26, James K. Lowden <[email protected]> said:
>On Thu, 25 Dec 2014 05:32:45 -0700 (MST) Rick Kelly <[email protected]> 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. >A common misconception is that BEGIN TRANSACTION "takes a lock" in some >sense. It doesn't; it marks a point in logical time that will be >concluded with COMMIT/ROLLBACK. Locks, if any, are implicit in the >SELECT itself. 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). 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. 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. 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). This is inherent in how WAL works. Just because WAL is not in effect does not alter the fundamental workings of the transaction system. 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) which would be required in order for the loop semantics posited by the OP to work correctly. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
