thanks. I'll try it out, but sounds true enough; so there is no isolation level where transaction state keeps track of all the read versions of any item read so far by the transaction ? The main question is whether there's a way of avoiding implementing either those patterns of offline optimistic locking (version checking) or offline pessimistic locking ( lock table checking) , when you've got interactive client applications ? If there was an isolation level that kept track of the versions of items a transaction has read, and there was a "commit with continue" command, then you could avoid that extra work in clients - or maybe warping transactions for this purpose makes it more difficult to write transactions for the usual batch processing purposes. On Mon Oct 15 5:28 , "Trevor Talbot" sent: >On 10/15/07, Syan Tan wrote: > >> >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. >> >> you read the old value at the beginning of the transaction, and >> you don't re-read it , assuming it is infrequently changed, so >> if someone updates it concurrently, when you try to write, then >> you detect the conflict, and rollback to the savepoint. > >Transactions don't operate based on what you've read. "UPDATE ... >WHERE ..." finds the row(s) to update using the WHERE clause right >now, not based on any previously-read values. It does not know what >you've read before. > >The only difference is in the data you _can_ read. For the >SERIALIZABLE isolation level, that data was decided at the beginning >of the transaction. A row that was updated by another transaction >will make the version that you can see effectively "read only", so >when the UPDATE tries to change it, there will be a transactional >conflict due to the isolation level. > >> You DONT want a serializable transaction, because then you can't read >> the other committed value after rolling back to the savepoint. > >Correct. But the READ COMMITTED isolation level does not limit what >data you can see at the beginning of the transaction, so an UPDATE >will always find the latest version of a row. There is no conflict as >far as the transaction is concerned. > >> >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 >> >> this is the same as using your own version ids and incrementing them >> within the application, which would leave the xmin within postgresql's >> domain , and would also mean the application's sql is not tied >> to postgresql. > >Yes. You were asking for a feature in PostgreSQL that doesn't match >standard transaction semantics though, so I figured you wouldn't mind >a PostgreSQL-specific option :) > > >Assuming READ COMMITTED isolation level: > >> begin; >> select x from t1 where id=2; >> (store in variable x0 , display to user) >> ...(much later) >> (user changes stored x, at client, now x1) >> savepoint a; >> >> answ = n; >> >> do: >> try: >> update t1 set x=x1 where id = 2; >> commit-and-continue; >> catch: >> rollback to savepoint a; >> select x from t1 where id=2 ( store as x2) >> if (x2 x0) notify user "x has changed from x0 to x2, continue to write x1?" >> input answ; >> >> while answ ==y; >> >> >> In the first pass of the loop, the transaction hasn't read x a second >> time so the transaction state for x is at x0, > >Transaction state is not based on what you've read; it doesn't know. > >> if x has been changed by another transaction's commit, then the catch will >> execute , > >The UPDATE will find the latest version of the row. The change made >by the other transaction is not a problem at this isolation level (you >can see it), so the UPDATE will simply proceed and change it anyway. >The catch block will never execute. > >> If the transaction was serializable , and another transaction has committed, >> this would never work, because a commit would nullify the other >> transactions write without this transaction ever having seen the other >> transactions write, so this transaction would always be forced to rollback. > >Exactly. But without SERIALIZABLE isolation, any operations you >perform always see the other transaction's write, so there is never >any conflict. > >Also keep in mind that MVCC is not the only way to implement >transactions; pure locking is more common in other databases. In the >locking model, most transactions prevent others from writing until >after they are finished. Rows simply can't have different versions >(and of course concurrent performance is awful). ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
