MYSQL数据库之事务

一、事务的ACID特性

  • 原子性（ Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。

  • 一致性（ Consistency）：数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。例如对银行转帐事务，不管事务成功还是失败，应该保证事务结束后ACCOUNTS表中Tom和Jack的存款总和不变。

  •  隔离性（ Isolation）：多个事务并发访问时，事务之间是隔离的。

  • 持久性（ Durability）：在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚

二、事务的用法

步骤1，开启事务(start transaction 或 begin)

步骤2，执行sql操作(普通sql操作)

步骤3，提交/回滚(commit/rollback)

注意:建表的时候,选择 Innodb引擎 才支持事务

默认情况下，MySQL是自动提交事务，每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。如果某一组操作需要在一个事务中，那么需要使用start transaction，一旦rollback或commit就结束当次事务，之后的操作又自动提交。

如果需要在当前会话的整个过程中都取消自动提交事务，进行手动提交事务，就需要设置set autocommit = false;或set autocommit = 0;那样的话每一句SQL都需要手动commit提交才会真正生效。rollback或commit之前的所有操作都视为一个事务，之后的操作视为另一个事务，还需要手动提交或回滚。

和Oracle一样，DDL语句是不能回滚的，并且部分的DDL语句会造成隐式的提交，因此最好事务中不要涉及DDL语句。

实操：

准备测试表 
-- 准备测试表
DROP TABLE IF EXISTS t_stu_detail;
CREATE TABLE t_stu_detail (
	id INT PRIMARY KEY AUTO_INCREMENT,
	id_card CHAR(18),
	birthday CHAR(10),
	phone CHAR(11),
	email VARCHAR(25),
	address VARCHAR(100)
);
INSERT INTO t_stu_detail VALUES
(1, '123456789012345678', '1990-01-21', '12345678901', 'a@163.com', '北七家');
INSERT INTO t_stu_detail VALUES
(2, '123456789012345677', '1990-02-21', '12345678902', 'b@163.com', '北七家');

SELECT * FROM t_stu_detail;
事务测试 
#开启手动处理事务模式
set autocommit = false;
#开始事务（推荐）
start transaction;
#查看当前表的数据
select * from t_stu_detail;
#删除整张表的数据
delete from t_stu_detail;
#查询该表数据，发现显示删除后的结果
select * from t_stu_detail;
#回滚
rollback
#查看当前表的数据，发现又回来了
select * from t_stu_detail;
#删除整张表的数据
delete from t_stu_detail;
#提交事务
commit;
#查看当前表的数据，发现真删除了
select * from t_stu_detail;

#插入一条记录
INSERT INTO t_stu_detail VALUES
(1, '123456789012345678', '1990-01-21', '12345678901', 'a@163.com', '北七家');
#保存还原点1
savepoint point1;
#插入一条记录
INSERT INTO t_stu_detail VALUES
(2, '123456789012345677', '1990-02-21', '12345678902', 'b@163.com', '北七家');
#保存还原点2
savepoint point2;
#查看当前效果
select * from t_stu_detail;
#回滚到某个还原点
rollback to point1;
#提交事务
commit;

#清空表
truncate t_stu_detail;
#回滚，对于truncate无法回滚
rollback;

#修改表结构
alter table t_stu_detail add description varchar(50);
#回滚，对于修改表结构的语句无法回滚
rollback;

三、数据库的隔离级别

1、并发问题有哪些？

对于同时运行的多个事务（多线程并发）, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题: （问题的本质就是线程安全问题，共享数据的问题）

脏读: 事务A读取了已经被事务B更新但还没有被提交的字段，之后若事务B回滚, 事务A读取的内容就是临时且无效的。

不可重复读: 事务A读取了一个字段, 然后事务B更新并提交了该字段，之后事务A再次读取同一个字段, 值就不同了。

幻读: 当事务A按照某个条件多次查询相同范围的数据时，由于事务B的插入或删除操作，导致后续查询时发现结果集大小发生变化。

2、隔离级别的介绍

数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

  • Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE. Oracle 默认的事务隔离级别为: READ COMMITED
  • Mysql 支持 4 中事务隔离级别. MySQL 默认的事务隔离级别为: REPEATABLE-READ

每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个变量 @@tx_isolation, 表示当前的事务隔离级别.

查看当前的隔离级别select @@tx_isolation;

查看全局的隔离级别select @@global.tx_isolation;

设置当前 mySQL 连接的隔离级别set tx_isolation ='repeatable-read';

设置数据库系统的全局的隔离级别 set global tx_isolation ='read-committed';


隔离级别 描述
READ-UNCOMMITTED 允许事务读取其他事务未提交的数据，脏读、不可重复读、幻读的问题都会出现
READ-COMMITTED 只允许事务读取其他事务已经提交的数据，可以避免脏读，但是不可重复读、幻读的问题仍然会出现
REPEATABLE-READ

确保事务可以多次从一个字段中读取相同的值，好比在事务开启时对现有的数据进行了拍照，其他事务对数据的修改 ，不管事务是否提交，我这里读取的是拍照下来的数据，可以避免脏读和不可重复读， 但幻读的问题仍然存在。

注意: INNODB 使用了MVCC,即多版本并发控制技术解决了幻读 。真正的像拍照一样，其他事务新插入或删除的记录也看不出来.
SERIALIZABLE 确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止 其他事务对该表执行插入、更新、删除操作，所有并发问题都可以避 免，但是性能十分低下。

四、示例演示

0、演示准备

>使用test数据库，建一个 temp 表

USE test;
DROP TABLE IF EXISTS temp;
CREATE TABLE temp (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME CHAR(18)
);
INSERT INTO temp VALUES
(NULL, 'zhangsan'),
(NULL, 'lisi'),
(NULL, 'wangwu');
SELECT * FROM temp;

 >>分别打开两个DOS窗口，连接mysql：mysql -uroot -proot ，修改相关配置

USE test;
-- 设置手动提交事务
SET autocommit = FALSE;
-- 修改隔离级别为‘读未提交’
SET tx_isolation ='READ-UNCOMMITTED';
-- 查看隔离当前会话隔离级别
select @@tx_isolation;

1、脏读演示

窗口1执行第一次查询【窗口1提不提交事务都不会影响脏读的演示】：

START TRANSACTION;SELECT * FROM temp;

窗口2执行【未提交】：

START TRANSACTION;UPDATE temp SET NAME = 'rose' WHERE id = 3;

窗口1执行第二次查询【数据发生改变】：

SELECT * FROM temp;


窗口2执行【回滚】：

ROLLBACK;

窗口1执行第三次查询【数据恢复到第一次查询的结果了】：

结论：当前事务可以查看到其他事务已修改但未提交的数据，当时其他事务回滚操作后，已被读取的数据就是临时无效的。

2、不可重复读演示【略】

3、幻读演示【略】

五、数据库锁粒度

1、行级锁

InnoDB的前三个隔离级别是行级锁，另外，InnoDB表的行锁也不是绝对的。

InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表， 例如update table set num=1 where name like "%aaa%"

 

2、表级锁

InnoDB事务隔离级别是序列化，将会发生整张表的锁

InnoDB引擎既支持行级锁又支持表级锁；而MyISAM引擎只支持表级锁

就是说当对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。

posted @ 2022-11-02 16:17  danielzzz  阅读(82)  评论(0)  编辑  收藏  举报