mysql on里用or

转载

mob64ca13ff28f1 2024-07-14 10:53:04

文章标签 mysql on里用or 版本号数据共享锁 文章分类 MySQL 数据库

事务的基本要素（ACID）

原子性（Atomicity）：事务开始后的所有操作，要么全部做完，要么全部不做。事务执行过程中出错，会回滚到事务开始前的状态
一致性（Consistency）：数据开始前和结束后，数据库的完整性约束没有被破坏。比如：A向B转账，不可能A扣了钱，B却没收到
隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。
持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

事务的并发问题

脏读

A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的就是脏数据。

这种情况常发生于转账和取款操作中：

时间顺序	转账事务	取款事务
1		开始事务
2	开始事务
3		查询账户余额为2000元
4		取款1000元，余额被更改为1000元
5	查询账户余额为1000元（产生脏读）
6		取款操作发生未知错误，事务回滚余额变更为2000元
7	转入2000元，余额变更3000元（脏读的1000+2000）
8	提交事务

备注：按照正常逻辑，此时账户余额应该为4000元

不可重复读

事务A在执行读取操作中，需要查询两次数据，前一次查询账户余额1000元，此时事务B更改账户余额为2000并提交后，这个时候事务A查询账户余额为2000元，和之前查询的不一样，成为不可重复读

时间顺序	事务A	事务B
1	开始事务
2	第一次查询，账户余额1000
3		开始事务
4		更改账户余额2000
5		提交事务
6	第二次查询，账户余额2000

幻读

事务A在执行读取操作，需要两次统计数据的总量，前一次读取数据总量后，此时事务B执行了新增事务的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就想产生了幻觉一样，平白无故多了几条数据，成为幻读

时间顺序	事务A	事务B
1	开始事务
2	第一次查询，数据总量100条
3		开始事务
4		新增100条数据
5		提交事务
6	第二次查询，数据总量200条

不可重复读和幻读的区别
：
（1）、不可重复读是读取了其他事务更改的数据，针对update操作

解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据

（2）、幻读是读取了其他事务新增的数据，针对insert操作

解决：使用标记锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

SQL92标准（SQL隔离级别）

Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事物的执行结果。本隔离级别很少用于实际应用，因为它的性能不比其他级别好多少。读取未提交的数据，也称之为脏读

Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。解决了脏读

Repeatable Read（可重读）

这是Mysql的默认事务隔离级别。解决了不可重复读

Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

隔离级别	脏读	不可重复读	幻读
Read Uncommitted（读取未提交内容）	√	√	√
Read Committed（读取提交内容）	×	√	√
Repeatable Read（可重读）	×	×	√(Innodb解决了幻读)
Serializable（可串行化）	×	×	×

锁

InnoDB引擎的锁机制：Innodb支持事务，支持行锁和表锁用的比较多，Myisam不支持事务，只支持表锁。

Mysql官方文档锁的种类

This section describes lock types used by InnoDB.

Shared and Exclusive Locks//共享锁和排它锁

Intention Locks//意向锁（意向共享锁和意向排它锁）

Record Locks//记录锁

Gap Locks//间隙锁

Next-Key Locks//临界锁

Insert Intention Locks

AUTO-INC Locks

Predicate Locks for Spatial Indexes

1、 Shared and Exclusive Locks（共享锁和排它锁）

共享锁（S）：允许一个事务去读一行，阻止其他事务获取相同数据集的排它锁。

排它锁（X）：允许获得排它锁的事务更新数据，阻止其他事务获取相同数据集的共享锁和排它锁

手动加锁

共享锁（S）：
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他锁（X)
：SELECT * FROM table_name WHERE ... FOR UPDATE。

2、Intention Locks（意向锁）

意向锁又分为意向共享锁（IS）和意向排他锁（IX）

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁不需要用户干预

对于UPDATE、DELETE和INSERT语句，Innodb会自动给涉及的数据集加排它锁，对于普通SELECT语句，Innodb不会加任何锁。

3、Record Locks

单条索引记录加锁，Record Lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚合主键索引，那么锁住的就是这个隐藏的聚合主键索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁。

4、Gap Locks

在索引记录之间的间隙加锁，或者在某一条索引记录的之前或之后加锁，不包含改索引记录本身，Gap Lock的机制主要是是解决了幻读问题。
Repeatable Read隔离级别才有的

5、Next-key Locks

Next-key Locks是在Innodb和Repeatable Read的情况下使用的数据库锁，官网有介绍，Next-key Locks是行锁和Gap Locks的组合。

Gap Locks介绍

假如是for update级别的操作，先看看几条总结的何时加锁的规则

唯一、主键索引

精确等值查询：Next-key Locks就退化为Record Locks锁，不加Gap Locks锁
范围查询：会锁住where条件中的响应的范围，范围中的记录以及间隙，换言之就是加上Record Locks锁和Gap Locks

非唯一索引

精确等值查询：Next-key Lock会加Record Locks和Gap Locks
范围查询：会锁住where条件中的响应的范围，范围中的记录以及间隙，换言之就是加上Record Locks锁和Gap Locks

非索引查询：全表记录Record Locks锁，全表间隙加Gap Locks锁

Gap Locks机制解决幻读演示：

Innodb+Repeatable Read

表结构 ID主键索引 NAME唯一索引 age普通索引

Gap Locks只会对阻塞insert操作，因为Gap区间不存在任何记录，除了insert操作，其他的操作等价于空操作，所以mysql不去阻塞它

CREATE TABLE `user` (
  `ID_` bigint(20) NOT NULL AUTO_INCREMENT,
  `AGE_` int(11) DEFAULT NULL,
  `HEIGTH_` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID_`),
  KEY `age` (`AGE_`)
) ENGINE=InnoDB AUTO_INCREMENT=372 DEFAULT CHARSET=utf8mb4;

演示实例1（主键索引+精确等值读）

#Session1
BEGIN;

select * from user where ID_=4 for update;
select * from user where ID_=3 for update;

Session2
BEGIN;

insert user(ID_,AGE_)  VALUES(4,1)
insert user(ID_,AGE_)  VALUES(3,1)
insert user(ID_,AGE_)  VALUES(5,1)

session1中4有值，3没有值，都会加Record Locks
session2中插入ID=4、3都会阻塞，ID=5不会阻塞

演示实例2（主键索引+范围读）

#Session1
BEGIN;

select * from user where ID_<4 for update;

#Session2
BEGIN;
insert user VALUES(1,'test',1)
insert user VALUES(4,'test',1)
insert user VALUES(5,'test',1)

session1范围读ID<4的，Gap区间是(-∞,4];
session2插入ID=1、4会阻塞，但是ID=5不会阻塞。

演示实例3（非唯一索引+精确等值读）

#Session1
BEGIN;

select * from user where AGE_=4 for update;

#Session2
BEGIN;

insert user(AGE_) VALUES(1)
insert user(AGE_) VALUES(4)

insert user(AGE_) VALUES(0)
insert user(AGE_) VALUES(5)
ROLLBACK;

Session1中查询age=4，区间是[1，4]
sesison2中age=0、5不会阻塞，age=1、4会阻塞

演示实例4（非唯一索引+范围读）

#Session1
BEGIN;

select * from user where AGE_>7 for update;

#Session2
BEGIN;
insert user(AGE_) VALUES('test',6)

insert user(AGE_) VALUES('test',7)
insert user(AGE_) VALUES('test',8)

Session1中查询age>7；区间是[7，∞+]
Session2中插入age=6成功，age=7、8阻塞

演示实例5（非索引条件读）

#Session1
BEGIN;
select * from user where HEIGTH_=4 for update;

select * from user where HEIGTH_>4 for update;

#Session2
BEGIN;

insert user(HEIGTH_) VALUES(1)
insert user(HEIGTH_) VALUES(4)

insert user(HEIGTH_) VALUES(7)
ROLLBACK;

session1中不管是精确等值读还是范围读，session2中插入任何值都会阻塞

从上面五个演示可以看出来，innodb搜索引擎中，通过索引查询锁的是行，非索引查询使用的锁定的整张表

回头可以看脏读、不可重复度、幻读。可以通过加锁的方式解决

隔离级别	脏读	不可重复读	幻读	解决锁
Read Uncommitted（读取未提交内容）	√	√	√
Read Committed（读取提交内容）	×	√	√	写事务加入X锁
Repeatable Read（可重读）	×	×	√(Innodb解决了幻读)	MVCC
Serializable（可串行化）	×	×	×

MVCC

InnoDB的MVCC，是通过在每行纪录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间（或删除时间），当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行纪录的版本号进行比较。在REPEATABLE READ隔离级别下，MVCC具体的操作如下：

SELECT
InnoDB会根据以下两个条件检查每行纪录：
InnoDB只查找版本早于当前事务版本的数据行，即，行的系统版本号小于或等于事务的系统版本号，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。行的删除版本，要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除。
只有符合上述两个条件的纪录，才能作为查询结果返回。
INSERT
InnoDB为插入的每一行保存当前系统版本号作为行版本号。
DELETE
InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
UPDATE
InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时，保存当前系统版本号到原来的行作为行删除标识。

优点：
保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好。

缺点：
每行纪录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

MVCC

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。