On Friday, 9 January, 2015 16:43, James K. Lowden <jklow...@schemamania.org> said: >On Wed, 07 Jan 2015 21:47:24 -0700 "Keith Medcalf" <kmedc...@dessus.com> wrote:
>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. :-) Yes, it is a part of the SQL Standard isolation levels in excess of the default default of READ COMMITTED. Different vendors call the isolation levels by different names -- MS SQL Server calls them Read Uncomitted, Read Committed, Repeatable Read, Snapshot, and Serializable; DB/2 calls them No Commit (similar to autocommit), Uncommitted Read, Cursor Stability, Read Stability, Repeatable Read. DB/2 Repeatable Read = MS Serializable (which is the SQL Standard Serializable isolation level). Cursor Stability = Read Committed. These describe the isolation to be maintained be simultaneous Units of Work. The standard Read Committed/Cursor Stability level does not isolate the unit of work from changes made (and committed) by other units of work. The higher levels do. For example, if a unit of work is executed with Read Committed the two select statements may see changes to the database which were committed on another unit of work. With Serializable / Repeatable Read, those changes will not be visible. In fact, the database will not permit the updates to rows that have been read at the Serializable / Repeatable Read level. http://publib.boulder.ibm.com/iseries/v5r1/ic2924/info/db2/rbafzmstisol.htm http://msdn.microsoft.com/en-us/library/ms173763.aspx >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. Indeed it does. It is up to the programmer to set the isolation levels required and then set the transaction (unit of work) boundaries correctly. Most only know or use the default Read Committed / Cursor Stability level which is usually the default mode, which works as you describe. SQLite in standard journal mode uses "read committed/cursor stability" isolation between connections to the same database. I have double checked, and if you surround multiple selects with explicit begin/end, then that unit of work cannot be interrupted by updates and hence you then have "Repeatable Read" isolation. Whether or not this is intended it must work this way because the introduction of a unit of work means that there is intent to update at some point and the database, within the unit of work, must maintain cursor stability (at least) until an actual update occurs which will escalate the shared lock to an exclusive lock. In fact, if you do this, you will be unable to commit the update transaction until the other unit of work is completed (even if it only contains select statements). In WAL mode the transaction mode is always snapshot between units of work on separate connections, but only one connections' unit of work can convert to "update" at a time. Other connections doing just selects between a BEGIN COMMIT will continue to see the same snapshot even if another connection commits database changes. It appears that the concurrent snapshot can also escalate to an update lock is released by another and sometimes it cannot -- if it is the head of the uncommitted WAL, it can, if it is not, it cannot. >Interesting discussion, Keith. Thanks for the clarification. --- 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
