MySQL_事务与锁

Bertha 。 2023-07-04 04:55 2阅读 0赞

### 文章目录 ###

*  事务特性（ACID性）
 *   *  原子性（Atomicity）
     *  一致性（Consistency）
     *  隔离性（Isolation）
     *  持久性（Durability）
 *  事务并发问题
 *   *  脏读（Dirty Reads）
     *  不可重复读（Non-Repeatable Reads）
     *  幻读（Phantom Reads）
 *  事务隔离级别
 *   *  读未提交：read uncommitted
     *  读已提交：read committed
     *  可重复读：repeatable read
     *  序列化：serializable
     *  注意
 *  事务控制语句
 *  JDBC事务处理
 *  锁的分类
 *   *  根据数据操作类型划分
     *   *  读锁（共享锁）
         *  写锁（排它锁）
     *  对数据操作的颗粒度
     *   *  表锁
         *   *  相关命令
             *  案例1-加读锁
             *  案例2-加写锁
             *  案例结论
             *  表锁分析
             *   *  查看被加锁的表
                 *  分析表锁
         *  行锁
         *   *  特点
             *  基本演示
             *  无索引行锁
             *  间隙锁
             *   *  定义
                 *  危害
             *  如何锁定一行
             *  案例总结
             *  行锁分析
         *  页锁
 *  MVCC
 *   *  select
     *  insert
     *  delete
     *  update
 *  参考

MySQL事务主要用于处理操作量大，复杂度高的数据。由一步或几步数据库操作序列组成逻辑执行单元，这系列操作要么全部执行，要么全部放弃。在MySQL中只有使用了InnoDB数据库引擎的数据库或表才支持事务。事务用来管理insert，update，delete语句。

在事务进行过程中，未结束之前，DML语句时不会更改底层数据，只是将历史操作记录，在内存中完成记录。只有在事务结束时，而且时成功结束的时候，才会修改底层硬盘中的数据。

在MySQL中，默认情况下，事务是自动提交的，也就是说，只要执行一句DML语句就开启了事务，并且提交了事务，可以用set语句改变自动提交模式

-- 禁止|允许
    set autocommit=0|1;

# 事务特性（ACID性） #

## 原子性（Atomicity） ##

事务时应用中最小的执行单位，是应用中不可再分的最小逻辑执行体，一组事务，要么成功，要么撤回。

## 一致性（Consistency） ##

事务执行的结果，必须使数据库从一个一致性状态变到另一个一致性状态。当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。一致性时通过原子性来保证的。

## 隔离性（Isolation） ##

各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务都是隔离的。也就是说，并发执行的事务之间不能看到对方的中间状态，并发执行的事务之间不能相互影响。事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

## 持久性（Durability） ##

事务一旦提交，对数据所做的任何该表，都要记录到永久存储器中，通常是保存进物理数据库。

# 事务并发问题 #

## 脏读（Dirty Reads） ##

所谓脏读就是对脏数据的读取，而脏数据所指的就是未提交的数据。一个事务正在对一条记录做修改，在这个事务完成并提交之前，这条数据时处于待定状态的（可能提交也可能回滚），这时，第二个事务来读取这条没有提交的数据，并据此做进一步处理，就会产生未提交数据依赖关系。这种现象称为脏读，应该避免。

## 不可重复读（Non-Repeatable Reads） ##

一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。也就是说这个事务在两次读取之间该数据被其他事务所修改。

## 幻读（Phantom Reads） ##

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

# 事务隔离级别 #

SQL标准定义了4种隔离级别，包括了一些具体规则，用来限定事务之间的哪些改变时可见的，哪些时不可见的。低级别的隔离一般支持更高的并发处理，并拥有更低的系统开销。四种隔离级别为：

## 读未提交：read uncommitted ##

这是最低级别的隔离等级，允许其他事务看到没有提交的数据。这种级别会导致脏读。如果一个事务已经开始写数据，则另一个事务不允许同时进行写操作，但允许读操作。该隔离级别可通过排他写锁实现。避免了更新丢失，却可能出现脏读。也就是说事务B读取到了事务A未提交的数据。select语句以非锁定方式执行，所以有可能读到脏数据，隔离级别最低。

## 读已提交：read committed ##

读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。该隔离级别避免了脏读，但是可能出现不可重复读。事务A先读取了数据，事务B紧接着修改了数据并提交事务，事务A再次读取数据时，数据发生改变。

## 可重复读：repeatable read ##

在开始读取数据（事务开启）时，不再允许修改操作，事务开启，不允许其他事务的update修改操作。但可能还会有幻读的问题。因为幻读问题对应的是insert操作，而不是update操作。

MySQL默认隔离级别。

## 序列化：serializable ##

提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行（锁表）。如果仅仅通过行级锁 是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。序列化时最高级别的事务隔离，同时代价花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不近可以避免脏读，不可重复读，还避免了幻读。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoaWxsX0x5bg_size_16_color_FFFFFF_t_70]

## 注意 ##

*  SQL规范所规定的标准，不同的数据库具体的实现可能有些差异。
 *  MySQL中默认事务隔离级别时可重复读，并不会锁住读取到的行。
 *  事务隔离级别为读已提交时，写数据只会锁住相应的行。
 *  事务隔离级别为可重复读时，如果有索引的时候，以索引为条件更新数据，会存在间隙锁，行锁，下一键锁的问题，从而锁住一些行；如果没有索引，更新数据会锁住整张表。
 *  事务隔离级别为序列化时，读写数据都会锁住整张表。
 *  隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库的隔离级别设为read committed，可以避免脏读，而具有较好的并发性能。尽管会导致不可重复读，幻读这些问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

-- 修改事务隔离级别
    set [session|global] transaction isolation level
    {
       read uncommitted|read committed|repeatable read|serializable}
    -- 查看隔离级别
    select @@global.tx_isolation;
    select @@session.tx_isolation;
    select @@tx_isolation;

mysql默认事务隔离级别repeatable\_read

# 事务控制语句 #

-- 显式开启一个事务
    start transaction;
    -- 提交事务，并持久化
    commit;
    commit work;	
    -- 回滚，结束事务，撤销未提交的修改
    rollback;
    rollback work;
    -- 在事务中创建一个保存点，一个事务中可以有多个保存点
    savepoint identifier;
    -- 删除一个保存点，没有时会抛异常
    release savepoint identifier;	
    -- 回滚到标记点，不需要取消所有的修改操作
    rollback to identifier;
    -- 设置隔离级别
    set [session|global] transaction isolation level
    {
       read uncommitted|read committed|repeatable read|serializable}

# JDBC事务处理 #

conn.setAutoCommit(false);
    conn.commit();
    conn.rollback();
    
    Savepoint point conn.setSavepoint();
    conn.rollback(point);
    
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

# 锁的分类 #

## 根据数据操作类型划分 ##

### 读锁（共享锁） ###

针对同一份数据，多个读操作可以同时进行而不会互相影响

### 写锁（排它锁） ###

当前写操作没有完成前，它会阻断其他写锁和读锁。

## 对数据操作的颗粒度 ##

### 表锁 ###

#### 相关命令 ####

-- 手动增加表锁
    lock table 表名 read（write），表名2 read（write）
    -- 查看表上加过的锁
    show open tables
    -- 释放表锁
    unlock tables

#### 案例1-加读锁 ####

<table> 
 <thead> 
  <tr> 
   <th>session1</th> 
   <th>session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>获得表mylock的读锁 <code>lock table mylock read；</code></td> 
   <td>连接终端</td> 
  </tr> 
  <tr> 
   <td>可执行 <code>select * from mylock</code></td> 
   <td>也可执行</td> 
  </tr> 
  <tr> 
   <td>不可查询其他没有锁定的表 <code>select * from book</code></td> 
   <td>可以查询</td> 
  </tr> 
  <tr> 
   <td>执行插入或者更新操作锁定的表会提示错误</td> 
   <td>会一直等待获得锁</td> 
  </tr> 
  <tr> 
   <td>释放锁</td> 
   <td>完成插入操作</td> 
  </tr> 
 </tbody> 
</table>

#### 案例2-加写锁 ####

<table> 
 <thead> 
  <tr> 
   <th>session1</th> 
   <th>session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>lock table mylock write</td> 
   <td>待session1开启写锁后，连接终端</td> 
  </tr> 
  <tr> 
   <td>当前session对锁定的表进行查询、更新、插入操作都可以执行</td> 
   <td>其他session对锁定表查询被阻塞，等待锁被释放</td> 
  </tr> 
  <tr> 
   <td>释放锁</td> 
   <td>查询返回</td> 
  </tr> 
 </tbody> 
</table>

#### 案例结论 ####

MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表写锁。

MySQL的表锁有两种模式：

*  表共享读锁
 *  表独占写锁

读锁会阻塞写，但是不会阻塞读，而写锁会把读和写都阻塞。

#### 表锁分析 ####

##### 查看被加锁的表 #####

show open tables

##### 分析表锁 #####

通过检查`table_locks_waited`和`table_locks_immediate`状态变量来分析系统上的表锁定：

show status like 'table%';

*  table\_locks\_immediate  
    产生表锁的次数，表示可以立即获取锁的查询次数，每立即获取锁值+1
 *  table\_locks\_waited  
    出现表锁争用而发生等待的次数（不能立即获取锁的次数，每等待一次+1），此值高则表示存在较严重的表锁争用情况。

MyISAM 的读写调度是写优先，所以不适合做写为主的表的引擎，因为写锁之后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

### 行锁 ###

#### 特点 ####

*  偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
 *  InnoDB与MyISAM的最大不同有两点
    
     *  支持事务（TRANSACTION）
     *  采用了行级锁

#### 基本演示 ####

<table> 
 <thead> 
  <tr> 
   <th>session1</th> 
   <th>session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>set autocommit=0</td> 
   <td>set autocommit=0</td> 
  </tr> 
  <tr> 
   <td>update test set b=b1 where a=1; 更新但是不提交</td> 
   <td>update test set b=b2 where a=1; 被阻塞等待</td> 
  </tr> 
  <tr> 
   <td>commit</td> 
   <td>commit 正常更新</td> 
  </tr> 
 </tbody> 
</table>

#### 无索引行锁 ####

比如 `update test set b=b1 where name='zhs';`如果zhs不用‘’导致系统自动转换类型，行锁会变表锁

#### 间隙锁 ####

<table> 
 <thead> 
  <tr> 
   <th>session1</th> 
   <th>session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>update test set b=a*20 where a&gt;1 and a&lt;5</td> 
   <td>insert intio test values(2,200) 阻塞</td> 
  </tr> 
  <tr> 
   <td>commit</td> 
   <td>阻塞解除，完成插入</td> 
  </tr> 
 </tbody> 
</table>

##### 定义 #####

当用范围条件而不是相等条件检索数据，并请求共享或者排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙GAP

InnoDB也会对这个gap加锁，这种锁机制就是所谓的间隙锁Next-Key 锁

##### 危害 #####

因为query执行过程中通过范围查找的话，会锁定整个范围内所有的索引键值，即使这个键值不存在。当锁定一个范围键值之后，即使某些不存在的键值也会被无辜锁定，造成在锁定的时候无法插入锁定键值范围内的任何数据。

#### 如何锁定一行 ####

begin
    -- select 。。。 for update
    select * from test where a=1 for update
    -- 提交
    commit;

#### 案例总结 ####

InnoDB实现行锁，锁定机制带来的心更难损耗比表锁更高，但并发处理能力远远优于MyISAM表锁。在使用不当时，性能也会表现得更差。

#### 行锁分析 ####

show status like 'innodb_row_lock%'

*  innodb\_row\_lock\_current\_waits:当前正在等待锁定的数量
 *  innodb\_row\_lock\_time:从系统启动到现在锁定时间的总长度
 *  innodb\_row\_lock\_time\_avg:每次等待所花平均时间
 *  innodb\_row\_lock\_time\_max:所花最长时间
 *  innodb\_row\_lock\_waits：等待总次数
 *  优化建议
    
     *  尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
     *  合理设计索引，尽量缩小锁的范围
     *  尽可能较少检索条件，避免间隙锁
     *  尽量控制事务大小，减少锁定资源量和时间长度
     *  尽可能低级别事务隔离

### 页锁 ###

开销和加锁时间界于表锁和行锁之间：会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

# MVCC #

加锁是一种控制并发的方式,但是加锁毕竟是一个比较消耗资源的操作,因此MySQL也实现了MVCC(Multi-Version Concurrency Control ),核心思想是未每一条数据加上两个版本号,一个是当前的数据版本号,一个是该数据的删除版本号.通过版本的控制,在一定程度上尚避免加锁也可以实现并发控制.

在MySQL中,MVCC的大致工作原理如下:

## select ##

查询语句指挥获取符合下面两个条件的数据:

*  数据的版本号小于等于当前事务的版本号,这样可以保证查到的数据要么是之前就存在的,要么是本事务操作的.
 *  数据的删除版本号要么为空,要么大于事务当前的版本号.这样可以保证在此事务之前,该行数据没有被删除.

## insert ##

插入数据的当前版本号等于当前事务的版本号.

## delete ##

将删除行的删除版本号设置为当前事务的版本号.

## update ##

对原数据进行删除操作,然后插入新数据,所以相当于上面两个操作的合集.

# 参考 #

[一文搞懂MySQL事务隔离级别][MySQL]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoaWxsX0x5bg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200208154038482.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoaWxsX0x5bg==,size_16,color_FFFFFF,t_70
[MySQL]: http://huyan.couplecoders.tech/mysql/%E8%AF%BB%E4%B9%A6%E7%AC%94%E8%AE%B0/%E9%AB%98%E6%80%A7%E8%83%BDmysql/2019/06/07/%E4%B8%80%E6%96%87%E6%90%9E%E6%87%82MySQL%E4%BA%8B%E5%8A%A1%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB/