MySQL社区

 找回密码
 注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

搜索
查看: 17073|回复: 20

[事务及锁] 通过一个实例说明高并发条件下,合理使用innodb的锁机制解决问题

[复制链接]
发表于 2009-10-28 21:01:45 | 显示全部楼层 |阅读模式
本帖最后由 yinshi 于 2009-11-1 12:11 编辑

场景:
有一个存储过程在单进程下运行良好。
涉及表:
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;

评分

1

查看全部评分

 楼主| 发表于 2009-10-28 21:14:38 | 显示全部楼层
本帖最后由 yinshi 于 2009-10-28 21:15 编辑

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;
 楼主| 发表于 2009-10-28 21:18:33 | 显示全部楼层
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;
 楼主| 发表于 2009-10-28 21:27:29 | 显示全部楼层
介绍一下这里反映的场景。这里是售票的一个步骤。应用程序单进程调用这个存储过程。传入输入参数number。即本次买多少张票。最后返回具体售出的彩票序号。
为保证售出的彩票不出现重复情况。在本存储过程之间会预先将所有的彩票以随机顺序存入t_sellticket中。used默认为0,即没有售出。剩下的就很容易理解了,t_tmpsellticket表示我已经卖出的彩票。我每次调用存储过程首先获得当前t_tmpsellticket的数量,作为本次售票的期号,同一批售票的期号一样。然后我在t_sellticket中取出number张未售出的彩票放到t_tmpsellticket。接着标志一下这些彩票为已售出状态。最后返回已售出的彩票序号。
 楼主| 发表于 2009-10-28 21:30:33 | 显示全部楼层
由于应用需要考虑负载均衡,因此当多个并发的售票进程同时分别调用该存储过程时问题出现了。
首先是java报错,java.sqlexception.deadlock fund when trying to get lock, try restartint trancation.
 楼主| 发表于 2009-10-28 21:31:03 | 显示全部楼层
待续。后面看看我在mysql日志里看到什么。
 楼主| 发表于 2009-10-31 23:29:00 | 显示全部楼层
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;
 楼主| 发表于 2009-10-31 23:32:20 | 显示全部楼层
**(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下面是锁定的记录,略)
 楼主| 发表于 2009-11-1 11:49:16 | 显示全部楼层
本帖最后由 yinshi 于 2009-11-1 12:18 编辑

**(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
 楼主| 发表于 2009-11-1 12:10:44 | 显示全部楼层
本帖最后由 yinshi 于 2009-11-1 12:12 编辑

查询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语句,就不难找到问题所在了:
 楼主| 发表于 2009-11-1 12:15:28 | 显示全部楼层
当对第1个语句实施锁定读时,t_tmpsellticket被锁定。相关的簇索引(cluster index)记录也被锁定

SELECT IFNULL(max(id),0) as high
into p
from t_tmpsellticket for update;
 楼主| 发表于 2009-11-1 12:20:42 | 显示全部楼层
几乎同时
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)释放造成死锁。
 楼主| 发表于 2009-11-1 12:23:40 | 显示全部楼层
这个问题要解决,必须适应高并发应用的特点,怎么处理才能让多个业务能并行处理。显然 lock_mode x越少越好。 尽量使用lock_mode S。大家共享相同的资源,各取所需,互不影响。方为上策。
 楼主| 发表于 2009-11-1 12:59:19 | 显示全部楼层
所以这里有两个问题要解决。
1 如何在不使用锁定读的情况下避免重复售票。
答案:这里最后把逻辑放在应用层处理,通过集群缓存treecache构造一个全局售票管理器,管理售票数量。然后每个并发应用进程排队向全局售票管理器申请购票数量,由全局售票管理器为每个进程返回相应的不重复的销售范围。进程接收范围后就可以并行调用选票存储过程处理了。
 楼主| 发表于 2009-11-1 13:01:52 | 显示全部楼层
本帖最后由 yinshi 于 2009-11-1 13:15 编辑

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

本版积分规则

QQ|申请友链|小黑屋|Archiver|手机版|MySQL社区 ( 京ICP备07012489号   
联系人:周生; 联系电话:13911732319

GMT+8, 2024-3-29 20:15 , Processed in 0.091166 second(s), 24 queries , Gzip On.

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表