MySQL 的事务和锁（一）

最近做了一些和交易系统有关的东西，也乘机复习了一下 MySQL 的事务和锁机制。

1、事务

什么是事务呢？按照标准的描述：

A transaction symbolizes a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in database. Transactions in a database environment have two main purposes:

To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a database remain uncompleted, with unclear status.
To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs' outcomes are possibly erroneous.

翻译过来就是，事务是对于数据库的操作序列，事务的目的有两个：

提供一种从失败中回复的可靠机制，同时在系统挂掉的时候保证数据库的一致性
为并发访问数据库提供一种隔离机制

如何使用事务：


# -*- coding: utf-8 -*-
import time
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import BIGINT, INTEGER, VARCHAR
from sqlalchemy.schema import Column

Model = declarative_base()
engine = create_engine(
     'mysql+mysqldb://root@127.0.0.1/test_session?charset=utf8mb4',
    echo=True,
    pool_size=2,
    max_overflow=5,
    pool_timeout=0,
    pool_recycle=3600
)

Session = sessionmaker(bind=engine)

import 了一大堆东东之后，创建了一个 engine，然后 bind 到一个 session 中，后面就可以用 Session() 生成可以用的 session 了.

使用事务创建两个用户

session = Session()
account = Account(
    member_id=1,
    amount=20
)

session.add(account)

account = Account(
    member_id=2,
    amount=0
)

session.add(account)session.commit()

上面，在账户里面创建了两个账户，第一个账户，初始有 20 元，第二个账户，初始为 0 元

使用事务进行转账


# 第一笔转账 account1 -> account2
session1 = Session()
account1 = session1.query(Account).get(1)
account2 = session1.query(Account).get(2)
account1.amount -= 10
account2.amount += 10

# 第二笔转账 account1 -> account2
session2 = Session()
account1 = session2.query(Account).get(1)
account2 = session2.query(Account).get(2)
account1.amount -= 10
account2.amount += 10

# 提交事务
session1.commit()
session2.commit()

# 查询余额
session = Session()
account1 = session.query(Account).get(1)
account2 = session.query(Account).get(2)
print account1.amount, account2.amount

上面大致模拟了，两个并发的事务，理论上，两笔转账以后，account1 的余额为 0 元，account2 的余额为 20 元，但实际上输出为：

10 10

可见，数据库的事务虽然提供了所谓的隔离，但是依然不能保证结果的正确性，这里得使用数据库提供的锁。

2、锁

首先看看 Mysql 关于锁的文档：

LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...

lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE

UNLOCK TABLES

所以看起来，锁的类型主要有 READ、READ LOCAL、LOW_PRIORITY WRITE、WRITE，但是，文档下面又有两句：

For InnoDB tables, READ LOCAL is the same as READ.

The LOW_PRIORITY modifier has no effect. In previous versions of MySQL, it affected locking behavior, but this is no longer true. It is now deprecated and its use produces a warning. Use WRITE without LOW_PRIORITY instead.

所以我们只需要关注，READ LOCK 和 WRITE LOCK 就可以了，接下来试验一下，两种锁的区别：

READ LOCK


session1 = Session()
account1 = session1.query(Account).with_lockmode('read').get(1)
account2 = session1.query(Account).with_lockmode('read').get(2)

account1.amount -= 10
account2.amount += 10

session2 = Session()
account1 = session2.query(Account).with_lockmode('read').get(1)
account2 = session2.query(Account).with_lockmode('read').get(2)

account1.amount -= 10
account2.amount += 10

session1.commit()
session2.commit()

我们的程序会阻塞在 session1.commit 这里，最后报了这个异常：

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) 
(1205, 'Lock wait timeout exceeded; try restarting transaction')
[SQL: u'UPDATE account SET amount=%s WHERE account.id = %s'] [parameters: ((960L, 1L), (2040L, 2L))]

Lock wait timeout exceeded 等待锁超时，此时如果我们捕获这个异常，那么 session2.commit 就会成功。

所以关于 READ 锁，结论就是：

可以被多个 session 持有
没有被释放之前，其他事务不能更新被加锁的内容

上面就是因为，READ LOCK 同时被 session1 和 session2 持有，所以 session1 试图更新内容的时候，会因为等待 session2 而超时，所以 READ LOCK 通常用在一个场景下：就是本事务未完成或者回滚之前，不希望其他事务更新读取的内容，比如需要定时导出某个记录的快照，我希望在我导出完成之前，这条记录不被更新，就可以用 READ LOCK.

WRITE LOCK

同样是上面的代码，把 read 换成 update

这次卡在了 account1 = session2.query(Account).with_lockmode('update').get(1) 上，所以结论是：

WRITE LOCK 只能被一个 session 持有
没有释放前，其他会话不能读取加锁的内容

这个使用场景比较多，比如上面咱们的转账，当这里并发的转账请求到来的时候，第一个会话，持有了 1 和 2 的锁，session2 就需要等待 session1 完成，才能继续，在这里的结局就是超时报错

总结

所以，想要改写内容的时候，如果要求保证数据一致性，就得使用 WRITE LOCK。READ LOCK 的使用场景，多见与上下逻辑中，对于某个字段值有依赖，需要在进入会话之后，保持该字段不被其他会话修改。
同时，也总结出一点，就是写这种程序的时候，一定要专心。

关于加锁的规则，下次再说，这里面也有很多坑，搞不好，就锁了整个表