dea死锁处理和大事务处理
2024-08-27 05:05:26
死锁处理流程:
show full processlist; # 获得当前所有数据库连接
select id, db, user, host, command, time, state, info from information_schema.processlist where command != 'Sleep' order by time desc\G;
select id, db, user, host, command, time, state, info from information_schema.processlist order by time desc limit 10\G;
show engine innodb status; # 查看innodb的事务. select * from information_schema.innodb_trx;# 查找当前事务 select * from information_schema.innodb_locks;# 查找当前已经锁定的事务 select * from information_schema.innodb_lock_waits;# 查找当前等待锁的事务
select * from performance_schema.events_statements_current\G;
# 联系业务,确定是否可杀。
kill 线程id;# 若是lock tables语句锁定,则比较麻烦 #
select trx_id,INNODB_TRX.trx_state,INNODB_TRX.trx_started,se.conn_id as processlist_id,trx_lock_memory_bytes,se.user,se.command,se.state,se.current_statement,se.last_statement from information_schema.INNODB_TRX,sys.session as se where trx_mysql_thread_id=conn_id\G;
# 1、时间转换
select unix_timestamp('2019-12-05 12:26:35');
select from_unixtime(1515980716); # 2、将二进制文件转换为可读性的sql语句,里面有事务的时间戳和线程id
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
# 若是其他二进制格式,则需要先转换
# mysqlbinlog --base64-output=DECODE-ROWS -v -v mysql-bin.001361 > glc_bin_1361.sql
#
# 3、匹配时间戳或者线程id,找出问题的事务。
# 未提交长事务,这里仅仅打印出大于3秒的未提交事务
select p.id as thread_id,
p.user as user,
p.host as host,
p.db as database_name,
t.trx_started,
p.time as trx_sleep_seconds,
time_to_sec(timediff(now(),t.trx_started)) as trx_open_seconds,
t.trx_isolation_level,
t.trx_tables_locked,
t.trx_rows_locked,
t.trx_state,
p.command as process_state
from information_schema.innodb_trx t inner join information_schema.processlist p on t.trx_mysql_thread_id=p.id
where t.trx_state='RUNNING' and p.command='Sleep' and p.time> \G;
# 获取某个线程执行过的最近10条sql语句
select h.thread_id,
h.event_id,
h.event_name,
(h.timer_end-h.timer_start)/10000000000 times,
h.current_schema,
h.sql_text,
h.rows_affected
from performance_schema.events_statements_history h
where h.thread_id=18495382
order by h.thread_id,h.event_id limit 1000 \G;
最新文章
- 【干货分享】流程DEMO-补打卡
- django 1.10 CSRF验证失败的解决过程
- awk多模式匹配
- Java abstract
- Caffe 深度学习框架介绍
- Cryptography加密和解密
- Python学习笔记1——人人都爱列表
- c++封装性
- AngularJs 基础(60分钟入门) (转)
- EF实体类配置总结
- javascript焦点图之垂直滚动
- shell写多行到文件中
- Swift中实现Observable机制
- XDM-跨文档消息传送
- 集合——iterator迭代器
- Vistual Studio Code配置
- 16 多校8 Rikka with Parenthesis II
- linux 3.10 gro的理解和改进
- magento开发中文手册
- 训练赛第二场C题 zoj 2339 Hyperhuffman