代码之家 › 专栏 › 技术社区 › Alexander Torstling

SQL原子增量和锁定策略-这安全吗?

increment atomic locking sql

Alexander Torstling · 技术社区 · 15 年前

我有一个关于SQL和锁定策略的问题。例如,假设我的网站上有一个图像的查看计数器。如果我有一个存储过程或类似程序来执行以下语句:

START TRANSACTION;
UPDATE images SET counter=counter+1 WHERE image_id=some_parameter;
COMMIT;

s1: begin
s1: begin
s1: read counter for image_id=15, get 0, store in temp1
s2: read counter for image_id=15, get 0, store in temp2
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: write counter for image_id=15 to (temp2+1), which is also 1
s1: commit, ok
s2: commit, ok

最终结果:图像的值“1”不正确,应为2。

我的问题是:

这种情况可能吗?
如果是这样,事务隔离级别是否重要?
是否有冲突解决程序可以将此类冲突检测为错误?

我对一个通用的答案感兴趣,但如果没有,我对MySql和InnoDB特定的答案感兴趣,因为我正试图使用这种技术在InnoDB上实现序列。

编辑: 下面的场景也可能会导致相同的行为。我假设我们处于隔离级别READ\u committed或更高级别,因此s2从事务开始获取值,尽管s1已经将“1”写入计数器。

s1: begin
s1: begin
s1: read counter for image_id=15, get 0, store in temp1
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: read counter for image_id=15, get 0 (since another tx), store in temp2
s2: write counter for image_id=15 to (temp2+1), which is also 1
s1: commit, ok
s2: commit, ok

2 回复 | 直到 15 年前

Quassnoi 15 年前

UPDATE 查询在其读取的页面或记录上放置更新锁。

当决定是否更新记录时,锁被解除或提升为独占锁。

这意味着在这种情况下:

s1: read counter for image_id=15, get 0, store in temp1
s2: read counter for image_id=15, get 0, store in temp2
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: write counter for image_id=15 to (temp2+1), which is also 1

s2 会等到 s1 决定是否编写计数器,而这种情况实际上是不可能的。

它将是这样的:

s1: place an update lock on image_id = 15
s2: try to place an update lock on image_id = 15: QUEUED
s1: read counter for image_id=15, get 0, store in temp1
s1: promote the update lock to the exclusive lock
s1: write counter for image_id=15 to (temp1+1), which is 1 
s1: commit: LOCK RELEASED
s2: place an update lock on image_id = 15
s2: read counter for image_id=15, get 1, store in temp2
s2: write counter for image_id=15 to (temp2+1), which is 2

InnoDB , DML

ConcernedOfTunbridgeWells 14 年前

如果锁定没有正确完成,那么肯定有可能获得这种类型的竞争条件,并且默认的锁定模式(readcommitted)允许这样做。在这种模式下,read-only在记录上放置一个共享锁,这样它们都可以看到0,递增它并将1写入数据库。

为了避免这种竞争情况,您需要在读取操作上设置一个独占锁“Serializable”和“Repeatable Read”并发模式可以做到这一点,对于单行操作,它们几乎是等价的。

要使其完全原子化,您必须:

设置适当的 transaction isolation level
开始交易
更新它
提交事务。

您还可以使用HOLDLOCK(T-SQL)或等效的提示对read强制一个独占锁,具体取决于您的SQL方言。

为了实现一个序列,您将需要以原子方式获取值 ,因此更新本身可能不是您所需要的全部。读取仍然必须在事务中进行(将它在变量中获得的内容存储起来),并在读取期间发出一个排他锁。

请注意,要在不创建热点的情况下执行此操作,您的数据库需要有适当的支持 autonomous (nested) transactions 在存储过程中。请注意,有时“嵌套”用于表示链接事务或保存点,因此该术语可能有点混乱。我编辑了这个来引用自治事务。

IIRC-Oracle支持自治事务,但是DB/2直到最近才支持,sqlserver不支持 Grey and Reuter 详细地讲一下它们的实施有多困难。在实践中,我猜很可能不是这样。基督教青年会。