mysql 死锁求解！！！

[安德烈_T]

I found there is a deadlock for your TrackIT system. The SQL should be related to your core Biz. Pls improve it. BTW, I always suspect our application always did table scan according to following envidence. Pls check the SQL that I highlighted.

------------------------
LATEST DETECTED DEADLOCK
------------------------
101028 10:04:19
*** (1) TRANSACTION:
TRANSACTION 0 78662038, ACTIVE 0 sec, process no 21427, OS thread id 1181301056 updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 527, query id 5179358 192.168.203.32 mysqladm Updating
update `hdt_call_ticket` set `appid`=48, `poster_id`=292901, `poster_sub_id`=93934, `poster_user_type`='E', `call_subtype_id`=null, `call_type_id`=501, `areas_affected`=null, `entry_datetime`='2010-10-28 09:44:51.0', `delete_id`=null, `delete_datetime`=null, `entry_id`=19255, `modify_datetime`='2010-10-28 10:01:01.0', `source_type`=7, `status_type`=1, `priority_type`=7, `severity_type`=1, `causecode_type`=null, `modify_user_id`=19255, `close_user_id`=null, `close_datetime`=null, `call_desc`='From: John Gillespie \r\nSent: Thursday, October 28, 2010 1:01 PM\r\nTo: ** Technical Support\r\nSubject: FW: Follow Up\r\nImportance: High\r\n', `close_desc`='', `invoice`='', `country_code`='US', `auto_email_alert`=1, `validation_no`='', `quote_no`=null where `ticketid`=416456
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4377629 page no 30 n bits 952 index `hdt_call_ticketI6` of table `HPDK/hdt_call_ticket` trx id 0 78662038 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 121 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000001; asc ;; 1: len 8; hex 8000000000065aca; asc Z ;;

*** (2) TRANSACTION:
TRANSACTION 0 78662009, ACTIVE 0 sec, process no 21427, OS thread id 1080588608 fetching rows, thread declared inside InnoDB 499
mysql tables in use 2, locked 2
6 lock struct(s), heap size 1216, undo log entries 814
MySQL thread id 328, query id 5179312 192.168.203.32 mysqladm Sending data
insert into tt_tmp_tickets(ticketid)
select ticketid
from hdt_call_ticket
where status_type <> NAME_CONST('v_close_status',6)

以上是DBA发给我的，

涉及的存储过程如下
BEGIN


DECLARE v_close_status INTEGER;

DROP TEMPORARY TABLE IF EXISTS tt_ticket_ids;
create TEMPORARY table tt_ticket_ids
(
seq BIGINT AUTO_INCREMENT PRIMARY KEY,
ticket_id NUMERIC(18,0) not null
);

DROP TEMPORARY TABLE IF EXISTS tt_ticket_temp;
create TEMPORARY table tt_ticket_temp
(
seq BIGINT AUTO_INCREMENT PRIMARY KEY,
ticket_id NUMERIC(18,0),
assign_id NUMERIC(18,0),
assign_to_group NUMERIC(18,0),
assign_to_user NUMERIC(18,0),
resolved_datetime DATETIME,
assign_by NUMERIC(18,0)

);

DROP TEMPORARY TABLE IF EXISTS ticket_temp;
create TEMPORARY table ticket_temp
(
seq BIGINT AUTO_INCREMENT PRIMARY KEY,
ticket_id NUMERIC(18,0),
assign_id NUMERIC(18,0),
assign_to_group NUMERIC(18,0),
assign_to_user NUMERIC(18,0),
resolved_datetime DATETIME,
assign_by NUMERIC(18,0)

);

DROP TEMPORARY TABLE IF EXISTS tt_ticket_as_temp;
create TEMPORARY table tt_ticket_as_temp
(
seq BIGINT AUTO_INCREMENT PRIMARY KEY,
ticket_id NUMERIC(18,0),
assign_id NUMERIC(18,0)
);

select ref_no INTO v_close_status from hdt_hardcode_xref f where f.hard_code = 'S002CLOSE' and f.type = 'STATUS';

DROP TEMPORARY TABLE IF EXISTS tt_tmp_tickets;
create TEMPORARY table tt_tmp_tickets
(
ticketid NUMERIC(18,0)
);

insert into tt_tmp_tickets(ticketid)
select ticketid
from hdt_call_ticket
where status_type <> v_close_status;

insert into tt_ticket_temp(ticket_id,assign_id,assign_to_group,assign_to_user,resolved_datetime,assign_by)
select ticket.ticketid ,assign.id,assign.assign_to_group, assign.assign_to_user ,assign.resolved_datetime ,assign.assigned_by
from tt_tmp_tickets as ticket, hdt_call_assignment as assign
where assign.ticketid = ticket.ticketid;

insert into tt_ticket_as_temp(ticket_id,assign_id)
select ticket.ticketid, max(assign.id)
from tt_tmp_tickets as ticket, hdt_call_assignment as assign
where ticket.ticketid = assign.ticketid
group by ticket.ticketid;

insert into ticket_temp(ticket_id,assign_id,assign_to_group,assign_to_user,resolved_datetime,assign_by)
select a.ticket_id ,a.assign_id,a.assign_to_group, a.assign_to_user ,a.resolved_datetime ,a.assign_by
from tt_ticket_as_temp b, tt_ticket_temp a
where b.ticket_id = a.ticket_id and b.assign_id = a.assign_id and a.assign_to_group = v_group_id;

if(v_assignby_id = -1) then

insert into tt_ticket_ids(ticket_id)
select ticket_id from ticket_temp where assign_to_user is null and resolved_datetime is null;

insert into tt_ticket_ids(ticket_id)
select ticket_id from ticket_temp where assign_to_user is not null and resolved_datetime is not null;

else

insert into tt_ticket_ids(ticket_id)
select ticket_id from ticket_temp where assign_by = v_assignby_id and assign_to_user is null and resolved_datetime is null;

insert into tt_ticket_ids(ticket_id)
select ticket_id from ticket_temp where assign_by = v_assignby_id and assign_to_user is not null and resolved_datetime is not null;

end if;
select distinct ticket_id from tt_ticket_ids order by ticket_id desc;

truncate table ticket_temp;
truncate table tt_ticket_temp;
truncate table tt_ticket_ids;
truncate table tt_ticket_as_temp;
truncate table tt_tmp_tickets;

drop TEMPORARY table IF EXISTS tt_tmp_tickets;
drop TEMPORARY table IF EXISTS ticket_temp;
drop TEMPORARY table IF EXISTS tt_ticket_temp;
drop TEMPORARY table IF EXISTS tt_ticket_ids;
drop TEMPORARY table IF EXISTS tt_ticket_as_temp;

END

请教求解方案

[zuoxingyu]

insert into tt_ticket_ids(ticket_id)
select ticket_id from ticket_temp where assign_by = v_assignby_id and assign_to_user is null and resolved_datetime is null;


insert into ..... select .....这样会导致后面select 的表锁表。改成
select select ticket_id into @A from ticket_temp where assign_by = v_assignby_id and assign_to_user is null and resolved_datetime is null;
insert into tt_ticket_ids(ticket_id) values(@A);

这样就会根据你的索引情况，去选择是锁表还是锁行了。

[djy5262]

学习关注中。