聊一聊mysql的锁和事务隔离级别

锁类型 - 性能锁

从性能上来看，可以分为两种类型的锁，分别是：乐观锁和悲观锁。

乐观锁是数据版本号（version）机制实现的，一般通过为数据库表增加一个数字类型的 “version” 字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值 1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败。

悲观锁，见名知意，它指的是对数据被外界（包括当前系统的其它事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排它性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

锁类型 - 操作类型锁

从对数据库的操作类型上面可以分为读锁和写锁。

两者均属于悲观锁。

读锁也叫共享锁，针对同一份数据，多个读操作可以同时进行，但是写操作不允许。

写锁也叫排它锁，当写操作完成时，读写都不允许。

锁类型 - 操作粒度锁

从数据库的操作粒度上可以分为表级锁、行级锁和页锁。

MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；

BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；

InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

粒度锁特性

表级锁：

• 开销小，加锁快；

• 不会出现死锁；

• 锁定粒度大，发生锁冲突的概率最高,并发度最低。

行级锁：

• 开销大，加锁慢；

• 会出现死锁；

• 锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

页面锁：

• 开销和加锁时间界于表锁和行锁之间；

• 会出现死锁；

• 锁定粒度界于表锁和行锁之间，并发度一般。

加锁时机

MyISAM存储引擎在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。

MyISAM的加锁方式为：

lock table 表1 read(write), 表2 read/write;

MyISAM查看表锁语句：

show open tables;  // 在查询出的结果中In_use字段为1的，则表示加了表锁

InnoDB存储引擎因为支持事务，所以它的锁一般和事务的隔离级别有一定关系，具体请参考后面。

数据库事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 只有Innodb 存储引擎支持事务。

• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

• 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

• BEGIN 或 START TRANSACTION 显式地开启一个事务；

• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

• ROLLBACK TO identifier 把事务回滚到标记点；

• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务

• ROLLBACK 事务回滚

• COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交

• SET AUTOCOMMIT=1 开启自动提交

事务并发时可能引发的问题

脏读：

事务A正在对一条记录修改，事务B读了A正在修改的数据。

但是此时事务A并未提交。

后续因为一些需要事务A自己可能回滚，那么事务B读到的数据就是无效的脏数据。

破坏了事务的一致性的要求。

不可能重复读：

在一次事务中，多次执行同样的查询条件，获取到的结果不一致，也就是读到了其他事务的修改的数据。

违背了事务的隔离性

幻读：

和不可重复读的区别是：幻读是读取到了新增的数据。

事务隔离机制

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

1. 读未提交（READ UNCOMMITTED）

2. 读提交 （READ COMMITTED）

3. 可重复读 （REPEATABLE READ）

4. 串行化 （SERIALIZABLE）

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。

注意这里和oracle的区别，oracle默认的是读已提交

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。

下图是 4 种隔离级别对这三个问题的解决程度：

从图中可以看出，其中只有串行化的隔离级别全部解决了这 三 个问题，其他的 3 个隔离级别都有缺陷。

但是因为数据库的事务隔离越严格，并发副作用越小，性能方面锁付出的代价就越大。所以串行化我们在工作中其实很少用，或者说压根不用。

而且串行化类似于队列顺序执行了，这根并发很明显是冲突的。

日常开发中，由于大部分应用 对“不可重复读'和“幻读”并不敏感，他们可能更多关心的是数据并发访问的能力，所以大多数时候我们都会采用的可重复读的事务隔离级别。（也就是MySQL默认的）

InnoDB 行锁

上文我们介绍过，MyISAM存储引擎是会根据其执行的操作类型对数据加相应表锁的（读锁/写锁）。

而InnoDB则有所不同，具体如下：

1、在非串行化的隔离级别下面select语句不会加锁，但是update，insert，delete会根据条件加行锁。且行锁是加在索引上面的，如果这些语句没有走索引，那么也会加表锁。

2、如果条件是范围，那么该范围内的所有行，包括每行记录所在的间隙区间都会被加上锁，就算该行数据还未被插入也会被加锁，这就是所谓的间隙锁，间隙锁在可重复读隔离级别下面才会生效。

举个例子：

A表现有的主键id是1、2、4、8、10、30；

那么间隙区间就有2-4,4-8,8-10,10-30,30-正无穷五个区间。

当我们执行如下语句：

update A set XXX= 'XXX' where id > 5 and id <23

首先[5,23]这个范围的数据就会被加锁；

对应的5是落在4-8这个间隙区间的，则这个区间的所有数据都会被加锁；

23是落在10-30这个间隙区间，这个区间的记录同样也会被加锁；

所以执行上述sql时，(4,23]左开又闭，这个区间的数据都会被加锁。

如果执行的语句为：

update A set XXX= 'XXX' where id > 18 and id <31；

那么(10,正无穷) 则都会被加锁，这点需要特别注意。

综上所述，我们在开发中应做到如下三点：

1.尽可能让所有数据查找，修改都通过索引来完成，避免无索引行锁升级为表锁

2.尽可能减少条件范围，缩小锁的范围，尽量避免间隙锁

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

InnoDB手动加锁方式

手动添加共享锁:

select * from table where id=1 lock in share mode ;

这样就给当前的这一行加了共享锁（读锁）,其他的事务就不能修改这行数据了.

手动添加排他锁:

 select * from table where id=1 for update ;

这样就给当前的这一行加了排他锁（写锁）,当我们这个事务修改时,别的事务就不能修改了（其他事务得排队等待，也就是阻塞）。

总结

好了，MySQL的锁和事务隔离大致就先讲到这了。

希望对你们有帮助。