如何在 SQL Server 2005 中解决 dirty read 等并发问题

结论

这三类问题不能统一靠“提高事务隔离级别”解决：

• Dirty read：SQL Server 默认的 READ COMMITTED 已禁止读取未提交数据，因此不要使用 NOLOCK 或 READ UNCOMMITTED。不过，示例中的发票问题更准确地说，是报表截止时间与付款提交时间发生了竞争。解决时需要明确业务截止规则，并在发送前复核。
• Non-repeatable read：预订航班座位时，不要采用“先查询，再无条件更新”的方式。可以使用带条件的原子 UPDATE，也可以用 UPDLOCK 锁住座位，直到事务结束。
• Lost Updates：金额变更应在数据库中通过 AmountDue = AmountDue + @Delta 原子更新。如果业务必须先读取再计算，则使用 rowversion 进行乐观并发控制。

相关操作都应放在尽可能短的显式事务中，同时正确处理死锁、超时和并发冲突。

1. 发票与付款同时发生

这不一定是 dirty read

真正的 dirty read，是读取了其他事务尚未提交的数据。例如设置：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

或者在查询中使用：

SELECT ...
FROM CustomerAccount WITH (NOLOCK);

SQL Server 2005 默认使用 READ COMMITTED。如果没有 NOLOCK 等提示，查询不会读到其他事务尚未提交的付款。

即使没有 dirty read，下面的执行顺序仍完全可能发生：

1. 发票查询先读到欠款 $1000。
2. 付款事务随后提交。
3. 报表继续按查询时的数据生成发票。

数据库无法自行判断，同样发生在 1

AM 的付款究竟应该计入本次发票，还是留到下一批处理。业务需要先定义截止规则，例如：

• 报表开始前已经提交的付款计入本批发票；
• 报表开始后提交的付款计入下一批；
• 发票发送前保留一段宽限期，并再次核对余额。

禁止脏读

先确认报表没有使用 NOLOCK 或 READ UNCOMMITTED：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT CustomerId, AmountDue
FROM dbo.CustomerAccount
WHERE AmountDue > 0;

保证整份报表读取一致

如果报表包含多个查询，普通的 READ COMMITTED 可能让不同查询看到不同时间点的数据。SQL Server 2005 可以启用快照隔离：

ALTER DATABASE Billing
SET ALLOW_SNAPSHOT_ISOLATION ON;

然后在报表事务中使用：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

SELECT CustomerId, AmountDue
FROM dbo.CustomerAccount
WHERE AmountDue > 0;

-- 其他报表查询

COMMIT TRANSACTION;

快照隔离可以让整笔报表事务看到一致的数据版本，但不会包含事务开始后才提交的付款。它解决的是报表内部的数据一致性，不是业务截止时间问题。

启用快照隔离会增加 tempdb 的版本存储负担，因此需要提前评估容量以及长事务带来的影响。

发送前复核余额

如果业务希望尽量避免向已经付款的客户发送发票，可以先生成待发送记录，在真正发送前再次检查：

UPDATE i
SET i.Status = 'Cancelled'
FROM dbo.InvoiceQueue AS i
JOIN dbo.CustomerAccount AS a
    ON a.CustomerId = i.CustomerId
WHERE i.Status = 'Pending'
  AND a.AmountDue <= 0;

外部发送程序只处理状态仍为 Pending 的记录。复核和“领取待发送记录”最好合并成一个原子操作，以免复核后余额再次变化。

如果付款与发票生成必须严格串行，可以针对同一个客户使用 UPDLOCK 和 HOLDLOCK。但让整份报表长期持有大量锁，通常会严重阻塞正常付款。因此，更合适的方案是明确截止时间，使用快照生成报表，并在发送前复核。

2. 航班座位被重复预订

使用条件更新最可靠

不要先查询座位状态，过一段时间再无条件更新。应将“检查空闲”和“标记已预订”合并到一条语句中：

BEGIN TRANSACTION;

UPDATE dbo.FlightSeat
SET Status = 'Booked',
    BookingId = @BookingId
WHERE FlightId = @FlightId
  AND SeatNo = @SeatNo
  AND Status = 'Available';

IF @@ROWCOUNT = 0
BEGIN
    ROLLBACK TRANSACTION;
    RAISERROR('Seat is already booked.', 16, 1);
    RETURN;
END;

-- 写入订单、乘客等相关记录

COMMIT TRANSACTION;

并发执行时，SQL Server 会对更新的记录加排他锁。只有一个事务能把座位从 Available 改为 Booked，其他事务得到的 @@ROWCOUNT 会是 0。

“Seat is already booked”表示发生了正常的并发冲突，并非数据损坏。应用应该提示用户重新选择座位，不能自动覆盖已有预订。

必须先读取时使用更新锁

如果预订前必须读取票价、舱位等信息，可以在事务中提前申请更新锁：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;

SELECT Status, Price
FROM dbo.FlightSeat WITH (UPDLOCK, HOLDLOCK)
WHERE FlightId = @FlightId
  AND SeatNo = @SeatNo;

-- 检查状态并完成相关业务处理

UPDATE dbo.FlightSeat
SET Status = 'Booked',
    BookingId = @BookingId
WHERE FlightId = @FlightId
  AND SeatNo = @SeatNo
  AND Status = 'Available';

IF @@ROWCOUNT = 0
BEGIN
    ROLLBACK TRANSACTION;
    RAISERROR('Seat is already booked.', 16, 1);
    RETURN;
END;

COMMIT TRANSACTION;

UPDLOCK 会从读取阶段开始持有更新锁，HOLDLOCK 则相当于对该表引用采用 SERIALIZABLE 语义。锁会一直保留到事务结束。

这类事务必须足够短。持锁期间不能等待用户输入、填写乘客资料或完成支付，否则会造成长时间阻塞。常见做法是创建带过期时间的临时占座记录，而不是一直保持数据库事务。

还应为同一航班中的座位建立唯一约束，作为最后一道保护：

CREATE UNIQUE INDEX UX_FlightSeat_FlightId_SeatNo
ON dbo.FlightSeat (FlightId, SeatNo);

3. 避免客户余额发生 Lost Update

使用原子增量更新

常见的错误做法是，两个柜台都先读到 $2000，各自在应用中计算，然后分别把 $1000 和 $2500 作为绝对余额写回。后提交的值会覆盖先提交的值。

正确做法是直接在数据库中表达余额变化。

柜台一录入付款：

UPDATE dbo.CustomerAccount
SET AmountDue = AmountDue - 1000.00
WHERE CustomerId = @CustomerId;

柜台二录入购买：

UPDATE dbo.CustomerAccount
SET AmountDue = AmountDue + 500.00
WHERE CustomerId = @CustomerId;

即使两条语句同时执行，SQL Server 也会通过更新锁和排他锁让它们依次完成。最终结果是：

2000 - 1000 + 500 = 1500

实际系统不应直接写死金额，而应使用参数：

UPDATE dbo.CustomerAccount
SET AmountDue = AmountDue + @AmountChange
WHERE CustomerId = @CustomerId;

付款传入负数，新增消费传入正数。

同时记录余额流水

只保存一个余额不利于审计。更稳妥的做法是在同一个事务中写入流水，并更新汇总余额：

SET XACT_ABORT ON;

BEGIN TRANSACTION;

INSERT INTO dbo.AccountTransaction
(
    CustomerId,
    AmountChange,
    TransactionType,
    CreatedAt
)
VALUES
(
    @CustomerId,
    @AmountChange,
    @TransactionType,
    GETDATE()
);

UPDATE dbo.CustomerAccount
SET AmountDue = AmountDue + @AmountChange
WHERE CustomerId = @CustomerId;

IF @@ROWCOUNT = 0
BEGIN
    ROLLBACK TRANSACTION;
    RAISERROR('Customer account does not exist.', 16, 1);
    RETURN;
END;

COMMIT TRANSACTION;

流水表保存事实记录，余额字段只是汇总结果。出现异常时，可以根据流水重新核对余额。

必须先读后写时使用 rowversion

如果新值无法表示为简单的增量更新，可以增加版本列：

ALTER TABLE dbo.CustomerAccount
ADD RowVer rowversion;

读取数据时同时取得版本：

SELECT AmountDue, RowVer
FROM dbo.CustomerAccount
WHERE CustomerId = @CustomerId;

更新时检查版本是否仍然相同：

UPDATE dbo.CustomerAccount
SET AmountDue = @NewAmount
WHERE CustomerId = @CustomerId
  AND RowVer = @OriginalRowVer;

IF @@ROWCOUNT = 0
BEGIN
    RAISERROR(
        'The account was modified by another transaction. Reload and retry.',
        16,
        1
    );
END;

如果另一个柜台已经修改了这条记录，RowVer 就会发生变化，当前更新不会覆盖新数据。应用需要重新读取并计算，再决定是否重试。

隔离级别不能代替正确的更新方式

SQL Server 2005 常见的隔离级别包括：

提高隔离级别会增加锁等待、阻塞或版本存储开销。即使采用较高的隔离级别，不合理的“读取旧值，在应用中计算，再写回绝对值”仍可能引发冲突或死锁。

这三个场景可以分别采用以下方案：

• 发票：使用 READ COMMITTED 或 SNAPSHOT，明确报表截止时间，并在发送前复核。
• 座位：使用带状态条件的原子 UPDATE；流程较复杂时，再配合 UPDLOCK。
• 余额：使用原子增量更新；复杂计算则通过 rowversion 进行乐观并发控制。

事务还应捕获 SQL Server 死锁错误 1205。确认操作具备幂等性后，可以进行有限次数的重试。事务范围越短，访问表和记录的顺序越一致，发生死锁的概率就越低。

备注：内容仅供参考。