常见的 InnoDB 锁介绍

发表于

1、写在最前面

近期在处理一个批量表单上传的优化需求时，遇到一个innoDB的加锁问题，导致多线程在批量处理表单并插入数据库数据的时候出现LockTimeOutException等待锁超时异常。错误异常如下

1
2
3
4
复制代码org.springframework.dao.CannotAcquireLockException: could not
execute statement; SQL [n/a]; nested exception is
org.hibernate.exception.LockTimeoutException: could not execute
statement...

网上找了很多资料也比较零碎，通过自己对Mysql以及innodb的理解，结合Mysql官方文档对Mysql的锁以及加锁机制做一次简单的介绍。

2、事务的隔离级别

先来回顾一下事务的隔离级别：

  • Read-Uncommitted:未提交读，可以读取到其他未提交的事务修改的数据。
  • Read-Committed:提交读，只允许读取已提交事务的数据，可能导致不可重复读、幻读问题。
  • RePeatable-Read:可重复读，同一个事务内多次相同的查询结果是一致的，可以解决重复读问题。
  • serializable:串行化，最高隔离级别，每次都要获取表级锁，并发性能很差。
隔离级别 Dirty-Read NonRepeatable-Read Phantm-Read
Read-Uncommitted YES YES YES
Read-Committed NO YES YES
Repeatable-Read NO NO YES
Serializable NO NO NO

表注：不同的隔离级别可能导致的读取问题

查看数据库的隔离级别
mysql> select @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation;

image

可看出Mysql的默认隔离级别是Repeatable-Read

3、InnoDB Locking

  • 共享锁和排它锁 (shared locks、exclusive (X) locks)
  • 意向锁 (Intention Locks)
  • 记录锁 (Record Locks)
  • 间隙锁 (Gap Locks)
  • 自增锁 (AUTO-INC Locks)
3.1 共享锁和排它锁

InnoDB实现了两种标准的行级锁，共享锁(shared (S) locks) 和排他锁 (exclusive (X) locks).

  • 共享锁允许一个占有锁的事务去读取一行数据
  • 排它锁则允许事务对某一行记录进行写操作

如果一个事务持有了一个共享锁，其他事务仍然可以获取这行记录的共享锁，但不能获取到这行记录的排它锁。当一个事务获取到了某一行的排它锁，则其他事务将无法再获取这行记录的共享锁和排它锁。

transaction T1 transaction T2
begin; begin;
update user set name=’loy’ where id = 1; update user set name=’loy’ where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
commit;

分析一下上面出现错误的原因，当T1执行 update user set name='loy' where id = 1;时T1先获取到X锁由于T1没有执行commit; 所以他一直持有
这把锁当T2也执行更新操作的时候就会一直等待X导致获取锁超时。

3.2 意向锁

意向锁是一种表级锁，和基本锁一样也分为共享锁和排他锁。

  • 意向共享锁 (Intention shared (IS)) 将要去获取某一行的共享锁。
  • 意向排它锁 (Intention exclusive (IX)) 将要去获取某一行的排它锁。

事务在获取共享锁之前必须先获取意向共享锁，同理获取排他锁之前必须先获取意向排它锁，意向锁不会阻塞其他任何对表的操作，他只是告诉其他事务他将要去获取某一行的共享锁或者排他锁。

X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible

注：基本锁和意向锁兼容矩阵

3.3 记录锁

记录是是作用在索引上的一种锁，他锁住的是某一条记录的索引而非记录本身，如果当前表没有索引那么 InnoDB将会为其创建一个隐藏的聚合索引，而Record Locks将会锁住这个隐藏的聚合索引。

3.4 间隙锁

间隙锁和记录锁一样也是作用在索引上。不同的是记录锁只作用于一条索引记录而间隙锁可以锁住一个范围内的索引。举个例子:

SELECT id from t_user where id BETWEEN 5 AND 10 FOR UPDATE;
间隙锁将会在(4,5)和(10,11)之间加锁，他将阻止其他事务在这个范围内插入数据。

3.5 自增锁

自增锁是一种特殊的表级锁，他只作用在包含自增列的插入操作时。当一个事务正在插入一条数据时，其他的任何事务都必须等待整个事务完成插入操作，在取获取锁来执行插入操作。
官方文档是这样描述的：
An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

看到这里我们在回到文章开头的地方，重新思考异常出现的原因。其实就很明了了。在需要保存数据的表中就包含有自动增长的列 id当多线在同时插入数据时就会导致 AUTO-INC Locks 锁竞争激烈，当线程等待锁的时间过长就会有上诉的异常抛出。

4、死锁分析

并发可能导致的死锁分析

数据库建表

1
2
3
4
5
6
复制代码CREATE TABLE `user` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
T1 T2 T3
begin begin begin
INSERT INTO user values(2, ‘tom’, 10) INSTERT INTO user values(2, ‘tom’, 10) INSTERT INTO user values(2, ‘tom’, 10)
rollback;
Query OK, 1 row affected (47.29 sec)
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

看一下在T1 roolback之前各事务锁的占用情况

image

此时T1获取到的id=2这一行的X排它锁，T2和T3获取到的则是S共享锁，当我们把T1 rollback 以后T1将释放以获取到的X锁，此时的T2和T3都持有S锁，回顾S、X锁的兼容矩阵，因为S锁和X锁是冲突的，T2和T3都在等待对方释放S锁，导致死锁。

5、总结

尽管在日常开发中对InnoDB锁没有太多的接触，但是了解InnoDB加锁机制在遇到相关问题的时候会让你处理起来更得心应手。InnoDB还有其他一些比较有意思的锁感兴趣的同学可以移步Mysql官方文档学习。

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%