MySQL的事务

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

   原子性（Atomicity，或称不可分割性） 
   一致性（Consistency）
   隔离性（Isolation，又称独立性）
   持久性（Durability）

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

1、用BEGIN、COMMIT、ROLLBACK来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、调整MySQL的事务提交模式

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

正常提交事务

mysql> CREATE TABLE runoob_transaction_test( id INT(5)) engine=innodb;  # 创建数据表
Query OK, 0 ROWS affected (0.04 sec)
 
mysql> SELECT * FROM runoob_transaction_test;
Empty SET (0.01 sec)
 
mysql> BEGIN;  # 开始事务
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> INSERT INTO runoob_transaction_test VALUE(5);
Query OK, 1 ROWS affected (0.01 sec)
 
mysql> INSERT INTO runoob_transaction_test VALUE(6);
Query OK, 1 ROWS affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 ROWS affected (0.01 sec)
 
mysql>  SELECT * FROM runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 ROWS IN SET (0.01 sec)

事务回滚

mysql> BEGIN;    # 开始事务
Query OK, 0 ROWS affected (0.00 sec)
 
mysql>  INSERT INTO runoob_transaction_test VALUES(7);
Query OK, 1 ROWS affected (0.00 sec)
 
mysql> ROLLBACK;   # 回滚
Query OK, 0 ROWS affected (0.00 sec)
 
mysql>   SELECT * FROM runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 ROWS IN SET (0.01 sec)

一个事务读取到另外一个事务未提交的数据。

mysql> CREATE TABLE `test_table` (
    ->   `id` INT NOT NULL AUTO_INCREMENT,
    ->   `val` INT NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB ;
Query OK, 0 ROWS affected (0.15 sec)
 
mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> INSERT INTO test_table VALUE (1, 1);
Query OK, 1 ROW affected (0.00 sec)

session 1	session 2
begin;
update test_table set val = val+1;
	begin;
	mysql> select * from test_table; +----+-----+ \| id \| val \| +----+-----+ \| 1 \| 2 \| +----+-----+ 1 row in set (0.00 sec)

一个事务在读取某些数据后的某个时间，再次读取以前读取过的数据。却发现其读出的数据已经发生了变化或者已经被删除。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 ROWS affected (0.00 sec)

session 1

session 2

mysql> BEGIN;
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> SELECT * FROM test_table WHERE id = 1;
+----+-----+
| id | val |
+----+-----+
|  1 |   4 |
+----+-----+
1 ROW IN SET (0.00 sec)-

mysql> BEGIN;
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> UPDATE test_table SET val = val+1;
Query OK, 1 ROW affected (0.00 sec)
ROWS matched: 1  Changed: 1  Warnings: 0
 
mysql> commit;
Query OK, 0 ROWS affected (0.02 sec)

mysql> SELECT * FROM test_table WHERE id = 1;
+----+-----+
| id | val |
+----+-----+
|  1 |   5 |
+----+-----+
1 ROW IN SET (0.00 sec)
 
mysql>

幻读：一个事务在前后两次查询同一个范围的时候，后一次查询看到了第一次查询没有看到的行
官网说明：https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. 
For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 ROWS affected (0.00 sec)

session 1

session 2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_table where id = 2;
Empty set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_table value (2, 1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from test_table where id = 2;
+----+-----+
| id | val |
+----+-----+
|  2 |   1 |
+----+-----+
1 row in set (0.01 sec)

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE-READ;
Query OK, 0 ROWS affected (0.00 sec)

session 1

session 2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_table where id = 2;
Empty set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_table value (2, 1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test_table value (2, 2);
ERROR 1062 (23000): Duplicate entry '2' for key 'test_table.PRIMARY'

-- MySQL通过MVCC和间隙锁的方式，处理RR隔离级别下的幻读问题
mysql> select * from test_table where id = 2;
Empty set (0.00 sec)

脏读、不可重复读、幻读，都是数据库读一致性的问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本可以为分两种
- 一种是在读取数据之前，对其加锁，阻止其他事务对数据加锁
- 另一种是不加锁，通过一定的机制生成一个数据请求时间点的数据快照,并用这个快照来提供一定级别的一致性读取。这种技术叫做数据多版本并发控制（MVCC）
关于MVCC，可以参考官网的介绍 - https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html
数据库的事务隔离级别越严格，并发引发的问题越小，但付出的代价越大。为了解决隔离和并发的矛盾，ISO/ANSI SQL92定义了4个事务隔离级别。通过选择不同的隔离级别来平衡隔离和并发之间的矛盾。 读未提交：read uncommitted 读已提交：read committed 可重复读：repeatable read 串行化：serializable
官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
隔离级别与一致性问题

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED（读未提交）	√	√	√
READ COMMITTED（读已提交）		√	√
REPEATABLE READ（可重复读）			√
SERIALIZABLE（串行化）

查询当前MySQL的事务隔离级别

mysql> SELECT @@SESSION.tx_isolation; -- 查询会话级的事务隔离级别(MySQL 5.7)
+------------------------+
| @@SESSION.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 ROW IN SET (0.00 sec)
 
mysql> SELECT @@SESSION.transaction_isolation; -- 查询会话级的事务隔离级别(MySQL 8)
+---------------------------------+
| @@SESSION.transaction_isolation |
+---------------------------------+
| REPEATABLE-READ                 |
+---------------------------------+
1 ROW IN SET (0.00 sec)
 
mysql> SELECT @@global.tx_isolation; -- 查询全局的事务隔离级别(MySQL 5.7)
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 ROW IN SET (0.00 sec)
 
mysql> SELECT @@global.transaction_isolation; -- 查询全局的事务隔离级别(MySQL 8)
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ             |
+--------------------------------+
1 ROW IN SET (0.00 sec)
 
mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | VALUE           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 ROW IN SET (0.00 sec)

设置当前MySQL的事务隔离级别

-- 设置会话的隔离级别
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;
 
-- 设置全局的隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

事务传播行为类型	说明
REQUIRED	如果有事务在运行，当前的方法就在这个事务内运行；否则，就启动一个新的事务，并在自己的事务内运行
REQUIRES_NEW	当前的方法必须启动新事务，并在它自己的事务内运行；如果有事务正在运行，应该将它挂起
MANDATORY	当前的方法必须运行在事务内部，如果没有正在运行的事务，将抛出异常
SUPPORTS	如果有事务在运行，当前的方法就在这个事务内运行；否则它可以不运行在事务中
NOT_SUPPORTED	当前的方法不应该运行在事务中，如果有运行的事务，将它挂起
NEVER	当前的方法不应该运行在事务中，如果有运行的事务，就抛出异常
NESTED	如果有事务在运行，当前的方法就应该在这个事务的嵌套事务内运行。否则，就启动一个新的事务，并在它自己的事务内运行

SELECT * FROM information_schema.innodb_trx ；

innodb_trx表:
  trx_id: 事务ID。
  trx_state: 事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
  trx_started: 事务开始时间。
  trx_requested_lock_id: 事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。
  trx_wait_started: 事务开始等待的时间。
  trx_weight: 事务的权重。
  trx_mysql_thread_id: 事务线程 ID，可以和 PROCESSLIST 表 JOIN。
  trx_query: 事务正在执行的 SQL 语句。
  trx_operation_state: 事务当前操作状态。
  trx_tables_in_use: 当前事务执行的 SQL 中使用的表的个数。
  trx_tables_locked: 当前执行 SQL 的行锁数量。
  trx_lock_structs: 事务保留的锁数量。
  trx_lock_memory_bytes: 事务锁住的内存大小，单位为 BYTES。
  trx_rows_locked: 事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。
  trx_rows_modified: 事务更改的行数。
  trx_concurrency_tickets: 事务并发票数。
  trx_isolation_level: 当前事务的隔离级别。
  trx_unique_checks: 是否打开唯一性检查的标识。
  trx_foreign_key_checks: 是否打开外键检查的标识。
  trx_last_foreign_key_error: 最后一次的外键错误信息。
  trx_adaptive_hash_latched: 自适应散列索引是否被当前事务锁住的标识。
  trx_adaptive_hash_timeout: 是否立刻放弃为自适应散列索引搜索 LATCH 的标识。
 
innodb_locks表:
  lock_id: 锁 ID。
  lock_trx_id: 拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。
  lock_mode: 锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。
  lock_type: 锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。
  lock_table: 被锁定的或者包含锁定记录的表的名称。
  lock_index: 当 LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。
  lock_space: 当 LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。
  lock_page: 当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。
  lock_rec: 当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。
  lock_data: 当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL。
 
innodb_lock_waits表:
  requesting_trx_id: 请求事务的 ID。
  requested_lock_id: 事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。
  blocking_trx_id: 阻塞事务的 ID。
  blocking_lock_id: 某一事务的锁的 ID，该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。

-- 查看正在锁的事务
SELECT * FROM information_schema.innodb_locks;
 
-- 查看等待锁的事务
SELECT * FROM information_schema.innodb_lock_waits;
 
-- 列举在表缓存中当前被打开的非TEMPORARY表
--   Database: 含有该表的数据库
--   Table: 表名称
--   In_use: 表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用
--   Name_locked: 表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作
SHOW OPEN TABLES WHERE In_use > 0;
 
-- 查询进程
SHOW PROCESSLIST;

[mysql, 事务]

MySQL的事务

事务的定义

事务的控制

事务的控制方式主要有2种

事务控制语句

事务演示

正常提交事务

事务回滚

并发事务带来的问题

脏读

不可重复读

幻读

事务的隔离性

事务的隔离级别

查询当前MySQL的事务隔离级别

设置当前MySQL的事务隔离级别

事务的传播机制

查询数据库正在进行的事务

我的技术分享