数据库并发锁

序

今天在做企业补贴优惠券的项目过程中遇到：并发状态下怎么保证数据库字段值的一致性问题。业务逻辑是：每个企业都可以有政府发放的优惠券，优惠券有多个类目，不同类目的预补贴金额不同，而且优惠券的总金额为一定的。每当企业下单的时候都需要判断下是否使用该类目的优惠券，且使用金额是否超出该类目的预售总额，还有是否超出所有类目下的优惠券总额。当企业下单时，先判断优惠券总额表中是否还有剩余金额，再在事务中下单并修改优惠券总额表的已使用金额字段。当多个用户同时操作该表时，并发状态下会出现超买。

数据库模型

订单表关联企业补贴券额度表。

先了解下MySQL数据库锁

锁概念

当并发事务同时访问一个资源时，有可能导致数据不一致，因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。

锁就是其中的一种机制。
锁类型

数据库上的操作可以归纳为两种：读和写

多个事务同时读取一个对象的时候，是不会有冲突的。同时读和写，或者同时写才会产生冲突。因此为了提高数据库的并发性能，通常会定义两种锁：共享锁和排它锁。

2.1 共享锁（Shared Lock，也叫S锁）

共享锁(S)表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。（如果试衣间的门还没被锁上，顾客都能够同时进去参观）

产生共享锁的 sql:
```
sql：select * from ad_plan lock in share mode;
```
2.2 排他锁(Exclusive Lock，也叫X锁)

排他锁也叫写锁(X)。排他锁表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。(某个顾客把试衣间从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面给打开了)

产生排他锁的sql：
```
select * from ad_plan for update;
```
```
执行sql： select * from information_schema.innodb_locks; 可以查看锁。
```
锁的粒度(锁级别)

MySQL有三种锁的级别：页级、表级、行级。

MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。 MySQL这3种锁的特性可大致归纳如下：表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

数据库引擎通常必须获取多粒度级别上的锁才能完整地保护资源。

3.1 行锁(Row Lock) 对一行记录加锁，只影响一条记录。通常用在DML语句中，如INSERT, UPDATE, DELETE等。 InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！用下面例子来说明一下： CREATE TABLE test_index(id int , name VARCHAR(50),age int )engine=innodb ; INSERT INTO test_index values(1,’张一’,15); INSERT INTO test_index values(3,’张三’,16); INSERT INTO test_index values(4,’张四’,17); INSERT INTO test_index values(5,’张五’,19); INSERT INTO test_index values(7,’刘琦’,19); 不再启用多事务描述了，直接解释执行查询语句 explain select * from test_index where id = 1; +—-+————-+————+——+—————+——+———+——+——+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+————+——+—————+——+———+——+——+————-+ | 1 | SIMPLE | test_index | ALL | NULL | NULL | NULL | NULL | 5 | Using where | +—-+————-+————+——+—————+——+———+——+——+————-+ type: all ，rows: 5 很明显是会使用全表锁。增加索引，id加唯一索引，age加普通索引。 ALTER TABLE test_index ADD UNIQUE uk_id(id), ADD index idx_age(age); mysql> explain select * from test_index where id = 1; +—-+————-+————+——-+—————+——-+———+——-+——+——-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+————+——-+—————+——-+———+——-+——+——-+ | 1 | SIMPLE | test_index | const | uk_id | uk_id | 5 | const | 1 | NULL | +—-+————-+————+——-+—————+——-+———+——-+——+——-+ type: const ，key:uk_id,rows: 1 很明显是会使用行锁，锁定一条记录。

下面做个有趣的实验：两个事务，TX1加共享行锁, 查询age=17的记录， TX2往数据库里插入一条age=18的记录。 TX1: mysql> set autocommit=0; mysql> select * from test_index where age=17 lock in share mode; +——+——+——+ | id | name | age | +——+——+——+ | 4 | 张四 | 17 | +——+——+——+ 1 row in set (0.00 sec)

TX2: mysql> set autocommit=0; mysql> insert test_index values(8,’test’,18); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 结果是TX2获取锁超时，看来TX1锁定的并不止age=17的记录，不存在的间隙age=18，也被加锁了。

执行select * from information_schema.innodb_locks;可以看到加锁的具体信息 +————–+————-+———–+———–+———————+————+————+———–+———-+——————–+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +————–+————-+———–+———–+———————+————+————+———–+———-+——————–+ | 45288:57:5:5 | 45288 | X,GAP | RECORD | test.test_index | idx_age | 57 | 5 | 5 | 19, 0x000000000208 | | 45289:57:5:5 | 45289 | S,GAP | RECORD | test.test_index | idx_age | 57 | 5 | 5 | 19, 0x000000000208 | +————–+————-+———–+———–+———————+————+————+———–+———-+——————–+

行锁S、X锁上做了一些精确的细分，在代码中称作Precise Mode。这些精确的模式, 使的锁的粒度更细小。可以减少冲突。
A.间隙锁(Gap Lock),只锁间隙。
B.记录锁(Record Lock) 只锁记录。
C.Next-Key Lock(代码中称为Ordinary Lock)，同时锁住记录和间隙。 D.插入意图锁(Insert Intention Lock),插入时使用的锁。在代码中，插入意图锁，实际上是GAP锁上加了一个LOCK_INSERT_INTENTION的标记。

行锁兼容矩阵
G I R N
G + + + + I – + + – R + + – – N + + – –
- 代表兼容， -代表不兼容. G代表Gap锁，I代表插入意图锁,R代表记录锁，N代表Next-Key锁.
  S锁和S锁是完全兼容的，因此在判别兼容性时不需要对比精确模式。
  精确模式的检测，用在S、X和X、X之间。从这个矩阵可以看到几个特点：
  A. INSERT操作之间不会有冲突。
  B. GAP,Next-Key会阻止Insert。
  C. GAP和Record,Next-Key不会冲突
  D. Record和Record、Next-Key之间相互冲突。
  E. 已有的Insert锁不阻止任何准备加的锁。
Gap lock：间隙锁只会出现在辅助索引（index）上，唯一索引(unique)和主键索引是没有间隙锁。间隙锁（无论是S还是X）只会阻塞insert操作。间隙锁的目的是为了防止幻读（但是需要应用自己加锁，innodb默认不会加锁防止幻读）。

3.2 页面锁 3.3 表锁(Table Lock) 对整个表加锁，影响标准的所有记录。通常用在DDL语句中，如DELETE TABLE,ALTER TABLE等。
很明显，表锁影响整个表的数据，因此并发性不如行锁好。在MySQL 数据库中，使用表级锁定的主要是MyISAM，Memory等一些非事务性存储引擎。

因为表锁覆盖了行锁的数据，所以表锁和行锁也会产生冲突（商场关门了，试衣间自然也没法使用了）。如: A. trx1 BEGIN B. trx1 给 T1 加X锁，修改表结构。 C. trx2 BEGIN D. trx2 给 T1 的一行记录加S或X锁（事务被阻塞，等待加锁成功) trx1要操作整个表，锁住了整个表。那么trx2就不能再对T1的单条记录加X或S锁，去读取或修这条记录。 3.3.1 表锁—意向锁为了方便检测表级锁和行级锁之间的冲突，就引入了意向锁。 A. 意向锁分为意向读锁(IS)和意向写锁(IX)。
B. 意向锁是表级锁，但是却表示事务正在读或写某一行记录，而不是整个表。所以意向锁之间不会产生冲突，真正的冲突在加行锁时检查。
C. 在给一行记录加锁前，首先要给该表加意向锁。也就是要同时加表意向锁和行锁。

采用了意向锁后，上面的例子就变成了： A. trx1 BEGIN
B. trx1 给 T1 加X锁，修改表结构。
C. trx2 BEGIN
D. trx2 给 T1 加IX锁（事务被阻塞，等待加锁成功)
E. trx2 给 T1 的一行记录加S或X锁.

表锁的兼容性矩阵 IS IX S X IS + + + – IX + + – – S + – + – X – – – –
- 代表兼容， -代表不兼容意向锁之间不会冲突, 因为意向锁仅仅代表要对某行记录进行操作。在加行锁时，会判断是否冲突。

解决方案

两种思路：一种是悲观锁，另外一种是乐观锁；简单的说就是一种假定这样的问题是高概率的，最好一开始就锁住，免得更新老是失败；另外一种假定这样的问题是小概率的，最后一步做更新的时候再锁住，免得锁住时间太长影响其他人做有关操作。

悲观锁实现（查询锁记录）

确认下单前查询，这个查询必须要加锁（select ……for update nowait），有人会说，在这里做个查询确认记录是否有改变不就行了吗，是的，是要做个确认，只是你不加for update就不能保证你在查询到更新提交这段时间里这条记录没有被其他会话更新过，所以这种方式也需要在查询时锁定记录，保证在这条记录没有变化的基础上再做更新，若有变化则提示告知用户。
乐观锁实现（版本列法）

在表上加一个版本列，这一列可以是NUMBER或 DATE/TIMESTAMP列，加这列的作用就是用来记录这条数据的版本（在表设计时一般我们都会给每个表增加一些NUMBER型和DATE型的冗余字段，以便扩展使用，这些冗余字段完全可以作为版本列用），在应用程序中我们每次操作对版本列做维护即可。在更新时我们把上次版本作为条件进行更新。在对一行进行更新的时候限制条件=主键+版本号，同时对记录的版本号进行更新。

参考资料：

MySQL上的数据库锁

序

先了解下MySQL数据库锁

解决方案

CATALOG

FEATURED TAGS

FRIENDS