MySQL事务

MySQL事务

第一章 事务操作

事务概述

• 事务指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败.
• 事务作用：保证在一个事务中多次SQL操作要么全都成功,要么全都失败.

1.1 mysql事务操作

sql语句	描述
start transaction;begin;	开启事务
commit	提交事务
rollback	回滚事务

• 准备数据

# 创建一个表：账户表.
create database webdb;
# 使用数据库
use webdb;
# 创建账号表
create table account(
	id int primary key auto_increment,
	name varchar(20),
	money double
);
# 初始化数据
insert into account values (null,'jack',10000);
insert into account values (null,'rose',10000);
insert into account values (null,'tom',10000);

• 操作
  • MYSQL中可以有两种方式进行事务的管理：
    • 自动提交：MySql默认自动提交。及执行一条sql语句提交一次事务。
    • 手动提交：先开启，再提交
• 方式1：手动提交

start transaction;
update account set money=money-1000 where name='jack';
update account set money=money+1000 where name='rose';
commit;
#或者
rollback;

• 方式2：自动提交，通过修改mysql全局变量“autocommit”进行控制

show variables like '%commit%';
* 设置自动提交的参数为OFF:
set autocommit = 0;  -- 0:OFF  1:ON

1.2 jdbc事务操作

Connection 对象的方法名	描述
conn.setAutoCommit(false)	开启事务
conn.commit()	提交事务
conn.rollback()	回滚事务

代码演示

//事务模板代码
public void demo01() throws SQLException{
	// 获得连接
	Connection conn = null;
	
	try {
		//#1 开始事务
		conn.setAutoCommit(false);
		
		//.... 加钱 ,减钱
		
		//#2 提交事务
		conn.commit();
	} catch (Exception e) {
		//#3 回滚事务
		conn.rollback();
	} finally{
		// 释放资源
		conn.close();
	}
}

1.3案例：JDBC事务分层（dao、service）传递Connection

分析

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UcSVtbkW-1682754340021)(img/03.png)]

• 开发中，常使用分层思想

  • 不同的层次结构分配不同的解决过程，各个层次间组成严密的封闭系统

• 不同层级结构彼此平等

• 分层的目的是：

  • 解耦
  • 可维护性
  • 可扩展性
  • 可重用性

• 不同层次，使用不同的包表示

  • com.doit 公司域名倒写
  • com.doit.dao dao层
  • com.doit.service service层
  • com.doit.domain javabean
  • com.doit.utils 工具

第二章 事务总结

2.1 事务特性：ACID

• 原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

• 一致性（Consistency）事务前后数据的完整性必须保持一致。

• 隔离性（Isolation）事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。

• 持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

2.2 并发访问问题

如果不考虑隔离性，事务存在3中并发访问问题。

1. 脏读：一个事务读到了另一个事务未提交的数据.

2. 不可重复读：一个事务读到了另一个事务已经提交(update)的数据。引发另一个事务，在事务中的多次查询结果不一致。

3. 虚读 /幻读：一个事务读到了另一个事务已经提交(insert)的数据。导致另一个事务，在事务中多次查询的结果不一致。

2.3 隔离级别：解决问题

• 数据库规范规定了4种隔离级别，分别用于描述两个事务并发的所有情况。

1. read uncommitted 读未提交，一个事务读到另一个事务没有提交的数据。

   a)存在：3个问题（脏读、不可重复读、虚读）。

   b)解决：0个问题

2. read committed 读已提交，一个事务读到另一个事务已经提交的数据。

   a)存在：2个问题（不可重复读、虚读）。

   b)解决：1个问题（脏读）

3. repeatable read:可重复读，在一个事务中读到的数据始终保持一致，无论另一个事务是否提交。

   a)存在：1个问题（虚读）。

   b)解决：2个问题（脏读、不可重复读）

4. serializable 串行化，同时只能执行一个事务，相当于事务中的单线程。

   a)存在：0个问题。

   b)解决：3个问题（脏读、不可重复读、虚读）

• 安全和性能对比

  • 安全性：serializable > repeatable read > read committed > read uncommitted
  • 性能 ： serializable < repeatable read < read committed < read uncommitted

• 常见数据库的默认隔离级别：

  • MySql：repeatable read
  • Oracle：read committed

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q1mu0Enz-1682754340022)(img/01.jpg)]

2.4 演示

• 隔离级别演示参考

• 查询数据库的隔离级别

show variables like '%isolation%';
或
select @@tx_isolation;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8eAW9a4K-1682754340022)(img/01.png)]

• 设置数据库的隔离级别

  • set session transactionisolation level 级别字符串
  • 级别字符串：readuncommitted、read committed、repeatable read、serializable
  • 例如：set session transaction isolation level read uncommitted;

• 读未提交：readuncommitted

  • A窗口设置隔离级别
    • AB同时开始事务
    • A 查询
    • B 更新，但不提交
    • A 再查询？-- 查询到了未提交的数据
    • B 回滚
    • A 再查询？-- 查询到事务开始前数据

• 读已提交：read committed

  • A窗口设置隔离级别
    • AB同时开启事务
    • A查询
    • B更新、但不提交
    • A再查询？–数据不变，解决问题【脏读】
    • B提交
    • A再查询？–数据改变，存在问题【不可重复读】

• 可重复读：repeatable read

  • A窗口设置隔离级别
    • AB 同时开启事务
    • A查询
    • B更新， 但不提交
    • A再查询？–数据不变，解决问题【脏读】
    • B提交
    • A再查询？–数据不变，解决问题【不可重复读】
    • A提交或回滚
    • A再查询？–数据改变，另一个事务

• 串行化：serializable

  • A窗口设置隔离级别
  • AB同时开启事务
  • A查询
    • B更新？–等待(如果A没有进一步操作，B将等待超时)
    • A回滚
    • B 窗口？–等待结束，可以进行操作

第三章 锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资
源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发
访问性能的一个重要因素。打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？ 这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

锁分类
从对数据库操作的类型分，分为读锁和写锁

• 读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
• 写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分，分为表锁和行锁

表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
一般用在整表数据迁移的场景。

CREATE TABLE mylock(
	id INT  PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) 
)ENGINE=MYISAM;
INSERT INTO mylock(NAME) VALUES
('a'),
('b'),
('c'),
('d'),
('e');

• 手动增加表锁

lock table 表名称 read(write),表名称2 read(write);

• 查看表上加过的锁

show open tables;

• 删除表锁

unlock tables;

案例分析(加读锁）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aWrGhuD0-1682754340023)(img/02.jpg)]

当前session和其他session都可以读该表,当前session在有锁时不能读其他表

当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FSiRyE2N-1682754340023)(img/03.jpg)]

当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞

结论

1、对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当
读锁释放后,才会执行其它进程的写操作。
2、对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进
程的读写操作

行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最
高。

InnoDB与MYISAM的最大不同有两点：

• InnoDB支持事务（TRANSACTION）
• InnoDB支持行级锁

CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(100),
	balance INT 
);

INSERT INTO `account` (`name`,`balance`) VALUES 
('lilei', '450'),
('hanmei', '16000'),
('lucy', '2400');

一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞

InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行
锁。

索引失效导致行锁变表锁

CREATE TABLE test_innodb(
	id INT PRIMARY KEY AUTO_INCREMENT,
	b VARCHAR(100),
    KEY(b)
);
INSERT INTO test_innodb(b) VALUES
('100'),
('200'),
('500'),
('1000'),
('600'),
('700'),
('800');

update test_innodb set  id = 10 where b = '1000';

update test_innodb set  id = 1000 where b = '800';

锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁
session1 执行：update test_innodb set id = 10 where b = 1000;
session2 对该表任一行操作都会阻塞住

间隙锁(Gap Lock)

间隙锁，锁的就是两个值之间的空隙。

INSERT INTO account (id , NAME ,balance) VALUES
(10,'jack','1000'),
(20,'john','2000');

假设account表中数据如下

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OgCGIzfK-1682754340024)(img/07.png)]

那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，
在Session1下面执行 update account set name = ‘aaa’ where id > 8 and id <18;，则其他Session没
法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在
(3,20]区间都无法修改数据，注意最后那个20也是包含在内的。
间隙锁是在可重复读隔离级别下才会生效。

临键锁(Next-key Locks)
Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。

第四章 视图

视图是一种虚拟表 ，本身是不具有数据 的，占用很少的内存空间，它是 SQL 中的一个重要概念.视图建立在已有表的基础上, 视图赖以建立的这些表称为基表.视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3RbZFi4g-1682754340024)(img/08.png)]

4.1 创建视图

完成语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句 [WITH [CASCADED|LOCAL] CHECK OPTION]

创建视图语句 create view 可选项 ALGORITHM 子句表示视图处理算法
共三个参数： MERGE | TEMPTABLE | UNDEFINED
MERGE
引用视图和视图定义的语句的文本被合并，使视图定义的部分取代语句的相应部分。

TEMPTABLE
视图中的结果被检索到一个临时表中，然后用来执行语句。

UNDEFINED
MySQL选择使用哪种算法。如果可能的话，它更倾向于MERGE而不是TEMPTABLE，因为MERGE通常更有效率，而且如果使用临时表，视图无法更新。

[WITH [CASCADED|LOCAL] CHECK OPTION] 视图的检查规则 默认CASCADED

简化版本

CREATE VIEW 视图名称
AS 查询语句

准备表数据

create table student (
  id int(3) PRIMARY KEY ,
  name varchar(20) not null,
  sex varchar(4),
  birth int(4),
  department varchar(20),
  address varchar(50)
);

-- 向student表插入记录的INSERT语句如下：
insert into student values(901,'张老大','男',1985,'计算机系','北京市海淀区');
insert into student values(902,'张老二','男',1986,'中文系','北京市昌平区');
insert into student values(903,'张三','女',1990,'中文系','湖南省永州市');
insert into student values(904,'李四','男',1990,'英语系','辽宁省阜新市');
insert into student values(905,'王五','女',1991,'英语系','福建省厦门市');
insert into student values(906,'王六','男',1988,'计算机系','湖南省衡阳市');

create table score(
   id int(3)   PRIMARY KEY ,
   stu_id int(3)  not null,
   c_name varchar(20) ,
   grade int(3)
)

insert into score values(1,901,'计算机',98);
insert into score values(2,901,'英语',80);
insert into score values(3,902,'计算机',65);
insert into score values(4,902,'中文',88);
insert into score values(5,903,'中文',95);
insert into score values(6,904,'计算机',70);
insert into score values(7,904,'英语',92);
insert into score values(8,905,'英语',94);
insert into score values(9,906,'计算机',90);
insert into score values(10,906,'英语',85);

创建单表视图

CREATE VIEW v_student AS
SELECT
id,NAME,sex
FROM student t
WHERE id = 901;

SELECT * FROM v_student;

-- 针对别名的处理，可以在创建视图的子查询中指定对应的别名
CREATE VIEW v_student1
AS
SELECT id stu_id,name stu_name,sex gender
FROM student ;
-- 也可以在创建视图的视图名称后添加对应的别名字段
CREATE VIEW v_student2(stu_id,stu_name,gender)
AS
SELECT id ,name ,sex
FROM student ;

实际上就是我们在 SQL 查询语句的基础上封装了视图 VIEW，这样就会基于 SQL 语句的结果集形成一张虚拟表。

在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字段列表一致。如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同。

创建多表视图

CREATE VIEW v_student_score
AS
SELECT t1.id,t1.name,t2.c_name,t2.grade
FROM student t1 left join score t2
on t1.id = t2.stu_id;
-- 查询视图
SELECT * FROM v_student_score;

基于视图创建视图

CREATE VIEW v_student_score1 AS
SELECT * FROM
v_student_score
WHERE grade > 80;


select * from v_student_score1;

4.2 视图其他操作

查看视图

-- 查看数据库的表对象、视图对象
SHOW TABLES;
-- 查看视图结构
DESC / DESCRIBE 视图名称;
--  查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G
-- 查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;

更新视图数据

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然

create view vv_student as select id,name,sex from student;

insert  into vv_student values(909,'liuyan','女');
select * from student;

select * from vv_student;

注意:

要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。另外当视图定义出现如下情况时，视图不支持更新操作：

1. 在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
2. 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
3. 在定义视图的SELECT语句中使用了 JOIN联合查询 ，视图将不支持INSERT和DELETE操作；
4. 在定义视图的SELECT语句后的字段列表中使用了 数学表达式 或 子查询 ，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；
5. 在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING、 UNION 等，视图将不支持INSERT、UPDATE、DELETE；
6. 在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、DELETE；
7. 视图定义基于一个不可更新视图 ；
8. 常量视图。

create  view  v_score as select stu_id,sum(grade) sum_grade  from score group by  stu_id;

select * from v_score;
update  v_score set sum_grade =  200 where stu_id=901;

总的来说，视图作为虚拟表 ，主要用于方便查询 ，不建议更新视图的数据。对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。

修改视图结构

-- 使用CREATE OR REPLACE VIEW 子句修改视图
create OR REPLACE view  v_score as select stu_id,avg(grade) avg_grade  from score group by  stu_id;

-- ALTER VIEW 视图名称 AS查询语句
alter view  v_score as select stu_id,sum(grade) sum_grade  from score group by  stu_id;

删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。

DROP VIEW IF EXISTS 视图名称;
DROP VIEW IF EXISTS 视图名称1,视图名称2,视图名称3,...;

数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。

4.3 视图总结

优点

视图优点	解释说明
操作简单	将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简化了开发人员对数据库的操作。
减少数据冗余	视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。
数据安全	MySQL将用户对数据的 访问限制 在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有隔离性 。视图相当于在用户和实际的数据表之间加了一层虚拟表
灵活多变	当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。
简化复杂查询逻辑	数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。

缺点

如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂， 可读性不好 ，容易变成系统的潜在隐患。因为创建视图的 SQL 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。实际项目中，如果视图过多，会导致数据库维护成本的问题。

所以，在创建视图的时候，你要结合实际项目需求，综合考虑视图的优点和不足，这样才能正确使

用视图，使系统整体达到最优。