通过一个实例说明高并发条件下，合理使用innodb的锁机制解决问题

yinshi

场景：
有一个存储过程在单进程下运行良好。
涉及表：
create table 't_tmpsellticket'
(
id int auto_increment,
pool int default 0,
ticketid int default 0,
primary key (id)
) engine=innodb default charset=utf8;
create table 't_sellticket'
(
id int auto_increment,
pool int default 0,
ticketid int default 0,
used int default 0,
primary key (id)，
key ‘sellticket’（ticketid）
) engine=innodb default charset=utf8;

yinshi

createt definner='root'@'loclahost' procedure 'sp_getpresellticket'(in number)
begin
declare p int default 0;
set autocommit =0;
start transaction;
select ifnull(max(id),0) as high
into p
from t_tmpsellticket for update;
set @sql='insert into t_tmpsellticket(pool,ticketid,maxid) select pool,tocketid,';
set @sql=concat(@sql,cast(p as BINARY);
set @sql=concat(@sql,' from t_sellticket where used=0 limit ';
set @sql=concat(@sql,cast(number as BINARY));
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

yinshi

update t_sellticket,t_tmpsellticket
set t_sellticket.used=1
where t_sellticket.pool=t_tmpsellticket.pool and t_sellticket.ticketid=t_tmpsellticket.ticketid;
select ticketid
from t_tmpsellticket
where maxid=p
for update;
commit;
end $$
delimiter;

yinshi

介绍一下这里反映的场景。这里是售票的一个步骤。应用程序单进程调用这个存储过程。传入输入参数number。即本次买多少张票。最后返回具体售出的彩票序号。
为保证售出的彩票不出现重复情况。在本存储过程之间会预先将所有的彩票以随机顺序存入t_sellticket中。used默认为0，即没有售出。剩下的就很容易理解了，t_tmpsellticket表示我已经卖出的彩票。我每次调用存储过程首先获得当前t_tmpsellticket的数量，作为本次售票的期号，同一批售票的期号一样。然后我在t_sellticket中取出number张未售出的彩票放到t_tmpsellticket。接着标志一下这些彩票为已售出状态。最后返回已售出的彩票序号。

yinshi

由于应用需要考虑负载均衡，因此当多个并发的售票进程同时分别调用该存储过程时问题出现了。
首先是java报错，java.sqlexception.deadlock fund when trying to get lock, try restartint trancation.

yinshi

待续。后面看看我在mysql日志里看到什么。

yinshi

innodb monitor 日志如下：
091011 15：08：43 INNODB MONITOR OUTPUT
LATEST DETECTED DEADLOCK
091011 150639
***（1） TRANSACTION
TRANSACTION 015928902,ACTIVE PROCESS NO 8125,OS THREAD ID 1439533968
START INDEX READ
MYSQL TABLES IN USE 1 LOCK1
MYSQL THREAD ID 21587
QUERY ID 11171131 10.10.10.100 ROOT
OPTIMITING
SELECT IFNULL（max（id),0) as high
into p
from t_tmpsellticket for update;

yinshi

**(1) waiting for this lock to be
granted
record lock space id 0 page no 49157
bits 448 index 'primary' of
'bingo_jms'/t_tmpsellticket ' trx id 015928902 lock_mode x waiting
record lock heap heap no 381 physical record下面是锁定的记录，略)

yinshi

**（2） TRANSACTION：
transaction 015928900 ,ACTIVE 0 SEC
PROCESS MO 8125,OS THREAD ID 1439734672
FETCHING ROWS THREAD DECLARE INSIDE INNODB 366
MYSQL TABLES IN USE 2,LOCKED 2
166 LOCKSTRUCTS,HEAP SIZE
44352 ,UNDO LOG ENTRIES 200
MYSQL THREAD ID 21492 ,QUERY ID 11171124
10.10.10.101 ROOT SEND DATA
UPDATE T_sellticket,t_tmpsellticket
set t_sellticket.used=1
where t_sellticket.pool=t_tmpsellticket.pool and t_sellticket.ticketid=t_tmpsellticket.ticketid
**(2) HOLD THE LCOK(s)
record locks space id 0 page no 49157 n bits
448 index 'primary' of table 'bingo_jms/t_tmpsellticket'
trx id 015928900 lock_mode x lock rec but not gap

yinshi

查询MySQL官网文档，发现这跟MySQL的索引机制有关。MySQL的InnoDB引擎是行级锁，我原来的理解是直接对记录进行锁定，实际上并不是这样的。

要点如下:


不是对记录进行锁定，而是对索引进行锁定；


在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking；

如语句UPDATE T_sellticket,t_tmpsellticket
set t_sellticket.used=1
where t_sellticket.pool=t_tmpsellticket.pool and t_sellticket.ticketid=t_tmpsellticket.ticketid会锁定所有pool和ticketid的所有记录，在该语句完成之前，你就不能对锁定的记录进行操作；


当非簇索引(non-cluster index)记录被锁定时，相关的簇索引(cluster index)记录也需要被锁定才能完成相应的操作。


再分析一下发生问题的两条SQL语句，就不难找到问题所在了：

yinshi

当对第1个语句实施锁定读时，t_tmpsellticket被锁定。相关的簇索引(cluster index)记录也被锁定

SELECT IFNULL（max（id),0) as high
into p
from t_tmpsellticket for update;

yinshi

几乎同时
UPDATE T_sellticket,t_tmpsellticket
set t_sellticket.used=1
where t_sellticket.pool=t_tmpsellticket.pool and t_sellticket.ticketid
t_tmpsellticket相关的簇索引(cluster index)记录也将被锁定。在多进程分别调用存储过程的情况下，两边互相等待簇索引(cluster index)释放造成死锁。

yinshi

这个问题要解决，必须适应高并发应用的特点，怎么处理才能让多个业务能并行处理。显然 lock_mode x越少越好。 尽量使用lock_mode S。大家共享相同的资源，各取所需，互不影响。方为上策。

yinshi

所以这里有两个问题要解决。
1 如何在不使用锁定读的情况下避免重复售票。
答案：这里最后把逻辑放在应用层处理，通过集群缓存treecache构造一个全局售票管理器，管理售票数量。然后每个并发应用进程排队向全局售票管理器申请购票数量，由全局售票管理器为每个进程返回相应的不重复的销售范围。进程接收范围后就可以并行调用选票存储过程处理了。

yinshi

2 如何避免并发处理条件下的死锁，保障进程接收范围后就可以并行调用选票存储过程处理。
答案：
lock_mode x越少越好。 尽量使用lock_mode S。不需要使用limit限定查询范围，直接按售票范围查询已售出彩票，这样可以充分利用簇索引。
然后不再使用t_tmpsellticket中的used表示已售出。写入t_tmpsellticket中的记录就表示已售出。这样可以规避update对表的锁定.
通过把pool和ticketid进行编码，将两个连接字段压缩为一个连接字段。这样可以大大提高查询效率。