MySQL事务

最新推荐文章于 2023-06-07 23:19:39 发布

niceyz

最新推荐文章于 2023-06-07 23:19:39 发布

阅读量464

点赞数 1

本文链接：https://blog.csdn.net/yz2015/article/details/85236319

版权

本文目录：

1.事务介绍
2.事务入门
   1.命令行演示
   2.代码演示
3.事务特性
4.事务隔离级别
   1.安全隐患
       a.读的问题
       b.写的问题
   2.隔离级别
       a.读未提交
       b.读已提交
       c.可重复读
       d.序列化 | 串行化
5.事务管理
   1.三层结构介绍
   2.三层结构中事务管理
       a.传递Connection
       b.使用ThreadLocal

1.事务介绍

事务是指包含多个微小逻辑单元的一组操作，只有其中有一个逻辑失败了，那么这一组操作就全部以失败告终，不存在一半成功，一半不成功的状况。
银行转账的例子，一次性往两张表添加记录（一个扣钱，一个加钱），需要确保这两张表能全部成功添加，不允许一张表成功，一张表失败这种情况出现。

2.事务入门
1. 命令行演示

开启事务：
start transaction;

提交事务让操作生效：
commit;

回滚事务回到以前：
rollback;

默认情况下，mysql对每一行命令执行之后，会立即生效，
是因为里面有一个开关 commit = on | 1 默认执行完一行命令就立即提交，立即生效

查询提交的开关：
show variables like '%commit%';

把默认提交开关关掉：
set autocommit = off;

#创建数据库
DROP DATABASE yz_tran;
CREATE DATABASE yz_tran;
#创建表
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) DEFAULT NULL,
`money` DECIMAL(14,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

#添加数据
INSERT INTO account(NAME,money)VALUES
("张三",1000),
("李四",1000);
SELECT * FROM `account`;

cmd连接MySQL：

mysql –u root –p 输入密码连接mysql

加钱、扣钱操作：

mysql> use yz_tran;
Database changed
mysql> update account set money= money-100 where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from account;
+----+------+---------+
| id | name | money |
+----+------+---------+
| 1 | 张三 | 900.00 |
| 2 | 李四 | 1000.00 |
+----+------+---------+
2 rows in set (0.00 sec)

mysql> update account set money= money+100 where id = 1;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from account;
+----+------+---------+
| id | name | money |
+----+------+---------+
| 1 | 张三 | 1000.00 |
| 2 | 李四 | 1000.00 |
+----+------+---------+
2 rows in set (0.00 sec)

开启事务：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set money= money-100 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

查询，这里金额减少，注意这是内存中的数据
mysql> select * from account;
+----+------+---------+
| id | name | money |
+----+------+---------+
| 1 | 张三 | 1000.00 |
| 2 | 李四 | 900.00 |
+----+------+---------+
2 rows in set (0.00 sec)

在另一个客户端刷新，金额并没有变：

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

查询，这里金额增加，注意这是内存中的数据
mysql> select * from account;
+----+------+---------+
| id | name | money |
+----+------+---------+
| 1 | 张三 | 1100.00 |
| 2 | 李四 | 900.00 |
+----+------+---------+
2 rows in set (0.00 sec)
在另一个客户端刷新，金额依然没有变：

提交事务，刚才的操作确认过没有问题，让事务生效：commit;
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from account;
+----+------+---------+
| id | name | money |
+----+------+---------+
| 1 | 张三 | 1100.00 |
| 2 | 李四 | 900.00 |
+----+------+---------+
2 rows in set (0.00 sec)

在另一个客户端刷新，金额发生改变：

操作反悔，转错人了，不想转账了(或者代码出现异常)返回之前：
开启事务：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

转错了：
mysql> update account set money= money-100 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from account;
+----+------+---------+
| id | name | money |
+----+------+---------+
| 1 | 张三 | 1000.00 |
| 2 | 李四 | 900.00 |
+----+------+---------+
2 rows in set (0.00 sec)

回滚：
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from account;
+----+------+---------+
| id | name | money |
+----+------+---------+
| 1 | 张三 | 1100.00 |
| 2 | 李四 | 900.00 |
+----+------+---------+
2 rows in set (0.00 sec)

其他客户端查询，还是原来的金额：

默认情况下，mysql对每一行命令执行之后，会立即生效，是因为里面有一个开关 commit = on | 1 默认执行完一行命令就立即提交，立即生效
查询提交的开关：
mysql> show variables like '%commit%';

把默认提交开关关掉：
mysql> set autocommit = off;
Query OK, 0 rows affected (0.00 sec)

关掉自动提交后，需要手动提交：
mysql> select * from account;
+----+------+---------+
| id | name | money |
+----+------+---------+
| 1 | 张三 | 1100.00 |
| 2 | 李四 | 900.00 |
+----+------+---------+
2 rows in set (0.00 sec)

mysql> update account set money= money-100 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
+----+------+---------+
| id | name | money |
+----+------+---------+
| 1 | 张三 | 1100.00 |
| 2 | 李四 | 900.00 |
+----+------+---------+
2 rows in set (0.00 sec)

mysql> update account set money= money-100 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
+----+------+---------+
| id | name | money |
+----+------+---------+
| 1 | 张三 | 1000.00 |
| 2 | 李四 | 900.00 |
+----+------+---------+
2 rows in set (0.00 sec)

其他客户端查询，金额并没有真正发生改变：

mysql> commit;
Query OK, 0 rows affected (0.10 sec)
commit提交之后，金额减少：

2-2.代码演示

工具类：
public class JdbcUtil {
static ComboPooledDataSource dataSource = null;
static {
dataSource = new ComboPooledDataSource();
}

/**
* 获取连接对象
* @return
*/
public static Connection getConn(){
try {

return dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}

public static void close(Connection conn , Statement statement){
close(conn , statement , null);
}

/**
* 关闭连接，释放资源
*/
public static void close(Connection conn , Statement statement , ResultSet rs){
try {
if(rs != null){
rs .close();
}
if(statement != null){
statement .close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

c3p0-config.xml
<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>
<default-config>

<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///yz_tran</property>
<property name="user">root</property>
<property name="password">root</property>

<property name="initialPoolSize">10</property>

<property name="maxIdleTime">30</property>

<property name="maxPoolSize">100</property>

<property name="minPoolSize">10</property>

<property name="maxStatements">200</property>
</default-config>
</c3p0-config>

测试类：
public class TestTransaction {
private static final String TAG = "TestTransaction";
@Test
public void testDemo(){
Connection conn = null;
PreparedStatement ps = null;
try {
//1. 获取连接
conn = JdbcUtil02.getConn();
//开启事务 set autocommit = off;
conn.setAutoCommit(false);
//2. 转账
String sql = "update account set money = money - ? where id = ?";
//张三扣钱
ps= conn.prepareStatement(sql);
ps.setInt(1,100);
ps.setInt(2,1);
ps.executeUpdate();

int a = 1 /0 ;

//李四加钱
ps.setInt(1,-100);
ps.setInt(2,2);
ps.executeUpdate();
System.out.println("转账完毕");
// 提交事务
conn.commit();
} catch (Exception e) {
e.printStackTrace();
System.out.println("转账失败");
try {
// 发生转账异常回滚事务
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
JdbcUtil02.close(conn , ps);
}
}
}

查看MySQL隔离级别：
老版本mysql用的是 tx_isolation
新版本mysql用的是 transaction_isolation

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

3.事务特性 ACID
原子性(Atomicity)：事务中的逻辑要全部执行，不可分割。(原子是物理中最小单位)
一致性(Consistency)：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态
隔离性(Isolation)：一个事务的执行过程中不能影响到其他事务的执行，即一个事务内部的操作及使用的数据对其他事务是隔离的，并发执行时各个事务之间互不干扰。
持久性(Durability)：即一个事务一旦提交，它对数据库的改变是永久性的。之后的其他操作不应该对其执行结果有任何影响。

4.事务隔离级别
事务隔离级别主要用来解决事务并发执行时引发的问题。
如果两个事物同时或者交错执行，那么他们的执行结果可能会受对方影响，这会导致数据的前后显示不一致。所以为了保证并发操作数据的正确性及一致性，SQL规范于1992年提出了数据库事务隔离级别。
事务的并发主要有两个方面的问题：读的问题 | 写的问题，相对于写的问题，读的问题出现的几率更高些。

   1.安全隐患
   如果事务没有任务隔离设置，那么在并发情况下会出现以下问题。
       a.读的问题
       脏读：指一个事务读到了另一个事务还没有提交的数据。
       不可重复读：一个事务读到了另一个事务已提交的更新的数据，导致多次查询结果不一致。针对update
       虚读|缓读：一个事务读到了另一个事务已提交的插入的数据，导致多次查询结果不一致。针对insert

       b.写的问题
       写的问题其实就只有一个，就是丢失更新。
       丢失更新：指一个事务去修改数据库，另一个事务也修改数据库，最后那个事务，不管是提交还是回滚都会造成前面一个事务的数据更新丢失。
       相比读的问题，写的问题出现几率较少，真的出现丢失更新，就必须是两个事务同一时间操作一条数据。

       解决办法：
       1.悲观锁
       该没有干活就想着会丢失更新。查询数据的后面跟上关键字for update
       2.乐观锁
       程序员自己控制，在表里面增加一个字段version(版本的意思)，默认是0。只要有一次操作，这个版本就递增。下次来操作，先核对版本号。如果对不上，表示是旧的数据，得先查询。

   2.隔离级别
       a.读未提交
       b.读已提交
       c.可重复读
       d.序列化 | 串行化

设置读未提交
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

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

客户端B进行账号修改操作，此时客户端B还没有提交事务

客户端A进行查询，读取到了客户端B还没有提交的数据

客户端B进行回滚

读未提交，什么问题都解决不了。

设置读已提交

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

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

客户端B进行修改操作：

客户端B提交事务

客户端A再次查询

读已提交：一个事务能读取到另一个事务已经提交的数据
解决：脏读
待解决：不可重复读，虚读

设置可重复读
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

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

客户端B提交事务：

客户端A查询：

虚读：insert操作

客户端B提交事务：

客户端A再次查询，MySQL已经把虚读给解决了。
https://www.jianshu.com/p/2953c64761aa mysql避免幻读

设置序列化|串行化 MySQL最高隔离级别
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

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