当前位置: 首页>后端>正文

mysql 查出事务ID 如何删除 mysql 事务sql

事务及四大特性

什么是事务

数据库事务(Database Transaction),是指作为单个逻辑工作单元执行的一系列操作,要么完全地执行,要么完全地不执行。
简单的说:事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行,要么都执行成功,要么都执行失败,即都执行成功才算成功,否则就会恢复到这堆SQL执行之前的状态。
下面以银行转账为例,张三转100块到李四的账户,这至少需要两条SQL语句:

  • 给张三的账户减去100元;
update 账户表 set money=money-100 where name='张三';
  • 给李四的账户加上100元。
update 账户表 set money=money+100 where name='李四';

如果在第一条SQL语句执行成功后,在执行第二条SQL语句之前,程序被中断了(可能是抛出了某个异常,也可能是其他什么原因),那么李四的账户没有加上100元,而张三却减去了100元,在现实生活中这肯定是不允许的。
如果在转账过程中加入事务,则整个转账过程中执行的所有SQL语句会在一个事务中,而事务中的所有操作,要么全都成功,要么全都失败,不可能存在成功一半的情况。
也就是说给张三的账户减去100元如果成功了,那么给李四的账户加上100元的操作也必须是成功的;否则,给张三减去100元以及给李四加上100元都是失败的。

事务的四大特性

事务的四大特性(ACID)是:
(1)原子性(Atomicity):事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功,要么全部执行失败。
(2)一致性(Consistency):事务执行后,数据库状态与其它业务规则保持一致。如转账业务,无论事务执行成功与否,参与转账的两个账户金额之和在事务前后应该是保持不变的。

张三:1000	1000-500=500		1000
李四:1000	1000+500=1500 		1000

(3)隔离性(Isolation):隔离性是指在并发操作中,不同事务之间应该隔离开来,使每个并发中的事务不会相互干扰。也就是说,在事中务查看数据更新时,数据所处的状态要么是另一事务修改它之前的状态,要么是另一事务修改它之后的状态,事务不会查看到中间状态的数据。例如:在A事务中,查看另一B事务(正在修改张三的账户金额)中张三的账户金额,要查看到B事务之前的张三的账户金额,要么查看到B事务之后张三的账户金额。

事务1: 查询A、B账户金额之和(1000+1000、500+1500、1000+1000)
事务2: A转账给B 500元
		A - 500 = 500
		B + 500 = 1500

(4)持久性(Durability):一旦事务提交成功,事务中所有的数据操作都必须被持久化到数据库中,即使提交事务后,数据库马上崩溃,在数据库重启时,也必须能保证通过某种机制恢复数据。

开启事务---A给B转账500元
A: 1000 - 500 = 500	(成功了)	在日志中记录,事务成功,A账户金额更新为500
B: 1000 + 500 = 1500 (成功了)	在日志中记录,事务成功,B账户金额更新为1500
结束事务---回滚/提交

MySQL中的事务

在默认情况下,MySQL每执行一条SQL语句,都是一个单独的事务。因为底层在执行SQL语句之前会自动开启事务,在SQL语句执行完后,会立即结束事务!
如果需要在一个事务中包含多条SQL语句,那么需要手动开启事务和结束事务。

  • 开启事务:start transaction;
  • 结束事务:commit(提交事务)或 rollback(回滚事务)。 在执行SQL语句之前,先执行 strat transaction,这就开启了一个事务(事务的起点),然后可以去执行多条SQL语句,最后要结束事务,commit表示提交,即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback,表示回滚,即回滚到事务的起点,之前做的所有操作都被撤消了!
    下面演示A账户给B账户转账的例子:
    准备数据:
-- 1、创建数据库jt_db数据库(如果不存在才创建)
create database if not exists jt_db charset utf8;
use jt_db; -- 选择jt_db数据库
-- 2、在 jt_db 库中创建 acc 表(银行账户表),要求有id(主键),name(姓名),money(账户金额)
drop table if exists acc;
create table acc(
    id int primary key auto_increment,
    name varchar(50),
    money double
);
-- 3、往 acc 表中, 插入2条记录
insert into acc values(null,'A',1000);
insert into acc values(null,'B',1000);
-- 查询acc表中的所有记录
select * from acc;

下面分别演示事务开启及执行一系列SQL之后,回滚事务、提交事务及中断操作的效果。
rollback(回滚事务)

-- 查询acc账户表中A和B的金额
select * from acc;
-- 开启事务
start transaction;
-- 开始转账,A账户减去100元
update acc set money=money-100 where name='A';
-- 查询acc账户表中A和B的金额
select * from acc;
-- B账户增加100元
update acc set money=money+100 where name='B';
-- 查询acc账户表中A和B的金额
select * from acc;
-- 回滚事务
rollback;
-- 再次查询acc账户表中A和B的金额
select * from acc;

commit(提交事务):将上面的操作再做一次,最后将rollback替换为commit,即提交事务

commit;

中断操作:将上面的操作再做一次,最后将rollback替换为quit,即中断操作

quit;

事务并发读问题

事务并发读问题

多个事务对相同的数据同时进行操作,这叫做事务并发。
在事务并发时,如果没有采取必要的隔离措施,可能会导致各种并发问题,破坏数据的完整性等。这些问题中,其中有三类是读问题,分别是:脏读、不可重复读、幻读。
(1)脏读(dirty read):在一个事务中,读取到另一个事务未提交更新的数据,即读取到了脏数据;
例如:在一个事务中,A给B转账100元但未提交事务,在另一事务中查询账户金额,查询到了A未提交更新的数据,我们称之为脏读。

提示:需要将数据库的事务隔离级别设置为最低,才能够看到脏读现象
事务1:开启事务; A - 100 = 900; B + 100 = 1100; (没有提交事务)
事务2:开启事务; 查询B账户的金额 1100, 这个过程叫做脏读, 1100就是一个脏数据

(2)不可重复读(unrepeatable read):对同一记录的两次读取结果不一致,因为在两次查询期间,有另一事务对该记录做了修改(是针对修改操作)
例如:在事务1中,前后两次查询A账户的金额,在两次查询之间,另一事物2对A账户的金额做了修改(并且也提交了事务),此种情况可能会导致事务1中,前后两次查询的结果不一致。这就是不可重复读。

事务1:开启事务---
	第一次读取A账户的金额:1000
	第二次读取A账户的金额:900
事务2:开启事务---
	A账户 - 100 = 900;
	提交事务---

(3)幻读(虚读)(phantom read):对同一张表的两次查询结果不一致,因为在两次查询期间,有另一事务进行了插入或者是删除操作(是针对插入或删除操作);

事务1:开启事务---
	select * from acc where id=3;//不存在id为3的记录
	insert into acc value(3,'C',2000);
	select * from acc where id=3;//存在id为3的记录
事务2:开启事务---
	insert into acc value(3,'C',2000);
	提交事务---

注意:mysql默认的是不允许出现脏读和不可重复读,所以在下面演示之前需要设置mysql允许出现脏读、不可重复读等。

set tx_isolation='read-uncommitted'; -- 设置mysql的事务隔离级别

1、脏读示例:

-- 在窗口1中,开启事务,执行A给B转账100元
set tx_isolation='read-uncommitted'; -- 允许脏读、不可重复读、幻读
use jt_db; -- 选择jt_db库
start transaction; -- 开启事务
update acc set money=money-100 where name='A';
update acc set money=money+100 where name='B';
-- 在窗口2中,开启事务,查询B的账户金额
set tx_isolation='read-uncommitted'; -- 允许脏读、不可重复读、幻读
use jt_db; -- 选择jt_db库
start transaction; -- 开启事务
select * from acc where name='B'; -- 出现脏数据
-- 切换到窗口1,回滚事务,撤销转账操作。
rollback; -- 回滚事务
-- 切换到窗口2,查询B的账户金额
select * from acc where name='B';

在窗口2中,B看到自己的账户增加了100元(此时的数据A操作事务并未提交),此种情况称之为"脏读"。
2、不可重复读示例:

-- 在窗口1中,开启事务,查询A账户的金额
set tx_isolation='read-uncommitted'; -- 允许脏读、不可重复读、幻读
use jt_db; -- 选择jt_db库
start transaction; -- 开启事务
select * from acc where name='A';
-- 在窗口2中,开启事务,查询A的账户金额减100
set tx_isolation='read-uncommitted'; -- 允许脏读、不可重复读、幻读
use jt_db; -- 选择jt_db库
start transaction; -- 开启事务
update acc set money=money-100 where name='A'; -- A账户减去100
select * from acc where name='A';
commit; -- 提交事务
-- 切换到窗口1,再次查询A账户的金额。
select * from acc where name='A'; -- 前后查询结果不一致

在窗口1中,前后两次对同一数据(账户A的金额)查询结果不一致,是因为在两次查询之间,另一事务对A账户的金额做了修改。此种情况就是"不可以重复读"
3、幻读示例:

-- 在窗口1中,开启事务,查询账户表中是否存在id=3的账户
set tx_isolation='read-uncommitted'; -- 允许脏读、不可重复读、幻读
use jt_db; -- 选择jt_db库
start transaction; -- 开启事务
select * from acc where id=3;
-- 在窗口2中,开启事务,往账户表中插入了一条id为3记录,并提交事务。
-- 设置mysql允许出现脏读、不可重复度、幻读
set tx_isolation='read-uncommitted';
use jt_db; -- 选择jt_db库
start transaction; -- 开启事务
insert into acc values(3, 'C', 1000);
commit; -- 提交事务
-- 切换到窗口1,由于上面窗口1中查询到没有id为3的记录,所以可以插入id为3的记录。
insert into acc values(3, 'C', 1000); -- 插入会失败!

在窗口1中,查询了不存在id为3的记录,所以接下来要执行插入id为3的记录,但是还未执行插入时,另一事务中插入了id为3的记录并提交了事务,所以接下来窗口1中执行插入操作会失败。
探究原因,发现账户表中又有了id为3的记录(感觉像是出现了幻觉)。这种情况称之为"幻读"
以上就是在事务并发时常见的三种并发读问题,那么如何防止这些问题的产生?
可以通过设置事务隔离级别进行预防。

事务隔离级别

事务隔离级别分四个等级,在相同数据环境下,对数据执行相同的操作,设置不同的隔离级别,可能导致不同的结果。不同事务隔离级别能够解决的数据并发问题的能力也是不同的。

set tx_isolation='read-uncommitted';

1、READ UNCOMMITTED(读未提交数据)
安全性最差,可能出现任何事务并发问题(比如脏读、不可以重复读、幻读等)
但性能最好(不使用!!)
2、READ COMMITTED(读已提交数据)(Oracle默认)
安全性较差
性能较好
可以防止脏读,但不能防止不可重复读,也不能防止幻读;
3、REPEATABLE READ(可重复读)(MySQL默认)
安全性较高
性能较差
可以防止脏读不可重复读,但不能防止幻读问题;
4、SERIALIZABLE(串行化)
安全性最高,不会出现任何并发问题,因为它对同一数据的访问是串行的,非并发访问;
性能最差;(不使用!!)
MySQL的默认隔离级别为REPEATABLE READ,即可以防止脏读和不可重复读

设置隔离级别(了解)

MySQL查询当前的事务隔离级别

select @@tx_isolation;

1、MySQL设置事务隔离级别(了解)
(1) set tx_isolation=‘read-uncommitted’; 
安全性最差,容易出现脏读不可重复读幻读,但性能最高
(2) set tx_isolation=‘read-committed’;
安全性一般,可防止脏读,不能防止不可重复读幻读
(3) set tx_isolation=‘repeatable-read’;
安全性较好,可防止脏读不可重复读,但不能防止幻读
(4) set tx_isolation=‘serialiable’;
安全性最好,可以防止一切事务并发问题,但是性能最差。
2、JDBC设置事务隔离级别
JDBC中通过Connection提供的方法设置事务隔离级别:

Connection.setTransactionIsolation(int level)

参数可选值如下:

Connection.TRANSACTION_READ_UNCOMMITTED 1(读未提交数据)
Connection.TRANSACTION_READ_COMMITTED 2(读已提交数据)
Connection.TRANSACTION_REPEATABLE_READ 4(可重复读)
Connection.TRANSACTION_SERIALIZABLE 8(串行化)
Connection.TRANSACTION_NONE 0(不使用事务)

提示:在开发中,一般情况下不需要修改事务隔离级别
3、JDBC中实现转账例子
提示:JDBC中默认是自动提交事务,所以需要关闭自动提交,改为手动提交事务
也就是说, 关闭了自动提交后, 事务就自动开启, 但是执行完后需要手动提交或者回滚!!
(1)执行下面的程序,程序执行没有异常,转账成功!A账户减去100元,B账户增加100元。
(2)将第4步、5步中间的代码放开,再次执行程序,在转账过程中抛异常,转账失败!由于事务回滚,所以A和B账户金额不变。

public static void main(String[] args) throws SQLException {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
        //1.获取连接
        conn = JdbcUtil.getConn();
        //2.关闭JDBC自动提交事务(默认开启事务)
        conn.setAutoCommit(false);
        //3.获取传输器
        stat = conn.createStatement();
        /* ***** A给B转账100元 ***** */
        //4.A账户减去100元
        String sql = "update acc set money=money-100 where name='A'";
        stat.executeUpdate(sql);
        //int i = 1/0; // 让程序抛出异常,中断转账操作
        //5.B账户加上100元
        sql = "update acc set money=money+100 where name='B'";
        stat.executeUpdate(sql);
        //6.手动提交事务
        conn.commit();
        System.out.println("转账成功!提交事务...");
    } catch (Exception e) {
    	e.printStackTrace();
    	//一旦其中一个操作出错都将回滚,使两个操作都不成功
    	conn.rollback();
   		System.out.println("执行失败!回滚事务...");
    } finally{
    	JdbcUtil.close(conn, stat, rs);
    }
}



https://www.xamrdz.com/backend/3yd1922044.html

相关文章: