主页 > 未分类 > 数据库锁超时故障[mysql]

数据库锁超时故障[mysql]

2015年1月7日 发表评论 查看评论
TOKUDB的数据清理,删除一个大表的部分数据后发现主从同步挂了。报错是:
Last_Error: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'gpsbox'. Query: 'insert into im_gpsone_su(type_id,send_custid,title,content,priority,send_time,receive_time,receive_custid,user_id,STATUS,operator,gps_time,lng,lat,speed,gps_status,course,id) values(12,1000000,'00000000000000000000000000001100','?aι±|°2??£o£;δ??3μ;',1,'2013-11-27 01:35:04','2013-11-27 01:35:04',1553869,1001711,'0',null,'2013-11-27 01:35:04','113.800631','22.676428','10','01000000000000000000000000000000','181',429542040)'
 
因为数据库是跨机房主主同步,然后每个主的下面带1个从只用于读。
这个报错表明因为删除数据的SQL语句锁表了,然后另外一个主同步过来的命令没法申请到锁,锁申请超时导致该报错。
然后就导致主从同步异常。
 
查了下TOKUDB的锁超时说明:
tokudb lock timeout
This variable controls the amount of time that a transaction will wait for a lock held by another transaction to
be released. If the con?icting transaction does not release the lock within the lock timeout, the transaction
that was waiting for the lock will get a lock timeout error. The units are milliseconds. A value of 0 disables
lock waits. The default value is 4000 (four seconds).
默认只有四秒,这会很容易导致该错误。InnoDB的锁超时默认是50秒。
所以可以调大这个时间。
 
还有一点,就是当两个主的主从同步都故障的时候,两个从数据库的主从同步是正常的。
因为从库只有一个写的来源,所以不会出现锁超时的问题。
 
但肚腩觉得这有点奇怪,TOKUDB号称使用了MVCC技术,怎么还是发生这种锁冲突。
What does MVCC compliant mean?
 
Multi-Version Concurrency Control (MVCC) is a technique for improving multi-user database performance. It does this by eliminating row-level locking and table locking. In doing so, it ensures that locks acquired for querying (reading) data don’t conflict with locks acquired for writing data and so reading never blocks writing and writing never blocks reading.
TokuDB has been MVCC compliant starting with version 5.0.
 
它通过多版本并发控制(MVCC)技术取代行级锁和表锁定,以避免锁冲突。但为何这里还发生了这个错误。
然后肚腩找了下官方文档,发现这个和TOKUDB的一个Bug很像:
https://github.com/Tokutek/ft-engine/issues/133
Prohaska举了一个例子:
client 1 transaction gets a range lock on (SK,-infinity) to (+infinity,+infinity). this range lock prohibits other transactions from inserting into the table until the first transaction is retired.
 
Example:
 
Session 1 – table create
mysql> create table uniqueTest (id int, unique key(id));
 
Session 1 – insert transaction start
mysql> set session autocommit=off;
mysql> begin;
mysql> insert into uniqueTest values (1);
 
Session 2 – insert other row
mysql> set session autocommit=on;
mysql> insert into uniqueTest values (12345);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select @@tokudb_last_lock_timeout;
*************************** 1. row ***************************
@@tokudb_last_lock_timeout: {“mysql_thread_id”:4, “dbname”:”./test/uniqueTest-key-id”, “requesting_txnid”:41469663, “blocking_txnid”:41469660, “key_left”:”ff0139300000″, “key_right”:”+infinity”}
 
证明这个加锁的问题存在问题,变成锁住整张表了。
肚腩测试一下是否是同一个Bug:
在测试环境建同样结果的表
CREATE TABLE `t_bus_run_info` (
  `id` bigint(20) NOT NULL,
  `bus_id` int(11) DEFAULT NULL,
  `station_id` int(11) DEFAULT NULL',
  `arrive_time` datetime DEFAULT NULL ,
  `leave_time` datetime DEFAULT NULL,
  `stay_time` int(11) DEFAULT NULL ',
  `create_time` datetime DEFAULT NULL,
  `modi_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` char(8) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_bus_id` (`bus_id`),
  KEY `idx_arrive_time` (`arrive_time`),
  KEY `idx_leave_time` (`leave_time`),
  KEY `idx_busid_arrivetm_leavetm` (`bus_id`,`arrive_time`,`leave_time`)
) ENGINE=TOKUDB DEFAULT CHARSET=gbk COMMENT='车辆到站时间表'
随便插入几条数据。
INSERT INTO t_bus_run_info VALUES (1,1,1,NOW(),NOW(),12,NOW(),NOW(),1);
INSERT INTO t_bus_run_info VALUES (2,1,1,NOW(),NOW(),12,NOW(),NOW(),1);
INSERT INTO t_bus_run_info VALUES (3,1,1,NOW(),NOW(),12,NOW(),NOW(),1);
INSERT INTO t_bus_run_info VALUES (4,1,1,NOW(),NOW(),12,NOW(),NOW(),1);
 
然后模拟删除大数据的操作,弄一个事务,不提交
SET SESSION autocommit=off;
BEGIN;
DELETE FROM t_bus_run_info WHERE leave_time <'2013-11-27 18:25:34' 
 
开另外一个Session,然后尝试插入操作,果然报错:
Query : INSERT INTO t_bus_run_info VALUES (15,1,1,NOW(),NOW(),12,NOW(),NOW(),1)
Error Code : 1205
Lock wait timeout exceeded; try restarting transaction
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000
 
这里的时间是选第一条记录的时间,然后插入的时间是大于这个时间点的。
理想情况是锁住这个时间点前面的行,不影响后面的行插入,
但实际上,如果这个session不提交,那么后面的插入操作时进行不下去的。
所以这个问题是由于TOKUDB范围锁太大导致的。
 
再测试下过滤条件是主键的情况是否还存在这个问题。
[session1]:
SET SESSION autocommit=off;
BEGIN;
DELETE FROM t_bus_run_info WHERE id < 30;
session不做commit.
 
[session2]
INSERT INTO t_bus_run_info VALUES (33,1,1,NOW(),NOW(),12,NOW(),NOW(),1)
ID大于30的插入成功。
 
INSERT INTO t_bus_run_info VALUES (12,1,1,NOW(),NOW(),12,NOW(),NOW(),1)
Error Code : 1205
Lock wait timeout exceeded; try restarting transaction
ID小于12的报错。
 
也就是说对于主键,TOKUDB是能够正确加锁的。对于非主键,就不行了。即便这个过滤条件是索引。
 

原创文章,转载请注明: 转载自肚腩照明月'blog

本文链接地址: 数据库锁超时故障[mysql]

文章的脚注信息由WordPress的wp-posturl插件自动生成


  1. 匿名
    2015年8月19日16:13 | #1

    我的测试结果是,即便事务中用的是主键索引也会锁表,触发器也会锁表

  2. 2016年8月31日12:04 | #2

    目前也遇到了,感谢分享~

SEO Powered by Platinum SEO from Techblissonline