mysql 死锁求解！！！

Question

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请教求解方案

Answer 1

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);这样就会根据你的索引情况，去选择是锁表还是锁行了。