mysql 死锁求解!!!
------------------------
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
请教求解方案
相关推荐
Java后端笔试核心考点:JVM堆、JDK工具与MySQL引擎实战解析
在Java服务端工程师的招聘笔试中,算法、JVM内存、JDK工具链、数据库引擎与语言基本功往往是考察重点。理解存储分层模型,掌握堆内存的堆代结构,能借助JDK自带工具定位线上性能问题,熟悉InnoDB的事务、锁与MVCC机制,是后端开发者必备的排查能力。对称数求解、方法重载辨析以及DELETE与TRUNCATE的选型,则从代码实践与SQL语义两个维度检验工程经验。从基础概念到原理分析,再结合真实故障场景与性能调优命令,本文带你系统梳理这些高频考点,并延伸到候选数法实现、jstat监控、jstack死锁检测等
数据库原理第4版习题参考答案:考点拆解与实战验证指南
数据库原理是计算机领域的核心学科,常从关系代数、SQL语言、范式理论等基础概念展开。理解关系代数中选择与投影的次序、NOT EXISTS嵌套逻辑、模式分解的无损性等核心原理,是掌握数据库设计与数据管理的关键。这些原理不仅用于应对考试,更支撑着真实业务中的查询优化、事务一致性和并发控制。在工程实践中,借助MySQL等数据库工具复现习题场景,能够直观理解锁等待、死锁检测等机制,将抽象概念转化为可验证的实战经验。针对高频出现的“数据库面试题”和“数据库课程设计”,吃透教材例题解析能有效搭建从理论到应用的桥梁。围绕
计算机考研考博经典考题汇总(一次刷新世界观-我相信VIP总是有原因的)
操作系统 操作系统的特点? – 共享:资源可被多个并发执行的进程使用 – 并发:可以在同一时间间隔处理多个进程,需要硬件支持 – 虚拟:将物理实体映射成为多个虚拟设备 – 异步:进程执行走走停停,每次进程执行速度可能不同,但OS需保证进程每次执行结果相同 进程的三个组成部分? 程序段、数据段、PCB(Process Control Block) 并发与并行区别? 并发:同一间隔 并行:同一时刻 进程切换的过程? 保持处理机上下文 -> 更新PCB -> 把PCB移入相应队列(就绪、阻塞) -&g
企业数字化转型AI大模型赋能智能体平台及AI场景应用规划方案(生态合作版).pptx
企业数字化转型AI大模型赋能智能体平台及AI场景应用规划方案(生态合作版).pptx
MCGS的Http协议驱动,支持http协议
硬件配置 参数设置 设备属性 设备命令 通讯状态 附录 #HTTP 1、 设备简介 驱动类型 该驱动为独立设备,直接添加构件即可,不需要挂载到父设备下 通讯协议 采用标准HTTP1.1协议 通讯方式 驱动构件为客户端。 2、 硬件连接 本软件在通讯之前,请保证通讯连接正确。通讯连接方式:采用标准的RJ45接口的以太网接线(对调网线或使用HUB交换)。 3、 设备通讯参数 本设备为独立设备,无父设备参数 4、 设备构件参数设置 “HTTP设备”参数设置如下: <1> 内部属性: 构建url信息。 <2> 最小采集周期:本软件对设备进行操作的时间周期,单位为ms,默认为100ms。 <3> 通讯等待时间:通讯数据接收等待时间,默认设置为200ms,当采集速度要求较高或数据量较大时,设置值可适当减小或增大。 <4> HttpHead:http head添加需要的字段,不需要添加http head时值为空,默认不添加。 5、 设备命令 本设备构件提供设备命令,用于进行相应的操作,设备命令的格式如下: 设备命令 命令介绍 GetHttpHead 格式1 GetHttpHead(headStr) 用途 获取http head参数 参数 headStr 参数可以为字符型变量、字符串常量 例1 !SetDevice(设备0,6,"GetHttpHead(headStr)") 获取http head数据保存到headStr中 SetHttpHead 格式1 SetHttpHead(headStr) 用途 设置http head参数 参数 headStr 参数可以为字符型变量、字符串常量



