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