>>> Paul Schlie <sch...@comcast.net> wrote: >> Kevin Grittner <kevin.gritt...@wicourts.gov> wrote: >> There is a related thread on which I'm attempting to come up with >> documentation to assist those familiar with true serializable >> behavior who are attempting to recognize application coding >> patterns where the differences between that and snapshot isolation >> are material, with tips on how to handle these differences. There >> seems to be some question whether the patterns in which anomalies >> occur are common enough to merit comment. >> >> If you could reference any concise and accessible work on these >> anomalies and practical workarounds in application code, it would >> be much appreciated. > > Personally; although compliance may reduce the execution performance > of such so designated transactions, it will correspondingly warrant > correct results, and should be the goal rather than documenting > non-conformance; as those who wish to embed more direct control over > transaction evaluation into their specification to enable their > improved concurrent execution efficiency by utilizing more relaxed > evaluation semantics, remain free to do without penalty. (Simple > examples of the risk of non-compliance already seem sufficiently > identified in your example and first reference cited). > > Merely documenting that transactions designated to be evaluated at > the isolation level SERIALIZABLE may not yield expected results, as > currently identified, seems sufficient in the short term; and as/if > enough interest develops otherwise, so may an effort to warrant > compliance; I suspect. > > (as that known to most often be fine, can't be relied upon in > practice) Thank you for your perspective. I'm not sure that I totally followed you, so let me restate to see if it sounds right to you. You are suggesting that minimal discussion of the problem, the initial example I provided, and more discussion of how to ensure correct semantics would be what is needed? Filling in more detail if interest is expressed by users? If so, the draft of a partial replacement for the partial replacement of text in "Serializable Isolation versus True Serializability" may be close to what you're suggesting -- if additional guidance on when to use what additional locks is provided. I'll paste below my signature for comment. It's a little rough yet, but looking to see if I'm on the right track. The first paragraph is a slightly modified form of a suggestion from Robert Haas in: http://archives.postgresql.org/pgsql-hackers/2008-12/msg01732.php -Kevin PostgreSQL's MVCC framework, snapshot isolation, and limited automatic row-level locking permit a greater degree of concurrency than some other databases; however, even when the transaction isolation level is set to serializable, serialization anomalies can occur in some situations. When it is important to prevent these anomalies, explicit row-level or table-level locking can be used at the expense of reduced concurrency. Since PostgreSQL protects a serializable transaction against changes in the view of the data, and uses locks to prevent modification of data which is being modified by a concurrent transaction, the anomalies can only occur when a transaction reads data which is modified by a concurrent transaction, and uses that as the basis of database modifications which are read by a concurrent transaction. Data consistency checks at the application level have a problem with this in general, and are addressed in section 13.4. Some examples of other types of anomalies follow, with suggestions on how to use explicit locking to prevent the anomalies where needed. Consider a system which involves recording receipts, each of which must go into a daily deposit. There is a control table with one row containing the current deposit date for receipts. Each transaction which is inserting a receipt selects the deposit date from the control table within its transaction, and uses it for the receipt's deposit date. Somewhere mid-afternoon the control table's date is updated, all subsequent receipts should fall into the new day, and a report is run listing the receipts for the day and giving the deposit total. If all transactions involved were truly serializable, any SELECT of receipts for a date prior to the deposit date of the control table would see the complete, final set of receipts. Under the PostgreSQL implementation, unless explicit locking is used, although data eventually gets to that state there can be a window of time during which a SELECT can return an incomplete list of receipts for a date which appears to be closed, even if all transactions for modifying and viewing data are SERIALIZABLE. This window of time runs from the commit of the transaction which updated the control table until the commit of any pending transactions which are inserting receipts and which obtained a snapshot before the update of the control table. To prevent this anomaly, a lock can be taken out on the receipt table to block all modification to that table. This should be done in the transaction which will update the control table, and should be acquired before the transaction selects or modifies any data.
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
