mysql

mysql学习笔记2-事务

发表于 2024/03/27 10:56:49 [mysql] 浏览次数：306

1.四大特性

  • 一致性
  • 隔离性
  • 持久性
  • 原子性

2.隔离性

四种隔离级别
  • READ UNCOMMITTED：脏读（一个事务会读取到另一个事务修改了但未提交的数据）。
  • READ COMMITTED：读取已提交（解决脏读）。
  • REPEATABLE READ：同一个事务中的两次读取的数据是一致的。
  • SERIALIZABLE：序列化,一个个事务排成序列的形式。事务一个挨一个执行，等待前一个事务执行完，后面的事务才可以顺序执行。（并发性极低）
查看mysql隔离级别
-- 全局隔离级别，会话隔离级别
 SELECT @@tx_isolation, @@GLOBAL.tx_isolation;
设置隔离级别
设置innodb的事务级别方法是：set 作用域 transaction isolation level 事务隔离级别，例如~
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

mysql> set global transaction isolation level REPEATABLE READ; //全局的

mysql> set session transaction isolation level read committed; //当前会话

3.开启事务

start transaction;
update t_user set amoount = amoount - 100 where name='Perter';
update t_user set amoount = amoount + 100 where name='Tom';
commit; //rollback

-- 设置回滚点
start transaction;
update t_user set amoount = amoount - 100 where name='Perter';
update t_user set amoount = amoount + 100 where name='Tom';
SAVEPOINT A;
UPDATE t_user SET amoount = 100;
rollback TO SAVEPOINT A; -- 回滚到设置的回滚点
commit;

4.SELECT * FROM [table_name] WHERE id=1 FOR UDPATE;

隔离级别:REPEATABLE READ 下

如果一个事务中有两个操作： 第一步：查询出数据， 第二步: 根据查询结果，在查询结果上做更新操作。

如果不加FOR UPDATE,select操作，执行的是快照读，读的是数据库记录的快照版本，是不加锁的。

对于第一步的查询操作加上 FOR UPDATE（或lock in share mode）,如果此时有其他事务在更新数据，并且未提交，那么此时的select会阻塞，直到其他事务提交释放锁。这样可以避免第一步和第二步之间有其他事务对数据进行修改。

如果有其他事务对数据进行修改，还未提交，那么另一个事务实用 FOR UPDATE查询会阻塞，等他其他事务提交，释放锁。

FOR UDPATE查询 一定要加上 条件，否则会锁住整张表，严重影响性能。

5.各种隔离级别下的加锁

  • 共享锁(Share Lock)：共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

  • 排他锁（eXclusive Lock）：排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

  • 我们通过update、delete等语句加上的锁都是行级别的锁。只有LOCK TABLE … READ和LOCK TABLE … WRITE才能申请表级别的锁。

  • 意向共享锁(IS锁):一个事务在获取（任何一行/或者全表）S锁之前，一定会先在所在的表上加IS锁

  • 意向排他锁(IX锁):一个事务在获取（任何一行/或者全表）X锁之前，一定会先在所在的表上加IX锁

  • 意向锁存在的目的? 假设事务T1，用X锁来锁住了表上的几条记录，那么此时表上存在IX锁，即意向排他锁。那么此时事务T2要进行LOCK TABLE … WRITE的表级别锁的请求，可以直接根据意向锁是否存在而判断是否有锁冲突。

隔离级别读操作写操作特点
未提交读(Read uncommitted)无锁行级共享锁事物1读，事物2可读可写；事物1写，事务2只可读(可读到事物1未提交的)
提交读(Read committed)行级共享锁（当读到时才加锁，一旦读完该行，立即释放该行级共享锁）行级排他锁事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放事物1写：事物2必须等到事物1提交了事物才可读，所以肯定是读到事物1提交后的数据事物1读两次：事物2在事物1两次读之间可以修改数据并提交。（因为事物1只在读的一瞬间会加共享锁，读完后立即释放锁）但事物1的两次读会读到不一样的结果，产生不可重复读。
可重复读(Repeatable reads)行级共享锁（读的一瞬间加锁，直到事物结束才释放锁）行级排他锁事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级排他锁，直到事务结束才释放事物1读：事物2可读不可写事物1写(更新)：事物2不可读不可写（可保证一个事物内的多次读取，同一条记录的字段是相同的，但是因为不是加的表锁，多次读之间其他事物虽然不能修改数据，但可以插入数据，引起多次读取返回的结果条数增多，造成幻读
可序列化(Serializable)表级共享锁 ，直到事务结束才释放表级排他锁 ，直到事务结束才释放可解决幻读

6.sql加锁分析

如下sql语句加锁分析：

select * from table where id = ?
select * from table where id < ?
select * from table where id = ? lock in share mode
select * from table where id < ? lock in share mode
select * from table where id = ? for update
select * from table where id < ? for update
快照读和当前读

在mysql中select分为快照读和当前读，执行下面的语句：

select * from table where id = ?;

执行的是快照读，读的是数据库记录的快照版本，是不加锁的。（这种说法在隔离级别为Serializable中不成立） 那么，执行

select * from table where id = ? lock in share mode;

会对读取记录加S锁 (共享锁)，执行

select * from table where id = ? for update

会对读取记录加X锁 (排他锁)，那么

加的是表锁还是行锁？
  • Read Uncommited(RU)：读未提交，一个事务可以读到另一个事务未提交的数据！
  • Read Committed (RC)：读已提交，一个事务可以读到另一个事务已提交的数据!
  • Repeatable Read (RR):可重复读，加入间隙锁，一定程度上避免了幻读的产生！另外就是记住从该级别才开始加入间隙锁(这句话记下来，后面有用到)!
  • Serializable：串行化，该级别下读写串行化，且所有的select语句后都自动加上lock in share mode，即使用了共享锁。因此在该隔离级别下，使用的是当前读，而不是快照读。
分析

下面来对开始的问题作出解答，假设有表如下，pId为主键索引

pId(int)name(varchar)num(int)
1aaa100
2bbb200
3bbb300
7ccc200

RC/RU（读已提交，读未提交）+条件列非索引（条件列不加索引）：

(1)select * from table where num = 200
不加任何锁，是快照读。
(2)select * from table where num > 200
不加任何锁，是快照读。
(3)select * from table where num = 200 lock in share mode
当num = 200，有两条记录。这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级S锁，采用当前读。
(4)select * from table where num > 200 lock in share mode
当num > 200，有一条记录。这条记录对应的pId=3，因此在pId=3的聚簇索引上加上行级S锁，采用当前读。
(5)select * from table where num = 200 for update
当num = 200，有两条记录。这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级X锁，采用当前读。
(6)select * from table where num > 200 for update
当num > 200，有一条记录。这条记录对应的pId=3，因此在pId=3的聚簇索引上加上行级X锁，采用当前读。

RC/RU+条件列是聚簇索引: pId是主键列，因此pId用的就是聚簇索引。此情况其实和RC/RU+条件列非索引情况是类似的:

(1)select * from table where pId = 2
不加任何锁，是快照读。
(2)select * from table where pId > 2
不加任何锁，是快照读。
(3)select * from table where pId = 2 lock in share mode
在pId=2的聚簇索引上，加S锁，为当前读。
(4)select * from table where pId > 2 lock in share mode
在pId=3，7的聚簇索引上，加S锁，为当前读。
(5)select * from table where pId = 2 for update
在pId=2的聚簇索引上，加X锁，为当前读。
(6)select * from table where pId > 2 for update
在pId=3，7的聚簇索引上，加X锁，为当前读。

为什么条件列加不加索引，加锁情况是一样的？

其实是不一样的。在RC/RU隔离级别中，MySQL Server做了优化。在条件列没有索引的情况下，尽管通过聚簇索引来扫描全表，进行全表加锁。但是，MySQL Server层会进行过滤并把不符合条件的锁当即释放掉，因此你看起来最终结果是一样的。但是RC/RU+条件列非索引比本例多了一个释放不符合条件的锁的过程！

RC/RU+条件列是非聚簇索引:

我们在num列上建上非唯一索引。此时有一棵聚簇索引(主键索引，pId)形成的B+索引树，其叶子节点为硬盘上的真实数据。以及另一棵非聚簇索引(非唯一索引，num)形成的B+索引树，其叶子节点依然为索引节点，保存了num列的字段值，和对应的聚簇索引。

1)select * from table where num = 200
不加任何锁，是快照读。
(2)select * from table where num > 200
不加任何锁，是快照读。
(3)select * from table where num = 200 lock in share mode
当num = 200，由于num列上有索引，因此先在 num = 200的两条索引记录上加行级S锁。接着，去聚簇索引树上查询，这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级S锁，采用当前读。
(4)select * from table where num > 200 lock in share mode
当num > 200，由于num列上有索引，因此先在符合条件的 num = 300的一条索引记录上加行级S锁。接着，去聚簇索引树上查询，这条记录对应的pId=3，因此在pId=3的聚簇索引上加行级S锁，采用当前读。
(5)select * from table where num = 200 for update
当num = 200，由于num列上有索引，因此先在 num = 200的两条索引记录上加行级X锁。接着，去聚簇索引树上查询，这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级X锁，采用当前读。
(6)select * from table where num > 200 for update
当num > 200，由于num列上有索引，因此先在符合条件的 num = 300的一条索引记录上加行级X锁。接着，去聚簇索引树上查询，这条记录对应的pId=3，因此在pId=3的聚簇索引上加行级X锁，采用当前读。

RR(可重复读)/Serializable（串行化）+条件列非索引:

RR级别需要多考虑的就是gap lock，他的加锁特征在于，无论你怎么查都是锁全表。如下所示 接下来分析开始

(1)select * from table where pId = 2
在RR级别下，不加任何锁，是快照读。
在Serializable级别下，是当前读，在pId=2的聚簇索引上加S锁，不存在gap lock。
(2)select * from table where pId > 2
在RR级别下，不加任何锁，是快照读。
在Serializable级别下，是当前读，在pId=3,7的聚簇索引上加S锁。在(2,3)(3,7)(7,+∞)加上gap lock
(3)select * from table where pId = 2 lock in share mode
是当前读，在pId=2的聚簇索引上加S锁，不存在gap lock。
(4)select * from table where pId > 2 lock in share mode
是当前读，在pId=3,7的聚簇索引上加S锁。在(2,3)(3,7)(7,+∞)加上gap lock
(5)select * from table where pId = 2 for update
是当前读，在pId=2的聚簇索引上加X锁。
(6)select * from table where pId > 2 for update
在pId=3,7的聚簇索引上加X锁。在(2,3)(3,7)(7,+∞)加上gap lock
(7)select * from table where pId = 6 [lock in share mode|for update]
注意了，pId=6是不存在的列，这种情况会在(3,7)上加gap lock。
(8)select * from table where pId > 18 [lock in share mode|for update]
注意了，pId>18，查询结果是空的。在这种情况下，是在(7,+∞)上加gap lock。

RR/Serializable+条件列是非聚簇索引:

这里非聚簇索引，需要区分是否为唯一索引。因为如果是非唯一索引，间隙锁的加锁方式是有区别的。
先说一下，唯一索引的情况。如果是唯一索引，情况和RR/Serializable+条件列是聚簇索引类似，唯一有区别的是:这个时候有两棵索引树，加锁是加在对应的非聚簇索引树和聚簇索引树上！大家可以自行推敲!
下面说一下，非聚簇索引是非唯一索引的情况，他和唯一索引的区别就是通过索引进行精确查询以后，不仅存在record lock，还存在gap lock。而通过唯一索引进行精确查询后，只存在record lock，不存在gap lock。老规矩在num列建立非唯一索引
(1)select * from table where num = 200
在RR级别下，不加任何锁，是快照读。
在Serializable级别下，是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加S锁，在(100,200)(200,300)加上gap lock。
(2)select * from table where num > 200
在RR级别下，不加任何锁，是快照读。
在Serializable级别下，是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加S锁。在(200,300)(300,+∞)加上gap lock
(3)select * from table where num = 200 lock in share mode
是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加S锁，在(100,200)(200,300)加上gap lock。
(4)select * from table where num > 200 lock in share mode
是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加S锁。在(200,300)(300,+∞)加上gap lock。
(5)select * from table where num = 200 for update
是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加X锁，在(100,200)(200,300)加上gap lock。
(6)select * from table where num > 200 for update
是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加X锁。在(200,300)(300,+∞)加上gap lock
(7)select * from table where num = 250 [lock in share mode|for update]
注意了，num=250是不存在的列，这种情况会在(200,300)上加gap lock。
(8)select * from table where num > 400 [lock in share mode|for update]
注意了，pId>400，查询结果是空的。在这种情况下，是在(400,+∞)上加gap lock。

关于作者

程序员，软件工程师，java, golang, rust, c, python,vue, Springboot, mybatis, mysql,elasticsearch, docker, maven, gcc, linux, ubuntu, centos, axum,llm, paddlepaddle, onlyoffice,minio,银河麒麟，中科方德，rpm