mysql锁解决并发问题

mysql锁解决并发问题

文章分为以下几个要点

1.问题描述以及解决过程

2.MySQL锁机制

3.数据库加锁分析

下面讨论的都是基于MySQL的InnoDB。

0. 问题描述以及解决过程

因为涉及到公司利益问题，所以下面很多代码和数据库信息，进行了缩减和修改，望见谅。

业务场景是优惠券系统规则规定了一个优惠券活动最多可发行多少张优惠券和每个用户最多可领取优惠券数量。下面列出两张表的结构。

活动表

CREATE TABLE`coupon_detail`(

`coup_id`int(11) NOT NULL AUTO_INCREMENT,

`act_code`char(6) NOT NULL DEFAULT''COMMENT '活动编号',

`coup_code`char(6) NOT NULL DEFAULT''COMMENT '优惠券编码',

`coup_user_id`int(11) NOT NULL DEFAULT'0'COMMENT '领取券用户id',

PRIMARY KEY(`coup_id`),

UNIQUE KEY`coup_code_idx`(`coup_code`) USING BTREE COMMENT '优惠券编码唯一索引',

KEY`coup_user_idx`(`coup_user_id`) USING BTREE COMMENT '用户id普通索引',

KEY`act_code_idx`(`act_code`) USING BTREE COMMENT '活动编码普通索引'

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='优惠券明细表';

假设一个优惠券活动设置的最大发行量为1000张优惠券，单个用户最多可领取1张优惠券。如下

insert into coupon_activity values(1,'000000',1000,1,0);

不考虑到并发的话，用户10领取act_code=’000000’活动的优惠券执行的sql如下。注意#{}里面的字段表示之前的sql 查询出来的字段。

alter table coupon_activity add coup_num_current int(11) NOT NULL DEFAULT'0'COMMENT '当前券领用量';

那么此时的执行代码就变成了下面这样的。

#统计出单个用户领取该券的数量，上了悲观锁

select* from coupon_activity where act_code = '000000';

insert into coupon_detail (coup_id,act_code,coup_code,coup_user_id) select(coup_id,act_code,coup_code,coup _user_id) from(select count(id) as num from coupon_detail where coup_user_id = 10and act_code = '0000 00')temp where temp.num < #{coup_per_num}

上面这条复杂的sql在高并发时会发生死锁的情况，但是确能得到正确的结果。我们来分析一下死锁的情形。

上面这条语句最里面的select where coup-user-id = 10 and act-code = ‘000000’ 会锁住这一行数据，但是当数据库

没有值的时候，就上不了锁，那么另外一个事务的select也能查询，

但是两个事务都对coup_user-id = 10 and act-code = ‘000000’上锁了，那么insert的时候两者都处于等待对方释放锁的状态，所以就发生了死锁，数据库解决死锁之后，只有一条数据

插入成功，这样也就得到了我们需要的结果。

在InnoDB中，锁是逐步获得的，因此发生死锁是可能的。发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另外一个事务获得锁，并继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。

1. mysql锁机制

InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

innodb 行级锁record-level

lock大致有三种：record lock, gap lock and Next-KeyLocks。

record lock 锁住某一行记录

gap lock 锁住某一段范围中的记录

next key lock 是前两者效果的叠加。

nnoDB实现了以下两种类型的行锁：

?共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；

?排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（意向共享锁和意向排他锁）。这两种意向锁都是表锁。意向锁是InnoDB自动加的，不需要用户干预。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任意锁。

事务可以通过以下语句显示给记录集加共享锁或者排他锁：

SELECT* FROM table_name WHERE ... LOCK IN SHARE MODE #共享锁

SELECT* FROM table_name WHERE ... FOR UPDATE#排他锁

InnoDB的行锁实现的特点：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将会使用表锁。因为MySQL的行锁是针对索引加的锁，

而不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引建，是会出现锁冲突的。

对于键值在条件范围内但并不存在的记录，叫做间隙。InnoDB会对这个间隙加锁，这种锁机制就是所谓的间隙锁（Next-Key 锁）。

InnoDB使用间隙锁的目的：一是为了防止幻读，二是为了满足其恢复和复制的需要。

InnoDB如何解决死锁问题的：

在InnoDB中，锁是逐步获得的，因此发生死锁是可能的。发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另外一个事务获得锁，并继续完成事务。但在涉及外部锁，

或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout 来解决。

2. 数据库加锁分析

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，

是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。

在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。

在MVCC并发控制中，读操作可以分成两类：快照读(snapshot read)与当前读(current read)。快照读，读取的是记录的可见版本(有可能是历史版本)，

不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

select* from table where?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

?提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别(不重复读)

?可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

?串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

MySQL InnoDB默认使用的级别是可重复读级别(Repeatable read),查找命令如下

?脏读:当一个事务进行的操作还未提交时，另外一个事务读到了修改的数据，这就是脏读，但是RR级别事务避免了脏读。

?不可重复读:是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。

那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。

这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。但是，RR级别是不会出现不一样的结果的，即使另一个事务提交了修改他也查不到变化。

?幻读：第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，

这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

2.2 sql语句加锁分析

1.如果id是主键或者是索引的话，那么锁定的行只有符合条件的那几行。

2.如果id非索引，那么会锁表。

语句3分析：

1.id或者nickname只要有一个是索引或者是主键的话，那么锁住的行都是符合条件的行。

但是要注意一个情况，如果你查看索引数据值存在大量重复的数据的话（重复的数要是where条件值），那么有可能条件是不会走索引，而是进行全表查询，所以此时锁住的也是全表。

因为索引扫描书超过30%时，会进行全表扫描。

本文作者：speend me