事务概述和MySQL中的事务管理

最新推荐文章于 2021-11-14 18:38:47 发布

weixin_34406086

最新推荐文章于 2021-11-14 18:38:47 发布

阅读量97

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/px828261/blog/1549337

版权

2019独角兽企业重金招聘Python工程师标准>>>

什么是事务

逻辑上的一组操作,要么全部成功,要么全部失败（这组操作不可分割）

MySQL管理事务的方式

自动管理(默认):一条SQL就是一个单独的事务，事务是自动提交的
手动事务:
- 关闭自动提交(仅对当前窗口有效)
  - show variables like 'autocommit'; 查看当前事务是否自动提交
  - set autocommit = off; 关闭自动提交
- 手动开启事务
  - start transaction : 开启事务（对数据的操作在临时表中进行）
  - rollback : 回滚事务 (取消操作)
  - commit : 提交事务（确认操作）
  - 在事务管理中执行sql，使用数据库内临时表保存，在没有进行事务提交或者回滚之前，其它用户无法看到事务操作的结果
  - SQL语言中只有DML才能被事务管理(insert/update/delete)

JDBC管理事务

核心API
- Connection.setAutoCommit(false);// 开启事务
- connection.commit();// 提交事务
- connection.rollback();// 回滚事务

Connection connection = null;
PreparedStatement prepareStatement = null;
try {
	connection = JDBCUtils.getConnection();
	// 开启事务.禁止自动提交
	connection.setAutoCommit(false);
	String sql1 = "update account set money = money - 200 where name ='aaa'";
	String sql2 = "update account set money = money + 200 where name ='bbb';";
	prepareStatement = connection.prepareStatement(sql1);
	prepareStatement.executeUpdate();
	prepareStatement = connection.prepareStatement(sql2);
	prepareStatement.executeUpdate();
	// 提交事务
	connection.commit();
} catch (Exception e) {
	// 回滚事务
	try {
		connection.rollback();
	} catch (SQLException e1) {
		e1.printStackTrace();
	}
} finally {
	JDBCUtils.release(connection, prepareStatement);
}

事务回滚点 SavePoint(类似与游戏中的存档)
- 当事务特别复杂，有些情况不会回滚到事务的最开始状态，需要将事务回滚到指定位置
核心API
- connection.setSavepoint();// 设置回滚点
- connection.rollback(savepoint);//事务回滚到指定回滚点

Connection connection = null;
PreparedStatement prepareStatement = null;
Savepoint savepoint = null;
try {
	connection = JDBCUtils.getConnection();
	// 开启事务
	connection.setAutoCommit(false);
	// 设置回滚点
	savepoint = connection.setSavepoint();
	String sql = "insert into person values (?,?)";
	prepareStatement = connection.prepareStatement(sql);
	for (int i = 1; i <= 5000; i++) {
		prepareStatement.setInt(1, i);
		prepareStatement.setString(2, "name" + i);
		prepareStatement.addBatch();
		// 模拟错误
		if (i == 3201) {
			int x = 11 / 0;
		}
		// 每隔200条数据执行一次批处理
		if (i % 200 == 0) {
			prepareStatement.executeBatch();
			prepareStatement.clearBatch();
		}
		if (i % 1000 == 0) {
			// 每一千条数据,创建一个回滚点
			savepoint = connection.setSavepoint();
		}
	}
	prepareStatement.executeBatch();
	prepareStatement.clearBatch();
	// 没有异常,提交事务
	connection.commit();
} catch (Exception e) {
	try {
		// 发生异常,事务回滚到指定回滚点
		connection.rollback(savepoint);
		// 提交事务
		connection.commit();
	} catch (SQLException e1) {
		e1.printStackTrace();
	}
} finally {
	JDBCUtils.release(connection, prepareStatement);
}

DBUtils管理事务

主页
Apache DBUtils (轻量级)
- 使用方便
- 只是对JDBC程序进行简单封装，从而简化开发者创建连接、结果集封装、释放资源
核心API
- QueryRunner : 是DBUtils 核心操作类
- ResultSetHandler : 结果集封装处理器
- DbUtils 工具类 : 加载驱动、关闭、事务提交、回滚
QueryRunner
- 如果使用 QueryRunner(DataSource ds) 构造QueryRunner 对象，数据库事务交给DBUtils框架进行管理.默认情况下每条sql就是一个事务,结合以下API使用
  - query(String sql, ResultSetHandler<T> rsh, Object... params)
  - update(String sql, Object... params)
- 如果使用 QueryRunner() 构造QueryRunner , 需要自己管理事务，因为框架没有连接池无法获得数据库连接.,结合以下API使用
  - query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)
  - update(Connection conn, String sql, Object... params)
DbUtils
- commitAndClose(Connection conn) : 提交并释放资源.异常需要自己处理
- commitAndCloseQuietly(Connection conn) : 提交并释放资源.异常由框架处理(其实没处理)
- rollbackAndClose(Connection conn) : 回滚并释放资源.异常需要自己处理
- rollbackAndCloseQuietly(Connection conn) : 回滚并释放资源.异常由框架处理(其实没处理)

将甲扣的钱转到乙案例实现之考虑事务

如果不考虑事务,会导致转账钱丢失的问题.所以必须考虑事务
事务是维持在同一个connection对象里的，保证所有的操作使用同一个连接对象即可
解决方法
- 传递参数Connection
- 线程绑定ThreadLocal
ThreadLocal简介
- 作用 : 把一个操作对象和当前线程绑定在一起. 其内部维护了一个Map集合.key就是当前线程,value就是要绑定的内容
- 常用API
  - set(T value) : 把一个对象和当前线程进行绑定.等价于Map.put(Thread.currentThread(),value)
  - T get() : 获取和当前线程绑定在一起的对象.等价于Map.get(Thread.currentThread())
  - remove() : 移除和当前线程绑定在一起的对象.等价于Map.remove(Thread.currentThread())

事务特性

事务的四大特性：

事务的四大特性（ACID）：
- 原子性(Atomicity)：事务的一组操作不可分割，要么都成功，要么都失败
- 一致性(Consistency)：事务前后数据保持完整性.转账前A和B账户总和2000元，转账后总和还是2000 元
- 隔离性(Isolation)：并发访问时,事务之间是隔离的,一个事务不应该影响其它事务的运行效果
- 持久性(Durability)：当事务一旦提交,事务数据永久存在,无法改变
企业开发中一定要保证事务原子性
事务最复杂问题都是由事务隔离性引起的

隔离性

不考虑事务隔离将引发的问题
- 脏读：一个事务读取另一个事务未提交的数据.这是数据库隔离中最重要的问题
- 不可重复读：一个事务读取另一个事务已提交的数据，在一个事务中两次查询结果不同(针对update操作)
- 虚读：一个事务读取另一个事务插入的数据，造成在一个事务中两次查询记录条数不同(针对insert操作)
数据库为了解决三类隔离引发问题,提供了四个数据库隔离级别（所有数据库通用）
- Serializable ：串行处理.可以解决三类问题
- Repeatable read ：可以解决不可重复读、脏读，但是会发生虚读.是MySQL的默认级别
- read committed : 可以解决脏读,会发生不可重复读、虚读.是Oracle的默认级别
- read uncommitted : 会导致三类问题发生
- 按照隔离级别从高到低排序 : Serializable > Repeatable read > read committed > read uncommitted
- 数据库隔离问题危害的排序 : 脏读> 不可重复读 > 虚读
- 多数数据库厂商都会采用Repeatable read或read committed两个级别.
更改事务隔离级别的语句
- set transaction isolation level 设置事务隔离级别
  - select @@tx_isolation查询当前事务隔离级别

隔离级别引发问题的小实验

脏读问题(read uncommitted)
- 开启两个窗口,执行一次查询,获得一个结果
- 将B窗口隔离级别设置为read uncommitted
  - set session transaction isolation level read uncommitted;
- 在A、B窗口分别开启一个事务 start transaction
- 在A窗口完成转账操作
  - update account set money= money - 200 where name='aaa';
  - update account set money= money +200 where name='bbb';
- 在B窗口进行查询,会读取到A窗口未提交的转账结果
- A窗口进行回滚rollback, B窗口查询结果恢复之前
不可重复读(read committed)
- 开启两个窗口,执行一次查询,获得一个结果
- 将B窗口隔离级别设置为read committed
  - set session transaction isolation level read committed;
- 在A、B窗口分别开启一个事务 start transaction
- 在A窗口完成转账操作
  - update account set money= money - 200 where name='aaa';
  - update account set money= money +200 where name='bbb';
- 此时在B窗口执行查询操作,数据不会发生改变.避免了脏读问题
- A窗口执行commit,B窗口再次执行查询,会读取到A窗口提交的结果.注意此时B窗口没有提交事务,也就是在同一事务中,读取到了两个结果.发生不可重复读问题
虚读(Repeatable read)
- 开启两个窗口,执行一次查询,获得一个结果
- 将B窗口隔离级别设置为Repeatable read
  - set session transaction isolation level repeatable read;
- 在A、B窗口分别开启一个事务 start transaction
- 在A窗口完成转账操作
  - update account set money= money - 200 where name='aaa';
  - update account set money= money +200 where name='bbb';
- 此时在B窗口执行查询操作,数据不会发生改变.避免了脏读问题
- A窗口执行commit,B窗口再次执行查询,数据仍然不会发生改变.避免了不可重复读.
- 此时如果在A窗口插入一条数据,而B窗口可以查询到,就是发生了虚读问题.但是这种情况发生的几率非常小.
Serializable
- 开启两个窗口,执行一次查询,获得一个结果
- 将B窗口隔离级别设置为read serializable
  - set session transaction isolation level serializable;
- 在A、B窗口分别开启一个事务 start transaction
- 在B窗口执行查询操作
- 在A窗口执行插入操作.此时A窗口将会被卡住,不会执行语句.直到B窗口提交或回滚,释放数据库资源

在JDBC中,可以通过Connection.setTransactionIsolation(int level) 来设置隔离级别.如果没有设置.会采用数据库的默认级别

丢失更新问题和悲观锁乐观锁机制

事务丢失更新问题 : 两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。
解决丢失更新的两种方式
- 事务和锁是不可分开的，锁一定是在事务中使用，当事务关闭锁自动释放
- 悲观锁：防止数据收到其他事务影响，在事务提交前，sql语句锁定相关数据，但是数据库性能开销大，长事务难以承受。
  - 假设丢失更新会发生
    - 使用数据库内部锁机制，进行表的锁定，在A修改数据时，A就将数据锁定，B此时无法进行修改
  - 在mysql中默认情况下，当你修改数据，自动为数据加锁（在事务中）,防止两个事务同时修改数据
  - 在mysql内部有两种常用锁
    - 读锁(共享锁)
      - 一张表可以添加多个读锁，如果表被添加了读锁（不是当前事务添加的），该表不可以修改
      - 语法 : select * from account lock in share mode;
      - 共享锁非常容易发生死锁
    - 写锁(排它锁)
      - 一张表只能加一个排它锁，排他锁和其它共享锁、排它锁都具有互斥效果。
      - 如果一张表想添加排它锁，前提是之前表一定没有加过共享锁和排他锁
        
        语法 :　select * from account for update ;
- 乐观锁：基于数据版本记录机制，当前事务提交的数据版本必须大于当前版本才能执行。
  - 假设丢失更新不会发生
  - 使用的不是数据库锁机制，而是一个特殊标记字段 : 数据库timestamp 时间戳字段