MySQL 事务

什么是事务

多条sql语句，要么全部成功，要么全部失败。MySQL的事务是在存储引擎层实现。 MySQL的事务有ACID

A 原子性(atomicity)：一个事务必须被视为一个不可分割的单元。

C 一致性(consistency)：数据库是从一种状态切换到另一种状态。

I 隔离性(isolation)：事务在提交之前，对于其他事务不可见。

D 持久性(durablity)：一旦事务提交，所修改的将永久保存到数据库。

mysql> CREATE TABLE bank
 -> (
 -> name VARCHAR(25),
 -> MONEY FLOAT
 -> )；
 Query OK, 0 rows affected (0.04 sec)
mysql> insert into bank(name,MONEY) values('lu','1000'),('qi','5000');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
Begin 或 start transaction开启事务
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE bank SET MONEY=MONEY-1000 WHERE name='qi';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE bank SET MONEY=MONEY+1000 WHERE name='lu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM bank;
+------+-------+
| name | MONEY |
+------+-------+
| lu | 2000    |
| qi | 4000    |
+------+-------+
2 rows in set (0.00 sec)
mysql> 
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM bank;
+------+-------+
| name | MONEY |
+------+-------+
| lu | 1000    
| qi | 5000    |
+------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM bank;
+------+-------+
| name | MONEY |
+------+-------+
| lu | 1000    |
| qi | 5000    |
+------+-------+
2 rows in set (0.00 sec)|

为什么要使用事务

1. 数据完整性：事务是确保数据在数据库中保持一致性和完整性的关键机制之一。通过使用事务，可以将多个操作作为一个原子操作来执行，要么全部执行成功，要么全部回滚到事务开始之前的状态，从而保证数据的完整性。
2. 并发控制：当多个用户同时访问数据库时，可能会发生并发冲突，比如多个用户同时修改同一行数据。事务可以在并发环境中提供隔离性，确保每个事务独立运行，不受其他事务的干扰。通过对事务的隔离性进行管理，可以避免脏读、不可重复读和幻读等并发访问问题，确保数据的一致性。
3. 数据回滚和恢复：当发生错误或异常情况时，事务可以实现数据的回滚和恢复。如果事务执行过程中出现错误，可以回滚到事务开始之前的状态，避免对数据库造成不可逆的影响。而且在系统故障或断电等意外情况下，事务的持久性保证了数据的持久存储，确保数据不会丢失。
4. 性能优化：使用事务可以减少频繁的数据库操作和通信开销。将多个操作合并为一个事务，在服务器端一次性执行，可以减少与数据库的交互次数，提高数据库的性能和吞吐量。

总的来说，使用MySQL事务可以提供数据的一致性、并发控制、数据回滚和恢复的功能，保障数据的正确性和可靠性，并提高数据库的性能和效率。

事务优点

1. 原子性（Atomicity）：事务是一个原子操作，将数据库从一个一致状态转变为另一个一致状态。如果事务的所有操作成功执行，则事务被提交（Commit），否则将被回滚（Rollback）到事务开始前的状态。这确保了数据库的完整性，能够保证数据的一致性。
2. 一致性（Consistency）：事务的执行将数据库从一个一致状态转变为另一个一致状态。在事务开始之前和结束之后，数据库始终保持一致的状态。如果事务执行期间出现错误或违反了约束条件，事务将被回滚，数据库将恢复到事务开始之前的状态。
3. 隔离性（Isolation）：事务的隔离性指的是并发执行的事务之间互不干扰，每个事务的中间状态对其他事务是不可见的。MySQL使用锁机制来保证事务的隔离性，避免了脏读、不可重复读和幻读等并发访问问题。
4. 持久性（Durability）：当事务提交后，它的修改将永久保存在数据库中，即使发生系统故障或断电等异常情况也不会丢失。MySQL使用日志（log）来保证事务的持久性，将事务的操作记录到日志中，在系统恢复后可以通过日志重新执行来还原事务。

事务的使用能够确保数据库操作的可靠性和一致性，尤其是在并发操作的情况下能够有效地保护数据的完整性，并提高系统的性能和并发能力。

总结事务命令

事务开始： start transaction

事务开始： begin

事务提交： commit

回 滚： rollback

查看自动提交模式是自动还是手动

事务有4种隔离级别 事务在提交之前对其他事务可不可见

1. read unaommitted(未提交读)

2. read committed(已提交读)

3. Repeatable read(可重复读)

4. seaializable(可串行化)

mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON       |
+---------------+-------+
1 row in set (0.01 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF      |
+---------------+-------+
1 row in set (0.00 sec)

详细解释：

未提交读

事务中修改没有提交对其他事务也是可见的，俗称脏读

mysql> CREATE TABLE student
 -> (
 -> id int not null auto_increment,
 -> name varchar(32) not null default '',
 -> primary key(id)
 -> )engine=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.13 sec)

两端的客户端都设置成未提交读

mysql> SET SESSION TX_ISOLATION='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

客户端A：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM student;
Empty set (0.00 sec)
mysql> INSERT INTO student(name) values('zhangyi');
Query OK, 1 row affected (0.00 sec)
mysql> //注意：此时事务未提交！！！

客户端B：

mysql> set session tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from student;
+----+---------+
| id | name |
+----+---------+
| 2 | zhangyi |
+----+---------+
1 row in set (0.00 sec)

客户端B可以查看到信息

总结：以上可以看出未提交读隔离级别非常危险，对于一个没有提交事务所做修改对另一个事务是可见状态，出现了脏读！非特殊情况不建议使用此级别

已提交读

多数数据库系统默认为此级别（MySQL不是）。已提交读级别为一个事务只能已提交事务所做的修改，也就是解决了未提交读的问题

mysql> SET SESSION TX_ISOLATION='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM student;
+----+---------+
| id | name |
+----+---------+
| 2 | zhangyi |
+----+---------+
1 row in set (0.00 sec) //此时去另一个客户端去查看
mysql> INSERT INTO student(name) values ('zhanger');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

客户端B：

mysql> select * from student;
+----+---------+
| id | name |
+----+---------+
| 2 | zhangyi |
+----+---------+
1 row in set (0.00 sec) //未提交的时候查看
mysql> select * from student;
+----+---------+
| id | name |
+----+---------+
| 2 | zhangyi |
| 3 | zhanger |
+----+---------+
2 rows in set (0.00 sec) //已提交事务之后查看

总结：从上面的例子可以看出，提交读没有了未提交读的问题，但是我们可以看到客户端A的一个事务中执行了两次同样的SELECT语句，，得到不同的结果，因此已提交读又被称为不可重复读。同样的筛选条件可能得到不同的结果

可重复读**--**解决了不可重复读的问题，数据库级别没有解决幻读的问题

mysql> SET SESSION TX_ISOLATION='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec) //两个客户端均设置为可重复读

然后两边一起开启一个事务

客户端A：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+---------+
| id | name |
+----+---------+
| 2 | zhangyi |
| 3 | zhangsi |
+----+---------+
2 rows in set (0.00 sec)
mysql> UPDATE student SET name='zhanger' WHERE id=3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.06 sec)

客户端B：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+---------+
| id | name |
+----+---------+
| 2 | zhangyi |
| 3 | zhangsi |
+----+---------+
2 rows in set (0.00 sec)
mysql> select * from student;
+----+---------+
| id | name |
+----+---------+
| 2 | zhangyi |
| 3 | zhangsi |
+----+---------+
2 rows in set (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+---------+
| id | name   |
+----+---------+
| 2 | zhangyi |
| 3 | zhanger |
+----+---------+
2 rows in set (0.00 sec)

总结：上面的例子我们得知，可重复读两次读取的内容不一样。数据库的幻读问题并没有得到解决。幻读只读锁定里面的数据，不能读锁定外的数据，解决幻读出了mvcc机制Mvcc机制。

**可串行化--是最高隔离级别，强制事务串行执行，执行串行了也就解决问题了，这个I别只有在对数据一致性要求非常严格并且没有并发的情况下使用 **

mysql> SET SESSION TX_ISOLATION='SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec) //客户端两边设置成可串行读

客户端A：

mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student where id < 10;
+----+---------+
| id | name |
+----+---------+
| 2 | zhangyi |
| 3 | zhanger |
| 4 | zhangwu |
| 5 | zhangwu |
| 6 | zhangwu |
| 7 | zhangqi |
+----+---------+
6 rows in set (0.00 sec)

客户端B：

mysql> insert into student (name) values('zhangqi');
ERROR 1205 (HY000): Lock wait timeout exceeded; trying transaction

总结：我们发现INSERT 语句被阻塞执行，原因是A执行了查询表student同时满足id<10，已被锁定。如果查询表student同时满足id<5，则新增语句可以正常执行

隔离级别

脏读

不可重复

幻读

加锁读

未提交读

是
是是
否

提交读

否
是是
否

可重复读

否

否
是
否

串行读

否

否

否
是