SQL 支持对数字列进行原子递增和递减操作。 “技巧”是使用相对右侧值遵循特定模式的更新查询。

[](https://res.cloudinary.com/practicaldev/image/fetch/s--UCXFLlkv--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://cdn-images-1. medium.com/max/1000/1%2ADZfjn33mWcGYNyq78LkVfg.jpeg)

我们最近重写了我们的库存管理系统,并通过利用原生 SQL 递增和递减操作来提高性能,同时降低操作复杂性。在这篇文章中,我们将深入探讨这些操作的细节和常见问题,并将我们的新实现与之前的实现进行比较,以突出优势。

SQL 支持对数值列进行原子递增和递减操作。 “技巧”是使用基于以下模式的更新查询:

-- This assumes the existence of a table defined as:
-- CREATE TABLE test(id SERIAL PRIMARY KEY, x INTEGER);
UPDATE test set x = x - 1 where id = 1;
复制

进入全屏模式 退出全屏模式

此查询中有两个重要元素:

• WHERE 子句必须是确定性的(稍后会详细介绍)。

• 更新语句的右侧是使用相对值,而不是传递一个绝对的、预选的值(稍后还会详细介绍)。

PostgreSQL 文档有一个很好的例子。

死锁风险

需要注意的是,由于 UPDATE 查询将隐式使用行级锁,如果多个事务在隔离级别设置为 READ COMMITTED、REPEATABLE READ 或 SERIALIZABLE 的情况下运行,则可能会发生死锁。

示例

让我们在测试表中插入两行。

INSERT INTO test VALUES (1, 0);
INSERT INTO test VALUES (2, 0);
复制

进入全屏模式 退出全屏模式

我们可以用两个 psql 会话触发死锁:

$1> psql
psql1> BEGIN;
psql1> UPDATE test SET x = x + 1 WHERE id = 1;
-- A lock is acquired on the row with id 1, no other transactions can update it

$2> psql
psql2> BEGIN;
psql2> UPDATE test SET x = x + 1 WHERE id = 2;
-- A lock is acquired on the row with id 2, no other transactions can update it

psql1> UPDATE test SET x = x + 1 WHERE id = 2;
-- The second session hasn't committed yet, this operation is now waiting

psql2> UPDATE test SET x = x + 1 WHERE id = 1;
-- The first session hasn't committed yet, this operation is now waiting
复制

进入全屏模式 退出全屏模式

僵局!每个会话都在等待另一个会话提交或回滚:

ERROR: deadlock detected DETAIL: Process 14803 waits for ShareLock on transaction 43356; blocked by process 14431. Process 14431 waits for ShareLock on transaction 43357; blocked by process 14803. HINT: See server log for query details. CONTEXT: while updating tuple (0,1) in relation "test"
复制

进入全屏模式 退出全屏模式

PostgreSQL 会在几秒钟后自动检测情况并自动回滚其中一个事务,让另一个事务成功提交。

注意:所有事务隔离级别都会发生这种情况

解决方案

防止这种情况的一种方法是在事务中更新多行时使用确定性排序,在这种情况下,如果两个事务都通过升序对行进行排序,例如,就不会有任何死锁。

确定性条件

正如 PostgreSQL 文档中所解释的,使用 READ COMMITTED 隔离级别使增量查询对事务安全的原因是 UPDATE 查询的 WHERE 子句中使用的条件的确定性。

让我们看看一个不太简单的查询会发生什么:

$1> psql
psql1> BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
psql1> UPDATE test SET x = x + 1 WHERE id = 2;
-- A lock is acquired on the row with id 2, no other transaction can update it

$2> psql
psql2> BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
psql2> UPDATE test set x = x + 1 WHERE x % 2 = 0;
-- A lock is acquired on all rows with an even x value, since there's a lock on the row with id 2, this query waits for the first transaction to commit or rollback

psql1> UPDATE test set x = x + 1 WHERE x % 2 = 0;
-- The second session hasn't committed yet, this query is now waiting as well
复制

进入全屏模式 退出全屏模式

这造成了另一个僵局。

底线是:只要您在主键(或不可变列)上使用相等条件,就不用担心太多。如果你不......好吧,很难说会发生什么。

注意:使用诸如 REPEATABLE READ 或 SERIALIZABLE 之类的限制性隔离级别实际上可能会使事情变得更加复杂,因为应用程序代码需要使用某种重试逻辑来处理序列化失败。文章底部的代码部分有例子

传递给 UPDATE 查询的新值不知道当前值是什么,这就是使该查询起作用的原因,它会简单地将值(在获取行上的锁之后)增加到它的正负值给定的差异。

如果我们要先读取值并使用它来计算新值,我们将需要依赖更复杂的锁定机制来确保值在我们读取它之后和更新完成之前不会改变。

真实世界示例

电子商务公司通常会跟踪平台上销售的每个 SKU 的库存,一个简单的库存表可以定义为:

CREATE TABLE inventories(sku VARCHAR(3) PRIMARY KEY, quantity INTEGER);
复制

进入全屏模式 退出全屏模式

我们库存系统的简化版本如下:

• 获取购物车中的所有SKU

• 按字典顺序对 SKU 进行排序(以防止死锁)

• 发出类似于 UPDATE inventory SET quantity u003d quantity - x WHERE sku u003d y RETURNING quantity 的查询,其中 x 是请求的数量,y 是实际的 SKU 值。如果退回的数量太少,则会引发错误并中止购买过程。

我们的原始(过度设计)解决方案

几年前,当我们在 Harry's 编写库存系统的第一个版本时,我们没有意识到我们只能依靠 SQL 来发出原子减量操作,我们最终使用了 Redis。

Redis 开箱即用地支持此类操作(INCR/INCRBY&DECR/DECRBY),并且是单线程的,默认情况下不会暴露任何竞争条件。

它绝对是一个有效的实现(并且在很长一段时间内都运行良好),但由于库存数据“存在”在两个不同的数据存储中:Redis 和 PostgreSQL,因此它为实现增加了显着的运营成本。

实现可以概括为:

• 我们需要减少 SKU x 的库存

• 是Redis中的值吗?

• 如果没有,从DB中读取并在Redis中设置

• Redis 中的递减

• 检查新值,如果太低则中止

示例代码

我用 Ruby 写了一个小测试套件(适用于 MySQL 和 PostgreSQL),突出了本文中提到的不同概念

• 相对更新查询的“安全性”,即使在读取未提交的事务中也是如此

• READ UNCOMMITTED 和 READ COMMITTED 事务中的绝对更新问题

• 使用 REPEATABLE READ 或 SERIALIZABLE 事务的示例,要求应用程序显式处理序列化错误的重试。

最初发表于engineering.harry's.com于2017年6月28日。

---