Performance & Concurrency - PostgreSQL 9 Administration - page 257

Using optimistic locking
If you are doing lots of transactions which look like the following:
BEGIN;
SELECT * FROM ACCOUNTS WHERE HOLDER_NAME ='BOB' FOR UPDATE;
<do some calculations here>
UPDATE ACCOUNTS SET BALANCE = 42.00 WHERE HOLDER_NAME ='BOB';
COMMIT;
Then you may gain some performance by moving from explicit locking ( SELECT … FOR
UPDATE ) to optimistic locking.
Optimistic locking assumes that others don't update the same record, and checks this at
update time, instead of locking the record for the time it takes to process the information on
the client side.
How to do it...
Rewrite your application so that the preceding transaction is transformed into something like
the following:
BEGIN;
SELECT A.*, (A.*::text) AS OLDACCINFO
FROM ACCOUNTS A WHERE HOLDER_NAME ='BOB';
<do some calculations here>
UPDATE ACCOUNTS SET BALANCE = 42.00
WHERE HOLDER_NAME ='BOB'
AND (A.*::text) = <OLDACCINFO from select above>;
COMMIT;
Then, check that the UPDATE did update one row in your application code. If it did not, then
the account for bob was modified between SELECT and UPDATE , and you probably need to
re-run your whole transaction.
How it works...
Instead of locking Bob's row for the time the data from select is processed in the client, it
queries the old state of Bob's account record in variable OLDACCINFO , and then uses this
value to check that the record has not changed.
You can also save all fields individually, and then check them all in UPDATE query, or if you
can have an automatic last_change field, then you can use this. Or, if you actually care only
about a few fields changing, such as BALANCE and can ignore others, say E_MAIL , then you
can only check the relevant fields in UPDATE .