Mysql事务和锁

事务ACID特性

事务由一组SQL组成，其具有以下四个特性：

• 原子性（Atomicity）：事务是一个原子操作，不可分割，要么全部执行完成，要么全部不执行；
• 一致性（Consistent）：在事务开始和结束时，数据都必须保持一致；
• 隔离性（Isolation）：事务不会收到外部操作影响，事务的中间状态对外部是不可见的，反之亦然；
• 持久性（Durable）：事务提交之后，对于数据的影响是永久性的，系统出现故障也能保持；

并发事务带来的问题

更新丢失（Lost Update）或脏写

多个事务去更新同一行数据，最后的更新覆盖了其他事务的更新，导致其他事务的更新丢失。

脏读（Dirty Read）

事务A读取到事务B已经修改但还未提交的数据。
如果事务B回滚，那事务A读取到的数据就无效了，不符合事务一致性要求。

不可重复度（NonRepeatable Read）

在事务A中，在不同时刻相同的SQL查询语句执行结果不一致，不符合隔离性。

幻读（Phantom Read）

在事务A中，读到了事务B提交的新增数据，不符合隔离性。

事务隔离级别

数据库提供事务隔离级别来解决并发事务带来的问题：

事务隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
读未提交(Read Uncommitted)	可能	可能	可能
读已提交(Read Committed)	不可能	可能	可能
可重复读(Repeatable Read)	不可能	不可能	可能
串行化(Serializable)	不可能	不可能	不可能

Mysql默认事务隔离级别可重复读，Oracle默认读已提交。
Mysql设置和查看事务隔离级别

--Mysql设置事务隔离级别
set tx_isolation='Repeatable-Read';
--查看当前事务隔离级别
show variables like 'tx_isolation';

如果Spring设置了事务隔离级别，就使用Spring的，没有就使用Mysql设置的事务隔离级别。

锁

和其他共享数据一样，Mysql中数据也需要锁机制来保证数据并发访问的一致性和有效性。
锁冲突也是影响数据库并发访问性能的重要因素。

锁分类

• 性能维度

  • 乐观锁（数据版本号CAS对比实现）

    乐观锁认为访问数据时不会发生锁冲突，是乐观的，乐观锁会在你对数据更新时，会对比该数据之前的版本号，如果一致就修改，不一致就获取最新的值进行修改。（CAS）

  • 悲观锁

    悲观锁总是认为一定会发生锁冲突，是悲观的，所以在访问数据时先加锁。

• 数据操作类型维度
  • 读锁（悲观锁）
  • 写锁（悲观锁）
• 数据操作粒度维度

  • 表锁

    对整个表加锁

  • 行锁

    对当前行加锁

• 锁的性质

  • 共享锁（Shared，S锁）

    读锁的性质是共享锁，加了读锁的数据，其他读操作也可以对这份数据进行读取。

  • 排它锁（exclusive，X锁）

    写锁的性质是排它锁，加了写锁的数据，会阻断其他读锁和写锁。

表锁

对操作的整个表进行加锁，

• 优点
  • 开销小
  • 加锁快
  • 不会出现死锁
• 缺点
  • 锁粒度大，发生锁冲突概率高
  • 并发低
• 应用场景
  • 整张表的数据迁移

手动加表锁

lock table <tableName> read|write, <tableName2> read|write

查看该表加过的锁

show open tables;

删除表锁

unlock tables;

行锁

对操作的数据行row加锁

• 缺点
  • 开销大
  • 加锁慢
  • 可能会出现死锁
• 优点
  • 锁粒度小，发生锁冲突概率低
  • 并发高

InnoDB与MyISAM最大区别

• InnoDB支持事务

• InnoDB支持行锁，MyISAM只支持表锁

• MyISAM执行Select语句前，会对查询的表加读锁，执行Update、Insert和Delete操作会对表加写锁；

• InnoDB执行Select前，不会加锁（非Serializable串行隔离级别），执行Update、Insert和Delete时会对行加写锁；

可重复读（Repeatable-Read）

在可重复读隔离级别下，Mysql使用了MVCC（Multi-Version Concurrency Control）多版本并发控制机制，

• Select操作不会更新版本号，读取当前数据的历史版本，快照读；
• Insert、Update和Delete会更新版本号，读取当前数据的最新版本，当前读。

间隙锁（Gap Lock）

间隙锁对操作的数据两个值之间的间隙进行加锁，在可重复读隔离级别下才会生效。

Mysql在可重复读隔离级别下，利用间隙锁在某些情况下可以解决幻读问题。

比如user表中有以下这些数据：

id	name	age
1	小明	10
2	小张	11
3	小王	12
12	小红	13
35	小爱	14

当你执行update user set age = 18 where 5 < id and id < 16;，会对5-16范围内包含的所有行记录以及行记录所在的间隙加写锁。上面user表id行存在的间隙为(3, 12)，(12, 35)，(35, 正无穷大)，也就是会对(3, 35]加写锁，最后的35是包括在内的。

临键锁（Next-Key Locks）

临键锁是行锁与间隙锁的组合。上面那个(3, 35]整个区间就可以叫做临键锁。

行锁升级为表锁

在RR（Repeatable Read）级别下，以非索引列为where条件进行更新，行锁会变成表锁。
InnoDB的行锁是针对索引加的锁，不是对记录，如果该索引失效，都会从行锁升级为表锁。

锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)。
比如

-- 在RR隔离级别，在session1中执行，name不是索引字段，会对user表加共享锁，读锁
select * from user where name = '小红' lock in share mode;

-- 在session2中执行下面的sql不会阻塞，因为session1对user表加了共享锁，session2中的也是共享锁，读锁。
select * from user where name = '小明' lock in share mode;

-- 在session2中执行这条语句会被阻塞，因为for update是排它锁
select * from user where name = '小明' for update;

-- 执行这条更新语句也会阻塞，update会加写锁。
update user set age = 101 where name = '小明';

Mysql锁分析

可以通过以下语句分析系统行锁竞争情况

show status like 'innodb_row_lock%';
-- 结果
variable_name					value
Innodb_row_lock_current_waits	0
Innodb_row_lock_time			515817
Innodb_row_lock_time_avg		14328
Innodb_row_lock_time_max		51110
Innodb_row_lock_waits			36

各个variable_name说明：

• Innodb_row_lock_current_waits：当前正在等待锁的数量；
• Innodb_row_lock_time：从系统启动到现在等待锁总时间；
• Innodb_row_lock_time_avg：每次等待锁需要的平均时间；
• Innodb_row_lock_time_max：从系统启动到现在等待锁最大的一次时间；
• Innodb_row_lock_waits：从系统启动到现在等待锁的总次数；

优化建议

特别注意这三个状态量的值：
Innodb_row_lock_time_avg （等待锁平均时长）
Innodb_row_lock_waits （等待锁总次数）
Innodb_row_lock_time（等待锁总时长）
尤其是等待锁总次数很高，而且每次等待锁平均时间也不小的时候，就要分析系统中为什么会出现在这么多锁等待了。

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; 

死锁

死锁就是两个请求互相持有对方等待的锁。
例如依次执行下列语句就会出现死锁：

-- 1.session1执行
select * from user where id = 1 for update;
-- 2.session2执行
select * from user where id = 2 for update;
-- 3.session1执行
select * from user where id = 2 for update;
-- 4.session2执行
select * from user where id = 1 for update;

一般情况下，Mysql可以自动检测死锁兵回滚产生死锁的那个事务，但有些情况下Mysql没办法自动检测死锁。

-- Mysql检测到死锁，回滚事务
[SQL]select * from user where id = '1' for update;
[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

锁优化

• 尽量让数据检索走索引，避免不能走索引的情况下行锁升级表锁；
• 合理设计索引，尽量减小锁的范围；
• 尽量减小检索条件的范围，避免间隙锁；
• 尽量控制事务的大小，减少锁定资源的数量和时间，涉及事务加锁的sql尽量放在事务最后执行，因为Mysql只有在需要加锁的时候才会真正开启事务， 生成事务id，刚start transaction时会生成一个临时事务id；
• 尽可能低级别的事务隔离，比如互联网公司使用Mysql时，一般都会使用读已提交事务隔离（Read-Committed）级别而不是默认的可重复读（Repeatable-Read）；