MySQL: 事务与锁定表

我对事务和锁表的区别有点困惑，锁表是为了确保数据库完整性和 SELECT 和 UPDATE 保持同步，没有其他连接干扰它。我需要:

SELECT * FROM table WHERE (...) LIMIT 1


if (condition passes) {
// Update row I got from the select
UPDATE table SET column = "value" WHERE (...)


... other logic (including INSERT some data) ...
}

我需要确保没有其他查询会干扰并执行相同的 SELECT(在该连接完成更新行之前读取“旧值”)。

我知道我可以默认使用 LOCK TABLES table来确保每次只有一个连接执行此操作，并在完成后解锁它，但这似乎有点过了。在事务中包装它是否会做同样的事情(确保在其他连接仍在处理时不会有其他连接尝试相同的进程) ？或者 SELECT ... FOR UPDATE或 SELECT ... LOCK IN SHARE MODE会更好？

88510 次浏览
小开

我会用

START TRANSACTION WITH CONSISTENT SNAPSHOT;

首先，和一个

COMMIT;

结束。

在这两者之间所做的任何事情都与数据库 如果您的存储引擎支持事务(即 InnoDB)的其他用户隔离开来。

小开

在尝试使用 IF NOT EXISTS ...然后执行 INSERT时，我也遇到了类似的问题，当多个线程更新同一个表时，这会导致竞态条件。

我在这里找到了问题的解决方案: 如何在标准 SQL 中编写 INSERT IF NOT EXISTS 查询

我知道这并不能直接回答您的问题，但是将检查和插入作为单个语句执行的原则是非常有用的; 您应该能够修改它来执行更新。

小开

您希望在事务中使用 SELECT ... FOR UPDATE或 SELECT ... LOCK IN SHARE MODE，正如您所说的，因为通常 SELECT，无论它们是否在事务中，都不会锁定表。选择哪一个取决于您是否希望其他事务能够在事务进行中读取该行。

Http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

START TRANSACTION WITH CONSISTENT SNAPSHOT不会为您做这个技巧，因为其他事务仍然可以出现并修改该行。这在下面的链接的顶部提到。

如果其他疗程同时进行 你可以更新同一个表[ ... ] 看到桌子在一个状态，从来没有 存在于数据库中。

Http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

小开
最佳答案

锁定表可以防止其他 DB 用户影响您锁定的行/表。但是锁本身并不能确保逻辑出现在一致的状态。

想象一下银行系统。当你在网上支付账单时，至少有两个账户会受到交易的影响: 你的账户，钱就是从这个账户中取出的。还有接收者的账户，钱会转到这个账户。还有银行的账户，他们会很高兴地把交易中收取的所有服务费存进去。鉴于(如今每个人都知道)银行异常愚蠢，让我们假设它们的体系是这样运作的:

$balance = "GET BALANCE FROM your ACCOUNT";
if ($balance < $amount_being_paid) {
charge_huge_overdraft_fees();
}
$balance = $balance - $amount_being paid;
UPDATE your ACCOUNT SET BALANCE = $balance;


$balance = "GET BALANCE FROM receiver ACCOUNT"
charge_insane_transaction_fee();
$balance = $balance + $amount_being_paid
UPDATE receiver ACCOUNT SET BALANCE = $balance

现在，由于没有锁和没有事务，这个系统很容易受到各种竞态条件的影响，其中最大的竞态条件是在您的帐户上执行多次支付，或者并行执行接收方的帐户。虽然您的代码已经恢复了您的余额，并且正在进行巨大的透支费用()之类的事情，但是完全有可能其他一些支付将并行地运行相同类型的代码。他们会检索你的余额(比如说，100美元) ，完成他们的交易(取出你正在支付的20美元，和他们正在欺骗你的30美元) ，现在两个代码路径有两个不同的余额: 80美元和70美元。取决于哪一个最后完成，你最终会在你的帐户中有这两个余额中的任何一个，而不是50美元，你应该已经结束了($100-$20-$30)。在这种情况下，“银行错误对你有利”。

假设你用的是锁。你的账单付款($20)首先打管，所以它赢得和锁定您的帐户记录。现在你有了独家使用权，可以从余额中扣除20美元，然后平静地写回新的余额... ... 你的账户最终会得到预期的80美元。但是... 呃... 你试图去更新接收器的账户，它被锁定了，锁定的时间比代码允许的长，超出了你的交易时间... 我们正在处理愚蠢的银行，所以没有适当的错误处理，代码只是拉一个 exit()，你的20美元消失在一团电子。现在你损失了20美元，你还欠听筒20美元，你的电话被收回了。

那么... 输入交易记录。你开始一个交易，你借记你的帐户20美元，你试图信贷的接收者与20美元... 和东西爆炸再次。但是这一次，代码可以只做 rollback，而不是 exit()，然后，你的 $20神奇地被添加回你的帐户。

最后，归结起来就是:

锁可以防止其他人干扰你正在处理的数据库记录。事务可以防止任何“稍后”的错误干扰您已经完成的“较早”的事情。两者都不能保证事情最终会顺利解决。但是他们在一起，就可以了。

在明天的课程: 死锁的喜悦。

小开

您与锁和事务混淆了。它们在 RMDB 中是两种不同的东西。当事务专注于数据隔离时，锁可以防止并发操作。查看 这个的伟大的澄清文章和一些优雅的解决方案。

小开

事务概念和锁是不同的，但是事务使用锁来帮助它遵循 ACID 原则。 如果您希望表能够防止其他人在您进行读/写操作时在同一时间点进行读/写操作，那么您需要一个锁来实现这一点。 如果要确保数据的完整性和一致性，最好使用事务。 我认为在带锁的事务中隔离级别的概念是混合的。 请搜索事务的隔离级别，SERIALIZE 应该是您想要的级别。

小开

我已经开始研究同样的话题，原因和你在问题中提到的一样。我对 SO 中给出的答案感到困惑，因为它们只是部分答案，没有提供全局。在阅读了来自不同 RDMS 提供商的一些文档之后，下面是我的观点:

交易

语句是数据库命令，主要用于读取和修改数据库中的数据。事务是单个或多个语句执行的范围。它们提供了两样东西:

  1. 一种保证事务中的所有语句都正确执行的机制，或者在出现单个错误的情况下，由这些语句修改的任何数据都将恢复到其最后的正确状态(即回滚)。这种机制所提供的称为 原子性。
  2. 一种保证并发读语句可以查看数据而不会出现下面描述的某些或全部现象的机制。

脏读(Dirty read) : 事务读取并发写入的数据 未提交的事务。

不可重复读取: 事务重新读取以前读取的数据 并发现数据已被另一个事务(即 提交)。

幻影读取: 事务重新执行查询，返回一组 满足搜索条件并且发现行集合 满足条件的情况已经改变，由于另一个最近提交 交易。

序列化异常: 成功提交组的结果 事务的排序与所有可能的运行顺序不一致 一次一笔交易。

这种机制所提供的称为 与世隔绝，允许语句选择事务中不应该发生的现象的机制称为 隔离级别。

例如，这是 PostgreSQL 的隔离级别/现象表: enter image description here

如果数据库系统违反了任何描述的承诺，则回滚更改并通知调用方。

如何实施这些机制来提供这些担保将在下面描述。

锁的类型

  1. 独占锁定: 当一个独占锁定通过一个资源获取时，不能通过该资源获取任何其他的独占锁定。独占锁始终在修改语句(INSERT、 UPDATE 或 DELETE)之前获取，并在事务完成后释放它们。要在修改语句之前显式获取排他锁，可以使用 FORUPDATE (PostgreSQL，MySQL)或 UPDLOCK (T-SQL)等提示。
  2. 共享锁: 可以通过一个资源获取多个共享锁。但是，不能同时通过资源获取共享锁和排他锁。基于隔离级别的数据库实现，在读语句(SELECT，JOIN)之前可能获得共享锁，也可能不获得共享锁。

锁定资源范围

  1. 行: 执行语句的单行。
  2. Range: 基于语句(SELECT... WHERE)中给出的条件的特定范围。
  3. 表: 整个表(主要用于防止批量更新等大语句的死锁)

例如，SQL-Server 不同隔离级别的默认共享锁行为: enter image description here

僵局

锁定机制的缺点之一是死锁。当一个语句进入等待状态时，就会发生死锁，因为请求的资源被另一个等待语句持有，而这个等待语句又在等待另一个等待语句持有的资源。在这种情况下，数据库系统检测到死锁并终止一个事务。不小心使用锁会增加死锁的几率，但是即使没有人为错误，死锁也可能发生。

快照(数据版本)

这是一种隔离机制，它为语句提供在特定时间获取的数据的副本。

  1. 语句开始: 为语句执行开始时所采用的语句提供数据副本。它还通过在事务完成之前保留此数据来帮助实现回滚机制。

  2. 事务开始: 为事务开始时所采用的语句提供数据副本。

所有这些机制一起提供 一致性。

当涉及到乐观锁和悲观锁时，它们只是并发问题方法分类的名称。

悲观并发控制:

锁系统防止用户以下列方式修改数据: 影响其他用户。当用户执行导致 锁，则其他用户无法执行将 与锁发生冲突，直到所有者释放它 悲观控制，因为它主要是在环境中使用 数据存在很大的争用，保护数据的成本 使用锁的开销比回滚事务的开销小，如果 发生并发冲突。

乐观并发控制:

在乐观并发控制中，用户不锁定数据时，他们 当用户更新数据时，系统会检查是否有其他用户 用户在读取数据后更改了数据。如果另一个用户更新了 数据时，将引发一个错误。通常，接收错误的用户 回滚事务并重新开始。这被称为乐观 因为它主要用在低水平的环境中 争用数据，以及偶尔回滚一个 事务的成本低于读取时锁定数据的成本。

例如，默认情况下，PostgreSQL 使用快照来确保读数据没有更改，如果更改则回滚，这是一种乐观的方法。但是，SQL-Server 默认使用读锁来提供这些承诺。

实现细节可能会根据您选择的数据库系统而更改。但是，根据数据库标准，他们需要使用这些机制以这样或那样的方式提供这些声明的事务保证。如果您想了解更多关于这个主题或关于特定实现的详细信息，以下是一些有用的链接。

  1. SQL-Server-事务锁定和行版本控制指南
  2. PostgreSQL-事务隔离
  3. PostgreSQL-显式锁定
  4. 一致的非锁定读取
  5. MySQL 锁定
  6. 了解隔离级别(短片)

提交答案