概述
以下资料是网络收集外加部分个人理解,当前数据库版本:MySQL Ver 14.14 Distrib 5.7.11, for Win64 (x86_64)。
记得以前,当出现:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction,要解决是一件麻烦的事情。特别是当一个SQL执行完了,但未COMMIT,后面的SQL想要执行就是被锁,超时后结束,DBA光从数据库无法着手找出源头是哪个SQL锁住了。有时候看看 show engine innodb status, 并结合 show full processlist 能暂时解决问题,但一直不能精确定位。
在5.5中,information_schema 库中增加了三个关于锁的表(MEMORY引擎)
Innodb锁
Innodb存储引擎实现了如下2种标准的行级锁:
共享锁(S lock),允许事务读取一行数据,select语句后加lock in share mode。
排它锁(X lock),允许事务删除或者更新一行数据,select语句后加for update。
当一个事务获取了行r的共享锁,那么另外一个事务也可以立即获取行r的共享锁,因为读取并未改变行r的数据,这种情况就是锁兼容。但是如果有事务想获得行r的排它锁,则它必须等待事务释放行r上的共享锁—这种情况就是锁不兼容,二者兼容性如下表格所示:
X 排它锁
S 共享锁
X 排它锁
冲突
冲突
S 共享锁
冲突
兼容
另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),不需要我们代码控制,这两种意向锁都是表锁。
意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
意向锁的作用:
因为表锁覆盖了行锁的数据,所以表锁和行锁也会产生冲突。比如A事务申请表锁,B事务申请行级锁,或者A事务申请行级锁,B事务申请表锁。这时候B事务的申请是需要被阻塞的。那么怎么判断B事务该阻塞呢?遍历表的每一行看看是否有行级锁吗?这样效率非常差。这时候就引入了意向锁。在申请行锁前,数据库自动为我们申请了对应的意向锁,因为意向锁是表锁,这时候如果再申请表锁,就自然会阻塞了。
innodb_locks
当前出现的锁
lock_id
InnoDB内部的唯一锁ID
lock_trx_id
拥有这个锁的事务ID
lock_mode
锁模式,S, X, IS, IX等
lock_type
锁类型,RECORD或者TABLE
lock_table
被锁的表或包含被锁记录的表
lock_index
被锁的索引,不是行级锁时为NULL
lock_space
被锁的表空间号,不是行级锁时为NULL
lock_page
被锁的页号,不是行级锁时为NULL
lock_rec
被锁的Heap号,不是行级锁时为NULL
lock_data
被锁的记录的主键,不是行级锁时为NULL
innodb_lock_waits
当前等待的锁
requesting_trx_id
正在请求的、受阻的事务ID
requested_lock_id
事务正在等待的锁ID
blocking_trx_id
阻塞其他事务的事务ID
blocking_lock_id
阻塞其他事务的事务持有的锁ID
innodb_trx
当前所有事务
trx_id
InnoDB内部的唯一事务ID
trx_state
事务状态,RUNNING, LOCK WAIT等
trx_started
事务开始时间
trx_requested_lock_id
事务正在等待的锁ID
trx_wait_started
事务开始等待的时间
trx_weight
事务的权重,当发生死锁回滚的时候,优先选择该值最小的进行回滚
trx_mysql_thread_id
事务线程ID,即show full processlist中的ID
trx_query
执行的SQL语句
trx_operation_state
事务当前操作状态
trx_tables_in_use
执行当前SQL时有多少个表被使用
trx_tables_locked
执行当前SQL时有多少个表有行锁
trx_lock_structs
事务保留的锁的数量
trx_lock_memory_bytes
事务锁占据的内存大小(B)
trx_rows_locked
事务锁定的大概行数
trx_rows_modified
事务修改和插入的行数
trx_concurrency_tickets
即innodb_concurrency_tickets系统变量
trx_isolation_level
事务隔离级别
trx_unique_checks
是否唯一性检查
trx_foreign_key_checks
是否外键检查
trx_last_foreign_key_error
最后的外键错误详细信息
trx_adaptive_hash_latched
trx_adaptive_hash_timeout
trx_is_read_only
1表示事务是只读的
trx_autocommit_non_locking
1表示事务是不包含FOR UPDATE或者LOCK IN SHARE MODE语句,并且autocommit是enable的
以上翻译并不完整,如果有疑问的可以参考官网。
步骤
首先:
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
获取到blocking_trx_id
然后:
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
查找trx_id和上面获取到的blocking_trx_id一样的记录,获取这条记录的trx_mysql_thread_id
最后:
kill 上面获取到的trx_mysql_thread_id
这样就把阻塞其他事务的事务线程杀掉了。