On 10/14/07, Syan Tan <kittylitter@xxxxxxxxxxxxx> wrote: > I meant commit and continue current transaction. The transaction is opened > on the user application caching composite data from many tables regarding > a root object. Because almost all applications cache data, there is apparently > a pattern "optimistic offline lock" where orb middleware basically adds > a version field to rows , because transactions are usually begun just > when the user has done a modification to a displayed value, and now > wants to change an old cached value which he believes is the current value. Well, transactional semantics won't help you here. In order to detect a change occurred, what you want is a SERIALIZABLE transaction: you want the update to fail if the row it matches is no longer the same version as your snapshot. However, in order to read the new value to decide if you want to update it anyway, you need to leave your current snapshot. As soon as you do that, ALL previously read values lose the update checks that snapshot provided you. If you use a READ COMMITTED transaction, there is no check, since the update can see the new value itself. Nested transactions or the ability to commit some data without leaving the current transaction won't get you want you want either, since you're really looking for per-row behavior, not per-transaction. > The middleware starts a transaction, and reads the version number, and > if it has been incremented since the initial transaction that read > the value and the version number, it then informs the user that > a new old value exists, and whether he wants to overwrite it. A way to do this using PostgreSQL's own row version data came up recently on this list. Have a look at this post and the one following it: http://archives.postgresql.org/pgsql-general/2007-10/msg00503.php ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
