SELECT FOR UPDATE案例分析

案例1：解决更新丢失问题

丢失更新是另一个锁导致的问题，简单来说其就是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。例如：

1. 事务T1将行记录r更新为v1，但是事务T1并未提交。
2. 与此同时，事务T2将行记录r更新为v2，事务T2未提交。
3. 事务T1提交。
4. 事务T2提交。

但是，在当前数据库的任何隔离级别下，都不会导致数据库理论意义上的丢失更新问题。这是因为，即使是READ UNCOMMITTED的事务隔离级别，对于行的DML操作，需要对行或其他粗粒度级别的对象加锁。因此在上述步骤2中，事务T2并不能对行记录r进行更新操作，其会被阻塞，直到事务T1提交。

虽然数据库能阻止丢失更新问题的产生，但是在生产应用中还有另一个逻辑意义的丢失更新问题，而导致该问题的并不是因为数据库本身的问题。实际上，在所有多用户计算机系统环境下都有可能产生这个问题。简单地说来，出现下面的情况时，就会发生丢失更新：

1. 事务T1查询一行数据，放入本地内存，并显示给一个终端用户User1。
2. 事务T2也查询该行数据，并将取得的数据显示给终端用户User2。
3. User1修改这行记录，更新数据库并提交。
4. User2修改这行记录，更新数据库并提交。

显然，这个过程中用户User1的修改更新操作“丢失”了，而这可能会导致一个“恐怖”的结果。设想银行发生丢失更新现象，例如一个用户账号中有10000元人民币，他用两个网上银行的客户端分别进行转账操作。第一次转账9000人民币，因为网络和数据的关系，这时需要等待。但是这时用户操作另一个网上银行客户端，转账1元，如果最终两笔操作都成功了，用户的账号余款是9999人民币，第一次转的9000人民币并没有得到更新，但是在转账的另一个账号却会收到这9000元，这导致的结果就是钱变多，而账不平。也许有读者会说，不对，我的网银是绑定USB Key的，不会发生这种情况。是的，通过USB Key登录也许可以解决这个问题，但是更重要的是在数据库层解决这个问题，避免任何可能发生丢失更新的情况。

要避免丢失更新发生，需要让事务在这种情况下的操作变成串行化，而不是并行的操作。即在上述四个步骤的1中，对用户读取的记录加上一个排他X锁。同样，在步骤2的操作过程中，用户同样也需要加一个排他X锁。通过这种方式，步骤2就必须等待一步骤1和步骤3完成，最后完成步骤4。下表所示的过程演示了如何避免这种逻辑上丢失更新问题的产生。

丢失更新问题的处理方法

Time	会话A	会话B
1	BEGIN;
2	SELECT cash into @cash FROM account WHERE user = pUser FOR UPDATE;
3		SELECT cash into @cash FROM account WHERE user = pUser FOR UPDATE; #等待
	……	……
m	UPDATE account SET cash=@cash-9000 WHERE user=pUser
m+1	COMMIT
m+2		UPDATE account SET cash=@cash-1 WHERE user=pUser
m+3		COMMIT

有读者可能会问，在上述的例子中为什么不直接允许UPDATE语句，而首先要进行SELECT...FOR UPDATE的操作。的确，直接使用UPDATE可以避免丢失更新问题的产生。然而在实际应用中，应用程序可能需要首先检测用户的余额信息，查看是否可以进行转账操作，然后再进行最后的UPDATE操作，因此在SELECT与UPDATE操作之间可能还存在一些其他的SQL操作。

我发现，程序员可能在了解如何使用SELECT、INSERT、UPDATE、DELETE语句后就开始编写应用程序。因此，丢失更新是程序员最容易犯的错误，也是最不易发现的一个错误，因为这种现象只是随机的、零星出现的，不过其可能造成的后果却十分严重。

实验一：两个事务使用SELECT FOR UPDATE处理同一行数据（WHERE条件是主键）

-- 创建测试表
CREATE TABLE account (
    cash DECIMAL(10, 2),
    user VARCHAR(255),
    PRIMARY KEY (user)
);

-- 填充数据
INSERT INTO account (cash, user) VALUES (1000.00, '张三');
INSERT INTO account (cash, user) VALUES (10000.00, '李四');

BEGIN;

SELECT cash into @cash
FROM account
WHERE user = '张三' FOR UPDATE;  -- 第一个事务执行到这里

UPDATE account
SET cash=@cash-100
WHERE user='张三';

COMMIT;

BEGIN;

SELECT cash into @cash
FROM account
WHERE user = '张三' FOR UPDATE;  -- 第二个事务执行到这里，可以发现阻塞，不能继续执行后面的语句

UPDATE account
SET cash=@cash-100
WHERE user='张三';

COMMIT;

实验二：两个事务使用SELECT FOR UPDATE处理不同行数据（WHERE条件是主键）

-- 创建测试表
CREATE