SQL行锁和事务

Question

我对关系数据库真的很陌生。我正在做一个涉及财务的项目，所以我希望任何影响平衡的操作不会同时发生，我想使用锁来实现这一点，但是我不确定如何使用它们。我现在有一个愿景:我希望每个操作都有一个单独的表，并在用户表中有一个余额字段，其值将从所有相关的表中派生。令人遗憾的是，我实际上永远不会更新现有的记录-只会添加它们。我希望确保在这些表中一次只为每个用户插入一条记录。例如:3个事务同时发生，因此3个记录将添加到任何相关的表中。其中两条记录具有相同的userid，是指向我的users表的外键，而另一条记录具有不同的userid。我希望我的具有相同外键的记录是流水线的，而另一个记录可以在任何时候完成。我该如何实现这一点？有没有更好的方法来解决这个问题？

Answer 1

1. 对所有表使用ENGINE=InnoDB。
2. 使用事务：

开始；完成单个动作提交的所有工作；

单个操作的典型示例是从一个帐户中提取资金，然后将其添加到另一个帐户中。删除将包括检查透支，在这种情况下，您将拥有ROLLBACK而不是COMMIT的代码。

您获得的锁可以确保单个操作的所有操作要么完全完成，要么什么都不做。这甚至适用于系统在BEGIN和COMMIT之间崩溃的情况。

没有begin和commit，但有了autocommit=ON，每条语句都隐式地包含在begin和commit中。这就是前面答案中的原子示例是‘UPDATE’。但是，如果从一个账户中扣除的钱需要添加到另一个账户中，那么在UPDATE之后发生崩溃会发生什么呢？钱就消失了。所以你真的需要

 BEGIN;
 if not enough funds, ROLLBACK and exit
 UPDATE to take money from one account
 UPDATE to add that money to another account
 INSERT into some log or audit trail to track all transactions
 COMMIT;

在每个步骤之后进行检查--回滚，并对任何意外错误采取规避措施。

如果两个(或更多)操作同时发生会发生什么？

• 一个等待另一个。
• 出现死锁并强制回滚。

但是，在任何情况下，数据都不会被弄乱。

再说一句……在某些情况下，您需要FOR UPDATE

BEGIN;
SELECT some stuff from a row FOR UPDATE;
test the stuff, such as account balance
UPDATE that same row;
COMMIT;

FOR UPDATE对其他线程说：“不要插手这一行，我很可能会修改它；请等我完成。”如果没有FOR UPDATE，另一个线程可能会潜入并耗尽您认为在那里的钱的帐户。

对你的一些想法的评论：

• 一个表通常足以满足多个用户及其帐户的需求。它将包含每个帐户的“当前”余额。我提到了一个“日志”；那将是一个单独的表；它将包含一个“历史”(而不仅仅是“当前”的info).
• FOREIGN KEYs )，这在本文的讨论中几乎无关紧要。它们有两个用途:验证另一个表中有一个应该在那里的行；以及隐式地创建一个INDEX，使该检查成为faster.
• Pipelining？如果您每秒处理的事务不超过100个，那么您只需要担心BEGIN..COMMIT逻辑。
• 的“相同时间”和“同时”是错误使用的术语。两个用户同时访问数据库的可能性很小--考虑到浏览器延迟、网络延迟、操作系统延迟等。另外，这些步骤中的大多数都会强制活动转到单个文件。网络强制一条消息在另一条消息之前到达。同时，如果你的一个“事务”花了0.01秒，谁会关心“同时”的请求是否必须等待它完成。重点是，如果需要，我所描述的将强制“等待”，以避免弄乱数据。

尽管如此，仍然可能会有一些“同时”--如果事务不涉及相同的行，那么从BEGIN到COMMIT所需的几毫秒可能会重叠。考虑一下几乎同时到来的两个事务的时间线：

BEGIN;  -- A
pull money from Alice  -- A
      BEGIN;   -- B
      pull money from Bobby  -- B
give Alice's money to Alan  -- A
      give Bobby's money to Betty  --B
COMMIT;   --A
      COMMIT;  --B