Mysql基础3（事务，锁，mvcc）

分手后的思念是犯贱 2022-03-17 06:54 259阅读 0赞

> **文章最前**： 我是Octopus，这个名字来源于我的中文名--章鱼；我热爱编程、热爱算法、热爱开源。所有源码在我的个人[github][] ；这博客是记录我学习的点点滴滴，如果您对 Python、Java、AI、算法有兴趣，可以关注我的动态，一起学习，共同进步。

**相关文章：**

1.  [Mysql基础1(索引)][Mysql_1]
2.  **[Mysql基础2（引擎，体系结构，查询机制））][Mysql_2]**
3.  [Mysql基础3（事务，锁，mvcc）][Mysql_3_mvcc]
4.  [Mysql基础4（深入理解mvcc）][Mysql_4_mvcc]

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

**文章目录：**

1.事务

2. 锁

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

**InnoDB的特性：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70][]

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

### **1.事务** ###

**   什么是事务？**

事务是数据库的最小执行单位，是单个逻辑工作单元执行的一系列操作，事务是一组不可分割的操作组合。

典型的事务场景：

update user\_account set balance =balance-1000 where userID=3;

update user\_account set balance= balance+1000 where userID=1;

begin/start transaction   ---手工

commit/rollback             ----事务提交或回滚

set session autocommit =on/off;    ----设定事务是否自动开启

**  JDBC编程：**

connection.setAutoCommit(boolean);

**Spring 事务aop编程：**

expression=execution(\*);

**事务的ACID特性：**

原子性：要么一起执行成功，要么全部失败进行回滚

一致性：数据状态的改变是一致性的，a-100;b+100;a+b的综合不变

隔离性：事务和事务之间是相互没有影响

持久性：事务提交了就会永远保持，就算数据库宕机，重启之后也会继续执行

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

**1.脏读**：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 1][]

**   2.幻读：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 2][]

**  3.重复读**：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 3][]

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

**事务的四种隔离级别：**

1） Read uncommitted(读未提交) -未解决并发问题

事务未提交对其他事务是可见的；

2）  Read committed(读已提交) ----解决脏读问题

一个事务开始之后，智能看到自己提交的事务所做的修改，不可重复读；

3）  Repeatable Read(可重复读)----解决不可重复读问题

在同一个事务中多次读取同样的数据结果是一样的，这种隔离级别未定义解决幻读的问题；

4）  Serializable(可串行化)---解决所有问题

最高的隔离级别，通过强制事务的串行执行；

InnoDB引擎对隔离级别的支持程度：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 4][]

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

### 2. **锁** ###

锁是用于管理不同事务对共享资源的并发访问

**表锁与行锁的区别：**

锁定粒度：表锁>行锁

加锁效率：表锁>行锁

冲突概率：表锁>行锁

并发性能：表锁<行锁

Innodb存储引擎支持行锁和表锁（另类的行锁）

** Mysql InnoDB锁类型：**

1.共享锁（行锁）

2.排它锁（行锁）

3.意向锁共享锁（表锁）

4.意向锁排它锁（表锁）

5.自增锁

**行锁的算法：**

1.记录锁

2.间隙锁

3.临键锁

共享锁：又称为读锁，就是多个事物对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改；

加锁解锁方式：select \* form users where id=1 lock in share mode;

commit/rollback

排它锁：有称为写锁，排它锁不能与其他说并存。

加锁释放锁方式：

delete/update/insert 默认加上x锁

select \* form table\_name where .... for update

commit/rollback

行锁到底锁住了什么？

InnoDB的行锁是通过给索引上的索引项加锁来实现的。

只有通过索引条件进行数据检索，InnoDB才能使用行级锁，否则InnoDB将使用表锁（锁住索引的所有记录）

表锁：Lock tables xx read/write;

意向锁共享锁&意向锁排它锁：

意向共享锁(IS)：表示事务准备给数据行加入共享锁，即一个数据行共享锁必须先取得该表的IS锁，意向共享锁之间可以相互兼容。

意向排它锁（IX）:表示事务准备给数据行加入排它锁，即一个数据行排它锁前必须先取得该表的IX锁，意向排它锁之间是相互兼容的。

意向锁（IS,IX）是InnoDB数据操作之前自动加的，不需要用户干预。

意义： 当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可快速返回该表不能启用表锁；

自增锁：

针对自增列自增长的一个特殊的表级别锁

show variables like 'innodb\_autoinc\_lock\_mode';

默认取值1，代表连续，事务未提交ID永久丢失

记录锁&间隙锁&临键锁

临键锁：锁住记录+区间（左开右闭）

当sql执行按照索引进行数据的检索时，查询条件为范围查找（between，and）等并有数据命中，则此时sql语句加上的锁为next-key locks，锁住索引的记录+区间（左开右闭）

间隙锁：锁住数据不存在的区间

记录锁：锁住具体的索引项，查询条件等匹配且查询的数据是存在。唯一索引，条件为精准匹配，退化为记录锁

临键锁：Innodb默认的行锁算法，当数据不存在临键锁退化为间隙锁，间隙锁只在可重复读事务隔离级别存在；

利用锁怎么解决脏读（在锁上加排它锁）：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 5][]

利用锁解决不可重复读问题（数据行加上共享锁）：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 6][]

**怎么解决幻读问题？**（加上临键锁）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 7][]

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

**死锁的介绍：**

1.多并发事务

2.没个事务都要持有锁

3.每个事务都要继续持有锁

4.事务之间产生加锁的循环等待，形成死锁

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

**避免死锁：**

1.类似的业务逻辑以固定的顺序访问表和行；

2.大事务拆小，大事务更倾向于死锁，如果业务允许，将大事务拆小

3.在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁的概率。

4.降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择

5.为表添加合理的索引，可以看到如果不走索引将会为表的每一行记录添加锁。

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

**查看mysql的设置的事务隔离级别**

select global .@@tx\_isolation;      select @@tx\_isolation;  
              tx1: set session autocommit=off;  
              update users set lastUpdate=now() where id =1;  
              在未做commit/rollback操作之前  
              在其他的事务我们能不能进行对应数据的查询（特别是加上了X锁的数据）  
              tx2: select \* from users where id > 1;  
              select \* from users where id = 1;

ex2：  
              tx1: begin  
              select \* from users where id =1 ;  
              tx2: begin  
              update users set lastUpdate=now() where id =1;  
     tx1:  
              select \* from users where id =1;

[github]: https://github.com/zhangyu345293721
[Mysql_1]: https://blog.csdn.net/zy345293721/article/details/87868179
[Mysql_2]: https://blog.csdn.net/zy345293721/article/details/87872793
[Mysql_3_mvcc]: https://blog.csdn.net/zy345293721/article/details/87884373
[Mysql_4_mvcc]: https://blog.csdn.net/zy345293721/article/details/87891718
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70]: /images/20220317/58657cb6d48c4fcba61907aa9d1d2b82.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 1]: /images/20220317/d31da935348b43e888e078205b3e6ac3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 2]: /images/20220317/1f90022236044d7e8ca951bc7ca7102e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 3]: /images/20220317/6da902ef1b394b9ea77275894ce70171.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 4]: /images/20220317/b623be6a9b96435b88cdc56f479d0ce8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 5]: /images/20220317/4ef6dc199293421b9ddfd29f24dc474f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 6]: /images/20220317/7639779cfa82473e94d52b0e3a0a0222.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 7]: /images/20220317/4ad6d4291c4d4502a59fbd420de08415.png