数据库事务并发访问中的问题及隔离机制

怼烎@ 2022-01-22 08:59 166阅读 0赞

#### 我们直接从隔离级别从低到高进行介绍 ####

--------------------

### 1.更新丢失 ###

#### 目前主流数据库都会自动进行枷锁来避免，所以不好从数据库层面进行模拟，举个例子： ####

<table> 
 <thead> 
  <tr> 
   <th align="center">取款事务</th> 
   <th align="center">存款事务</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">开始事务</td> 
   <td align="center">开始事务</td> 
  </tr> 
  <tr> 
   <td align="center">查询转账余额为100元</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center">查询转账余额为100元</td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center">存入20元,余额变为120元</td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center">提交事务</td> 
  </tr> 
  <tr> 
   <td align="center">取出10元,余额改为90元</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">回滚事务,余额恢复为100元</td> 
   <td align="center">更新丢失</td> 
  </tr> 
 </tbody> 
</table>

#### 从上表我们可以看出最终更新被覆盖导致了更新丢失 ####

--------------------

### 2.脏读 ###

#### 是指一个事务读取到了另一个事务未提交的数据 ####

> 如何解决：READ-COMMITTED事务隔离级别以上可避免

#### 下面我们进行验证 ####

首先我们先使用下面的语句获取当前的事务隔离级别可知数据库的默认级别`REPEATABLE-READ`

select @@tx_isolation;#查询隔离级别

下面我们使用下面的语句将当前事务的隔离级别设置为可读未提交  
`READ-UNCOMMITTED`(最低的事务隔离级别)

set session transaction isolation level read uncommitted;#修改隔离界别为 可读未提交

#### 下面我们模拟脏读 ####

#### 我们在数据库中先添加一个数据 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NTQyMDg1_size_16_color_FFFFFF_t_70]

<table> 
 <thead> 
  <tr> 
   <th align="left">模拟步骤</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1. 首先我们新建两个控制台来模仿两个不同的事务并在两个控制台都执行下面的语句，并将事务隔离级别都改为<code>READ_UNCOMMITTED</code></td> 
  </tr> 
  <tr> 
   <td align="left">2. 我们在控制台1中使用<code>start transaction;</code>来开始我们的事务</td> 
  </tr> 
  <tr> 
   <td align="left">3. 我们使用<code>select * from learn_sql.learn_sql where id = 1;</code>此时我们查询到的money为初值300.</td> 
  </tr> 
  <tr> 
   <td align="left">4. 此时我们使用<code>update learn_sql.learn_sql set money = 400 where id = 1;</code>执行过此命令之后我们再执行<code>select * from learn_sql.learn_sql where id = 1;</code>会发现此时的money已经改成了400.</td> 
  </tr> 
  <tr> 
   <td align="left">5. 我们使用控制台2使用<code>select * from learn_sql.learn_sql where id = 1;</code>查询可知此时的money已经变成了还未提交的400元。</td> 
  </tr> 
  <tr> 
   <td align="left">6.若此时控制台1未使用<code>commit</code>进行提交，而是<code>rollback</code>进行事务回滚，则此时就是发生了控制台2读取了未成功提交的数据。</td> 
  </tr> 
 </tbody> 
</table>

> 解决办法：使用下面的sql语句将事务隔离级别改为`READ_COMMITTED`，此时如果我们执行步骤4则会发现，控制台2获取的并不是400而是300。

set session transaction isolation level read committed;#修改隔离界别为 只可读提交

--------------------

### 3.不可重复读 ###

#### 指事务1多次读取同一个数据，事务2在事务1多次读取过程中进行的修改提交，导致事务1多次读取的数据不同。 ####

> 如何解决：REPEATABLE-READ事务隔离级别以上可避免

#### 下面我们开始模拟，首先我们先保证事务隔离级别为`READ_COMMITTED` ####

<table> 
 <thead> 
  <tr> 
   <th align="left">模拟步骤</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1.首先我们在控制台1中使用<code>start transaction;</code>开始事务，此时我们使用<code>select * from learn_sql.learn_sql where id = 1;</code>可知money为300，然后我们使用<code>update learn_sql.learn_sql set money = money + 100 where id = 1;</code></td> 
  </tr> 
  <tr> 
   <td align="left">2.此时我们使用控制台2中先使用<code>start transaction;</code>开始事务，再使用<code>select * from learn_sql.learn_sql where id = 1;</code>发现money为300。</td> 
  </tr> 
  <tr> 
   <td align="left">3.接着我们在控制台1使用<code>commit;</code>提交修改。这是我们控制台2中使用<code>select * from learn_sql.learn_sql where id = 1;</code>我们突然发现原来的300，突然变成了400.这就是发生了不可重复读的问题了，即多次查询结果不一致</td> 
  </tr> 
 </tbody> 
</table>

> 解决办法：我们使用下面的sql语句将食物隔离级别修改为`REPEATABLE-READ`

set session transaction isolation level repeatable read;

> 这时我们在步骤3中获取的money仍然为300，但是如果我们使用`update learn_sql.learn_sql set money = money + 100 where id = 1;`后再查询会发现money不是我们最后一步查询的300+100=400元，而是我们控制台1提交之后的400+100=500元，这就防止了在一次事务中多次读不一致，同时可以保证该食物提交结果正确。

--------------------

### 4.幻读 ###

#### 指事务1读取与搜索条件相匹配的若干行，事务2以插入或删除行的方式来修改事务1的结果集。 ####

> 如何解决：SERIALIZABLE事务隔离级别可避免

#### 下面我们开始模拟，首先我们先保证事务隔离级别为`READ_COMMITTED`由于mysql在技术层面上避免了`REPEATABLE-READ`隔离级别下发生，但是理论上并不可避免 ####

<table> 
 <thead> 
  <tr> 
   <th align="left">模拟步骤</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1.首先我们在控制台1中使用<code>start transaction;</code>开始事务，此时我们使用<code>select * from learn_sql.learn_sql;</code>可得到一条数据。</td> 
  </tr> 
  <tr> 
   <td align="left">2.此时我们使用控制台2中先使用<code>start transaction;</code>开始事务，再使用<code>insert into learn_sql.learn_sql (id,money);</code>接着我们在控制台2使用<code>commit;</code>提交修改。</td> 
  </tr> 
  <tr> 
   <td align="left">3.接着我们在控制台1使用<code>update learn_sql.learn_sql set money = 1000;</code>我们会发现这个时候我们修改的语句条数为两句，这与我们之前查询的一条不同，这就是幻读。</td> 
  </tr> 
 </tbody> 
</table>

> 解决办法：把事务隔离级别修改为`SERIALIZABLE`  
> 之后我们会发现在执行第二步是会被lock住无法操作，从而避免了幻读。

--------------------

### 5.总结： ###

<table> 
 <thead> 
  <tr> 
   <th>事务隔离级别</th> 
   <th>更新丢失</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>未提交读</td> 
   <td>避免</td> 
   <td>发生</td> 
   <td>发生</td> 
   <td>发生</td> 
  </tr> 
  <tr> 
   <td>已提交读</td> 
   <td>避免</td> 
   <td>避免</td> 
   <td>发生</td> 
   <td>发生</td> 
  </tr> 
  <tr> 
   <td>可重复读</td> 
   <td>避免</td> 
   <td>避免</td> 
   <td>避免</td> 
   <td>发生</td> 
  </tr> 
  <tr> 
   <td>串行化</td> 
   <td>避免</td> 
   <td>避免</td> 
   <td>避免</td> 
   <td>避免</td> 
  </tr> 
 </tbody> 
</table>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NTQyMDg1_size_16_color_FFFFFF_t_70]: /images/20220122/10b4b28d6b9547809dd4490b0288687b.png