MySQL基础-17 事物的介绍

事物的介绍

1 TCL 语言

transaction Control language 事务控制语言

2 事务

一个或一组sql 语句组成一个执行单元，这个执行单元要么全部执行要么全部不执行。

3 事务的属性（ACID）

1. 原子性（Atomicity）
   原子性指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2. 一致性（Consistency）
   事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
3. 隔离性（Isolation）
   事务隔离性指一个事务的执行不能被其他事务干扰，即一个事务内部操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
4. 持久性（Durability）
   持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据故障不应该对其有任何影响。

4 事务的创建

分类：

• 隐式事务：事务没有明显的开启和结束的标记，比如：insert,update,delete 语句。
• 显示事务：有明显的开启和结束的标记，前提：必须设置自动提交事务功能为禁用：set autocommit=0;

#查看事务是否开启
SHOW VARIABLES LIKE 'autocommit';

1. 步骤1：开启事务

SET autocommit=0;
START TRANSACTION; #可选的

2. 步骤2:编写事务中的sql语句
3. 步骤3：结束事务
   commit; 提交事务
   callback;回滚事务
   savepoint 节点名; 设置保存点

案例

CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20),
	balance DOUBLE
);

INSERT INTO account(username,balance) VALUES('张无忌',1000),('赵敏',1000);


#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务
UPDATE account SET balance=500 WHERE username='张无忌';
UPDATE account SET balance=1500 WHERE username='赵敏';
#结束事务
COMMIT;

SELECT * FROM account

演示 savepoint 的使用

set autocommit=0;
start transaction;
delete from account where id=1;
savepoint a; #设置保存点
delete from account where id=2;
rollback to a; #回滚到保存点处

5 事务并发问题介绍

对于同事运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：

1. 脏读：对于两个事务T1,T2，T1读取了已经被T2更新但还没有提交的字段，之后，若T2回滚，T1读取的内容就是临时且无效的。
2. 不可重复读：对于两个事务T1,T2，T1读取了一个字段，然后T2更新了该字段，之后，T1再次读取同一个字段，值就不同了。
3. 幻读：对于两个事务T1,T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后，如果T1再次读取同一个表，就会多出几行。

数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，使他们不会相互影响，避免各种并发问题。

数据库提供的4种事务隔离级别：

隔离级别	描述
read uncommited(读未提交数据)	允许事务读取未被其他事务提交的变更，脏读，不可重复读和幻读的问题都会出现
read commited(读已提交数据)	只允许事务读取已经被其他事务提交的变更，可以避免脏读，但不可重复读和幻读问题仍然可能出现。
repeatable read(可重复读)	确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新，可以避免脏读和不可重复读，但幻读的问题仍然存在。
serializable(串行化)	确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作，所有并发问题都可以避免，但效率低。

• Oracle 支持的2中事务隔离级别：read commited,serializable.Oracle默认的隔离级别为：read commited。
• Mysql 支持四种隔离级别，Mysql 默认的隔离级别为 ： repeatable read

案例

1. read uncommited
   命令行面板1

C:\Windows\system32>mysql -uroot -proot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# 查询隔离级别
mysql> select @@tx_isolation
    -> ;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

#重新设置隔离级别
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | username | balance |
+----+----------+---------+
|  1 | 张无忌        |    1000 |
|  2 | 赵敏         |    1000 |
+----+----------+---------+
2 rows in set (0.01 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set username='john' where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

命令行面板2

C:\Windows\system32>mysql -uroot -proot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> use test;
Database changed

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | username | balance |
+----+----------+---------+
|  1 | john     |    1000 |
|  2 | 璧垫晱     |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | username | balance |
+----+----------+---------+
|  1 | john     |    1000 |
|  2 | 赵敏         |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

表格流程显示如下：

	面板1	面板2	结果
1	select * from account;
2	set autocommit=0;update account set username=‘john’ where id=1;
3		set autocommit=0;select * from account;	面板1并未提交事务，但是面板2依旧可以查看到被更改的信息，出现了脏读现象
4	回滚 rollback；
5		select * from account;	username=‘张无忌’,跟上一次查看的结果不一样，出现不可重复度和幻读的现象。

2. read committed
   命令行面板1

mysql> set session transaction isolation level read committed;

mysql>  select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | username | balance |
+----+----------+---------+
|  1 | 张飞        |    1000 |
|  2 | 赵敏         |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set username='john' where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

命令行面板2

mysql> set session transaction isolation level read committed;

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | username | balance |
+----+----------+---------+
|  1 | john     |    1000 |
|  2 | 赵敏         |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

表格流程显示如下：

	面板1	面板2	结果
1	select * from account;		username=‘张无忌’
2	set autocommit=0;update account set username=‘张飞’ where id=1;
3		set autocommit=0;select * from account;	面板1并未提交事务，面板2获没有获取到面板1更新的数据，所以避免了脏读现象 username=‘john’
4 提交 commit；
5		select * from account;	username=‘张飞’,跟上一次查看的结果不一样，出现不可重复度和幻读的现象。

3. repeatable read
   命令行面板1

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql>  select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | username | balance |
+----+----------+---------+
|  1 | john     |    1000 |
|  2 | 赵敏         |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set username='刘备' where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

命令行面板2

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | username | balance |
+----+----------+---------+
|  1 | john     |    1000 |
|  2 | 赵敏         |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | username | balance |
+----+----------+---------+
|  1 | john     |    1000 |
|  2 | 赵敏         |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

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

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | username | balance |
+----+----------+---------+
|  1 | 刘备         |    1000 |
|  2 | 赵敏         |    1000 |
+----+----------+---------+
2 rows in set (0.01 sec)

出现幻读的情况

表格流程显示如下：

	面板1	面板2	结果
1	select * from account;		username=‘张无忌’
2	set autocommit=0;update account set username=‘刘备’ where id=1;
3		set autocommit=0; select * from account;	username=‘john’
4	commit;
5		select * from acount;
6		commit;set autocommit=0; select * from account;	username=‘刘备’,在面板2提交的情况下才获取到了面板一修改的信息
7	set autocommit=0;update account set username=‘mmm’ 暂时不执行	set autocommit=0;insert into account values(‘tt’,1000);commit;
8	update account set username=‘mmm’ 执行语句		发现修改了三条语句，此时出现了幻读现象

4. serailizable
   命令行面板1

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql>  select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | username | balance |
+----+----------+---------+
|  1 | 刘备         |    1000 |
|  2 | 赵敏         |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> update account set username='mmmmm';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> update account set username='mmmmm';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

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

命令行面板2

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values('关羽',2000);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into account values(null,'关羽',2000);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into account values(null,'关羽',2000);
Query OK, 1 row affected (15.89 sec)

表格流程显示如下：

	面板1	面板2	结果
1	set autocommit=0;
2		set autocommit=0;insert into account values(null,‘dd’,2000);	会出现阻塞等待的情况
3	update account set username=‘mmm’;commit;		这时候面板2sql语句会执行

小结

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

2. 设置当前mysql连接的隔离级别：
   set transaction isolation level read committed;

3. 设置数据库系统的全局隔离级别：
   set global transaction isolation level read committed;

事物的隔离级别脏读		不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read	×	×	√
serializable	×	×	×

6 delete 和 truncate 在事务使用的区别

演示delete

set aotucommit=0;
delete from account;
rollback;

回滚成功

演示 truncate

set aotucommit=0;
truncate table account;
rollback;

不支持回滚