简介
事务适用于保证数据的一致性,由一组相关的insert、delete、update(增删改)语句组成,该组的增删改语句要么全都成功,要么全部失败。
事务和锁
当执行事务操作时(增删改),mysql会在表上加锁,防止其他用户修改表中数据。
mysql数据库控制台事务的几个重要操作
1)start transaction --开始一个事务
2)savepoint 保存点名 --设置保存点
3)rollback to 保存点名 --回退事务
4)rollback --回退全部事务
5)commit --提交事务,所有操作生效,不能回退
#事务操作流程
-- 1.创建测试表
CREATE TABLE t9(
id INT,
`name` VARCHAR(32));
-- 2.开启事务
START TRANSACTION;
-- 3.设置保存点
SAVEPOINT a;
-- 4.执行dml操作
INSERT INTO t9 VALUES (100, 'tom');
SELECT * FROM t9;
-- 5.设置保存点
SAVEPOINT b;
-- 6.执行dml操作
INSERT INTO t9 VALUES (200, 'lucy');
SELECT * FROM t9;
-- 7.回退到b
ROLLBACK TO b;
SELECT * FROM t9;
-- 8.回退到a
ROLLBACK TO a;
SELECT * FROM t9;
-- 提交事务
COMMIT;
回退事务
保存点(savepoint)是实务中的点,用于取消部分事务,当结束事务(commit)时会自动的删除该事务所定义的所有保存点。当执行回退事务时,通过指定保存点可以回退到指定的点。
提交事务
使用commit语句可以提交事务,当执行了commit语句后,会确认事务的变化、结束事务、删除保存点、释放锁,数据生效。当使用commit语句结束事务后,其他会话【其他链接(例如多个用户连接,一个用户对数据库进行操作后没有提交,另一个用户能看到那些操作的内容,和隔离级别相关)】将可以查看到事务变化后的新数据。
事务细节
1)如果不开始事务,默认情况下增删改操作是自动提交的,不能回滚;
2)如果开始一个事务,没有创建保存点,可以执行rollback语句,默认退回到事务开始的状态;
3)可以在还没有提交的事务中创建多个保存点;
4)可以在事务没有提交前,选择回退到指定的个保存点(回退到更靠前的保存点后,不能再向指定的保存点之后的保存点回退);
5)InnoDB存储引擎支持事务,MyISAM不支持;
6)开始一个事务star transaction或者set autocommmit = off;
-- 事务细节
INSERT INTO t9 VALUES (300,'jack'); -- 执行后自动提交,不能回滚
SELECT * FROM t9;
ROLLBACK; -- 回滚后没有改变
START TRANSACTION; -- 回退后到这个位置
INSERT INTO t9 VALUES (400,'lucy');
INSERT INTO t9 VALUES (400,'smith');
SELECT * FROM t9;
ROLLBACK; -- 回退后,到了事务开始的时候
COMMIT;
-- 开始事务
SET autocommit = off;
MySQL事务隔离级别
在不同的事务中,根据隔离的强度不同,每一个事务看到的表中的数据不同。
1)多个连接开启各自事务,在操作数据库中的数据时,数据库系统要负责隔离操作,以保证各个连接在获取数据时的准确性;
2.如果不考虑隔离性可能会发生如下问题:①脏读;②不可重复读;③幻读;
★脏读(dirty read):当一个事务读取另一个事务尚未提交的修改时,产生脏读(A事务在对表进行数据操作,但是没有commit(提交);而B事务在对表数据进行操作时,操作的数据受到A事务修改的影响,发生脏读);
★不可重复读(nonrepeatable read):同一查询在同一事务中多次进行,由于其他提交事务所做的修改或删除,每次返回不同的结果集,此时发生不可重复读(A事务在对表进行数据操作,操作完成后commit(提交));而B事务在对表数据进行操作时(B事务操作时间长度包括A事务的操作),每次进行同一查询的数据内容不同,受到A事务操作的影响,发生不可重复读);
★幻读(phantom read):同一查询在同一事务中多次进行,由于其他提交事务所做的插入操作,每次返回不同的结果集,此时发生幻读。
MySQL隔离级别定义了事务与事务之间的隔离程度
MySQL隔离级别(4中) | 脏读 | 不可重复读 | 幻读 | 加锁读 |
读未提交(Read uncommitted) | 可能出现 | 可能出现 | 可能出现 | 不加锁 |
读已提交(Read committed) | 不可能出现 | 可能出现 | 可能出现 | 不加锁 |
可重复读(Repeatable read) | 不可能出现 | 不可能出现 | 不可能出现 | 不加锁 |
可串行化(Serializable) | 不可能出现 | 不可能出现 | 不可能出现 | 加锁 |
加锁:如果发现一个表正在被A事务操作并且没有commit(提交),那么B事务就不能够再操作这个表(查询数据时会进入等待),当A事务commit(提交)后,B事务才能对表中数据进行操作。
-- 1.开启多个控制台【A控制台进行A事务操作和B控制台进行B事务操作】
-- 2.查看A控制台当前mysql的隔离级别
SELECT @@tx_isolation;
/*
+------------------------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+------------------------------+
*/
-- 3.把B控制台的隔离级别设置为Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @@tx_isolation;
/*
+--------------------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+--------------------------------+
*/
-- 4.在A控制台创建表
CREATE TABLE `account`(
id INT,
`name` VARCHAR(32),
money INT);
-- 5.A控制台和B控制台先后启动事务
START TRANSACTION;
-- 6.A控制台依次进行添加数据、修改数据、删除数据的操作
-- B控制台多次查询表中数据,在A控制台进行操作的过程中每次查询都不相同
-- B控制台启动的B事务对表进行数据处理时受到A控制台A事务操作的影响;
-- 7.两个控制台提交事务后,依次改变B控制台的隔离级别到read committed、
-- repeatable read、serializable,进行重复实验,B控制台的事务操作逐渐不受
-- A控制台事务操作的影响。
-- 隔离级别read committed的控制台所进行的事务操作会发生不可重复读和幻读;
-- repeatable read 和 serializable不会发生脏读、不可重复读、幻读,前者不加锁,后者加锁
隔离级别设定的相关指令
1)查看当前会话隔离级别
select @@tx_isolation;
2)查看系统当前隔离级别
select @@global.tx_isolation;
3)设置当前会话隔离级别
set session transaction isolation level [read uncommitted [,read committed...]]
-- 隔离级别分为4种:
-- ①读未提交 read uncommitted
-- ②读已提交 read committed
-- ③可重复读 repeatable read
-- ④串行化 serializable
4)设置系统当前隔离级别
set global transaction isolation level [read uncommitted [,read committed...]]
-- 隔离级别分为4种:
-- ①读未提交 read uncommitted
-- ②读已提交 read committed
-- ③可重复读 repeatable read
-- ④串行化 serializable
5)mysql默认的事务隔离级别是repeatable read,一般情况下没有特殊要求,没有必要修改(该级别可以满足大部分项目需求);
6)隔离级别的全局修改,可以修改默认的隔离级别,修改my.ini配置文件,在[mysqld]栏目下编写:transaction-isolation = REPEATABLE-READ
可选隔离参数有:READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。
事务的ACID特性
1)原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生;
2)一致性(Consistency)
事务必须使用数据库从一个一致性状态变换到另外一个一致性状态(事务一旦提交之后,会进行一个整体性改变,开启一个新的事务);
3)隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离;
4)持久性(Durability)
持久性是指一个事务一旦被提交,他对数据库中的数据改变是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。
表类型和存储引擎
基本介绍
1)MySQL的表类型由存储引擎(Storage Engines)决定,主要包括MyISAM、innoDB、Memory等。
2)MySQL数据表主要支持六种类型,分别是:
①CSV:CSV存储引擎;
②Memory:基于散列,存储在内存中,对临时表有用;
③ARCHIVE:归档存储引擎;
④MRG_MYISAM:相同MyISAM表的集合;
⑤MYISAM:MyISAM存储引擎;
⑥InnoDB:支持事务、行级锁定和外键。
3)这六种又分为两类,一种是“事务安全型”(transaction-safe),比如:InnoDB;其余都属于第二类,称为“非事务安全型”(non-transaction-safe)[myisam 和 memory]。
主要的存储引擎/表类型特点
特点 | MyISAM | InnoDB | Memory | Archive |
批量插入的速度 | 高 | 低 | 高 | 非常高 |
事务安全 | 支持 | |||
全文索引 | 支持 | |||
锁机制 | 表锁 | 行锁 | 表锁 | 行锁 |
存储限制 | 没有 | 64TB | 有 | 没有 |
B树索引 | 支持 | 支持 | 支持 | |
哈希索引 | 支持 | 支持 | ||
集群索引 | 支持 | |||
数据缓存 | 支持 | 支持 | ||
索引缓存 | 支持 | 支持 | 支持 | |
数据可压缩 | 支持 | 支持 | ||
空间使用 | 低 | 高 | N/A | 非常低 |
内存使用 | 低 | 高 | 中等 | 低 |
支持外键 | 支持 |
1)MyISAM不支持事务、也不支持外键,但其访问速度快,对事务完整性没有要求;
2)InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引;
3)MEMORY存储引擎使用存在内存中的内容来创建表,每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常快,因为它的数据是放在内存中的,并且默认使用HASH索引。但是一旦服务器关闭,表中的数据就会丢失掉,表的结构还在。
#表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES;
-- InnoDB存储引擎
-- 1.支持事务;2.支持外键;3.支持行级锁;
-- myisam存储引擎
CREATE TABLE t16(
id INT,
`name` VARCHAR(32)) ENGINE MYISAM;
-- 1. 添加速度快;2.不支持外键;3.支持表级锁。
START TRANSACTION;
SAVEPOINT a;
INSERT INTO t16 VALUES(1,'tom');
SELECT * FROM t16;
ROLLBACK TO a; -- 回滚操作成功,但是出现警告,数据没有回滚回去(不支持事务)
SELECT * FROM t16;
COMMIT;
-- memory存储引擎
-- 1.数据存储在内存中(关闭MySQL服务,数据丢失,但是表结构还在);
-- 2.执行速度很快(没有IO读写,所以执行速度快);3.默认支持索引(HASH表)。
CREATE TABLE t17(
id INT,
`name` VARCHAR(32)) ENGINE MEMORY;
INSERT INTO t17 VALUES(1,'tom'), (2,'jack'),(3,'lucy');
SELECT * FROM t17;
-- 重启mysql后再次查询表,表结构还在数据消失,例如适用于用户状态
DESC t17;
SELECT * FROM t17;
-- 指令修改存储引擎
ALTER TABLE t17 ENGINE = INNODB;
如何选择表的存储引擎
1)如果应用不需要事务,处理的只是基本的CRUD操作,那么就选择MyISAM存储引擎,速度快;
2)如果需要事务,选择InnoDB;
3)Memory存储引擎是将数据存储在内存中,由于没有磁盘IO的等待,速度极快。但由于内存存储引擎,所做的任何修改在服务器重启后消失(例如:用户的在线状态)。
修改存储引擎
ALTER TABLE table_name ENGINE = StorageEngine_name;