一文看懂 MySQL事务隔离级别与锁

傷城~ 2021-12-12 03:19 413阅读 0赞

# **数据库锁** #

**共享锁(Shared lock)**

例1：

\----------------------------------------

T1: select \* from table (请想象它需要执行1个小时之久，后面的sql语句请都这么想象）

T2: update table set column1='hello'

过程：T1运行 （加共享锁)

T2运行等待T1运行完之后再运行T2

之所以要等，是因为T2在执行update前，试图对table表加一个排他锁，而数据库规定同一资源上不能同时共存共享锁和排他锁。所以T2必须等T1执行完，释放了共享锁，才能加上排他锁，然后才能开始执行update语句。

例2：

\----------------------------------------

T1: select \* from table

T2: select \* from table

这里T2不用等待T1执行完，而是可以马上执行。

分析：

T1运行，则table被加锁，比如叫lockA

T2运行，再对table加一个共享锁，比如叫lockB。

两个锁是可以同时存在于同一资源上的（比如同一个表上）。这被称为共享锁与共享锁兼容。这意味着共享锁不阻止其它session同时读资源，但阻止其它session update

例3：

\----------------------------------------

T1: select \* from table

T2: select \* from table

T3: update table set column1='hello'

这次，T2不用等T1运行完就能运行，T3却要等T1和T2都运行完才能运行。因为T3必须等T1和T2的共享锁全部释放才能进行加排他锁然后执行update操作。

例4：（死锁的发生）

\----------------------------------------

T1:begin tran

select \* from table (holdlock)** (holdlock意思是加共享锁，直到事务结束才释放)**

update table set column1='hello'

T2:begin tran

select \* from table(holdlock)

update table set column1='world'

假设T1和T2同时达到select，T1对table加共享锁，T2也对加共享锁，当T1的select执行完，准备执行update时，根据锁机制，T1的共享锁需要升级到排他锁才能执行接下来的update.在升级排他锁前，必须等table上的其它共享锁释放，**但因为holdlock这样的共享锁只有等事务结束后才释放**，所以因为T2的共享锁不释放而导致T1等(等T2释放共享锁，自己好升级成排他锁），同理，也因为T1的共享锁不释放而导致T2等。死锁产生了。

例5：

\----------------------------------------

T1:begin tran

update table set column1='hello' where id=10

T2:begin tran

update table set column1='world' where id=20

这种语句虽然最为常见，很多人觉得它有机会产生死锁，但实际上要看情况，如果id是主键上面有索引，那么T1会一下子找到该条记录(id=10的记录），然后对该条记录加排他锁，T2，同样，一下子通过索引定位到记录，然后对id=20的记录加排他锁，这样T1和T2各更新各的，互不影响。T2也不需要等。

但如果id是普通的一列，没有索引。那么当T1对id=10这一行加排他锁后，T2为了找到id=20，需要对全表扫描，那么就会预先对表加上共享锁或更新锁或排他锁(依赖于数据库执行策略和方式，比如第一次执行和第二次执行数据库执行策略就会不同）。但因为T1已经为一条记录加了排他锁，导致T2的全表扫描进行不下去，就导致T2等待。

死锁怎么解决呢？一种办法是，如下：

例6：

\---------------------------------------

T1:begin tran

select \* from table(xlock) (xlock意思是直接对表加排他锁)

update table set column1='hello'

T2:begin tran

select \* from table(xlock)

update table set column1='world'

这样，当T1的select 执行时，直接对表加上了排他锁，T2在执行select时，就需要等T1事务完全执行完才能执行。排除了死锁发生。但当第三个user过来想执行一个查询语句时，也因为排他锁的存在而不得不等待，第四个、第五个user也会因此而等待。在大并发情况下，让大家等待显得性能就太友好了，所以，这里引入了更新锁。

**更新锁(Update lock)**

为解决死锁，引入更新锁

例7：

\----------------------------------------

T1:begin tran

select \* from table(updlock) (加更新锁)

update table set column1='hello'

T2:begin tran

select \* from table(updlock)

update table set column1='world'

更新锁的意思是：“我现在只想读，你们别人也可以读，但我将来可能会做更新操作，我已经获取了从共享锁（用来读）到排他锁（用来更新）的资格”。一个事务只能有一个更新锁获此资格。

T1执行select，加更新锁。

T2运行，准备加更新锁，但发现已经有一个更新锁在那儿了，只好等。（T2加的是更新锁，更新锁与更新锁不兼容， 如果加的是共享锁， 共享锁和更新锁可以兼容，即T1，T2不可同时进行，但是T3，T4，T5只要不是事务，还是可以正常查询）

当后来有user3、user4...需要查询table表中的数据时，并不会因为T1的select在执行就被阻塞，照样能查询，相比起例6，这提高了效率

例8:

\----------------------------------------

T1:begin

select \* from table(updlock) (加更新锁）

update table set column1='hello' (重点：这里T1做update时，不需要等T2释放什么，而是直接把更新锁升级为排他锁，然后执行update)

T2:begin

select \* from table (T1加的更新锁不影响T2读取）

update table set column1='world' (T2的update需要等T1的update做完才能执行)

我们以这个例子来加深更新锁的理解，

第一种情况：T1先达，T2紧接到达；在这种情况中，T1先对表加更新锁，T2对表加共享锁，假设T2的select先执行完，准备执行update，发现已有更新锁存在，T2等。T1执行这时才执行完select，准备执行update，更新锁升级为排他锁，然后执行update，执行完成，事务结束，释放锁，T2才轮到执行update。

第二种情况：T2先达，T1紧接达；在这种情况，T2先对表加共享锁，T1达后，T1对表加更新锁，假设T2 select先结束，准备update，发现已有更新锁，则等待，后面步骤就跟第一种情况一样了。这个例子是说明：排他锁与更新锁是不兼容的，它们不能同时加在同一子资源上。

**排他锁（独占锁，Exclusive Locks)**

这个简单，即其它事务既不能读，又不能改排他锁锁定的资源。

例9

T1: update table set column1='hello' where id<1000

T2: update table set column1='world' where id>1000

假设T1先达，T2随后至，这个过程中T1会对id<1000的记录施加排他锁.但不会阻塞T2的update。

例10 (假设id都是自增长且连续的）

T1: update table set column1='hello' where id<1000

T2: update table set column1='world' where id>900

如同例9，T1先达，T2立刻也到，T1加的排他锁会阻塞T2的update.

**意向锁(Intent Locks)**

意向锁就是说在屋（比如代表一个表）门口设置一个标识，说明屋子里有人（比如代表某些记录）被锁住了。另一个人想知道屋子里是否有人被锁，不用进屋子里一个一个的去查，直接看门口标识就行了。

当一个表中的某一行被加上排他锁后，该表就不能再被加表锁。数据库程序如何知道该表不能被加表锁？一种方式是逐条的判断该表的每一条记录是否已经有排他锁，另一种方式是直接在表这一层级检查表本身是否有意向锁，不需要逐条判断。显然后者效率高。

例11：

\----------------------------------------

T1: begin tran

select \* from table (xlock) where id=10 --意思是对id=10这一行强加排他锁

T2: begin tran

select \* from table (tablock) --意思是要加表级锁

假设T1先执行，T2后执行，T2执行时，欲加表锁，为判断是否可以加表锁，数据库系统要逐条判断table表每行记录是否已有排他锁，如果发现其中一行已经有排他锁了，就不允许再加表锁了。只是这样逐条判断效率太低了。

实际上，数据库系统不是这样工作的。当T1的select执行时，系统对表table的id=10的这一行加了排他锁，还同时悄悄的对整个表加了意向排他锁(IX)，当T2执行表锁时，只需要看到这个表已经有意向排他锁存在，就直接等待，而不需要逐条检查资源了。

**计划锁(Schema Locks)**

例12：

\----------------------------------------

alter table .... (加schema locks，称之为Schema modification (Sch-M) locks

DDL语句都会加Sch-M锁，该锁不允许任何其它session连接该表。连都连不了这个表了，当然更不用说想对该表执行什么sql语句了。

**事务隔离级别（MySQL仅InnoDB引擎支持事务，所以也只有InnoDB有事务隔离级别）**

**Read Uncommit (未提交读。允许脏读)**

事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

**一级封锁协议：**

一级封锁协议，事务在对需要修改的数据上面（就是在发生修改的瞬间） 对其加共享锁（其他事务不能更改，但是可以读取-导致“脏读”），直到事务结束才释放。

**Read committed（读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。）**

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

**二级封锁协议：**

1）事务 在对**需要更新的数据**** **上（就是发生更新的瞬间） 加 **排他锁**** ****（直到事务结束）**** **， 防止其他事务读取未提交的数据，这样，也就避免了 “脏读” 的情况。2）事务 对当前被**读取的数据** 上面加**共享锁**（当读到时加上共享锁），一旦读完该行，**立即**** **释放该 该行的共享锁

二级封锁协议除防止了“脏读”数据，但是不能避免 **丢失更新，不可重复读，幻读** 。

但在二级封锁协议中，由于读完数据后**立即**** **释放共享锁，所以它不能避免**可重复读** ，同时它也不能避免 **丢失更新** ，如果事务A、B同时获取资源X，然后事务A先发起更新记录X，那么 事务B 将等待事务 A 执行完成，然后获得记录X 的排他锁，进行更改。这样事务 A 的更新将会被丢失。

具体情况如下：

<table> 
 <tbody> 
 <tr> 
 <td> 事务A </td> 
 <td> 事务B </td> 
 </tr> 
 <tr> 
 <td> 读取X=100（同时上共享锁） </td> 
 <td> 读取X=100（同时上共享锁） </td> 
 </tr> 
 <tr> 
 <td> 读取成功（释放共享锁） </td> 
 <td> 读取成功（释放共享锁） </td> 
 </tr> 
 <tr> 
 <td> UPDATE&nbsp;X=X+100 （上排他锁） </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> UPDATING A（等待事务A释放对X的排他锁） </td> 
 </tr> 
 <tr> 
 <td> 事务成功（释放排他锁）X=200 </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> UPDATE X=X+200（成功上排他锁） </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> 事务成功（释放排他锁）X=300 </td> 
 </tr> 
 </tbody> 
</table>

由此可以看到，事务A的提交被事务B覆盖了

**Repeatable read（重复读，就是在开始读取数据（事务开启）时，不再允许修改操作）**

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

**什么时候会出现幻读？**

事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

**三级封锁协议：**

三级封锁协议是：二级封锁协议加上事务 在读取数据的瞬间 必须先对其加 **共享锁** ，**但是** 直到**事务结束才释放** ，这样保证了可重复读（既是其他的事务职能读取该数据，但是不能更新该数据）。

三级封锁协议除防止了**“脏”数据** 和**不可重复读** 。但是这种情况不能避免 **幻读 和 丢失更新**** **的情况，在事务 A 没有完成之前，事务 B 可以新增数据，那么 当事务 A 再次读取的时候，事务B 新增的数据会被读取到

进阶：repeatable read 导致死锁的情况，参考上面讲解共享锁的文章

**Serializable 序列化**

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

**四级封锁协议：**

四级封锁协议是对三级封锁协议的增强，其实现机制也最为简单，直接对 事务中 所 读取 或者 更改的数据所在的表加表锁，也就是说，其他事务不能 读写 该表中的任何数据。这样所有的 **脏读，不可重复读，幻读** ，都得以避免！

值得一提的是：大多数数据库默认的事务隔离级别是Read committed，比如Sql Server , Oracle。Mysql的默认隔离级别是Repeatable read。

**乐观锁**（ Optimistic Locking ） 相对[悲观锁][Link 1]而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长[事务][Link 2]而言，这样的开销往往无法承受。而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

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

事务的特性：

1.原子性  事务是数据库逻辑的工作单元，事务包括的所有操作，要么都做，要么都不做。

2.一致性  事务执行的结果是使数据库从一个一致性状态变成另一个一致性状态。一致性与原子性是密切相关的。

3.隔离性  一个事务的执行不能被其他事务干扰。

4.持久性  一个事务一旦提交，它对数据库中数据的改变应该是永久性的。

**MyISAM 和 InnoDB 区别：**

**1、MyISAM类型的表强调的是性能，但是不支持事务、及外部键等高级功能。**

1.  MySQL默认采用的是MyISAM。
2.  MyISAM不支持事务，所以MyISAM就不存在事务隔离级别了，而InnoDB支持，所有有4种事务隔离级别，InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。
3.  InnoDB支持数据行锁定，MyISAM不支持行锁定，只支持锁定整个表。即MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。
4.  InnoDB支持外键，MyISAM不支持。
5.  InnoDB的主键范围更大，最大是MyISAM的2倍。
6.  InnoDB不支持全文索引，而MyISAM支持。全文索引是指对char、varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。
7.  MyISAM支持GIS数据，InnoDB不支持。即MyISAM支持以下空间数据对象：Point,Line,Polygon,Surface等。
8.  没有where的count(\*)使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count(\*)时它直接从计数器中读，而InnoDB必须扫描全表。所以在InnoDB上执行count(\*)时一般要伴随where，且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”？因为InnoDB中primary index是和raw data存放在一起的，而secondary index则是单独存放，然后有个指针指向primary key。所以只是count(\*)的话使用secondary index扫描更快，而primary key则主要在扫描索引同时要返回raw data时的作用较大。
9.  MyISAM和InnoDB锁的不同： [https://blog.csdn.net/zhanghongzheng3213/article/details/51753189][https_blog.csdn.net_zhanghongzheng3213_article_details_51753189]

**2、并发**

MyISAM读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读

InnoDB 读写阻塞与事务隔离级别相关

**3、场景选择**

MyISAM

*  不需要事务支持（不支持）
 *  并发相对较低（锁定机制问题）
 *  数据修改相对较少（阻塞问题），以读为主
 *  数据一致性要求不是非常高

1.  尽量索引（缓存机制）
2.  调整读写优先级，根据实际需求确保重要操作更优先
3.  启用延迟插入改善大批量写入性能
4.  尽量顺序操作让insert数据都写入到尾部，减少阻塞
5.  分解大的操作，降低单个操作的阻塞时间
6.  降低并发数，某些高并发场景通过应用来进行排队机制
7.  对于相对静态的数据，充分利用Query Cache可以极大的提高访问效率
8.  MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问

InnoDB

*  需要事务支持（具有较好的事务特性）
 *  行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
 *  数据更新较为频繁的场景
 *  数据一致性要求较高
 *  硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO

1.  主键尽可能小，避免给Secondary index带来过大的空间负担
2.  避免全表扫描，因为会使用表锁
3.  尽可能缓存所有的索引和数据，提高响应速度
4.  在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交
5.  合理设置innodb\_flush\_log\_at\_trx\_commit参数值，不要过度追求安全性
6.  避免主键更新，因为这会带来大量的数据移动

**4、其它细节**

1）InnoDB 中不保存表的具体行数，注意的是，当count(\*)语句包含 where条件时，两种表的操作是一样的

2）对于AUTO\_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引， 如果你为一个表指定AUTO\_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。自动增长计数器仅被存储在主内存中，而不是存在磁盘

3）DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除

4）LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用

5）如果执行大量的SELECT，MyISAM是更好的选择，如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表

**5、为什么MyISAM会比Innodb 的查询速度快**

InnoDB 在做SELECT的时候，要维护的东西比MYISAM引擎多很多；

1）InnoDB 要缓存数据和索引，MyISAM只缓存索引块，这中间还有换进换出的减少

2）innodb寻址要映射到块，再到行，MyISAM记录的直接是文件的OFFSET，定位比INNODB要快

3）InnoDB 还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护

MVCC ( Multi-Version Concurrency Control )多版本并发控制

InnoDB ：通过为每一行记录添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。但是InnoDB并不存储这些事件发生时的实际时间，相反它只存储这些事件发生时的系统版本号。这是一个随着事务的创建而不断增长的数字。每个事务在事务开始时会记录它自己的系统版本号。每个查询必须去检查每行数据的版本号与事务的版本号是否相同。让我们来看看当隔离级别是REPEATABLE READ时这种策略是如何应用到特定的操作的

SELECT InnoDB必须每行数据来保证它符合两个条件

1、InnoDB必须找到一个行的版本，它至少要和事务的版本一样老(也即它的版本号不大于事务的版本号)。这保证了不管是事务开始之前，或者事务创建时，或者修改了这行数据的时候，这行数据是存在的。

2、这行数据的删除版本必须是未定义的或者比事务版本要大。这可以保证在事务开始之前这行数据没有被删除。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21vc2hlbmdsdg_size_16_color_FFFFFF_t_70][]

[Link 1]: http://baike.baidu.com/view/1953079.htm
[Link 2]: http://baike.baidu.com/view/121511.htm
[Link 3]: http://baike.baidu.com/subview/709594/709594.htm
[Link 4]: http://baike.baidu.com/subview/118455/118455.htm
[https_blog.csdn.net_zhanghongzheng3213_article_details_51753189]: https://blog.csdn.net/zhanghongzheng3213/article/details/51753189
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21vc2hlbmdsdg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190710090045611.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21vc2hlbmdsdg==,size_16,color_FFFFFF,t_70