【MySQL】事务

/*转账可以分为两部分来完成：转入和转出。
只有这两个部分都完成才认为转账成功。

A账户给B账户转账100元：
A账户减少100元，
B账户增加100元。

*/

# A 账户减少100元
/*mysql> UPDATE account SET money = money - 100 WHERE name = 'A';*/
# B 账户增加100元
/*mysql> UPDATE account SET money = money + 100 WHERE name = 'B';*/

/*如果其中任意一条语句出现异常没有执行，则会导致两个账户的金额不同步，造成错误。*/

/*为了防止这种情况发生，就需要使用MySQL中的事务（Transaction）。
事务是针对数据库的一组操作，它可以由一条或多条SQL语句组成。
*/

/*
事务的概念：
指作为单个逻辑工作单元执行的一系列操作，  
要么完全地执行，要么完全地不执行。
事务的原理：
事务开启后，所有的操作都会保存到事务日志中，
事务日志只有得到commit命令后才会同步到数据表，
其他任何情况都会清空（rollback、断电、断开连接）。
*/

/*1、原子性（Atomicity）
1)操作事务时，要么全部执行成功，要么全部失败。
2)只要其中一个指令执行失败，所有的指令都执行失败，
3)数据进行回滚，回到执行指令前的数据状态。
*/
/*2、一致性（Consistency）
1)事务的执行使数据从一个状态转换为另一个状态，但是对于整个数据的完整性保持稳定。
2)MySQL中的一致性主要由日志机制实现的，通过日志记录数据库的所有变化，为事务恢复提供了跟踪记录。
*/

/*
3、隔离性（Isolation）
1)是指当一个事务在执行时，不会受到其他事务的影响。
2)保证了未完成事务的所有操作与数据库系统的隔离，
直到事务完成为止，才能看到事务的执行结果。
3)即要达到这么一种效果：对于任意两个并发的事务T1和T2，
在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，
这样每个事务都感觉不到有其他事务在并发地执行。
*/

/*
4、持久性（Durability）
指事务一旦提交，其对数据库的修改就是永久性的。
事务的持久性不能做到百分百的持久，一些外部原因导致数据库发生故障， 
如硬盘损坏，那么所有提交的数据可能都会丢失。
*/

/*查看当前会话中的隔离级别*/
SELECT @@session.transaction_isolation;/*8.0*/

/*将当前会话中的隔离级别修改回默认隔离级别 REPEATABLE READ */
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

/*建库*/
CREATE DATABASE shiwu CHARACTER SET utf8;
USE shiwu;
/*建表*/
CREATE TABLE sh_user(NAME CHAR(10),money INT(10));
/*插入数据*/
INSERT INTO sh_user VALUES('Alex',1000),('Bill',1000);
/*查看数据*/
SELECT NAME,money FROM sh_user;

/*开启事务*/
START TRANSACTION;
/*开始转账*/
UPDATE sh_user SET money = money - 100 WHERE NAME = 'Alex';
UPDATE sh_user SET money = money + 100 WHERE NAME = 'Bill';

/*提交事务 OR 事务回滚*/
COMMIT;/*ROLLBACK;*/

/*
事务的执行要么成功，要么就返回到事务开始前的状态，
这就保证了同一事务操作的同步性和数据的完整性。
*/
/*
MySQL中的事务不允许嵌套，
若在执行START TRANSACTION语句前上一个事务还未提交，
会隐式地执行提交操作。
*/
/*
事务处理主要是针对数据表中数据的处理，
不包括创建或删除数据库、数据表，修改表结构等操作，
而且执行这类操作时会隐式地提交事务。
当执行COMMIT或ROLLBACK后，当前事务就会自动结束。
ROLLBACK只能针对未提交的事务回滚，已提交的事务无法回滚。
*/

/*
MySQL默认是自动提交模式。
如果没有显式开启事务（START TRANSACTION），
每一条SQL语句都会自动提交（COMMIT）。
*/

/*查看事务是否自动提交 1自动提交 0手动提交*/
SELECT @@autocommit;
/*关闭自动提交*/
SET AUTOCOMMIT = 0;


/*保存点
在回滚事务时，若希望只撤销一部分，
可以用保存点来实现。
*/

/*设置保存点*/
SAVEPOINT point1;
/*回滚到保存点*/
ROLLBACK TO SAVEPOINT point1;
/*若不再需要一个保存点，使用如下语句删除。*/
RELEASE SAVEPOINT point1;


START TRANSACTION;
UPDATE sh_user SET money = money - 100 WHERE NAME = 'Alex';
SELECT NAME, money FROM sh_user;/*Alex剩900*/
SAVEPOINT s1;
UPDATE sh_user SET money = money - 50 WHERE NAME = 'Alex';
SELECT NAME, money FROM sh_user;/*Alex剩850*/
ROLLBACK TO SAVEPOINT s1;
SELECT NAME, money FROM sh_user;/*Alex剩900*/
COMMIT;

/*
一个事务中可以创建多个保存点，在提交事务后，事务中的保存点就会被删除。
*/

/*事务隔离级别*/

# ① 查看全局隔离级
SELECT @@global.transaction_isolation;
# ② 查看当前会话中的隔离级
SELECT @@session.transaction_isolation;
# ③ 查看下一个事务的隔离级
SELECT @@transaction_isolation;

/*
全局的隔离级：影响所有连接MySQL用户。
当前会话隔离级：只影响当前正在登录MySQL服务器的用户。（不会影响其他用户）
下一个事务的隔离级：仅对当前用户的下一个事务操作有影响。
*/

/*
REPEATABLE-READ：可重复读
READ UNCOMMITTED：读取未提交
READ COMMITTED：读取提交
SERIALIZABLE：可串行化
*/

/*设置当前会话隔离级别*/
SET SESSION TRANSACTION ISOLATION LEVEL
REPEATABLE READ
/*READ UNCOMMITTED*/
/*READ COMMITTED*/
/*SERIALIZABLE*/
;

/*设置全局会话隔离级别*/
SET GLOBAL TRANSACTION ISOLATION LEVEL
REPEATABLE READ
/*READ UNCOMMITTED*/
/*READ COMMITTED*/
/*SERIALIZABLE*/
;


/*事务的访问模式
若开发需要，可以将事务的访问模式设置为READ ONLY（只读模式），禁止对表进行更改。
*/

# ① 设置只读事务
SET [SESSION | GLOBAL] TRANSACTION READ ONLY
# ② 恢复成读写事务
SET [SESSION | GLOBAL] TRANSACTION READ WRITE


/*四种隔离级别解释*/

# READ UNCOMMITTED（读取未提交）
/*事务中最低的级别，可以读取到其他事务中未提交的数据。
也称为脏读（Dirty Read）：一个事务读取了另外一个事务未提交的数据。
*/

# READ COMMITTED（读取提交）
/*
大多数DBMS（如SQL Server、Oracle）的默认隔离级，但不包括MySQL。
只能读取其他事务已经提交的数据，避免了脏读问题。
但是会出现不可重复读（NON-REPEATABLE READ）问题：
一个事务中多次查询的结果不一致，原因是查询的过程中数据发生了改变。
*/

# REPEATABLE READ（可重复读）默认级别
/*
MySQL的默认事务隔离级，它解决了脏读和不可重复读的问题，
确保了同一事务的多个实例在并发读取数据时，会看到同样的结果。
*/

# SERIALIZABLE（可串行化）
/*
隔离级的最高级别，它在每个读的数据行上加锁，使之不会发生冲突，
解决了脏读、不可重复读和幻读的问题。
由于加锁可能导致超时（Timeout）和锁竞争（Lock Contention）现象，
性能是4种隔离级中最低的。
除非为了数据的稳定性，需要强制减少并发的情况时，才会选择此种隔离级。
*/

/*锁等待超时默认为50秒，可进行更改*/
SELECT @@innodb_lock_wait_timeout;