【MySQL篇】深入理解Mysql事务隔离级别与锁机制

最新推荐文章于 2024-06-02 14:54:06 发布

一剑逍遥

最新推荐文章于 2024-06-02 14:54:06 发布

阅读量979

点赞数 19

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_44781222/article/details/136694766

版权

提要

MySQL事务及ACID特性详解
MySQL事务隔离级别详解
MySQL锁机制详解
MySQL锁优化建议

思维导图

MySQL 事务概述

数据库一般都并发执行多个事务,事务及其ACID属性，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致我们说的脏写、脏读、不可重复读、幻读问题
为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制

事务及其ACID属性

什么是事务ACID属性

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性

原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行
一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。
隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

并发事务处理带来的问题

更新丢失(Lost Update)或脏写
- 最后的更新覆盖了由其他事务所做的更新
脏读（Dirty Reads）
- 事务A读取到了事务B已经修改但尚未提交的数据,不符合一致性要求
不可重读（Non-Repeatable Reads）
- 事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性
幻读（Phantom Reads）
- 事务A读取到了事务B提交的新增数据，不符合隔离性

事务隔离级别

为什么要事务隔离？

“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决

隔离级别详情

事务隔离级别分析

数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大

Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别

隔离分类

读未提交（read-uncommitted）

 开启：set tx_isolation='read-uncommitted'

读已提交（read-committed）

开启：set tx_isolation='read-committed

可重复读（repeatable-read）

   开启：set tx_isolation='repeatable-read';

串行化（serializable）

   开启：set tx_isolation='serializable';

查看当前事务隔离级别

show variables like 'tx_isolation'

设置事务隔离级别

set tx_isolation='REPEATABLE-READ';

锁详解

锁是计算机协调多个进程或线程并发访问某一资源的机制。

锁的分类

从性能上分为乐观锁(用版本对比来实现)和悲观锁
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)
读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据操作的粒度分，分为表锁和行锁

锁的具体分析

表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。

建表：

‐‐建表SQL
 CREATE TABLE `mylock` (
 `id` INT (11) NOT NULL AUTO_INCREMENT,
 `NAME` VARCHAR (20) DEFAULT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE = MyISAM DEFAULT CHARSET = utf8;

 ‐‐插入数据
 INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
 INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');
 INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');
 INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');

手动增加表锁

lock table 表名称 read(write),表名称2 read(write);

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

加读锁和加写锁

加读锁

当前session和其他session都可以读该表
当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待

加写锁

当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞

总结：

对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻赛对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作
对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作

行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高

InnoDB与MYISAM的最大不同有两点：

InnoDB支持事务（TRANSACTION
InnoDB支持行级锁

行锁演示：

一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞

行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

#SQL语句：
show status like 'innodb_row_lock%';

查看INFORMATION_SCHEMA系统库锁相关数据表

 ‐‐ 查看事务
 select * from INFORMATION_SCHEMA.INNODB_TRX;
 ‐‐ 查看锁
 select * from INFORMATION_SCHEMA.INNODB_LOCKS;
 ‐‐ 查看锁等待
 select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

 ‐‐ 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
 kill trx_mysql_thread_id

 ‐‐ 查看锁等待详细信息
 show engine innodb status\G;

状态量说明

Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度（等待总时长）
Innodb_row_lock_time_avg: 每次等待所花平均时间（等待平均时长）
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间Innodb_row_lock_waits:系统启动后到现在总共等待的次数（等待总次数）

当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。
总结：

MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。
读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞

无索引行锁会升级为表锁

锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

间隙锁

间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，某些情况下可以解决幻读问题

临建锁

Next-Key Locks是行锁与间隙锁的组合

死锁

思考：什么是死锁?

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象，需要四个条件，互斥条件，不剥夺条件，请求和保持条件，循环等待条件

死锁怎么处理？

如何解决：

将陷入死锁的进程全部撤销。
逐个作废死锁进程，直至死锁不再存在。
从死锁进程中逐个地强迫抢占其所占资源，直至死锁不存在

预防死锁：

资源一次性分配：一次性分配所有资源，这样就不会再有请求了：（破坏请求条件）
只要有一个资源得不到分配，也不给这个进程分配其他的资源：（破坏请保持条件）
可剥夺资源：即当某进程获得了部分资源，但得不到其它资源，则释放已占有的资源（破坏不可剥夺条件）
资源有序分配法：系统给每类资源赋予一个编号，每一个进程按编号递增的顺序请求资源，释放则相反（破坏环路等待条件）
以确定的顺序获得锁
超时放弃

避免死锁:
预防死锁的几种策略，会严重地损害系统性能。因此在避免死锁时，要施加较弱的限制，从而获得较满意的系统性能。由于在避免死锁的策略中，允许进程动态地申请资源。因而，系统在进行资源分配之前预先计算资源分配的安全性。若此次分配不会导致系统进入不安全的状态，则将资源分配给进程；否则，进程等待。其中最具有代表性的避免死锁算法是银行家算法。

检测死锁