文档章节

记一次处理MySql锁等待(Lock wait timeout exceeded)

-蒋全忠-
 -蒋全忠-
发布于 2014/04/14 15:31
字数 1385
阅读 36263
收藏 10
  • 环境

    MySQL5.5 

  • 现象

    A.数据更新或新增后数据经常自动回滚。

    B.表操作总报 Lock wait timeout exceeded 并长时间无反应

  • 解决方法

    A.应急方法:show processlist; kill掉出现问题的进程

    B.根治方法:select * from innodb_trx 查看有是哪些事务占据了表资源。

        C.我的方法:设置MySQL锁等待超时 innodb_lock_wait_timeout=50 ,autocommit=on

  • 该类问题导致原因

    据我分析,Mysql的 InnoDB存储引擎是支持事务的,事务开启后没有被主动Commit。导致该资源被长期占用,其他事务在抢占该资源时,因上一个事务的锁而导致抢占失败!因此出现 Lock wait timeout exceeded 

  • 接下来是转载的内容



  • 转载:http://blog.sina.com.cn/s/blog_6bb63c9e0100s7cb.html

  • MySQL 5.5 -- innodb_lock_wait 锁 等待

  • 记得以前,当出现: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_trx ## 当前运行的所有事务
    innodb_locks ## 当前出现的锁
    innodb_lock_waits ## 锁等待的对应关系

  • 看到这个就非常激动 ; 这可是解决了一个大麻烦,先来看一下表结构


  • root@127.0.0.1   : information_schema 13:28:38> desc innodb_locks;
    +-------------+---------------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------------+---------------------+------+-----+---------+-------+
    | lock_id | varchar(81) | NO | | | |#锁ID
    | lock_trx_id | varchar(18) | NO | | | |#拥有锁的事务ID
    | lock_mode | varchar(32) | NO | | | |#锁模式
    | lock_type | varchar(32) | NO | | | |#锁类型
    | lock_table | varchar(1024) | NO | | | |#被锁的表
    | lock_index | varchar(1024) | YES | | NULL | |#被锁的索引
    | lock_space | bigint(21) unsigned | YES | | NULL | |#被锁的表空间号
    | lock_page | bigint(21) unsigned | YES | | NULL | |#被锁的页号
    | lock_rec | bigint(21) unsigned | YES | | NULL | |#被锁的记录号
    | lock_data | varchar(8192) | YES | | NULL | |#被锁的数据
    +-------------+---------------------+------+-----+---------+-------+
    10 rows in set (0.00 sec)

    root@127.0.0.1   : information_schema 13:28:56> desc innodb_lock_waits;
    +-------------------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------------------+-------------+------+-----+---------+-------+
    | requesting_trx_id | varchar(18) | NO | | | |#请求锁的事务ID
    | requested_lock_id | varchar(81) | NO | | | |#请求锁的锁ID
    | blocking_trx_id | varchar(18) | NO | | | |#当前拥有锁的事务ID
    | blocking_lock_id | varchar(81) | NO | | | |#当前拥有锁的锁ID
    +-------------------+-------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)

    root@127.0.0.1   : information_schema 13:29:05> desc innodb_trx ;
    +----------------------------+---------------------+------+-----+---------------------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------------------------+---------------------+------+-----+---------------------+-------+
    | trx_id | varchar(18) | NO | | | |#事务ID
    | trx_state | varchar(13) | NO | | | |#事务状态:
    | trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事务开始时间;
    | trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id
    | trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间
    | trx_weight | bigint(21) unsigned | NO | | 0 | |#
    | trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事务线程ID
    | trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句
    | trx_operation_state | varchar(64) | YES | | NULL | |#事务当前操作状态
    | trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事务中有多少个表被使用
    | trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事务拥有多少个锁
    | trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
    | trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事务锁住的内存大小(B)
    | trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事务锁住的行数
    | trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事务更改的行数
    | trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事务并发票数
    | trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别
    | trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查
    | trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查
    | trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最后的外键错误
    | trx_adaptive_hash_latched | int(1) | NO | | 0 | |#
    | trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |#
    +----------------------------+---------------------+------+-----+---------------------+-------+
    22 rows in set (0.01 sec)

  • 下面我们来动手看看数据吧:
    ##建立测试数据:
    use test;
    create table tx1
    (id int primary key ,
    c1 varchar(20),
    c2 varchar(30))
    engine=innodb default charset = utf8 ;

  • insert into tx1 values
    (1,'aaaa','aaaaa2'),
    (2,'bbbb','bbbbb2'),
    (3,'cccc','ccccc2');

  • commit;

  • ###产生事务;
    ### Session1
    start transaction;
    update tx1 set c1='heyf',c2='heyf' where id =3 ;

  • ## 产生事务,在innodb_trx就有数据 ;
    root@127.0.0.1   : information_schema 13:38:21> select * from innodb_trx G
    *************************** 1. row ***************************
    trx_id: 3669D82
    trx_state: RUNNING
    trx_started: 2010-12-24 13:38:06
    trx_requested_lock_id: NULL
    trx_wait_started: NULL
    trx_weight: 3
    trx_mysql_thread_id: 2344
    trx_query: NULL
    trx_operation_state: NULL
    trx_tables_in_use: 0
    trx_tables_locked: 0
    trx_lock_structs: 2
    trx_lock_memory_bytes: 376
    trx_rows_locked: 1
    trx_rows_modified: 1
    trx_concurrency_tickets: 0
    trx_isolation_level: REPEATABLE READ
    trx_unique_checks: 1
    trx_foreign_key_checks: 1
    trx_last_foreign_key_error: NULL
    trx_adaptive_hash_latched: 0
    trx_adaptive_hash_timeout: 10000
    1 row in set (0.00 sec)

  • ### 由于没有产生锁等待,下面两个表没有数据 ;
    root@127.0.0.1   : information_schema 13:38:31> select * from innodb_lock_waits G
    Empty set (0.00 sec)

  • root@127.0.0.1   : information_schema 13:38:57> select * from innodb_locks G
    Empty set (0.00 sec)

  • #### 产生锁等待
    #### session 2
    start transaction;
    update tx1 set c1='heyfffff',c2='heyffffff' where id =3 ;


  • root@127.0.0.1   : information_schema 13:39:01> select * from innodb_trx G
    *************************** 1. row ***************************
    trx_id: 3669D83 ##第2个事务
    trx_state: LOCK WAIT ## 处于等待状态
    trx_started: 2010-12-24 13:40:07
    trx_requested_lock_id: 3669D83:49:3:4 ##请求的锁ID
    trx_wait_started: 2010-12-24 13:40:07
    trx_weight: 2
    trx_mysql_thread_id: 2346 ##线程 ID
    trx_query: update tx1 set c1='heyfffff',c2='heyffffff' where id =3
    trx_operation_state: starting index read
    trx_tables_in_use: 1 ##需要用到1个表
    trx_tables_locked: 1 ##有1个表被锁
    trx_lock_structs: 2
    trx_lock_memory_bytes: 376
    trx_rows_locked: 1
    trx_rows_modified: 0
    trx_concurrency_tickets: 0
    trx_isolation_level: REPEATABLE READ
    trx_unique_checks: 1
    trx_foreign_key_checks: 1
    trx_last_foreign_key_error: NULL
    trx_adaptive_hash_latched: 0
    trx_adaptive_hash_timeout: 10000
    *************************** 2. row ***************************
    trx_id: 3669D82 ##第1个事务
    trx_state: RUNNING
    trx_started: 2010-12-24 13:38:06
    trx_requested_lock_id: NULL
    trx_wait_started: NULL
    trx_weight: 3
    trx_mysql_thread_id: 2344
    trx_query: NULL
    trx_operation_state: NULL
    trx_tables_in_use: 0
    trx_tables_locked: 0
    trx_lock_structs: 2
    trx_lock_memory_bytes: 376
    trx_rows_locked: 1
    trx_rows_modified: 1
    trx_concurrency_tickets: 0
    trx_isolation_level: REPEATABLE READ
    trx_unique_checks: 1
    trx_foreign_key_checks: 1
    trx_last_foreign_key_error: NULL
    trx_adaptive_hash_latched: 0
    trx_adaptive_hash_timeout: 10000
    2 rows in set (0.00 sec)

  • root@127.0.0.1   : information_schema 13:40:12> select * from innodb_locks G
    *************************** 1. row ***************************
    lock_id: 3669D83:49:3:4 ## 第2个事务需要的锁
    lock_trx_id: 3669D83
    lock_mode: X
    lock_type: RECORD
    lock_table: `test`.`tx1`
    lock_index: `PRIMARY`
    lock_space: 49
    lock_page: 3
    lock_rec: 4
    lock_data: 3
    *************************** 2. row ***************************
    lock_id: 3669D82:49:3:4 ## 第1个事务需要的锁
    lock_trx_id: 3669D82
    lock_mode: X
    lock_type: RECORD
    lock_table: `test`.`tx1`
    lock_index: `PRIMARY`
    lock_space: 49
    lock_page: 3
    lock_rec: 4
    lock_data: 3
    2 rows in set (0.00 sec)

  • root@127.0.0.1   : information_schema 13:40:15> select * from innodb_lock_waits G
    *************************** 1. row ***************************
    requesting_trx_id: 3669D83 ## 请求锁的事务
    requested_lock_id: 3669D83:49:3:4 ## 请求锁的锁ID
    blocking_trx_id: 3669D82 ## 拥有锁的事务
    blocking_lock_id: 3669D82:49:3:4 ## 拥有锁的锁ID
    1 row in set (0.00 sec)

© 著作权归作者所有

-蒋全忠-
粉丝 5
博文 55
码字总数 49486
作品 0
昌平
程序员
私信 提问
Lock wait timeout exceeded; try restarting......

mysql异常java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction: 场景: 1.开启事务,更新本地数据库; 2.调用银行接口; 3.等待银行接口返回结果,银行返回成功...

凯文加内特
2016/06/13
463
0
浅析 innodb next-key lock

record lock:记录锁,也就是仅仅锁着单独的一行 gap lock:区间锁,仅仅锁住一个区间(注意这里的区间都是开区间,也就是不包括边界值,至于为什么这么定义?innodb官方定义的) next-key l...

长平狐
2012/11/01
976
0
mysql error:Lock wait timeout exceeded

1、锁等待超时。是当前事务在等待其它事务释放锁资源造成的。可以找出锁资源竞争的表和语句,优化你的SQL,创建索引等,如果还是不行,可以适当减少并发线程数。 2、你的事务在等待给某个表加...

DongHenry
2013/06/09
158
0
MySQL 5.5 InnoDB 锁状态解析

目录 目录 一 引子 二 表结构说明 三 INNODB 锁等待模拟 3.1 创建测试表,录入测试数据 3.2 模拟锁等待 3.3 再次模拟锁等待 3.4 查询锁等待 3.4.1 直接查看 innodblockwaits 表 3.4.2 innodb...

java_龙
2018/11/30
20
0
mysql事物锁等待超时,报错 Lock wait timeout exceeded; try restarting transaction

问题场景 问题出现环境: 1、在同一事务内先后对同一条数据进行插入和更新操作; 2、多台服务器操作同一数据库; 异常信息: mysqlexceptions.OperationalError: (1205, 'Lock wait timeout ...

aibati2008
2016/12/25
675
0

没有更多内容

加载失败,请刷新页面

加载更多

OpenStack 简介和几种安装方式总结

OpenStack :是一个由NASA和Rackspace合作研发并发起的,以Apache许可证授权的自由软件和开放源代码项目。项目目标是提供实施简单、可大规模扩展、丰富、标准统一的云计算管理平台。OpenSta...

小海bug
昨天
5
0
DDD(五)

1、引言 之前学习了解了DDD中实体这一概念,那么接下来需要了解的就是值对象、唯一标识。值对象,值就是数字1、2、3,字符串“1”,“2”,“3”,值时对象的特征,对象是一个事物的具体描述...

MrYuZixian
昨天
6
0
数据库中间件MyCat

什么是MyCat? 查看官网的介绍是这样说的 一个彻底开源的,面向企业应用开发的大数据库集群 支持事务、ACID、可以替代MySQL的加强版数据库 一个可以视为MySQL集群的企业级数据库,用来替代昂贵...

沉浮_
昨天
6
0
解决Mac下VSCode打开zsh乱码

1.乱码问题 iTerm2终端使用Zsh,并且配置Zsh主题,该主题主题需要安装字体来支持箭头效果,在iTerm2中设置这个字体,但是VSCode里这个箭头还是显示乱码。 iTerm2展示如下: VSCode展示如下: 2...

HelloDeveloper
昨天
7
0
常用物流快递单号查询接口种类及对接方法

目前快递查询接口有两种方式可以对接,一是和顺丰、圆通、中通、天天、韵达、德邦这些快递公司一一对接接口,二是和快递鸟这样第三方集成接口一次性对接多家常用快递。第一种耗费时间长,但是...

程序的小猿
昨天
10
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部