数据库--事务、行级锁(共享锁s、排它锁x)

参考：https://blog.csdn.net/2301_77032029/article/details/143276550
https://developer.aliyun.com/article/1436380

事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

CREATE TABLE `account` (
  `id` bigint NOT NULL AUTO_INCREMENT comment '主键ID',
  `name` varchar(255) DEFAULT NULL comment '姓名',
	money int DEFAULT NULL comment '余额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 comment '账户表';
INSERT INTO account(id,name,money) VALUES (null,'张三',2000),(null,'李四',2000);

-- 恢复数据
update account set money = 2000 WHERE name ='张三' or name = '李四';

-- 转账操作（张三给李四转账1000）
-- 查询张三账户余额
select * from account WHERE name = '张三';

-- 将张三账户余额-1000
update account set money = money - 1000 WHERE name ='张三';

-- 将李四账户余额+1000
update account set money = money + 1000 WHERE name ='李四';

设置为手动提交

select @@autocommit;
set @@autocommit =0;-- 设置为手动提交

当设置为手动提交后，执行了sql语句后，就需要进行手动提交：

commit;

-- 转账操作（张三给李四转账1000）
-- 查询张三账户余额
select * from account WHERE name = '张三';

-- 将张三账户余额-1000
update account set money = money - 1000 WHERE name ='张三';

-- 将李四账户余额+1000
update account set money = money + 1000 WHERE name ='李四';
-- 提交事务
commit;

回滚事务

-- 回滚事务
rollback;

开启事务

-- 方式二
-- 开启事务
begin;

-- 将张三账户余额-1000
update account set money = money - 1000 WHERE name ='张三';

程序执行报错 ...
-- 将李四账户余额+1000
update account set money = money + 1000 WHERE name ='李四';

-- 提交事务
commit;

-- 回滚事务
rollback;

-- 方式二
-- 开启事务
start transaction;

-- 将张三账户余额-1000
update account set money = money - 1000 WHERE name ='张三';

程序执行报错 ...
-- 将李四账户余额+1000
update account set money = money + 1000 WHERE name ='李四';

-- 提交事务
commit;

-- 回滚事务
rollback;

事务的四大特性

并发事务问题：同时有多个事务执行同一条数据

脏读、不可重复读、幻读

脏读

一个事务读取到另外一个事务还没有提交的数据

不可重复读

在一个事务内先后读取同一条记录，但两次读取的数据不同

幻读

一个事务按照条件查询数据时，没有对应得数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影

事务的隔离级别：解决并发事务问题

读未提交：是通过无锁机制实现的，事务可以读取其他事务尚未提交的数据
读已提交：是通过使用共享锁实现的，事务在读取数据时会使用共享锁（S锁），但在事务提交后会释放这些锁。因此，事务只能读取到其他事务已经提交的数据。
可重复读：事务在读取数据时会使用多版本并发控制（MVCC），确保事务在这个行期间看到的数据是一致的。MySQL使用快照读（Snapshot Read）来实现这一点。
参考：https://developer.aliyun.com/article/1436380
串行化：是通过共享锁+排它锁共同实现的，事务通过强制事务串行执行来避免并发问题。MySQL使用表级锁或行级锁来实现这一点，确保事务完全按照顺序执行
InnoDB 存储引擎默认使用行级锁。

MySQL 的行级锁与表级锁

在 MySQL 中，锁机制是保证并发控制和数据一致性的关键。根据锁的作用范围不同，主要分为行级锁和表级锁。

表级锁
定义：表级锁是 MySQL 中最基本的锁策略，也是最粗粒度的锁。当一个事务对某个表进行操作时，会锁定整个表，其他事务不能对该表进行任何修改操作。

优点：

实现简单，开销小。

资源消耗少，加锁快。

缺点：

锁粒度大，容易引发锁争用，导致并发性能下降。

适用场景：

适用于读多写少的场景，或者对数据一致性要求不高的场景。

示例：

MyISAM 存储引擎使用表级锁。

InnoDB 存储引擎在某些情况下也会使用表级锁，例如在执行 LOCK TABLES 语句时。

 -- MyISAM 存储引擎
 CREATE TABLE myisam_table (
     id INT PRIMARY KEY,
     name VARCHAR(50)
 ) ENGINE=MyISAM;
 ​
 -- 插入数据
 INSERT INTO myisam_table (id, name) VALUES (1, 'Alice'), (2, 'Bob');
 ​
 -- 事务1
 START TRANSACTION;
 UPDATE myisam_table SET name = 'Charlie' WHERE id = 1;
 ​
 -- 事务2
 START TRANSACTION;
 UPDATE myisam_table SET name = 'David' WHERE id = 2; -- 会被阻塞，直到事务1提交或回滚

行级锁
定义：行级锁是 MySQL 中最细粒度的锁，只锁定需要操作的行。行级锁可以最大限度地支持并发操作，但实现复杂，开销较大。

优点：

锁粒度小，能够支持高并发。

减少了锁冲突的概率，提高了系统的并发性能。

缺点：

实现复杂，开销较大。

可能会导致死锁问题。

适用场景：

适用于写多读少的场景，或者对数据一致性要求较高的场景。

示例：

InnoDB 存储引擎默认使用行级锁。

在 InnoDB 中，可以通过 SELECT … FOR UPDATE 和 SELECT … LOCK IN SHARE MODE 来显式地获取行级锁。

 -- InnoDB 存储引擎
 CREATE TABLE innodb_table (
     id INT PRIMARY KEY,
     name VARCHAR(50)
 ) ENGINE=InnoDB;
 ​
 -- 插入数据
 INSERT INTO innodb_table (id, name) VALUES (1, 'Alice'), (2, 'Bob');
 ​
 -- 事务1
 START TRANSACTION;
 SELECT * FROM innodb_table WHERE id = 1 FOR UPDATE;
 ​
 -- 事务2
 START TRANSACTION;
 SELECT * FROM innodb_table WHERE id = 2 FOR UPDATE; -- 不会被阻塞，因为锁定的是不同的行

## 记录锁和间隙锁

记录锁和间隙锁(next-key锁)是行级锁中区分的锁
记录锁和间隙锁(next-

key锁)是建立在基本锁(共享锁和排它锁)之上的大分类，是锁的位置的区分，在基础锁之上的细分的锁，是行级锁中的策略
参考：https://blog.csdn.net/weixin_48460141/article/details/124284443

在 InnoDB 中，可以通过 SELECT … FOR UPDATE 和 SELECT … LOCK IN SHARE MODE 来显式地获取行级锁。
共享锁：SELECT … LOCK IN SHARE MODE
排它锁： SELECT … FOR UPDATE

 -- InnoDB 存储引擎
 CREATE TABLE innodb_table (
     id INT PRIMARY KEY,
     name VARCHAR(50)
 ) ENGINE=InnoDB;
 ​
 -- 插入数据
 INSERT INTO innodb_table (id, name) VALUES (1, 'Alice'), (2, 'Bob');
 ​
 -- 事务1
 START TRANSACTION;
 SELECT * FROM innodb_table WHERE id = 1 FOR UPDATE;
 ​
 -- 事务2
 START TRANSACTION;
 SELECT * FROM innodb_table WHERE id = 2 FOR UPDATE; -- 不会被阻塞，因为锁定的是不同的行

死锁及其解决方法

定义：死锁是指两个或多个事务在等待对方释放锁，从而导致所有事务都无法继续执行的情况。

检测：

MySQL 会定期检测死锁，并选择一个事务进行回滚以解除死锁。

预防：
尽量减少事务的持有锁时间。
按照固定的顺序访问资源。
使用超时机制，避免长时间等待。

解决：
使用 SHOW ENGINE INNODB STATUS 查看当前的死锁信息。
分析日志，找出死锁的原因并优化事务逻辑。

=========================

mysql默认为可重复读事务隔离级别
读未提交、读已提交、可重复读、串行化

-- 查看事务隔离级别
select @@transaction_isolation;
-- 设置事务隔离级别
set[session|global] transaction isolation level{read uncommitted|read committed|repeatable read|serializable}

-- 查看事务隔离级别
select @@transaction_isolation;
-- 设置事务隔离级别
set session transaction isolation level read uncommitted;
set session transaction isolation level read repeatable read;

打开两个窗口进行多事务演示：

脏读演示

设置事务隔离级别为读未提交：才能出现脏读

set session transaction isolation level read uncommitted;

开启事务：

start transaction;

a事务在开启事务下查询数据：

select * from account;

b事务同时在开启事务下更新数据：

update account set money = money - 1000 where name = '张三';

a事务能在b事务没有提交事务情况下查询到了b事务未提交事务的更新数据

a事务能读取到b事务没有提交的更新数据就为脏读！
a事务为查询，b事务为更新！但这时都开启事务，但没有提交！

解决：

set session transaction isolation level read committed;

当设置为读已提交时，就不会出现脏读了：

不可重复读演示

设置事务隔离级别为读已提交：才能出现不可重复读

set session transaction isolation level read committed;

a事务在该事务开启情况下查询一条数据

select * from account;

b事务在该事务开启的情况下，进行更新该条数据后，并对该条数据进行提交：

a事务还在当前事务中，再次查询改数据，发现该数据变化了：

就形成了不可重复读

当事务隔离级别设置为可重复读，则能解决该问题：

set session transaction isolation level repeatable read;

可以看到，事务a在提交后，才能看见b事务对该条数据的修改！

幻读演示

事务隔离级别设置为可重复读

set session transaction isolation level repeatable read;

当a事务在该事务下查询一个还没有的数据时：

select * from account where id =3;

第一次没有查询到。
这时b事务开始进行插入操作，并提交：

 insert into account(id,name,money) values(3,'王五',2000)；

在a事务开启情况下查询是没有该数据的（因为是可重复读的隔离级别），但这时进行插入（相同数据）操作（该数据有唯一冲突，如主键）：

insert into account(id,name,money) values(3,'大刀王五',2000);

这时就包已经存在该数据了，这就是幻读

将事务隔离级别设置为：序列化就能解决了；

set session transaction isolation level serializable;

当a事务在操作该条数据时，b事务也开始操作该数据，但此时发现a事务在操作，b事务就会等待，只有a事务将该事务操作提交后，b事务才能继续操作

总结

并发事务问题：只有在多个事务下，一个事务中的数据不一致的问题
脏读、不可重复读都是查询数据不一致导致的，而幻读是由于查询+插入的业务导致的

• 脏读：为一个事务未提交情况下，先后查询同一条数据不一致，但这里是由于另一个事务也在修改该条数据，并且另外一个事务也还没有提交的情况下出现的
• 不可重复读：和脏读相似，也是一个事务未提交情况下，先后查询同一条数据不一致，，但是时在另外一个事务修改同一条数据，并且另外一个事务要提交该事务情况下才会出现！
• 幻读：时一个事务a开启情况下，查询一条数据，该条数据为null，于是准备插入，但此时另一个事务b也在插入该条数据，并提交了该事务，由于在可重复读的隔离级别下，a事务在本事务未提交情况下，是不会查询到b事务已提交的数据的，所以a事务继续插入该数据，但插入时，就会由于有唯一约束的数据报错，就出现了幻读

设置隔离级别：
读未提交：

set session transaction isolation level read uncommitted;

读已提交：

set session transaction isolation level read committed;

可重复读：

set session transaction isolation level repeatable read;

序列化：

set session transaction isolation level serializable;