一、查询当前事务的隔离级别
select @@tx_isolation;
二、查询执行中进程
1.语法介绍：
用于查看当前数据库有哪些线程正在运行，可以看到一共有多少个连接数，哪些是正在查询，哪些是休眠状态。在遇到mysql出现性能问题，慢sql等问题时，可以及时的查询到问题sql，以及关闭线程。
如果有root权限，则可以查看所有主题。否则，只能看到自己的线程（即正在使用的MySQL帐户关联的线程）
show full processlist;
或
show processlist;
区别：
如果不使用FULL关键字，只列出前100条，如果想全列出请使用show full processlist;
返回结果中State列值解释：
After create 当线程在创建表的函数末尾创建表（包括内部临时表）时，会发生这种情况。即使由于某些错误而无法创建表，也会使用此状态
Analyzing 线程正在计算MyISAM表键分布
checking permissions 线程正在检查服务器是否具有执行语句所需的权限
Checking table 该线程正在执行表检查操作
cleaning up 该线程已经处理了一个命令，并准备释放内存并重置某些状态变量
closing tables 该线程正在将更改的表数据刷新到磁盘并关闭已使用的表，这应该是一个快速的操作。如果没有，请验证您没有完整磁盘并且磁盘使用不是很大
converting HEAP to ondisk 该线程正在将内部临时表从 MEMORY表转换为磁盘表
copy to tmp table 线程正在处理一个ALTER TABLE语句。在创建具有新结构的表但在将行复制到其中之前，将发生此状态。对于处于此状态的线程，可以使用性能模式来获取有关复制操作的进度
Copying to group table 如果语句具有不同的条件ORDER BY和 GROUP BY标准，则按组对行进行排序并将其复制到临时表
Copying to tmp table 服务器正在复制到内存中的临时表
altering table 服务器正在执行ALTER TABLE
Copying to tmp table on disk 服务器正在复制到磁盘上的临时表，临时结果集变得太大， 因此，线程正在将临时表从内存更改为基于磁盘的格式以节省内存
Creating index 线程正在处理ALTER TABLE … ENABLE KEYS一个MyISAM表
Creating sort index 线程正在处理SELECT使用内部临时表解析的线程
creating table 线程正在创建一个表，这包括创建临时表
Creating tmp table 该线程正在内存或磁盘上创建临时表，如果表在内存中创建但稍后转换为磁盘表，则该操作期间的状态将为Copying to tmp table on disk
committing alter table to storage engine 服务器已完成 ALTER TABLE并提交结果
deleting from main table 服务器正在执行多表删除的第一部分，它仅从第一个表中删除，并保存用于从其他（引用）表中删除的列和偏移量
deleting from reference tables 服务器正在执行多表删除的第二部分，并从其他表中删除匹配的行
discard_or_import_tablespace 线程正在处理ALTER TABLE … DISCARD TABLESPACE或ALTER TABLE … IMPORT TABLESPACE
end 这发生在结束，但是在清理之前 ALTER TABLE， CREATE VIEW， DELETE， INSERT， SELECT，或 UPDATE语句
executing 该线程已开始执行语句
Execution of init_command 线程正在执行init_command系统变量值中的语句
freeing items 线程执行了一个命令，在此状态期间完成的一些项目的释放涉及查询缓存，这种状态通常紧随其后cleaning up
FULLTEXT initialization 服务器正准备执行自然语言全文搜索
init 出现这种情况的初始化之前 ALTER TABLE， DELETE， INSERT， SELECT，或 UPDATE语句。服务器在此状态下采取的操作包括刷新二进制日志，InnoDB日志和一些查询缓存清理操作 对于end中，可能会发生以下操作：1.删除表中的数据后删除查询缓存条目2.将事件写入二进制日志3.释放内存缓冲区，包括blob
Killed 有人KILL 向线程发送了一个语句，它应该在下次检查kill标志时中止。在MySQL的每个主循环中检查该标志，但在某些情况下，线程可能仍然需要很短的时间才能死掉。如果线程被某个其他线程锁定，则一旦另一个线程释放其锁定，kill就会生效
logging slow query 该线程正在向慢查询日志写一条语句
login 连接线程的初始状态，直到客户端成功通过身份验证
manage keys 服务器正在启用或禁用表索引
NULL 该状态用于SHOW PROCESSLIST时的状态
Opening tables 线程正在尝试打开一个表。这应该是非常快的程序，除非有什么东西阻止打开。例如，一个ALTER TABLE或一个 LOCK TABLE语句可以阻止在语句结束之前打开表。还值得检查您的table_open_cache价值是否足够大
optimizing 服务器正在对查询执行初始优化
preparing 在查询优化期间发生此状态
Purging old relay logs 该线程正在删除不需要的中继日志文件
query end 处理查询后但在freeing items状态之前发生此状态
Receiving from client 服务器正在从客户端读取数据包，Reading from net在MySQL 5.7.8之前调用此状态
Removing duplicates 该查询使用 SELECT DISTINCT的方式是MySQL无法在早期阶段优化掉不同的操作。因此，在将结果发送到客户端之前，MySQL需要额外的阶段来删除所有重复的行
removing tmp table 该线程在处理SELECT 语句后删除内部临时表。如果未创建临时表，则不使用此状态
rename 该线程正在重命名一个表
rename result table 线程正在处理一个ALTER TABLE语句，创建了新表，并重命名它以替换原始表
Reopen tables 该线程获得了表的锁定，但在获取锁定之后注意到基础表结构发生了变化。它释放了锁，关闭了桌子，并试图重新打开它
Repair by sorting 修复代码使用排序来创建索引
preparing for alter table 服务器正准备执行就地 ALTER TABLE
Repair done 该线程已完成对MyISAM表的多线程修复
Repair with keycache 修复代码通过密钥缓存逐个创建密钥，这比慢得多Repair by sorting
Rolling back 该线程正在回滚一个事务
Saving state 对于MyISAM诸如修复或分析的表操作，线程将新表状态保存到.MYI文件头。状态包括诸如行数， AUTO_INCREMENT计数器和密钥分发之类的信息
Searching rows for update 该线程正在进行第一阶段以在更新之前查找所有匹配的行。如果 UPDATE要更改用于查找所涉及行的索引，则必须执行此操作
Sending data 线程正在读取和处理SELECT语句的行 ，并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量磁盘访问（读取），因此它通常是给定查询生命周期中运行时间最长的状态
Sending to client 服务器正在向客户端写入数据包，Writing to net在MySQL 5.7.8之前调用此状态
setup 线程正在开始一个ALTER TABLE操作
Sorting for group 线程正在进行排序以满足GROUP BY
Sorting for order 线程正在进行排序以满足ORDER BY
Sorting index 该线程正在对索引页面进行排序，以便在MyISAM表优化操作期间进行更有效的访
Sorting result 对于SELECT声明，这类似于Creating sort index非临时表
statistics 服务器正在计算统计信息以开发查询执行计划，如果线程长时间处于此状态，则服务器可能是磁盘绑定执行其他工作
System lock 该线程已调用mysql_lock_tables() ，并且线程状态尚未更新。这是一个非常普遍的状态，可能由于多种原因而发生。例如，线程将请求或正在等待表的内部或外部系统锁定。InnoDB在执行期间等待表级锁定时会 发生这种情况LOCK TABLES
update 线程正准备开始更新表
Updating 线程正在搜索要更新的行并正在更新它们
updating main table 服务器正在执行多表更新的第一部分，它仅更新第一个表，并保存用于更新其他（引用）表的列和偏移量
updating reference tables 服务器正在执行多表更新的第二部分，并更新其他表中的匹配行
User lock 该线程将要求或正在等待通过GET_LOCK()呼叫请求的咨询锁，对于 SHOW PROFILE，此状态表示线程正在请求锁定（不等待它）
User sleep 线程已经调用了一个 SLEEP()调用
Waiting for commit lock FLUSH TABLES WITH READ LOCK 正在等待提交锁定
Waiting for global read lock FLUSH TABLES WITH READ LOCK 正在等待全局读锁定或read_only正在设置全局 系统变量
Waiting for tables 线程得到一个通知，表明表的底层结构已经改变，它需要重新打开表以获得新结构。但是，要重新打开表，它必须等到所有其他线程关闭了相关表
Waiting for table flush 线程正在执行FLUSH TABLES并且正在等待所有线程关闭它们的表，或者线程得到一个表的基础结构已经更改的通知，并且它需要重新打开表以获取新结构。但是，要重新打开表，它必须等到所有其他线程关闭了相关表
Waiting for lock_type lock 服务器正在等待THR_LOCK从元数据锁定子系统获取 锁定或锁定，其中 lock_type指示锁定的类型，此状态表示等待 THR_LOCK：Waiting for table level lock 这些状态表示等待元数据锁定：1.Waiting for event metadata lock 2.Waiting for global read lock 3.Waiting for schema metadata lock 4.Waiting for stored function metadata lock 5.Waiting for stored procedure metadata lock 6.Waiting for table metadata lock 7.Waiting for trigger metadata lock
Waiting on cond 线程正在等待条件变为真的通用状态
Writing to net 服务器正在将数据包写入网络，Sending to client从MySQL 5.7.8开始调用此状态
三、查询当前执行中的所有事务
select * from information_schema.innodb_trx;
INNODB_TRX 表包含信息关于每个事务(排除只读事务)当前执行的在InnoDB,包含是否事务是等待一个锁, 当事务启动后, SQL语句事务是正在执行
INNODB_TRX Columns 相关列信息:
a) trx_id：innodb存储引擎内部事务唯一的事务id。
b) trx_state：当前事务的状态。
c) trx_started：事务开始的时间。
d) trx_requested_lock_id：等待事务的锁id，如trx_state的状态为LOCK WAIT，那么该值代表当前事务之前占用锁资源的id，如果trx_state不是LOCK WAIT的话，这个值为null。
e) trx_wait_started：事务等待开始的时间。
f) trx_weight：事务的权重，反映了一个事务修改和锁住的行数。在innodb的存储引擎中，当发生死锁需要回滚时，innodb存储引擎会选择该值最小的事务进行回滚。
g) trx_mysql_thread_id：正在运行的mysql中的线程id，show full processlist显示的记录中的thread_id。
h) trx_query：事务运行的sql语句，在实际中发现，有时会显示为null值，当为null的时候，就是t2事务中等待锁超时直接报错（ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction）后，trx_query就显示为null值
比如事务t2正在运行trx_query: update test.t1 set b=“t2” where a=1的sql语句，t1先执行，所以是trx_state: RUNNING先申请的资源一直在运行，而t2后run的所以是trx_state: LOCK WAIT一直在等待t1执行完后释放资源。 但是并不能仔细判断锁的一些详细情况，我们需要再去看当前锁定的事务表数据。
四、查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
INNODB_LOCKS 表包含信息关于每个锁一个InnoDB 事务已经请求,但是没有获得锁,每个lock一个事务持有是堵塞另外一个事务
INNODB_LOCKS Columns 相关列信息:
a) lock_id：锁的id以及被锁住的空间id编号、页数量、行数量
b) lock_trx_id：锁的事务id。
c) lock_mode：锁的模式。
d) lock_type：锁的类型，表锁还是行锁
e) lock_table：要加锁的表。
f) lock_index：锁的索引。
g) lock_space：innodb存储引擎表空间的id号码
h) lock_page：被锁住的页的数量，如果是表锁，则为null值。
i) lock_rec：被锁住的行的数量，如果表锁，则为null值。
j) lock_data：被锁住的行的主键值，如果表锁，则为null值。
如以下查询 :
mysql> select * from INNODB_LOCKSG
- row **
lock_id: 3015646:797:3:2
lock_trx_id: 3015646
lock_mode: X
lock_type: RECORD
lock_table: test.t1
lock_index: PRIMARY
lock_space: 797
lock_page: 3
lock_rec: 2
lock_data: 1
- row **
lock_id: 3015645:797:3:2
lock_trx_id: 3015645
lock_mode: X
lock_type: RECORD
lock_table: test.t1
lock_index: PRIMARY
lock_space: 797
lock_page: 3
lock_rec: 2
lock_data: 1
2 rows in set (0.00 sec)
这里我们可以看到当前的锁信息了，2个事务都锁定了，看相同的数据lock_space: 797、lock_page: 3、lock_rec: 2可以得出事务t1和事务t2访问了相同的innodb数据块，再通过lock_data字段信息lock_data: 1，看到锁定的数据行都是主键为1的数据记录，可见2个事务t1和t2都申请了相同的资源，因此会被锁住，事务在等待。
通过lock_mode: X值也可以看出事务t1和t2申请的都是排它锁。
PS：当执行范围查询更新的时候，这个lock_data的值并非是完全准确。当我们运行一个范围更新时，lock_data只返回最先找到的第一行的主键值id；另外如果当前资源被锁住了，与此同时由于锁住的页因为InnoDB存储引擎缓冲池的容量，而导致替换缓冲池页面，再去查看INNODB_LOCKS表时，这个lock_data会显示未NULL值，意味着InnoDB存储引擎不会从磁盘进行再一次查找。
五、查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
INNODB_LOCK_WAITS 表包含了blocked的事务的锁等待的状态。当事务量比较少，我们可以直观的查看，当事务量非常大，锁等待也时常发生的情况下，这个时候可以通过INNODB_LOCK_WAITS表来更加直观的反映出当前的锁等待情况：
INNODB_LOCK_WAITSColumns 相关列信息:
a) requesting_trx_id：申请锁资源的事务id。
b) requested_lock_id：申请的锁的id。
c) blocking_trx_id：阻塞的事务id。
d) blocking_lock_id：阻塞的锁的id。
如以下查询:
mysql> select * from INNODB_LOCK_WAITSG
- row **
requesting_trx_id: 3015646
requested_lock_id: 3015646:797:3:2
blocking_trx_id: 3015645
blocking_lock_id: 3015645:797:3:2
1 row in set (0.00 sec)
mysql>
这里我们可以看到事务t1(3015646)申请了锁资源，而事务t2(3015645)则阻塞了事务t1的申请。
如何处理死锁
杀死进程
kill 进程ID
通过以上方法一可以查询对应死锁的数据库进程,可以直接杀掉
如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。
虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚而由应用程序重新提交。
下列方法有助于最大限度地降低死锁：
（1）按同一顺序访问对象。
（2）避免事务中的用户交互。
（3）保持事务简短并在一个批处理中。
（4）使用低隔离级别。
（5）使用绑定连接。
MySql锁表-解锁方法
-- 锁表解决方法
show full processlist; -- 显示完整的进程列表
select * from information_schema.innodb_trx; -- 查看有是哪些事务占据了表资源
kill 436;
查询出 连接状态是休眠 执行时间较长的进程
select * from information_schema.processlist where command != ‘Sleep’ and time > 5*60 order by time desc;
查询出 连接状态是休眠指定数据库
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE command !=‘sleep’ and db=‘acctint db’;
扫一扫
762
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
