事务隔离级别与锁原理《动手探索mysql原理》

最新推荐文章于 2024-01-25 16:50:33 发布

莫莫绵

最新推荐文章于 2024-01-25 16:50:33 发布

阅读量943

点赞数 2

分类专栏：数据库技术知识文章标签：事务 mysql 锁

本文链接：https://blog.csdn.net/qq_36428889/article/details/100101340

版权

数据库技术知识专栏收录该内容

3 篇文章 0 订阅

订阅专栏

ACID

事务相关操作：

使用隔离性：

1、读未提交(命令是：read uncommitted)

2、读已提交（read committed）

3、可重复读（repeatable read）（mysql默认的级别）

MVCC（Multi-Version Concurrency Control ，多版本并发控制）

事务

今天学习事务和锁了哈。

先看一下事务Tx，

事务，什么是事务呢？实际上就是你在业务上对数据库做的某个执行单元，这个单元是由用户定义的，必须是不可在分割的的执行单元。要么做要么不做。

我们通常谈到事务就会想到四个性质ACID：

ACID

原子性：和之前说的不可分割一样，一个事务要做就得全部做完，不能里面有些做了，有些没做。这是原子性。

隔离性：两个事务之间是隔离的，相互独立的，互不影响的。

一致性：前两个性质就是保持数据一致性的手段。不满足原子性或隔离性就可能造成数据的不一致。比如转账的时候，钱从一个用户转移到另一个用户，但是银行的钱是不会减少的，这就是一致。我们一般会对表写约束来保持一致性。

持久性：数据能够持久的保存。

事务相关操作：

mysql 有一个自动提交的参数autocommit，默认是on，表示事务是自动提交的。如果不想提交可以关掉，然后自己提交commit。比如写批量的时候。

我们可以vi开启一个单次事务。

回滚：你开启事务begin;的时候改动时，使用rollback；命令会回滚掉自己的操作。

保存点：比如你做了很多修改操作，然后突然修改错了一个，但是你回滚的话会把之前所有的都回滚掉，这时你可以在中间加一个保存点，这样就会只回滚到保存点。就和玩游戏的时候保存一样。

相关查询语句命令如下，你可以自己选择某些行自己操作试试（不要全部一起运行navicat查询时可以选择行运行），就能明白这些在做什么。

BEGIN;
INSERT into num (b) VALUES(10);
SELECT * from num;
ROLLBACK;
COMMIT;
SAVEPOINT haha;
ROLLBACK to haha;

当我们开启一个事务或者将系统变量autocommit设置为OFF的时候，讲道理是不会自动提交你的数据的。但是有一些语句使用了之后，就会偷偷的提交掉你的事务，这叫隐式提交。哪些语句会发生隐式提交呢？如下：

1、DDL语言，这是如：create、alter、drop啊这种对数据库、表、视图、存储过程做改变的操作。

2、在上一个事务没有提交时，重新又开了一个事务，那么上一个事务就会提交掉。

3、使用锁语句：lock tables unlock tables这样也会隐式提交。

4、使用加载数据的语句如：load data

5、其他一些：如check 、flush、reset啊这种命令。等等。

使用隔离性：

多个事务他们可能会操作同一个记录。那么不同的隔离级别会有不同的效果。mysql中我们可以对隔离性的级别进行设置，主要有以下几种级别：

1、读未提交(命令是：read uncommitted)

指一个事务可以读到别的事务没有提交的事务（可能造成别人的数据最后没有提交，但是你这里读了，这就是脏读）。所以这不建议使用。

2、读已提交（read committed）

指一个事务只能读到另一个事务已经提交的数据，并且只要另一个事务更新提交了，这个事务都能查到最新值。这也会出现一些问题，比如不可重复读：就是说同样的查询条件，在极短的时间内可能出来不同的结果，对于某些业务这也是有问题的。

如果一个事务先根据某个条件查询出一些数据，但是另一个事务又插入了一些满足这个条件的数据。那么原先的事务再次查询会把这些事务也查出来。这称之为幻读。

3、可重复读（repeatable read）（mysql默认的级别）

一个事务第一次读过某条记录后，即使其他事务修改提交了该记录的值，该事务之后再读该条记录时，读到的还是第一次读到的值，这就是可重复读。这避免了不可重复读。可能还会存在幻读问题，但是mysql会禁止幻读的发生，也就是说mysql已经帮我们在读的时候把这种现象解决了。

4、串行化(serializable）

上3种隔离级别都允许对同一条记录同时进行读-读、读-写、写-读的并发操作，如果我们不允许读-写、写-读的并发操作，可以使用SERIALIZABLE隔离级别，serializable对同一条记录的操作都是串行的，就是说排队进行。一个事务在读，另一个事务写就要等前面这个读的事务提交了再会去写。和同步差不多。所以不会出现脏读、幻读等现象。

设置和查询语句：

select @@tx_isolation;  //mysql8之前
select @@transaction_isolation   //mysql8使用

select @@global.transaction_isolation;  //全局的

set session transaction isolation level repeatable read; --会话

set global transaction isolation level repeatable read;  --全局

版本链

我在之前学索引的时候知道了，mysql每一行数据有三个隐藏字段：DB_ROW_ID（没有设主键的时候会自动生成的行唯一标识）和事务id（DB_TRX_ID）、回滚指针（DB_ROLL_PTR）。这个事务id其实就是记录最近一次修改的事务id。而回滚指针会指向上一个事务id，可以通过这个指针找到该记录修改前的信息，一个接一个形成一个版本链。

读已提交的级别是怎么实现的？

ReadView

如果你启动了一个事务，那么在事务读select的时候会生成一个readview，这里面有一个属性：m_ids，这个属性会保存还没有提交的事务信息。这样当在版本链中找的时候会去判断事务是不是处于未提交状态。这些未提交的数据存在undolog日志里。

这样我们看读已提交的级别是怎么实现的呢？比如有3个事务都对b字段进行了修改，如上图，但是只有trx_id=175提交了。那么我开新一个事务190去查询的时候按照读已提交应该是查到175这个事务提交的内容。怎么做的呢？首先我们查的时候先将所有没有提交的事务id存到m_ids中，然后看最新的事务id在不在m_ids中，如果在的话就找用回滚指针下一个版本。直到找到已经提交的版本。如果190查完之后，就有别的事务提交了，那么再查就发现它不在m_ids中了。就直接查到了。

ReadView中主要包含4个比较重要的内容：

1、m_ids：表示在生成ReadView时当前系统中活跃的（未提交的）读写事务的事务id列表。

2、min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。

3、max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。

4、creator_trx_id：表示生成该ReadView的事务的事务id

判断记录的某个版本是否可见？

如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下 trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

也就是说read committed的实现就是在每次读数据的时候生成一个readview

repeatable read其实也是用的readview，只不过是在这个事务第一次查的时候生成一个readview，之后查都只用这个readview。

这其实就是MVCC。

MVCC（Multi-Version Concurrency Control ，多版本并发控制）

指的就是在使用READ COMMITTD、 REPEATABLE READ这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程。它可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

之前说了mvcc它REPEATABLE READ在读的时候mysql解决了幻读的现象。

怎么解决的呢?

我先了解一下锁。

锁

首先是读锁和写锁，读锁就是说如果我给某行数据加了一个读锁，那么其他人就不能读这行数据了。加了写锁之后，别人就不能对这行做读、更新操作。

读锁：共享锁、Shared Locks、S锁一个资源可以加多个读锁

写锁：排他锁、Exclusive Locks、X锁。不能再加写锁。

这里的s和x锁之间的冲突关系如下：

	x锁	s锁
x锁	不冲突	不冲突
s锁	不冲突	冲突

假设我们给一行数据加了一把写锁，那么别人想再加一把写锁是不行的，需要等待。这是锁冲突会发生阻塞。但是还是有东西会不经过锁可以直接进来读数据：select * from ... where...这种条件不会来加锁，就不会发生锁冲突就不会阻塞。

如何加锁：

//读锁
select ... lock share mode;

//写锁
select ... for update;
DELETE ...
UPDATE ...
INSERT ...

select ... lock share mode; S锁，用于A事务读了之后限制其他事务不能修改这个数据。当然别的事务也加了读锁，A自己也就不能修改了。

select ... for update; X锁，用于读出数据后，其他事务即不能写，也不能加读锁，那么就导致只有自己可以修改数据

DELETE：删除一条数据时，先对记录加X锁，再执行删除操作。

INSERT：插入一条记录时，会先加隐式锁 隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问到。

UPDATE:如果被更新的列，修改前后没有导致存储空间变化，那么会先给记录加X锁，再直接对记录进行修改。如果被更新的列，修改前后导致存储空间发生了变化，那么会先给记录加X锁，然后将记录删掉，再Insert一条新记录。

如何释放锁：

事务结束，锁就结束了。锁是在事务里面的用的。

什么是隐式锁？

一个事务插入一条记录后，还未提交，这条记录会保存本次事务id，而其他事务如果想来对这个记录加锁时会发现事务id不对应，这时会产生X锁，所以相当于在插入一条记录时，隐式的给这条记录加了一把隐式X锁。

搞清楚了就再来两个锁：

行锁和表锁

行锁就是锁某一行数据。有以下：

LOCK_REC_NOT_GAP：单个行记录上的锁。

LOCK_GAP：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

LOCK_ORDINARY：是上面两种锁的合并。锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

我们使用事务和锁可以用下面的语句查看一些表信息：

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;   --查看当前的开启的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;  --查看当前开的锁

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;  --查看锁等待的关系

这写个显示表字段含义用一个思维导图粗略看一下：看的不爽就去官网看，我们还是自己逛官网，才能有所收获。

这里给出mysql8的：https://dev.mysql.com/doc/refman/8.0/en/innodb-trx-table.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-table.html

这里我们发现mysql8这个表已经被移除了，使用所述性能模式data_locks表代替

同样第三个也被代替了。

https://dev.mysql.com/doc/refman/8.0/en/data-lock-waits-table.html

//mysql8：
SELECT * FROM performance_schema.data_locks;

SELECT * FROM performance_schema.data_lock_waits;

read commited 级别下加锁：

//开启一个会话，a是主键 
begin;
select * from user where a = 1 for update;   --先加个x锁

//开启另一个会话

select * from user  where a = 1 for update;  --这时发现会阻塞
select * from user where a = 2 for update;  --这里就不会阻塞

我们可以自己操作试试，这里直接总结一下：

查询使用的是主键时，就在主键值对应的那一条数据加锁。

查询使用的是唯一索引时，就会对查询值所对应的唯一索引记录项和对应的聚集索引上的项加锁。

查询使用的是普通索引时，会对满足条件的索引记录都加上锁，同时对这些索引记录对应的聚集索引上的项也加锁。

查询的时候没有走索引，也只会对满足条件的记录加锁。

这里你是可以插入数据的。

简单说就是没有用到的就不加锁嘛，但是！我们看一下别的隔离级别：可重复读级别

repeatable read级别下加锁

如果查询使用主键和唯一索引加锁和上面的级别一致，

但是使用普通索引查询是会把间隙加锁。这样你就不能插入数据了。因为插入数据是在某个数据间隙插入的嘛。把间隙加了锁就加不进去了，这样也就防止了幻读。

不嫌麻烦，我们就上述动手操作一下：这里用的mysql8

首先创建一个user表，字段id，b,c,d,e这就写了，加几个数据。

我们给e加上一个普通索引，然后打开一个查询会话：

执行

begin;
select * from user where e = '6' for update; 
SELECT * FROM performance_schema.data_locks;

这时我们看到下图，这里一些不重要的字段我藏掉了。

我们看lock_data,发现主键3已经被锁，e_index这边也锁了‘6’。

接着再开一个查询会话，依次运行：


//会话2
begin;
 select * from user where id = 5 for update;   
 select * from user where id = 4 for update; 
 select * from user where id = 3 for update;  //堵塞
 select * from user where id = 2 for update;  
 insert user (b,c,d,e) values(20,1,1,'51'); //堵塞
 insert user (b,c,d,e) values(21,1,1,'61');  //堵塞

这里我索引排个序后e就是null，...6 所以前后间隙都会加锁这时我想在加几行数据看看，commit之后发现锁没有解除。。。

怎么试都不行，把会话关了都不行。。。我郁闷了，。 SHOW PROCESSLIST;命令看了一下。

关闭了在睡觉的所有线程，。用 kill 204;命令。

然后查看，锁解开了。挺郁闷的，我接着就加了几行数据：按e排序是这样

我们再看一下之前的操作给一个事务加X锁 select * from user where e = '6' for update;

这时我们发现e='661'这个也加锁了这就符合之前说会给同时对这些索引记录对应的聚集索引上的项也加锁。

好这时我们插入这条数据INSERT into user(b,e) VALUES(10,'joind');

发现加成功了。这样我们知道repeatable read级别的普通索引加X锁还是可以插入数据的，只不过是在加了锁的间隙不能插入数据。它解决幻读通过GAP锁。

最后提一下repeatable read级别查询的时候没有走索引，会对表中所有的记录以及间隙加锁。

IS锁、IX锁

我们刚刚操作的时候发现lock mode锁模式这边有一个IX模式。

这是个什么鬼呢？

IS锁：意向共享锁、Intention Shared Lock。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。

IX锁，意向排他锁、Intention Exclusive Lock。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

也就是说我们加一个X表的时候，会先加一个table级的IX锁。

IS、IX锁是表级锁，他们是为了在之后加表级的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。意向锁和意向锁是不冲突的。

表锁

现在我想对这个表加一个表锁，但是我发现我已经加了一个行锁了。这个表锁是不能加的。他会通过上面说的写意向锁去判断有没有行锁，这样就直接给你报锁冲突。

在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的 S锁或者X锁的。我们可以自己加锁，但是不建议使用，性能没有行锁高。InnoDB的一个优点就是行锁。

LOCK TABLES user READ; //对表t1加表级别的S锁。

LOCK TABLES user WRITE;//对表t1加表级别的X锁

以下是内容摘抄过来的，暂时了解以下吧。后面再更深入学习。

AUTO-INC锁：

在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT 修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。这样一个事务在持有 AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。

采用一个轻量级的锁，在为插入语句生成AUTO_INCREMENT修饰的列的值时获取一下这个轻量级锁，然后生成本次插入语句需要用到的AUTO_INCREMENT列的值之后，就把该轻量级锁释放掉，并不需要等到整个插入语句执行完才释放锁。

系统变量innodb_autoinc_lock_mode：

innodb_autoinc_lock_mode值为0：采用AUTO-INC锁。

innodb_autoinc_lock_mode值为2：采用轻量级锁。

当innodb_autoinc_lock_mode值为1：当插入记录数不确定是采用AUTO-INC锁，当插入记录数确定时采用轻量级锁。

悲观锁

悲观锁用的就是数据库的行锁，它认为数据库会发生并发冲突，就直接上来就把数据锁住，让其他事务不能修改，直至提交了当前事务。

乐观锁

乐观锁其实是一种思想，认为不会锁定的情况下去更新数据，如果发现不对劲，才不更新(回滚)。在数据库中往往会加一个version字段来实现。

死锁的检测：

系统变量innodb_deadlock_detect：控制是否打开死锁检测，默认是打开的。

系统变量innodb_lock_wait_timeout：等待锁的超时时间，默认50s。系统变量innodb_print_all_deadlocks：将所有的死锁日志写入到mysql的错误日志中，默认是关闭的。
检测到死锁时，InnoDB会在导致死锁的事务中选择一个权重比较小的事务来回滚，这个权重值可能是由该事务影响的行数(增加、删除、修改)决定的。 SHOW ENGINE INNODB STATUS; 看看最近死锁的日志。

学会避免死锁

1、以固定的顺序访问表和行
2、大事务拆小，大事务更容易产生死锁
3、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率
4、降低隔离级别
5、为表添加合理的索引

如有理解错误、不透彻或者有歧义的地方还请路过的大佬们指正！

好像什么事都没有做，夏天就已经过完了。

莫莫绵

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
事务隔离级别与锁原理《动手探索mysql原理》

目录ACID事务相关操作：使用隔离性：1、读未提交(命令是：read uncommitted)2、读已提交（read committed）3、可重复读（repeatable read）（mysql默认的级别）4、串行化(serializable）版本链读已提交的级别是怎么实现的？ReadView判断记录的某个版本是否可见？MVCC（Mult...
复制链接

扫一扫