MySQL数据库学习（二）------MySQL中的日志+事务+锁机制

电玩女神 2023-07-06 11:59 12阅读 0赞

### 文章目录 ###

*  MySQL数据库中的锁
 *   *  数据库锁的分类
     *  MyISAM与InnoDB关于锁方面的区别
     *   *  MyISAM中
         *  InnoDB
     *  MySQL 数据库中的死锁
     *   *  通过业务逻辑来解决死锁问题
         *  通过数据库的设置来解决死锁问题
     *  MyISAM和InnoDB各自的适用场景
 *  MySQL数据库中的事务
 *   *  数据库事务的四大特性
     *  事务并发访问的问题以及事务隔离机制
     *   *  事务的隔离级别
         *  事务并发访问引起的问题以及如何避免
 *  MySQL数据库中两大重要的日志模块
 *   *  binlog（归档日志）
     *   *  binlog日志文件的格式（statement,row,mixed）
     *  redo log（重做日志）

# MySQL数据库中的锁 #

## 数据库锁的分类 ##

*  按锁的粒度划分，可以划分为表级锁、行级锁、页级锁
 *  按锁的级别划分，可分为共享锁、排它锁
 *  按加锁的方式划分，可分为自动锁、显示锁
 *  按操作方式划分，可分为DML锁、DDL锁（其中增删该查这一类的语句对应的为DML锁，而变动表结构这一类的操作为DDL锁）
 *  按使用方式划分，可分为乐观锁，悲观锁

> **乐观锁**：总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。适用于多读场景。  
> **悲观锁**：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。适用于多写场景。

## MyISAM与InnoDB关于锁方面的区别 ##

> MyISAM默认用的是表级锁，不支持行级锁  
> InnoDB默认用的是行级锁，也支持表级锁

### MyISAM中 ###

1.  当进行SELECT时，会生成一个表级的读锁，此时若有UPDATE,INSERT,DELETE均会被BLOCK，陷入阻塞，直到读锁释放。
2.  同理，当进行UPDATE,INSERT,DELETE时，会生成一个表级的写锁，SELECT会被BLOCK，陷入阻塞直到写锁释放。

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

手动为表增加读/写锁：

LOCK TABLES table_name read|write;

> 读锁：即**共享锁** ，因为当已经有一个SELECT操作正在进行中时，此时对于同一张表再进行一个SELECT操作，第二个SELECT操作并不会被阻塞，而是正常执行，这个锁可以被两个SELECT操作所共享，所以也叫共享锁。但是，当在第一个SELECT语句后面加上FOR UPDATE，即将其变成一个**排他锁**，那么此时若还有一个SELECT操作，则该操作会陷入阻塞  
> 写锁：当已经有一个写锁时，此时若再设置一个写锁或者读，都会陷入阻塞状态。

### InnoDB ###

> InnoDB使用的是二段锁，即加锁和解锁是分成两个步骤：即先对同一个事务里的一批操作进行加锁，然后在commit时，再对加上的锁进行统一的解锁

由于在MySQL的InnoDB，事务默认是自动提交的，关闭事务的自动提交，可以用

SET autocommit = 0;

> 在InooDB中，对SELECT进行了优化，显式得加上读锁应该在SELECT语句的后面加上 lock in share mode；InnoDB中锁是默认支持行级的

> 在InnoDB中，sql没有用到索引的时候使用的是表级锁，用到索引的时候使用的是行级锁

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

## MySQL 数据库中的死锁 ##

MyISAM不支持行级锁，所以MySQL中的死锁主要是在说InnoDB存储引擎的死锁。

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

MySQL中解决死锁的两种方式：通过业务和通过数据库的设置

### 通过业务逻辑来解决死锁问题 ###

*  指定锁的获取顺序
 *  将大事务拆分成各个小事务
 *  在同一个事务中，一次锁定尽量多的资源，减少死锁概率
 *  给表建立合适的索引以及降低事务的隔离级别等

### 通过数据库的设置来解决死锁问题 ###

*  通过参数 innodb\_lock\_wait\_timeout 根据实际业务场景来设置超时时间，InnoDB引擎默认值是50s。
 *  发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb\_deadlock\_detect 设置为 on，表示开启这个逻辑（默认是开启状态）。

> “行级锁什么时候会锁住整个表？“  
> InnoDB行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。  
> **此处感谢一下牛客网，收获很大**。

## MyISAM和InnoDB各自的适用场景 ##

1.  MyISAM：

*  频繁执行全表count语句，MyISAM中有一个变量保存了表的行数，所以很快
 *  对数据进行增删查的效率不高，查询非常频繁
 *  没有事务

1.  InnoDB：

*  数据的增删改查都相当频繁（通过行级锁与表级锁就能理解）
 *  可靠性要求比较高，要求支持事务

# MySQL数据库中的事务 #

## 数据库事务的四大特性 ##

ACID

*  原子性（Automic)：事务包含的所有操作要么全部执行，要么全部失败回滚
 *  一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另外一个一致状态。
 *  **隔离性**（Isolation）：多个事务并发执行时，一个事务的执行不该影响其他事务的执行
 *  持久性（Durability）：一个事务一旦提交，他对数据库的提交应该永久保存在数据库中。

## 事务并发访问的问题以及事务隔离机制 ##

### 事务的隔离级别 ###

*  读未提交(Read Uncommitted)：

> **允许脏读取**。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。

*  读已提交(Read Committed)：

> **允许不可重复读取**，但不允许脏读取。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

*  可重复读(Repeatable Read)：

> **禁止不可重复读取和脏读取**，但是有时可能出现幻读。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务。

*  序列化(Serializable)：

> **提供严格的事务隔离**。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。

事务的隔离级别越高，对数据的完整性和一致性保证越佳，但是对并发操作的影响也越大。MySQL事务默认隔离级别是可重复读。

### 事务并发访问引起的问题以及如何避免 ###

1.  更新丢失：两个不同事务同时获得相同数据，然后在各自事务中同时修改了该数据，那么先提交的事务更新会被后提交事务的更新给覆盖掉，这种情况先提交的事务所做的更新就被覆盖，导致数据更新丢失。（MySQL所有事务隔离级别在数据库层面上均可避免）
2.  脏读：事务A读取了事务B未提交的数据，由于事务B回滚，导致了事务A的数据不一致，结果事务A出现了脏读（READ-COMMITED事务隔离级别以上可以避免，MySQL的InnoBD的默认隔离级别为**REPEATABLE\_READ**）

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

设置隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL read uncommited;

1.  不可重复读：一个事务在自己没有更新数据库数据的情况，同一个查询操作执行两次或多次得到的结果数值不同，因为别的事务更新了该数据，并且提交了事务（REPEATABLE\_READ事务隔离级别以上可以避免）
2.  幻读：事务A读的时候读出了N条记录，事务B在事务A执行的过程中增加 了1条，事务A再读的时候就变成了N+1条，这种情况就叫做幻读。（设置为SERIALIZABLE隔离级别即可避免，导致事务A看起来像出现幻觉一样，这是最高隔离级别）

> 不可重复读与幻读的区别：幻读是指一种结构上的改变，比如说条数发生了改变；不可重复读是指读出的数值发生了改变。

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

数据库层面规避上述问题的具体总结见下表：

<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>

> 出于性能考虑，事务隔离级别越高，安全性越高，串行化执行越严重，这样会降低数据库的并发度，因此需要根据业务去设置事务隔离级别

# MySQL数据库中两大重要的日志模块 #

在MySQL的使用中，更新操作是很频繁的，如果每一次更新操作都根据条件找到对应的记录，然后将记录更新，再写回磁盘，那么IO成本以及查找记录的成本都会很高。所以，就出现了日志模块，也就是说，我们的update更新操作是先写日志，在合适的时间才会去写磁盘，日志更新完毕就将执行结果返回给了客户端。

## binlog（归档日志） ##

*  bin log是Server层的日志，所有引擎都可以使用
 *  binlog是逻辑日志，记录语句的原始逻辑，如 给uid=1这一行的数据赋新值"Bob"，其中的statement形式，说白了本质也就是sql语句
 *  binlog是追加写，一个日志文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

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

### binlog日志文件的格式（statement,row,mixed） ###

1.  statement格式的binlog记录的是完整的SQL语句，优点是日志文件小，性能较好，缺点也很明显，那就是准确性差，遇到SQL语句中有now()等函数会导致不准确
2.  row格式的binlog中记录的是数据行的实际数据的变更，优点就是数据记录准确，缺点就是日志文件较大。
3.  mixed格式的binlog是前面两者的混合模式

> 目前大多数使用的是 row 模式，因为很多情况下对准确性的要求是排在第一位的。

## redo log（重做日志） ##

*  redo log是InnoDB引擎所特有的日志模块，是物理日志，记录了某个数据页上做了哪些修改
 *  InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么redo log总共就可以记录 4GB的操作。从头开始写，写到末尾就又回到开头循环写。
 *  InnoDB的redo log保证了数据库发生异常重启之后，之前提交的记录不会丢失，这个能力称为crash-safe。

在下一篇MySQL的总结中会总结一下SQL语句中一些技巧，还有就是一个高频面试点：慢查询的sql调优以及一些其它琐碎知识

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200219103617661.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4=,size_16,color_FFFFFF,t_70