【MySQL 06】事务处理

最新推荐文章于 2023-06-16 11:31:30 发布

小松悦读会 | kevinelstri

最新推荐文章于 2023-06-16 11:31:30 发布

阅读量819

点赞数

分类专栏： MySQL MySQL路线

本文链接：https://blog.csdn.net/kevinelstri/article/details/52400715

版权

MySQL 同时被 2 个专栏收录

16 篇文章 0 订阅

订阅专栏

MySQL路线

15 篇文章 7 订阅

订阅专栏

1、事务的ACID性质

事务具有4个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持续性（Durablility）。

以“银行转帐”为例：

原子性（Atomicity）：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。换句话说，事务是不可分割的最小单元。比如：银行转帐过程中，必须同时从一个帐户减去转帐金额，并加到另一个帐户中，只改变一个帐户是不合理的。
一致性（Consistency）：在事务处理执行前后，数据库是一致的。也就是说，事务应该正确的转换系统状态。比如：银行转帐过程中，要么转帐金额从一个帐户转入另一个帐户，要么两个帐户都不变，没有其他的情况。
隔离性（Isolation）：一个事务处理对另一个事务处理没有影响。就是说任何事务都不可能看到一个处在不完整状态下的事务。比如说，银行转帐过程中，在转帐事务没有提交之前，另一个转帐事务只能处于等待状态。
持续性（Durablility）：事务处理的效果能够被永久保存下来。反过来说，事务应当能够承受所有的失败，包括服务器、进程、通信以及媒体失败等等。比如：银行转帐过程中，转帐后帐户的状态要能被保存下来。

2、事务状态

SET AUTOCOMMIT = 0 , 禁止自动提交
SET AUTOCOMMIT = 1, 开启自动提交
START TRANSACTION：开始事务，autocommit设为0，如果已经有一个事务在运行，则会触发一个隐藏的COMMIT
COMMIT：提交事务，保存更改，释放锁
ROLLBACK：回滚本事务对数据库的所有更改，然后结束事务，释放锁
SAVEPOINT savepoint_name：创建一个savepoint识别符来ROLLBACK TO SAVEPOINT
ROLLBACK TO SAVEPOINT savepoint_name：回滚到从savepoint_name开始对数据库的所有更改，这样就允许回滚事务中的一部分，保证更改的一个子集被提交
SET TRANSACTION：允许设置事务的隔离级别
LOCK TABLES：允许显式的锁住一个或多个table，会隐式的关闭当前打开的事务，建议在执行LOCK TABLES语句之前显式的commit或rollback。我们一般所以一般在事务代码里不会使用LOCK TABLES

3、事务操作

(1) 首先创建employee数据表：

mysql> create table employee(
    -> employeeID char(4),
    -> name varchar(20) not null,
    -> job varchar(20),
    -> departmentID int
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into employee value ('7513' , 'Nora Edwar' , 'Programmer', 128);
mysql> insert into employee value ('9006' , 'Candy Burn' , 'Systems Ad',128 );
mysql> insert into employee value ( '9842' , 'Ben Smith' ,  'DBA' , 42);
mysql> insert into employee value ('9843',  'Pert Park'  , 'DBA' , 42 );
mysql> insert into employee value ('9845' , 'Ben Patel'  , 'DBA' , 128 );
mysql> insert into employee value ('9846' , 'Red Right' ,  null, 128 );
mysql> insert into employee value ('9847' , 'Run Wild'  ,  null , 128 );
mysql> insert into employee value ('9848' , 'Rip This J' , null , 128 );
mysql> insert into employee value ('9849' , 'Rip This J' , null  , 128 );
mysql> insert into employee value ( '9850' , 'Reader U' ,   null , 128 );
mysql> insert into employee value ('6651',  'Ajay Patel' , 'Programmer', 128 );

mysql> select * from employee;
+------------+------------+------------+--------------+
| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+
| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
+------------+------------+------------+--------------+

(2) SET AUTOCOMMIT=0：

mysql> set autocommit = 0;//禁止自动提交

mysql> insert into employee values(null,'test1',null,128);

mysql> savepoint s1;//创建一个savepoint识别符

mysql> insert into employee values(null,"test2",null,128);

mysql> savepoint s2;//创建一个savepoint识别符

mysql> insert into employee values(null,"test3",null,128);

mysql> savepoint s3;//创建一个savepoint识别符

mysql> select * from employee;
+------------+------------+------------+--------------+
| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+
| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
| NULL       | test1      | NULL       |          128 |
| NULL       | test2      | NULL       |          128 |
| NULL       | test3      | NULL       |          128 |
+------------+------------+------------+--------------+

(3) ROLLBACK TO SAVEPOINT：

mysql> rollback to savepoint s1;//回滚到s1标签处：

mysql> select * from employee;
+------------+------------+------------+--------------+
| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+
| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
| NULL       | test1      | NULL       |          128 |
+------------+------------+------------+--------------+

(4) COMMIT：

mysql> commit;//提交事务

mysql> rollback to savepoint s2;//一旦事务提交了，就不能再回滚
ERROR 1305 (42000): SAVEPOINT s2 does not exist

(5) SET AUTOCOMMIT=1：

mysql> set autocommit = 1;//自动提交事务

mysql>  insert into employee values(null,"test4",null,128);

mysql> savepoint s4;//一旦创建，自动提交

mysql> select * from employee;
+------------+------------+------------+--------------+
| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+
| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
| NULL       | test1      | NULL       |          128 |
| NULL       | test2      | NULL       |          128 |
| NULL       | test3      | NULL       |          128 |
| NULL       | test4      | NULL       |          128 |
+------------+------------+------------+--------------+

mysql> rollback to s4;//此时就无法回滚了
ERROR 1305 (42000): SAVEPOINT s4 does not exist

4、锁

共享锁、排它锁、悲观锁、乐观锁、行级锁、表级锁

共享锁：就是在读取数据的时候，给数据添加一个共享锁。共享和共享直接是不冲突的，但是和排他锁是冲突的。
排他锁：更新数据的时候，安装排他锁，禁止其他一切行为。
悲观锁：更新多，查询少时用，悲观锁不是数据库中真正的锁，是人们看待事务的态度。
乐观锁：更新少，查询多时用，乐观锁也不是数据库中真正的锁，是人们看待事务的态度。

5、并发处理

脏读：一个事务读取到了另外一个事务没有提交的数据
事务1：更新一条数据
———>事务2：读取事务1更新的记录
事务1：调用commit进行提交
此时事务2读取到的数据是保存在数据库内存中的数据，称为脏读。
读到的数据为脏数据
详细解释：
脏读就是指:当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，
另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个
事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。

不可重复读：在同一事务中，两次读取同一数据，得到内容不同
事务1：查询一条记录
———->事务2：更新事务1查询的记录
———->事务2：调用commit进行提交
事务1：再次查询上次的记录
此时事务1对同一数据查询了两次，可得到的内容不同，称为不可重复读

幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同
事务1：查询表中所有记录
———->事务2：插入一条记录
———->事务2：调用commit进行提交
事务1：再次查询表中所有记录
此时事务1两次查询到的记录是不一样的，称为幻读
详细解释：
幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，
这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表
中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，
就好象发生了幻觉一样。

6、事务隔离

事务隔离五种级别：

TRANSACTION_NONE 不使用事务。
TRANSACTION_READ_UNCOMMITTED 允许脏读。
TRANSACTION_READ_COMMITTED 防止脏读，最常用的隔离级别,并且是大多数数据库的默认隔离级别
TRANSACTION_REPEATABLE_READ 可以防止脏读和不可重复读，
TRANSACTION_SERIALIZABLE 可以防止脏读，不可重复读取和幻读，（事务串行化）会降低数据库的效率

以上的五个事务隔离级别都是在Connection接口中定义的静态常量，

使用setTransactionIsolation(int level) 方法可以设置事务隔离级别。
如:con.setTransactionIsolation(Connection.REPEATABLE_READ);

注意：事务的隔离级别受到数据库的限制，不同的数据库支持的的隔离级别不一定相同

summary：
（1）Serializable：可避免脏读、不可重复读、虚读情况的发生。
（2）Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读，是 mysql 默认的事务隔离级别）
（3）Read committed：可避免脏读情况发生。（读取已提交的数据）
（4）Read uncommitted：最低级别，以上情况均无法保证。（读取到了未提交的数据）