关于Flask中的SqlAlchemy的锁(with_for_update)

场景:

我建立了一个账簿体系,是由账号->账簿->明细这样的关系组成.

一个人可以有一个账号,n个类型的账簿,每条明细都关联账号的id以及对应账簿的id

其中账簿中,有5种金额的类型,包括:可用余额,赠送余额,冻结余额,在途余额,以及不可用余额.

第一次的解决

我在写第一版交易的时候,是在接口请求进来后,根据参数查询到是要对哪个账号的哪个账簿进行修改.

假设当前是甲账号的A账簿,扣除0.11元.

我在ORM查询时是这么写的:

result = MyModel.query.filter(and_(条件)).with_for_update(nowait=False, read=True).all()

这里解释一下原生mysql中的"FOR UPDATE"语句

FOR UPDATE 是在mysql中,实现行锁的方式.主要是用在select语句中.确保在select时根据where语句确认的所有行都被锁定.

mysql中的锁分为:共享锁和排他锁 行锁只实现在引擎层,但也不是所有引擎都支持,InnoDB支持.

共享锁(S锁):也被称为读锁,是可以共享的,互相不阻塞.多个客户端可以同时读取某一数据,互不影响.

SELECT * FROM table WHERE 语句 FOR SHARE; # 共享锁 可以读取 但不能修改
等同于
SELECT * FROM table WHERE 语句 LOCK IN SHARE MODE;

排他锁(X锁):也被称为写锁,不允许共享的.一个排他锁的存在会阻塞其他的共享锁以及排他锁.安全性高,在排他锁存在的情况下,进行写入时不会被读取和更改.

select * from table where 语句 for update

执行顺序:

1:select * from table where id = 1 for update;  #对id等于1的这一行 加了X锁 此时不允许再对这一行添加X和S锁
2:commit;
commit之后,锁就会被解除

场景:

select * from table ehere id = 1 for update;  # 该行被锁定,此时如果查询会等待
update table set a = '1' where id = 1;  # 更新该行
commit;  # 提交后解锁 或者rollback 回滚后

最常见的使用该语句的场景是:

1. 并发控制,

2. 事务处理.

3. 数据库准确读取

例如:

我当前修改余额的场景,如果我不加锁,那如果同时两个事务开启,对我同一个账簿的同一个金额做修改,

事务1查询余额时余额100元扣除10元,update为90,

但是事务2查询余额此时也是100元扣除100元,update为0,

理想结果是=要扣除110元,余额变为-10元.

结果却是0元或者90元,这对于金额的修改是很致命的.

不同用法

SELECT * FROM table WHERE 语句 FOR UPDATE;  # 遇到此行等待
SELECT * FROM table WHERE 语句 FOR UPDATE NOWAIT; # 遇到此行不等待直接报错
SELECT * FROM table WHERE 语句 FOR UPDATE SKIP LOCKED; # 遇到此行直接跳过
SELECT * FROM table WHERE 语句 FOR SHARE; # 共享锁 遇到可以读 但不能修改
SELECT * FROM table WHERE 语句 FOR SHARE NOWAIT; # 共享锁 不等待直接报错
SELECT * FROM table WHERE 语句 FOR SHARE SKIP LOCKED; # 共享锁 不等待直接报错

也就是说:"FOR UPDATE"和"FOR SHARE"一样都有"NOWAIT"和"SKIP LOCKED"的用法

以下为mysql8.0版本对参数的描述:

NOWAIT

A locking read that uses NOWAIT never waits to acquire a row lock. The query executes immediately, failing with an error if a requested row is locked.
翻译:
使用NOWAIT的锁定读取从不等待获取行锁定。查询会立即执行，如果请求的行被锁定，则会失败并返回错误。

---

SKIP LOCKED

A locking read that uses SKIP LOCKED never waits to acquire a row lock. The query executes immediately, removing locked rows from the result set.
翻译:
使用SKIP LOCKED的锁定读取从不等待获取行锁定。查询立即执行，从结果集中删除锁定的行。

注意

Queries that skip locked rows return an inconsistent view of the data. SKIP LOCKED is therefore not suitable for general transactional work. However, it may be used to avoid lock contention when multiple sessions access the same queue-like table.
NOWAIT and SKIP LOCKED only apply to row-level locks.
Statements that use NOWAIT or SKIP LOCKED are unsafe for statement based replication.
The following example demonstrates NOWAIT and SKIP LOCKED. Session 1 starts a transaction that takes a row lock on a single record. Session 2 attempts a locking read on the same record using the NOWAIT option. Because the requested row is locked by Session 1, the locking read returns immediately with an error. In Session 3, the locking read with SKIP LOCKED returns the requested rows except for the row that is locked by Session 1.
翻译:
跳过锁定行的查询返回的数据视图不一致。因此，SKIP LOCKED不适用于一般事务性工作。但是，当多个会话访问同一个类似队列的表时，可以使用它来避免锁争用。
NOWAIT和SKIP LOCKED仅适用于行级锁。
使用NOWAIT或SKIP LOCKED的语句对于基于语句的复制是不安全的。
以下示例演示NOWAIT和SKIP LOCKED。会话1启动一个事务，该事务对单个记录执行行锁定。会话2尝试使用NOWAIT选项对同一记录进行锁定读取。因为请求的行被会话1锁定，所以锁定读取会立即返回并返回一个错误。在会话3中，使用SKIP LOCKED进行的锁定读取返回请求的行，但会话1锁定的行除外。

# Session 1:
​
mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
​
mysql> INSERT INTO t (i) VALUES(1),(2),(3);
​
mysql> START TRANSACTION;
​
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+
​
# Session 2:
​
mysql> START TRANSACTION;
​
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.
​
# Session 3:
​
mysql> START TRANSACTION;
​
mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+

现在说完了原生sql对于for update和for share的应用.

现在回头说with_for_update.

以下是以下是selectable.py中对于此方法的注释对于with_for_update的描述:

        Specify a ``FOR UPDATE`` clause for this
        :class:`_expression.GenerativeSelect`.
​
        E.g.::
​
            stmt = select(table).with_for_update(nowait=True)
​
        On a database like PostgreSQL or Oracle, the above would render a
        statement like::
​
            SELECT table.a, table.b FROM table FOR UPDATE NOWAIT
​
        on other backends, the ``nowait`` option is ignored and instead
        would produce::
​
            SELECT table.a, table.b FROM table FOR UPDATE
​
        When called with no arguments, the statement will render with
        the suffix ``FOR UPDATE``.   Additional arguments can then be
        provided which allow for common database-specific
        variants.
​
        :param nowait: boolean; will render ``FOR UPDATE NOWAIT`` on Oracle
         and PostgreSQL dialects.
​
        :param read: boolean; will render ``LOCK IN SHARE MODE`` on MySQL,
         ``FOR SHARE`` on PostgreSQL.  On PostgreSQL, when combined with
         ``nowait``, will render ``FOR SHARE NOWAIT``.
​
        :param of: SQL expression or list of SQL expression elements
         (typically :class:`_schema.Column`
         objects or a compatible expression) which
         will render into a ``FOR UPDATE OF`` clause; supported by PostgreSQL
         and Oracle.  May render as a table or as a column depending on
         backend.
​
        :param skip_locked: boolean, will render ``FOR UPDATE SKIP LOCKED``
         on Oracle and PostgreSQL dialects or ``FOR SHARE SKIP LOCKED`` if
         ``read=True`` is also specified.
​
        :param key_share: boolean, will render ``FOR NO KEY UPDATE``,
         or if combined with ``read=True`` will render ``FOR KEY SHARE``,
         on the PostgreSQL dialect.
翻译:
    为此指定`FOR UPDATE``子句
：class:`_expression。GenerativeSelect`。
例如：
stmt=select（table）.with_for_update（nowait=True）
在像PostgreSQL或Oracle这样的数据库上，上面将呈现
语句类似：：
SELECT table.a，table.b FROM table FOR UPDATE NOWAIT
在其他后端，“nowait”选项被忽略，而是
将产生：
SELECT table.a，table.b FROM table FOR UPDATE
当在没有参数的情况下调用时，该语句将以后缀“FOR UPDATE”呈现。然后可以提供额外的参数，这些参数允许常见的数据库特定变体。
：param nowait：布尔值；将在Oracle上呈现“FOR UPDATE NOWAIT”和PostgreSQL方言。
：param read：布尔值；将在MySQL上呈现“LOCK IN SHARE MODE”，``用于PostgreSQL上的共享``。在PostgreSQL上，当与``nowait ``，将呈现``FOR SHARE nowait``。
：param of：SQL表达式或SQL表达式元素列表（通常为：类：“_schema.Column”对象或兼容的表达式），将呈现为“FOR UPDATE of”子句；PostgreSQL和Oracle支持。可以呈现为表或列，具体取决于后端。
：param skip_locked：布尔值，将呈现``FOR UPDATE skip locked``在Oracle和PostgreSQL方言上，或“FOR SHARE SKIP LOCKED”，如果``read=True“”也被指定。
：param key_share:boolean，将呈现``FOR NO key UPDATE ``，或者如果与“read=True”组合将呈现“FOR KEY SHARE”，关于PostgreSQL方言。

with_for_update方法初始化:

def with_for_update(
        self,
        nowait=False,  # 默认False /True = FOR UPDATE NOWAIT 不等待直接报错; False = FOR UPDATE  等待 当前事务可以修改所有字段
        read=False,  # 默认False /True = FOR SHARE NOWAIT 不等待直接报错 ; False = FOR SHARE 等待 其他事务不能修改所有字段
        of=None,  # 默认None /FOR UPDATE of
        skip_locked=False,  # 默认False /True =  FOR UPDATE SKIP LOCKED 不等待直接跳过 ; False =  FOR UPDATE 等待 
        key_share=False,  # 默认False /True = FOR KEY SHARE 其他事务不能改KEY字段 False =  FOR NO KEY UPDATE 当前事务可以改除KEY以外的字段
    ):

应用在我的使用场景中

result = MyModel.query.filter(and_(条件)).with_for_update(nowait=False, read=True).all()

我指定的是等待,但是读的时候不等待直接报错.所以我在上线后遇到并发,就会报错.

我将其修改为:

result = MyModel.query.filter(and_(条件)).with_for_update(nowait=False, read=False).all()
等同于
result = MyModel.query.filter(and_(条件)).with_for_update().all()

解决问题.