通过一个实例说明高并发条件下，合理使用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 startno int,in endno int)
begin

insert into t_tmpsellticket(pool,ticketid,maxid) select pool,tocketid,
cast(concat(cast(pool as BINARY),cast(ticketid as BINARY)) as signed)
from t_sellticket
where id>=startno and id<=endno;

select ticketid
from t_tmpsellticket
where maxid=startno;
end $$
delimiter;

yinshi

未来瓶颈来自存储和网络带宽

yinshi

这是因为sellticket非簇索引、和簇索引互相等待导致的死锁情况，于是老老实实实现第二点，问题解决。
在若干个节点同时运行情况下，售票效率可以通过增加主机实现了提高售票能力。

yinshi

为验证这个解决方案，只实现第1点。同样出现
update t_sellticket,t_tmpsellticket
set t_sellticket.used=1
where t_sellticket.pool=t_tmpsellticket.pool and t_sellticket.ticketid=t_tmpsellticket.ticketid;
死锁的情况。

yinshi

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

yinshi

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

yinshi

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

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

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

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

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

**（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

**(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

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

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