MySQL事务隔离级别与锁机制

概述

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

事务

事务是由一组SQL语句组成的逻辑处理单元，事务具有ACID属性：

• 原子性（Atomicity）：事务是一个原子的单元操作，对其数据的修改，要么全部执行，要么全部不执行

• 一致性（Consistent）：在事务开始和完成时，数据必须保持一致的状态。所有相关数据的规则必须应于事务的修改以保持数据的完整性

• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作的影响“独立”环境执行，事务处理过程中的中间状态对外部是不可见的。

• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现了系统故障也能够保持

并发事务处理带来的问题

更新丢失（Lost Update）或脏写

当两个或多个事务对同一条记录数据进行更新操作时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，最后的更新覆盖了由其他事务更新的操作。

脏读

一个事务在多一条记录做修改，在这个事务完成并提交之前，这条记录的数据就处于不一致的状态，若此时有其他事务来读取这条记录，如果不加控制的情况会出现“脏读”数据，如果对该数据进行后续的操作就会产生未提交的数据依赖关系，这种现象称为“脏读”。

不可重读（Non-Repeatable Reads）

一个事务在读取某些数据后的某个时候在此读取之前读过的数据，确发现其读取的数据已经发生了改变或默写记录已经被删除了，这种现象被称为”不可重读“。

幻读（Phantom Reads）

一个事务按相同的查询语句重新读取以前检索过的数据，确发现其他事务插入了满足其条件的新数据，这种现象称为“幻读”。

事务隔离级别

“脏读”、“不可重复读”、“幻读”，是数据库读一致性问题，数据库提供了事务隔离机制来解决，事务隔离级别：读未提交（Read uncommitted）、读已提交（Read committed）、可重复读（Repeatableread）、可串行化（Serializable），数据库的隔离级别越严格，并发副作用越小，Mysql数据库默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认使用Mysql设置的隔离级别，如果Spring设置了就用设置的隔离级别。

隔离级别	脏读(Dirty Read)	不可重复读(NonRepeatable Read)	幻读(Phantom Read)
读未提交(Read uncommitted)	可能	可能	可能
读已提交(Read committed)	不可能	可能	可能
可重复读(Repeatableread)	不可能	不可能	可能
可串行化 (Serializable)	不可能	不可能	不可能

常用命令

查看数据库的事务隔离级别命令

show variables like 'tx_isolation';

设置数据库的事务隔离级别

set tx_isolation='REPEATABLE-READ';

锁详解

锁是计算机协调多个进程或线程并发访问某一资源的机制。

锁分类

• 性能上分：乐观锁（用版本号对比来实现）和悲观锁。

• 操作粒度上分：表锁和行锁。

• 操作类型分：读锁和写锁（都是悲观锁）、意向锁。

读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

写锁（排它锁，X锁(eXclusive)）：当前操作没有完成，他会阻断其他写锁或读锁，数据修改的操作都会加写锁。

意向锁（Intention Lock）：I锁，针对表锁，主要是为了提高加表锁的效率，是Mysql自己加的，当有事务给表的数据加了共享锁或排它锁，同时会给表设置一个标识代表已经有了行锁，直接读取这个标识就可以确定自己该不该加表锁。主要分为：

意向共享锁（IS锁）：对整个表加共享锁之前，需要先获取到意向共享锁。

意向排它锁（IX锁）：对整个表加排它锁之前，需要先获取到意向排它锁。

表锁

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

常用命令

手动增加表锁

#添加读锁
lock table [表名] read;
#添加写锁
lock table [表名] write;
#查看表上加过的锁
show open tables;
#删除表锁
unlock tables;

总结

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

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

行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发读高。一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞，即读锁会阻塞写锁，但是不会阻塞读锁，而写锁会阻塞读锁和写锁。

锁优化建议

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

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

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

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

• 尽可能低级别事务隔离。