13518219792
以下五种方法可以快速定位全局锁的位置，仅供参考。
创新互联10多年成都定制网站服务;为您提供网站建设,网站制作,网页设计及高端网站定制服务,成都定制网站及推广,对OPP胶袋等多个方面拥有丰富的网站运维经验的网站建设公司。
方法1：利用 metadata_locks 视图
此方法仅适用于 MySQL 5.7 以上版本，该版本 performance_schema 新增了 metadata_locks，如果上锁前启用了元数据锁的探针（默认是未启用的），可以比较容易的定位全局锁会话。
方法2：利用 events_statements_history 视图此方法适用于 MySQL 5.6 以上版本，启用 performance_schema.eventsstatements_history（5.6 默认未启用，5.7 默认启用），该表会 SQL 历史记录执行，如果请求太多，会自动清理早期的信息，有可能将上锁会话的信息清理掉。
方法3：利用 gdb 工具如果上述两种都用不了或者没来得及启用，可以尝试第三种方法。利用 gdb 找到所有线程信息，查看每个线程中持有全局锁对象，输出对应的会话 ID，为了便于快速定位，我写成了脚本形式。也可以使用 gdb 交互模式，但 attach mysql 进程后 mysql 会完全 hang 住，读请求也会受到影响，不建议使用交互模式。
方法4：show processlist
如果备份程序使用的特定用户执行备份，如果是 root 用户备份，那 time 值越大的是持锁会话的概率越大，如果业务也用 root 访问，重点是 state 和 info 为空的，这里有个小技巧可以快速筛选，筛选后尝试 kill 对应 ID，再观察是否还有 wait global read lock 状态的会话。
方法5：重启试试！
查询死锁进程
采用如下存储过程来查询数据中当前造成死锁的进程。
drop procedure sp_who_lock
go
CREATE procedure sp_who_lock
as
begin
declare @spid int
declare @blk int
declare @count int
declare @index int
declare @lock tinyint
set @lock=0
create table #temp_who_lock
(
id int identity(1,1),
spid int,
blk int
)
if @@error0 return @@error
insert into #temp_who_lock(spid,blk)
select 0 ,blocked
from (select * from master..sysprocesses where blocked0)a
where not exists(select * from master..sysprocesses where a.blocked =spid and blocked0)
union select spid,blocked from master..sysprocesses where blocked0
if @@error0 return @@error
select @count=count(*),@index=1 from #temp_who_lock
if @@error0 return @@error
if @count=0
begin
select '没有阻塞和死锁信息'
return 0
end
while @indexA href="mailto:=@count"=@count
begin
if exists(select 1 from #temp_who_lock a where id@index and exists(select 1 from #temp_who_lock where idA href="mailto:=@index"=@index and a.blk=spid))
begin
set @lock=1
select @spid=spid,@blk=blk from #temp_who_lock where id=@index
select '引起数据库死锁的是: '+ CAST(@spid AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
select @spid, @blk
dbcc inputbuffer(@spid)
dbcc inputbuffer(@blk)
end
set @index=@index+1
end
if @lock=0
begin
set @index=1
while @indexA href="mailto:=@count"=@count
begin
select @spid=spid,@blk=blk from #temp_who_lock where id=@index
if @spid=0
select '引起阻塞的是:'+cast(@blk as varchar(10))+ '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@blk AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
dbcc inputbuffer(@spid)
dbcc inputbuffer(@blk)
set @index=@index+1
end
end
drop table #temp_who_lock
return 0
end
GO
--执行该存储过程
exec sp_who_lock
补充：
一、产生死锁的原因
在SQL Server中，阻塞更多的是产生于实现并发之间的隔离性。为了使得并发连接所做的操作之间的影响到达某一期望值而对资源人为的进行加锁(锁本质其实可以看作是一个标志位)。当一个连接对特定的资源进行操作时，另一个连接同时对同样的资源进行操作就会被阻塞，阻塞是死锁产生的必要条件。
二、如何避免死锁
1.使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；
2.设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；
3.优化程序，检查并避免死锁现象出现；
4.对所有的脚本和SP都要仔细测试，在正是版本之前；
5.所有的SP都要有错误处理（通过@error）；
6.一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁。
三、处理死锁
1、最简单的处理死锁的方法就是重启服务。
2、根据指定的死锁进程ID进行处理
根据第二步查询到的死锁进行，大致分析造成死锁的原因，并通过如下语句释放该死锁进程
kill pid --pid为查询出来的死锁进程号
3、通过存储过程杀掉某个库下面的所有死锁进程和锁
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_killspid]
GO
create proc sp_killspid
@dbname varchar(200) --要关闭进程的数据库名
as
declare @sql nvarchar(500)
declare @spid nvarchar(20)
declare #tb cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #tb
fetch next from #tb into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #tb into @spid
end
close #tb
deallocate #tb
go
--使用方法，“db_name”为处理的数据库名称
exec sp_killspid 'db_name'
第一步，查出已锁的进程
查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
``
查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
``
INNODB_TRX表主要是包含了正在InnoDB引擎中执行的所有事务的信息，包括waiting for a lock和running的事务
select * from information_schema.innodb_trx
``
第二步，kill进程
show engin innodb status; //最后一次死锁信息及sql
show open tables where in_use 0 //查看锁表
1.查看表是否被锁：
（1）直接在mysql命令行执行:showengineinnodbstatus\G。
（2）查看造成死锁的sql语句，分析索引情况，然后优化sql。
（3）然后showprocesslist,查看造成死锁占用时间长的sql语句。
（4）showstatuslike‘%lock%。
2.查看表被锁状态和结束死锁步骤：
（1）查看表被锁状态：showOPENTABLESwhereIn_use0;这个语句记录当前锁表状态。
（2）查询进程：showprocesslist查询表被锁进程；查询到相应进程killid。
（3）分析锁表的SQL：分析相应SQL，给表加索引，常用字段加索引，表关联字段加索引。
（4）查看正在锁的事物：SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCKS。
（5）查看等待锁的事物：SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCK_WAITS。
扩展资料
MySQL锁定状态查看命令：
Checkingtable：正在检查数据表（这是自动的）。
Closingtables：正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
ConnectOut：复制从服务器正在连接主服务器。
Copyingtotmptableondisk：由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creatingtmptable：正在创建临时表以存放部分查询结果。
deletingfrommaintable：服务器正在执行多表删除中的第一部分，刚删除第一个表。
deletingfromreferencetables：服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushingtables：正在执行FLUSHTABLES，等待其他线程关闭数据表。
Killed：发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked：被其他查询锁住了。
Sendingdata：正在处理SELECT查询的记录，同时正在把结果发送给客户端。
Sortingforgroup：正在为GROUPBY做排序。
Sortingfororder：正在为ORDERBY做排序。
Openingtables：这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTERTABLE或LOCKTABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removingduplicates：正在执行一个SELECTDISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopentable：获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repairbysorting：修复指令正在排序以创建索引。
Repairwithkeycache：修复指令正在利用索引缓存一个一个地创建新索引。它会比Repairbysorting慢些。
Searchingrowsforupdate：正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
Sleeping：正在等待客户端发送新请求。
Systemlock：正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
Upgradinglock：INSERTDELAYED正在尝试取得一个锁表以插入新记录。
Updating：正在搜索匹配的记录，并且修改它们。
UserLock：正在等待GET_LOCK()。
Waitingfortables：该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。
waitingforhandlerinsert：INSERTDELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。
锁是需要事务结束后才释放的。
一个是 MVCC，一个是两阶段锁协议。
为什么要并发控制呢？是因为多个用户同时操作 MySQL 的时候，为了提高并发性能并且要求如同多个用户的请求过来之后如同串行执行的一样（为了解决脏读、不可重复读、幻读）
官方定义：
两阶段锁协议是指所有事务必须分两个阶段对数据加锁和解锁，在对任何数据进行读、写操作之前，事务首先要获得对该数据的封锁；在释放一个封锁之后，事务不再申请和获得任何其他封锁。
对应到 MySQL 上分为两个阶段：
但是两阶段锁协议不要求事务必须一次将所有需要使用的数据加锁（innodb在需要的索引列数据才锁行），并且在加锁阶段没有顺序要求，所以这种并发控制方式会形成死锁。
MySQL有两种死锁处理方式：
死锁检测 （默认开启）
死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。
回滚
检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。
收集死锁信息：
减少死锁：
死锁解决：
