DB
MySQL

MySQL事务隔离级别与锁机制

事务基本特征ACID

事务是并发控制的单位，是用户定义的一个操作序列，由一组SQL语句组成的逻辑处理单元，这些操作要么都成功，要么都失败，是一个不可分割的工作单位。

  • Atomicity原子性：事务中的包含的操作被看做是一个逻辑单元，要么全部成功，要么全部失败
  • Isolation隔离性：多个用户可对同一个数据并发访问，而不破坏数据的正确性和完整性，并行事务的修改必须与其他并行事务的修改相互独立
  • Consistency一致性：合法的数据被写入到数据库，否则事务回滚到最初状态，数据库总是从一个一致性的状态转换到另一个一致性的状态
  • Durability持久性：事务结束后，事务处理的结果必须能够得到固化

四种隔离级别

数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致脏写、脏读、不可重复读、幻读等问题；为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。

Read Uncommitted读未提交

级别最低，一个事务可读到另外一个事务未提交的数据，事务在读数据的时候并未对数据加锁，在修改数据的时候只对数据增加行级共享锁。

事务1读取某行记录时，事务2也能对这行记录进行读取、更新，因为事务1并未对数据增加任何锁；

当事务2对该记录进行更新时，事务1再次读取该记录，能读到事务2对该记录的修改版本，因为事务2只增加了共享读锁，事务1可以再增加共享读锁读取数据，即使该修改尚未被提交；

事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束，因为事务1对数据增加了共享读锁，事务2不能增加排他写锁进行数据的修改；

1
set tx_isolation='Read-Uncommitted';

Read Committed读已提交

在一个事务修改数据过程中，如果事务还没提交，其他事务不能读该数据，事务对当前被读取的数据加行级共享锁且当读到时才加锁，一旦读完该行，立即释放该行级共享锁；事务在更新某数据的瞬间，必须先对其加行级排他锁，直到事务结束才释放。

事务1在读取某行记录的整个过程中，事务2都可以对该行记录进行读取，因为事务1对该行记录增加行级共享锁的情况下，事务2同样可以对该数据增加共享锁来读数据；

事务1读取某行的一瞬间，事务2不能修改该行数据，但只要事务1读取完改行数据，事务2就可以对该行数据进行修改。因为事务1在读取的一瞬间会对数据增加共享锁，任何其他事务都不能对该行数据增加排他锁。但事务1只要读完该行数据，就会释放行级共享锁，一旦锁释放，事务2就可以对数据增加排他锁并修改数据；

事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。因为事务1在更新数据时，会对该行数据增加排他锁，直到事务结束才会释放锁，所以在事务2没有提交之前，事务1都能不对数据增加共享锁进行数据的读取。所以可以解决脏读的现象，但不能解决不可重复读现象。

1
set tx_isolation='Read-Committed';

Repeatable Read可重复读

事务在读取某数据的瞬间，必须先对其加行级共享锁，直到事务结束才释放；事务在更新某数据的瞬间，必须先对其加行级排他锁，直到事务结束才释放。

事务1在读取某行记录的整个过程中，事务2都可以对该行记录进行读取，因为事务1对该行记录增加行级共享锁的情况下，事务2同样可以对该数据增加共享锁来读数据；

事务1在读取某行记录的整个过程中，事务2都不能修改该行数据，事务1在读取的整个过程会对数据增加共享锁，直到事务提交才会释放锁，所以整个过程中，任何其他事务都不能对该行数据增加排他锁。所以能解决不可重复读的读现象；

事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束，事务1在更新数据的时候，会对该行数据增加排他锁，直到事务结束才会释放锁，所以在事务2没有提交之前，事务1都能不对数据增加共享锁进行数据的读取。所以可以解决可重复读的现象，但不能解决幻读现象。若事务1对数据进行修改，事务2也对数据进行修改，此时事务2会被阻塞，直到事务1提交事务，事务1提交事务后，事务2会马上执行完成，但此时事务1只能查到自己更新的数据。事务2也只能查到自己更新的数据，不能查到事务1更新的数据即使事务1提交事务后。

1
set tx_isolation='Repeatable-Read';

Serializable串行化

可序列化的隔离级别中可以解决幻读，产生幻读的原因是事务在进行范围查询的时候没有增加范围锁所以导致幻读，范围锁range-locks：给SELECT 的查询中使用一个WHERE子句描述范围加锁。事务在读取数据时，必须先对其加表级共享锁 ，直到事务结束才释放；事务在更新数据时，必须先对其加表级排他锁 ，直到事务结束才释放。

事务1正在读取A表中的记录时，则事务2也能读取A表，但不能对A表做更新、新增、删除，直到事务1结束，因为事务1对表增加了表级共享锁，其他事务只能增加共享锁读取数据，不能进行其他任何操作；

事务1正在更新A表中的记录时，则事务2不能读取A表的任意记录，更不可能对A表做更新、新增、删除，直到事务1结束，事务1对表增加了表级排他锁，其他事务不能对表增加共享锁或排他锁，也就无法进行任何操作；

可序列化解决了脏读、不可重复读、幻读等读现象，但无法读取其它事务已修改但未提交的记录，在当前事务完成之前，其它事务不能修改目前事务已读取的记录，在当前事务完成之前，其它事务所插入的新记录，其索引键值不能在当前事务的任何语句所读取的索引键范围中。

1
set tx_isolation='Serializable';

脏读

脏读又称无效数据的读出，在一个事务的处理过程中读到另一个未提的交事务中的数据。

不可重复读

在对数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值与脏读的区别是：不可重复读是读取了前一事务提交的数据

虚读（幻读）

事务在操作过程中两次查询，第二次查询的结果包含了第一次查询中未出现的数据或缺少第一次查询中出现的数据，一般解决幻读的方法是增加间隙锁，锁定检锁范围为只读，这样就避免了幻读。

四种事务隔离级别从隔离程度上越来越高，但同时在并发性上也就越来越低。之所以有这么几种隔离级别，就是为了方便开发人员在开发过程中根据业务需要选择最合适的隔离级别。

隔离级别 脏读 不可重复读 幻读
读未提交 可能 可能 可能
读已提交 不可能 可能 可能
可重复读 不可能 不可能 可能
可串行化 不可能 不可能 不可能

默认的事务隔离级别是可重复读，用Spring开发程序时，若不设置隔离级别默认用MySQL设置的隔离级别，若Spring设置了就用已经设置的隔离级别 ，查询和设置当前数据库事务隔离级别：

1
2
show variables like 'tx_isolation';
set tx_isolation='REPEATABLE-READ';

锁分类

从性能上分为乐观锁，用版本对比来实现和悲观锁，从对数据库操作的类型分，分为读锁和写锁，都属于悲观锁，读锁是共享锁，S锁(Shared）：针对同一份数据，多个读操作可同时进行而互相不影响，写锁是排它锁，X锁(eXclusive）：当前写操作没有完成前，它会阻断其他写锁和读锁，从对数据操作的粒度分，分为表锁、间隙锁和行锁。

表级锁

每次操作锁住整张表。开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。 表锁相关操作：

1
2
3
4
5
6
-- 手动增加表锁
lock tables mylock write, employees read;
-- 查看表上加过的锁
show open tables;
-- 删除表锁
unlock tables;

行级锁

每次操作锁住一行数据。开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB支持事务和行级锁，MylSAM两者皆不支持。MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会自动给涉及的表加写锁。

InnoDB在非串行隔离级别下执行查询语句SELECT时，不会加锁，但update、insert、delete操作会加行锁。简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞

对MyISAM表的读操作，即加读锁，不会阻塞其他进程对同一表的读请求，但会阻赛对同一表的写请求，只有当，读锁释放后，才会执行其它进程的写操作。

对MylSAM表的写操作，即加写锁，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

1
show status like 'innodb_row_lock%';

  • Innodb_row_lock_current_waits：当前正在等待锁的数量
  • Innodb_row_lock_time：从系统启动到现在锁定总时间长度
  • Innodb_row_lock_time_avg：每次等待平均时间
  • Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
  • Innodb_row_lock_waits：系统启动后到现在总共等待的次数

当等待次数很高，且每次等待时长也不小时，就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划

间隙锁

锁的就是两个值之间的空隙，间隙锁在某些情况下可解决幻读问题，间隙锁在可重复读隔离级别下才会生效。

1
select * from account;

该数据的间隙有id为(2, 10)，(10, 20)，(20, 正无穷)这三个区间，全都是开区间；在Session1下执行：

1
update account set name = 'eleven11' where id > 8 and id <18;

则其他Session没法在这个范围所包含的所有行记录，包括间隙行记录，以及行记录所在的间隙里插入或修改任何数据，即id在(3,20]区间都无法修改插入数据，这里的(3,20]叫做临键锁；

若上面例子中id < 25，则相当于整个表都被锁住了，无法再更新或插入任何数据。

临键锁

Next-Key Locks是行锁与间隙锁的组合。

无索引行锁会升级为表锁

锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁，InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。且该索引不能失效，否则都会从行锁升级为表锁。

锁定某一行可用lock in share mode加共享锁，用for update加排它锁：

1
2
3
4
-- 加排它锁，其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交
select * from employees where id = 1 for update;
-- 加共享锁
select * from employees where id = 1 lock in share mode;

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁更高，但在整体并发处理能力方面要远远优于MyISAM的表级锁。当系统并发量高时，InnoDB整体性能和MyISAM相比会有比较明显的优势。

但InnoDB的行级锁同样也有其脆弱的一面，当使用不当时，可能会让Innodb整体性能表现不仅不能比MyISAM高，甚至可能会更差。

查看INFORMATION_SCHEMA系统库锁相关数据表

1
2
3
4
5
6
7
8
9
10
-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id;
-- 查看锁等待详细信息
show engine innodb status;

死锁

1
2
3
4
5
6
7
8
9
10
11
set tx_isolation='repeatable-read';
-- Session_1执行
select * from account where id = 1 for update;
-- Session_2执行：
select * from account where id = 2 for update;
-- Session_1执行：
select * from account where id = 2 for update;
-- Session_2执行：
select * from account where id = 1 for update;
-- 查看近期死锁日志信息：
show engine innodb status;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁。

优化

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

合理设计索引，尽量缩小锁的范围

尽可能减少检索条件范围，避免间隙锁

尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

尽可能低级别事务隔离

示例数据

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `account` (
 `id` INT (11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR (20) DEFAULT NULL,
 `balance` VARCHAR (20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE = InnoDb DEFAULT CHARSET = utf8;

-- 插入数据
INSERT INTO`account` (`id`, `name`, `balance`) VALUES ('1', 'zhangsan', 800);
INSERT INTO`account` (`id`, `name`, `balance`) VALUES ('2', 'lisi', 3000);
INSERT INTO`account` (`id`, `name`, `balance`) VALUES ('10', 'wanger', 2000);
INSERT INTO`account` (`id`, `name`, `balance`) VALUES ('20', 'mazi', 1000);