MySQL事务隔离及锁机制

一、事务及其AICD属性

事务是由一组sql组成的逻辑处理单元，具有其四种属性：

1.原子性

事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。

2.一致性

在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。

3.隔离性

数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。

4.持久性

事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

二、并发事务处理带来的问题及事务隔离级别

事务隔离级别总体简介

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

准备示例表：

CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`balance` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('aaa', '450');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('bbb', '160');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('ccc', '240');

1.脏读

a.介绍

一个事务对一条数据进行修改，当前事务未提交时，另一个事务读取了这条数据修改前后的值，导致读取的值不同。现象如下：
分别开启客户端A
由于mysq事务隔离级别l默认开启的是可重复读，所以为了演示脏读现象，则需要开启读未提交

#开启读未提交的事务隔离级别
set tx_isolation='read-uncommitted';
begin;
select * from account;

结果如上图，客户端A不要关闭。
开启客户端B

set tx_isolation='read-uncommitted';
begin;
update account set name='a1' where id =1;
select * from account;

如上图所示：客户端B将id为1的name值修改为a1，当前事务未提交，也就是数据库中存的值还是aaa。
我们再打开客户端A，继续查询表中的值：

由上图可看出，客户端B开启事务进行修改数据，未提交事务时，客户端A可以读取客户端B修改后的值。这明显有问题。

b.解决方式

mysql数据针对脏读问题，提供读已提交的事务隔离级别。
客户端A

#读已提交
set tx_isolation='read-committed';
begin;

客户端B

#读已提交
set tx_isolation='read-committed';
begin;
update account set name='a1' where id =1;

修改值后，未提交事务：

客户端A：查询表信息，看是否更改。从下图可以看出，在客户端B未提交事务时，客户端A这里查询不到变化。
而当客户端B提交事务后，客户端A可以查询到变化（在读已提交的事务隔离级别下，读取的是数据库的实时数据）。而这又导致另一个问题：不可重复读。

2.不可重复读

a.介绍

当一个事务修改了数据，提交事务后，导致另一个事务再读数据时，前后读取的值不一致。这种现象违反事务彼此的隔离性。

b. 解决方式

mysql为了解决可重复读，提供可重复读的事务隔离级别

#开启可重复读
set tx_isolation='repeatable-read';

打开客户端A、B按上面方式进行验证。

3.幻读

a.介绍

同时打开客户端A、B，分别开启事务；当客户端A事务新增一条数据，提交事务后，客户端B查询整个表记录是查询不到客户端A新增的那条记录的。但是，当在客户端B执行更新新增的那条记录时，居然成功。这种现象就是幻读。

b. 解决方式

可启动可串行化隔离级别，解决幻读问题

#开启可串行化
set tx_isolation='serializable';

打开客户端A、B按上面方式进行验证。
验证后，会发现：当客户端A执行查询时未提交事务的情况下，客户端B不能对客户端A事务查询的数据做操作。
当开启可串行化后，事务中对数据操作时，会对操作行进行加锁，再提交事务，释放锁后，其他事务才能对该数据进行操作。

三、锁

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

1.行锁

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

2.表锁

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

• 手动增加表锁
  lock table 表名称 read(write),表名称2 read(write);
• 查看表上加过的锁
  show open tables;
• 删除表锁
  unlock tables;

3.间隙锁

客户端A、B开启可重复读隔离级别，同时开始事务，在客户端B执行范围更新操作，在客户端A执行插入范围内的数据，需要等待客户端B的事务提交后才能执行成功。
客户端A

客户端B

由上图看出，表中记录id间隙有(4,7)，9，(10,正无穷)，客户端B执行id范围(0,8)的数据更新，涉及到的间隙范围有(4,7)，则会将其范围内的记录加锁，这就是所谓的间隙锁。
如果记录id的间隙为(5,9)，(12,30)，(40,正无穷)，而客户端执行id范围(8,20)的数据更新，涉及到的间隙范围有(5,9)，(12,30)，会将这些范围内的记录都加锁，其他事务无法对其修改、新增。

4.临键锁(Next-key Locks)

Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(4,7]的整个区间可以叫做临键锁。

5.无索引行锁会升级为表锁

• 锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁
  session1 执行：update account set balance = 800 where name = ‘lilei’;
  session2 对该表任一行操作都会阻塞住
  InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

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

 ‐‐ 查看事务
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\G;