dea死锁处理和大事务处理

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，找出问题的事务。

mysql> select connection_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>3  \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;

在实际应用中，我们要尽量防止锁等待现象的发生，下面介绍几种避免死锁的方法：

如果不同程序会并发存取多个表，或者涉及多行记录时，尽量约定以相同的顺序访问表，这样可以大大降低死锁的发生。

业务中要及时提交或者回滚事务，可减少死锁产生的概率。

在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。

对于非常容易产生死锁的业务部分，可以尝试使用升级锁粒度，通过表锁定来减少死锁产生的概率（表级锁不会产生死锁）。