Using optimistic locking in PostgreSQL

digoal

[注] 取材自《PostgreSQL 9 Administration Cookbook》
本书电子版可以到以下网址下载：
http://www.boobooke.com/bbs/thread-59726-1-1.html

首先看如下一个场景:
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;

在上面这个场景中，用户在做UPDATE之前先锁住需要被UPDATE的记录，进行完部分运算操作后再进行更新的操作，以便确保在更新前这部分记录不会被其他用户修改。
对于这种场景可以在应用设计时考虑使用乐观锁来获得整体性能的提升，乐观锁假设大多数情况下用户将要更新的记录在一定时间段内不会被其他用户更新。
做法举例如下:
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;
在上面这个场景中，用户首先将需要被更新的记录取出，运算完后，更新时与前面取出的记录进行比较。如果一致则更新成功。
【这种场景也存在一定的局限性，最好在设计时每个表都有PK，并且PK不会被更新。否则存在安全隐患】
使用场景扩展-1：
SELECT A.*, (A.*::text) AS OLDACCINFO
  FROM ACCOUNTS A WHERE HOLDER_NAME ='BOB';
这条SQL可以根据用户的需求，进行一定的调整。比如只要确保某些字段没有被其他用户更新就认为接下来的更新是有效的。如last_charge(标识最后一次更新的时间),balance(用户帐户余额) 这两个字段没有被其他用户更新的话就认为下一次更新是有效的。而其他字段如EMAIL这个不太重要的字段允许在运算过程中被更新.

使用场景扩展-2：
CREATE OR REPLACE FUNCTION consume_balance (
   i_username text, i_amount numeric(10,2), max_credit numeric(10,2),
   OUT success boolean, OUT remaining_balance numeric(10,2)) AS
$$
BEGIN
    UPDATE accounts SET balance = balance - i_amount
     WHERE username = i_username
       AND balance - i_amount > - max_credit
RETURNING balance
      INTO remaining_balance;
   IF NOT FOUND THEN
       success := FALSE;
       SELECT balance
         FROM accounts
        WHERE username = i_username
         INTO remaining_balance;
   ELSE
       success := TRUE;
   END IF;
END;
$$ LANGUAGE plpgsql;

以上场景是函数中使用乐观锁的示例，
在函数中使用的好处是简化交互过程，在同一个SESSION中复用执行计划等.
调用 :
SELECT * FROM consume_balance ('bob', 7, 0);