四、mysql核心-锁与事务

最新推荐文章于 2023-04-28 00:19:31 发布

wangle965235568

最新推荐文章于 2023-04-28 00:19:31 发布

阅读量221

点赞数

分类专栏： # mysql 文章标签： mysql读锁 mysql写锁 gap间隙锁事务隔离级别脏读不可重复度可重复读幻读

本文链接：https://blog.csdn.net/wangle965235568/article/details/106539531

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

前言：

关于什么是锁，为什么需要锁等概念这里不做解释了，可以看下我的另一谝关于jdk中并发的栏目，其中介绍了并发的各种只是框架，包括锁以及jdk的锁实现等等。并发编程、锁等知识框架

一、mysql中的锁

本章节讲述表锁和行锁，gap锁在本文后边事务中会讲到
因为MyISAM支持表锁，InnoDb支持表、行锁。所以按照引擎来讲述下这两个锁

1、表锁

特性
开销小，加锁快;不会出现死锁;锁定粒度大，发生锁冲突的概率最高,并发度最低。

适用场景
表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如 OLAP 系统

2、行锁

特性
开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低,并发度也最高
适用场景
行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理(OLTP)系统。

3、MyISAM锁

MySQL 的表级锁有两种模式: 表共享读锁(Table Read Lock) 表独占写锁(Table Write Lock)

3.1共享读锁

# session1 锁testmysam表
lock table testmysam READ 
# session2
select * from testmysam # 可以查询 
#session1
insert into testmysam value(2); # 报错
update testmysam set id=2 where id=1; # 报错
# session2 
insert into testmysam value(2); # 等待 
#session1 
insert into account value(4,'aa',123); #报错
select * from account ; #报错 
# session2 中
insert into account value(4,'aa',123); #成功 
# session1  
select s.* from lock table 表名 as 别名 read;

总结

对表1加读锁之后，只能对1进行读操作，不能写表1，也不能操作其他表
对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求

3.2独占写锁

# session1 对testmysam加写锁
lock table testmysam WRITE
# session1对testmysam增删改查都可以
insert testmysam value(3);
delete from testmysam where id = 3 
select * from testmysam
# session1对其他表操作报错
select s.* from testmysam s
insert into account value(4,'aa',123);
#session 2 会等待，因为写锁会阻塞读操作
select * from testmysam

总结：

写锁，对 MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作
对 MyISAM 表的写操作，当前 session 可以对本表做 CRUD,但对其他表进行操作会报错

4、InnoDB锁

共享锁又称:读锁：当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。
排它锁又称:写锁：当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

注：

两个事务不能锁同一个索引。
insert ，delete ， update 在事务中都会自动默认加上排它锁。
行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

# 建表
CREATE TABLE testdemo (
`id` int(255) NOT NULL ,
`c1` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
`c2` int(50) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX `idx_c2` (`c2`) USING BTREE )
ENGINE=InnoDB;
# 插入数据
insert into testdemo VALUES(1,'1',1),(2,'2',2);

# 开启事务1
BEGIN
select * from testdemo where id =1 for update
# 另一个session
update testdemo set c1 = '1' where id = 2 #成功 
update testdemo set c1 = '1' where id = 1 #等待

BEGIN
update testdemo set c1 = '1' where id = 1 
# 在另外一个 session 中
update testdemo set c1 = '1' where id = 1 等待

BEGIN
update testdemo set c1 = '1' where c1 = '1'
# 在另外一个 session 中
update testdemo set c1 = '2' where c1 = '2' #等待,因为c1行没有索引，直接会进行锁表操作

# 第一个 session 中
select * from testdemo where id =1 for update
# 第二个 session
select * from testdemo where id =1 lock in share mode #阻塞，写锁会阻塞读锁

5、锁的等待问题

工作中可能会遇到这种问题。

#程序员甲，正直调试代码
BEGIN
SELECT * FROM testdemo WHERE id = 1 FOR UPDATE
# 你正直完成的功能也要经过那部分的代码，你得上个读锁，然后你就只能等待他释放锁。
BEGIN
SELECT * FROM testdemo WHERE id = 1 lock in share mode

解决方案

# 查看mysql锁详情
select * from information_schema.INNODB_LOCKS;

发现有两个锁在对同一个数据进行操作，X (写锁)，另外一个是 S(读锁)

# 查看mysql正在等待锁的query
select * from sys.innodb_lock_waits

在这里插入图片描述
我现在执行的这个 sql 语句有了，另外看下最下面，kill 命令，你在工作中完全可以通过 kill 吧阻塞了的 sql 语句给干掉，你就可以继续运行了，不过这命令也要注意使用过，如果某同事正在做比较重要的调试，你 kill，被他发现可能会被暴打一顿。

二、事务

1、事务的概念

现在的很多软件都是多用户，多程序，多线程的，对同一个表可能同时有很多人在用，为保持数据的一致性，所以提出了事务的概念。

举例：A 给 B 要划钱，A 的账户-1000 元， B 的账户就要+1000 元，这两个 update 语句必须作为一个整体来执行，不然 A 扣钱了，B 没有加钱这种情况很难处理。

2、事务的特性

事务应该具有 4 个属性:原子性、一致性、隔离性、持久性。这四个属性通常称为 ACID 特性。

原子性(atomicity)
一个事务必须被视为一个不可分割的最小单元，整个事务中的所有操作要么全部提交成功，要么全部失败，对于一个事务来说，不可能只执行其中的一部分操作
一致性(consistency)
一致性是指事务将数据库从一种一致性转换到另外一种一致性状态，在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏
隔离性(isolation)
一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。 (对数据库的并行执行，应该像串行执行一样)
持久性(durability)
一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，已经提交的修改数据也不会丢失

3、事务并发存在的问题

3.1 脏读

事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

3.2不可重复度

事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。

3.3幻读

和不可重复读唯一的却别就是通过插入或者删除数据导致的，比如事务A插入主键id=1的数据，未提交。事务B去插入id=1的数据，报错：主键冲突。但是事务B查询的时候却查询不到id=1的数据，事务A一脸懵逼。幻读无疑发生了

4、解决事务并发问题-事务隔离级别

4.1未提交读(READ UNCOMMITED)

未提交读我们可以看到3.1、3.2、3.3三个问题都是存在的。因为事务几乎没有起到作用。

4.2已提交读 (READ COMMITED)

4.2.1解决了脏读问题

# 查看事务的隔离级别
show variables like '%tx_isolation%';
# 设置事务隔离级别
set SESSION TRANSACTION ISOLATION LEVEL read committed;

# session1 中
start TRANSACTION
update account set balance = balance -50 where id = 1

# session2 中查询 (数据并没改变) 
select * from account
# 回到第一个 session2 中
commit
# session2中数据已经改变
select * from account # (数据已经改变)

4.2.2存在不可重复度问题

# 设置隔离级别
set SESSION TRANSACTION ISOLATION LEVEL read committed;
# 事务A
start TRANSACTION
update account set balance = balance -50 where id = 1
# 事务B，查询数据未变化，因为A还未提交
select * from account
# 事务 A
commit
# 事务B中，发现事务已经改变，事务B两次读取的数据不一致，则存在不可重复读问题
select * from account

4.3可重复读(REPEATABLE READ)

4.3.1解决了不可重复读

例子和4.2.2中一样，只不过修改了隔离级别为可重复读

set SESSION TRANSACTION ISOLATION LEVEL repeatable read;

发现事务B两次读取到的数据是一致的，即解决了不可重复读问题。
但是，B读取不到A已经提交的数据了，这难道不会存在问题吗？

假设数据本来100
A事务减去50变为50
B事务读取是100
A事务commit
B事务读取还是100，再进行减50
B事务commit

那数据库中数据岂不是是50 ，但是我们明明是减了两次。这就违反了数据库完整性约束了呀。别慌，我们去数据库查看数据的时候，发现数据是0，并没有违反数据库完整性约束。为什么呢
因为可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）。

4.3.2存在幻读问题

# 事务A
begin
insert into account (id) values(1)
# 事务B
begin
insert into account (id) values(1) # 报错，主键冲突
select * from  account where id=1; # 查询不到数据，一脸懵逼，你告诉我主键冲突，但是我却查询不到数据，幻读发生了

mysql在可重复度的隔离级别下其实已经解决的幻读的问题，使用的是间隙锁（gap锁）

# 事务A
begin
# 插入数据并且对ID=1进行加锁（对索引加锁）
insert into account (id) values(1) for update
# 事务B
begin
# 发现id=1已经被锁住了，所以会阻塞等待
insert into account (id) values(1) # 报错，主键冲突
# 等到事务Acommit后就会报主键冲突，这时候再去查找id=1的数据就可以查找到了

4.4可串行化(SERIALIZABLE)

相当于锁表，性能太差，所以一般不建议使用

show variables like '%tx_isolation%';
set SESSION TRANSACTION ISOLATION LEVEL serializable;
# 事务A
begin
select * from account
# 事务B
begin
select * from account
# 发现根本就不让插入，因为事务A对整张表进行了加锁，我们必须等待事务Acommit释放锁之后才能进行写操作
insert into account VALUES(4,'deer',500)

gap锁小甜点

select @@tx_isolation;
create table t_lock_1 (a int primary key);
insert into t_lock_1 values(10),(11),(13),(20),(40);
# 事务A
begin
select * from t_lock_1 where a <= 13 for update;
# 事务B
insert into t_lock_1 values(21) 
insert into t_lock_1 values(19) #阻塞
# 因为在 rr 隔离级别中者个会扫描到当前值(13)的下一个值(20),并把这些数据全部加锁，所以20之前的额数据都被加锁了

create table t_lock_2 (
a int primary key,
b int, key (b)); 
insert into t_lock_2 values(1,1),(3,1),(5,3),(8,6),(10,8);
# 事务A
BEGIN
select * from t_lock_2 where b=3 for update;
# 事务B
// 不可执行，因为 a=5 上有一把记录锁 
select * from t_lock_2 where a = 5 lock in share mode; 
//  不可以执行，因为 b=2 在(1, 3]内
insert into t_lock_2 values(4, 2); 
// 不可以执行，因为 b=5 在(3, 6)内 
insert into t_lock_2 values(6, 5); 
// 可以执行，(2, 0)均不在锁住的范围内
insert into t_lock_2 values(2, 0);
// 可以执行，(6, 7)均不在锁住的范围内 
insert into t_lock_2 values(6, 7);