mysql锁等待时间太短会发生什么 mysql 锁等待超时

转载

风华绝代的java 2024-07-23 09:41:14

文章标签 mysql锁等待时间太短会发生什么 mysql lock 数据 sql语句 文章分类 MySQL 数据库

环境
MySQL5.5
现象
A.数据更新或新增后数据经常自动回滚。
B.表操作总报 Lock wait timeout exceeded 并长时间无反应
解决方法
show processlist; kill掉出现问题的进程
B.根治方法：select * from innodb_trx 查看有是哪些事务占据了表资源。

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

该类问题导致原因
Lock wait timeout exceeded

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)

processlist命令的输出结果显示了有哪些线程在运行，可以帮助识别出有问题的查询语句，两种方式使用这个命令。

1. 进入mysql/bin目录下输入mysqladmin processlist;

2. 启动mysql，输入show processlist;

如果有SUPER权限，则可以看到全部的线程，否则，只能看到自己发起的线程（这是指，当前对应的MySQL帐户运行的线程）。

得到数据形式如下（只截取了三条）：

mysql> show processlist;

 +-----+-------------+--------------------+-------+---------+-------+----------------------------------+----------

 | Id | User | Host           | db  | Command | Time| State    | Info                                                                                          

 +-----+-------------+--------------------+-------+---------+-------+----------------------------------+----------

 |207|root |192.168.0.20:51718 |mytest | Sleep   | 5   |        | NULL                                                                                                

 |208|root |192.168.0.20:51719 |mytest | Sleep   | 5   |        | NULL        

 |220|root |192.168.0.20:51731 |mytest |Query   | 84  | Locked |

 select bookname,culture,value,type from book where id=001

先简单说一下各列的含义和用途，第一列，id，不用说了吧，一个标识，你要kill一个语句的时候很有用。user列，显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。host列，显示这个语句是从哪个ip的哪个端口上发出的。呵呵，可以用来追踪出问题语句的用户。db列，显示这个进程目前连接的是哪个数据库。command列，显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。time列，此这个状态持续的时间，单位是秒。state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成，info列，显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：

　　Checking table
　正在检查数据表（这是自动的）。
　Closing tables
　正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
　Connect Out
　复制从服务器正在连接主服务器。
　Copying to tmp table on disk
　由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
　Creating tmp table
　正在创建临时表以存放部分查询结果。
　deleting from main table
　服务器正在执行多表删除中的第一部分，刚删除第一个表。
　deleting from reference tables
　服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
　Flushing tables
　正在执行FLUSH TABLES，等待其他线程关闭数据表。
　Killed
　发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
　Locked
　被其他查询锁住了。
　Sending data
　正在处理Select查询的记录，同时正在把结果发送给客户端。
　Sorting for group
　正在为GROUP BY做排序。
　Sorting for order
　正在为ORDER BY做排序。
　Opening tables
　这个过程应该会很快，除非受到其他因素的干扰。例如，在执Alter TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
　Removing duplicates
　正在执行一个Select DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
　Reopen table
　获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
　Repair by sorting
　修复指令正在排序以创建索引。
　Repair with keycache
　修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
　Searching rows for update
　正在讲符合条件的记录找出来以备更新。它必须在Update要修改相关的记录之前就完成了。
　Sleeping
　正在等待客户端发送新请求.
　System lock
　正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
　Upgrading lock
　Insert DELAYED正在尝试取得一个锁表以插入新记录。
　Updating
　正在搜索匹配的记录，并且修改它们。
　User Lock
　正在等待GET_LOCK()。
　Waiting for tables
　该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, Alter TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
　waiting for handler insert
　Insert DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。
　大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。
　还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。