19 数据库事务(Transaction)详解

62次阅读
没有评论

共计 3550 个字符,预计需要花费 9 分钟才能阅读完成。

一. 事务

1. 什么是事务

  • 事务是由一条或多条 SQL 语句组成的逻辑执行单元, 可以比喻成一个容器, 里面放的就是一堆 SQL 语句, 这些语句要么全部执行成功, 要么一个都无法执行成功 ( 原子性)

2. 为什么使用事务

  • 对数据进行一系列的操作的时候, 为了防止这些操作中部分操作成功而另一些操作失败, 从而造成数据的不正确性, 这个时候我们就需要使用事务将其回滚到原来的状态

3. 如何使用事务

  • 关键字
🍎开启事务
begin;  # 或者下面的语句  
start transaction;

🍎事务回滚(回滚到之前的状态, 并关闭事务)
rollback;  # 回滚 + 关闭

🍎事务提交(将修改提交, 并关闭事务)
commit;    # 提交 + 关闭

由上面关键字可以看出, 一个事务的开始对应一个回滚或者提交, 之后就需要重新开启事务

  • 银行余额示例
🍎先创建一个用户余额表并插入记录
create table user(
    id int primary key auto_increment,
    name varchar(16) not null,
    balance int not null
);

insert user(name,balance) value
    ("shawn",150000),
    ("song",20000),
    ("xing",520022),
    ("hai",10000);

select * from user;  # 查看一下所有记录

🍎开启事务
begin;  # 或者 start transaction;

🍎更新记录
update user set balance=100 where name="shawn";
update user set balance=100 where name="song";
update user set balance=100 where name="xing";
update user set balance=200 where name="hai";

select * from user;  # 查看一下是否修改成功

🍎事务回滚
rollback;  # 回滚之后, 该事务就关闭了
select * from user;  # 查看一下是否回滚到原来的数据

🍎再开启一个事务
begin;

🍎再次对数据进行更新
update user set balance=200 where name="shawn";
update user set balance=200 where name="song";
commit;  # 事务提交, 并关闭了该事务
select * from user;  # 查看数据的变化

rollback;  # 再次使用事务回滚将不在起作用, 因为事务已经关闭了 
select * from user;

19 数据库事务 (Transaction) 详解

19 数据库事务 (Transaction) 详解

19 数据库事务 (Transaction) 详解

  • pymysql 实现事务处理实例
select * from user where id=1;

19 数据库事务 (Transaction) 详解

import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    database="test02",
    charset="utf8"
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql1 = "select * from user;"
sql2 = "update user set name=%s where id=1;"
sql3 = "select * from user;"

try:
    cursor.execute(sql1)  # 执行 sql1
    print(cursor.fetchall())
    rows = cursor.execute(sql2,("song",))  # 执行 sql2
    print("row : %s"%rows)
    cursor.execute(sql3)  # 执行 sql3
    print(cursor.fetchall())
except Exception as E:
    conn.rollback()  # 事务回滚 (在 pymysql 其实可以不用回滚, 检测到异常这一段就结束了不会提交)
    print(f" 执行失败 : {E}")
else:
    conn.commit()    # 事务提交

cursor.close()
conn.close()

19 数据库事务 (Transaction) 详解

二. 事务的四大特征 (ACID)

1. 原子性(Atomicity)

  • 事务是一段程序的最小执行单元, 不可再分(就如同自然界的原子不可再分), 所以事务中的操作要么都成功, 要么都失败

2. 一致性(Consistency)

  • 事务的执行, 必须使数据库从一个一致性状态, 变成另一个一致性状态, 一致性是通过原子性来保证的

3. 隔离性(Lsolation)

  • 各个事务的执行互不干扰, 任意一个事务的内部操作对其他并发的事务, 都是隔离的; 也就是说 : 并发执行的事务之间不能看到对方的中间状态, 并发执行的事务之间不能相互影响

4. 持续性(Durability)

  • 也叫 "持久性", 指事务一旦提交, 对数据的任何改变都记录到永久存储器中, 通常是物理数据库

三. 事务的三种运行模式

隐式可以比喻成自动

显式可以比喻成手动

1. 自动提交事务 (隐式开启, 隐式提交)

每条单独的 SQL 语句都是一个事务, 语句执行完之后会自动执行 commit 操作, 如果出现错误则进行事务回滚至之前的状态

  • mysql 默认开启自动提交事务, 可通过下面语句来开启或关闭自动提交事务模式
set session autocommit=0;  
# 0 是关闭, 1 是开启
# [session 是当前会话变量 \ global 是全局变量]
  • SQL server 默认开启自动提交事务, 通过下面语句来关闭或开启
set implicit_transactions ON;  
# ON 是打开隐式事务模式或关闭自动事务模式
# OFF 是关闭隐式事务模式或打开自动提交事务模式
  • Oracle 默认显示提交事务, 通过下面语句开启或关闭自动提交事务模式
set autocommit on;
# on 是开启自动事务模式
# off 是关闭自动提交事务模式

2. 显式事务 (显式开启, 显式提交)

通过指定事务开始语句来显示的开启事务, 并由提交或者回滚命令来结束事务

  • mysqlstart transaction 或者 begin 语句开始事务, 以 commit 或者 rollback 语句结束事务

3. 隐式事务 (隐式开启, 显示提交)

在隐式事务中, 不需要使用 begin 或者 start transaction 来开启事务, 每个 SQL 语句第一次执行会自动开启事务, 但需要使用 commit 提交或者 rollback 回滚来结束事务

  • mysql 中通过关闭自动提交事务, 从而来达到隐式开启事务, 显示提交事务的目的
set session autocommit=0;
# 0 是关闭, 1 是开启
# [session 是当前会话变量 \ global 是全局变量]

4. 总结

MySQL 默认为每条 SQL 语句开启事务, 并且会在本条 SQL 执行完毕后自动执行 commit 操作进行提交, 设置手动提交的两种方式:

  • 直接设置 Mysql 的提交模式
set session|global autocommit=0  # 禁止自动提交
set session|global autocommit=1  # 开启自动提交
  • 手动开启事务
begin;   # 或者 start transaction;
    [SQL 语句]
commit;  # 或者 rollback;

四. 事务保存点 (savepoint)

1. 什么是事务的保存点

  • savepoint 类似于虚拟机中的快照, 运用于事务中
  • 每设置一个 savepoint 就是一个保存点
  • 当事务结束时会自动删除定义的所有保存点
  • 在事务没有结束之前可以回退到任意保存点

2. 设置和回滚保存点语法

savepoint [保存点名字];        # 设置保存点
rollback to [某个保存点名字];  # 回滚到某个保存点, 该保存点之后的操作都无效包括保存点
rollback;  # 回滚开启事务后的所有操作, 并删除所有保存点, 并结束事务

3. 事务保存点实验

  • 设置保存点, 可以设置很多个(one、two、three)
begin;
select * from user;
update user set name="shawn" where id=1;
savepoint one;
select * from user;
update user set name="xxxx" where id=3;
savepoint two;
select * from user;
delete from user where id>3;
savepoint three;
select * from user;

19 数据库事务 (Transaction) 详解

19 数据库事务 (Transaction) 详解

  • 回退到某个保存点, 可以跨越多个保存点
rollback to two;
select * from user;

19 数据库事务 (Transaction) 详解

  • 保存点 two 之后创建的保存点 three 也失效了
rollback to three;

19 数据库事务 (Transaction) 详解

  • rollback 回滚所有
rollback

19 数据库事务 (Transaction) 详解

五. 事务使用的原则

  • 保持事务短小
  • 尽量避免事务中的 rollback
  • 尽量避免 savepoint
  • 显式声明打开事务
  • 默认情况下,依赖于悲观锁,为吞吐量要求苛刻的事务考虑乐观锁
  • 锁的行越少越好,锁的时间越短越好

---end---

正文完
 
shawn
版权声明:本站原创文章,由 shawn 2023-06-16发表,共计3550字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(没有评论)