On 27-6-2014 19:25, Ann Harrison wrote: > > >> On Jun 23, 2014, at 3:57 PM, Nikolay Samofatov >> <nikolay.samofa...@red-soft.biz> wrote: >> >> Some records for a join can be read before a transaction is committed, and >> some after. Same with EXISTS. It can see different set of commits from the >> one when main row was read. >> You can see partial commits in results, even inside a single row returned by >> the query. >> Nobody is ready for this, this is CRAZY, nobody expects this. If this data >> is used for any remotely important purpose, you will get whammed. > > Right, that's the beauty of read-committed. It's not stable. And, in > Firebird, it performs no better than an isolation mode that is stable. So > why offer it at all? In both Interbase and NuoDB, major customers to be > insisted that in every database they used, they could see new data without > starting a new transaction. And that was the way databases behaved. No > amount of reference to scholarly literature could convince them in the face > their experience. So we put in a bad mode to close big sales. > >> The fact that inconsistency shows up infrequently, under parallel load and >> is not easily >> reproducible, sets you up to be eventually burned. And once burned you will >> hesitate to trust DMBS that behaves in such ways. > > And that's the way Oracle, MS SQL and others behaved at the time. >> >> This is not normal. This is a BUG. >> > The bug is the existence of read committed. The excuse is compatibility with > other databases, If they now offer cursor stabilty, then Firebird should > too. If there's a performance cost, make it an option. May I suggest using > the word "stability" rather than "sensitivity"? At least to my ear (and > feeble brain) sensistivity doesn't suggest seeing changes in route. > Stability suggests not seeing concurrent changes.
The term sensitivity comes from the SQL Standard, lets not invent new terms. Sensitivity is a property of the cursor, not of the transaction (although you probably can refuse certain sensitivities based on the transaction properties). SQL:2011 Foundation says: "The following paragraphs define several terms used to discuss issues relating to a cursor's operational sensitivity property: A change to SQL-data is said to be independent of a cursor CR if and only if it is not made by an <update statement: positioned> or a <delete statement: positioned> that is positioned on CR. A change to SQL-data is said to be significant to CR if and only if it is independent of CR, and, had it been committed before CR was opened, would have caused the sequence of rows in the result set descriptor of CR to be different in any respect. A change to SQL-data is said to be visible to CR if and only if it has an effect on the sequence of rows SR of the result set descriptor of CR by inserting a row in SR, deleting a row from SR, changing the value of a column of a row of SR, or reordering the rows of SR. If a cursor is open, and the SQL-transaction in which the cursor was opened makes a significant change to SQL-data, then whether that change is visible through that cursor before it is closed is determined as follows: — If the cursor is insensitive, then significant changes are not visible. — If the cursor is sensitive, then significant changes are visible. — If the cursor is asensitive, then the visibility of significant changes is implementation-dependent. If a holdable cursor is open during an SQL-transaction T and it is held open for a subsequent SQL-transaction, then whether any significant changes made to SQL-data (by T or any subsequent SQL-transaction in which the cursor is held open) are visible through that cursor in the subsequent SQL-transaction before that cursor is closed is determined as follows: — If the cursor is insensitive, then significant changes are not visible. — If the cursor is sensitive, then the visibility of significant changes is implementation-defined. — If the cursor is asensitive, then the visibility of significant changes is implementation-dependent." -- Mark Rotteveel ------------------------------------------------------------------------------ Open source business process management suite built on Java and Eclipse Turn processes into business applications with Bonita BPM Community Edition Quickly connect people, data, and systems into organized workflows Winner of BOSSIE, CODIE, OW2 and Gartner awards http://p.sf.net/sfu/Bonitasoft Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel