基于Postgresql 事务的提交与回滚解析

时间:2022-06-01 20:30:11

用过oracle或mysql的人都知道在sqlplus或mysql中,做一个dml语句,如果发现做错了,还可以rollback;掉,但在PostgreSQL的psql中,如果执行一个dml,没有先运行begin;的话,一执行完就马上提交了,不能回滚,这样容易导致误操作的发生,有没有什么办法避免这个风险呢?

当然有,在psql中默认是打开自动提交的,我们可以关闭自动提交,方法如下:

设置\set AUTOCOMMIT off

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
test=# create table test1 (x int);
CREATE TABLE
Time: 0.593 ms
test=# select * from test1;
 x
---
(0 rows)
Time: 0.309 ms
test=# rollback;
ROLLBACK
Time: 1.501 ms
test=# select * from test1;
ERROR: relation "test1" does not exist
LINE 1: select * from test1;
           ^
Time: 0.376 ms

这儿我们需要注意的是,不同步Oracle,PG的DDL事务一样是可以回滚的,并没有隐式提交的概念,这儿我们需要注意下

?
1
2
3
4
5
6
7
test=# \d   
    List of relations
 Schema | Name | Type | Owner
--------+------+-------+-------
 public | foo | table | kiwi
 public | test | table | kiwi
(2 rows)

补充:PostgreSQL-事务与commit优化

基本概念

 

事务 Transaction 是 数据库管理系统DBMS 执行过程中的一个逻辑单元,是一个 sql命令组成的序列。

其特点在于,当事务被提交DBMS后,DBMS需要确保所有的操作被完成;如果事务中有的操作没有成功完成,那么所有操作都将回滚,回滚到事务提交之前的状态

属性

 

事务具有以下四个标准属性

原子性:事务作为一个整体被执行,相当于一个原子

一致性:确保修改前后数据库都满足约束

隔离性:多个事务能并发执行,互不影响

持久性:已被提交的事务对数据库的修改应该永久保存在数据库中

适用场景

 

某人在商店使用电子货币支付100元,包括以下两个操作:

1. 消费者账户减少100元

2. 商家账户增加100元

事务的作用就是保证这两个操作要么都发生,要么都不发生,否则可能出现100元凭空消失。

事务控制

 

使用如下命令控制事务

begin 或者 begin transaction:开始一个事务

commit 或者 end transaction:提交事务,执行一系列sql

rollback:事务回滚

在开始一个事务后,除非遇到 commit 或者 rollback 命令,事务才会被执行;

如果还没遇到 commit 或者 rollback,数据库发生异常,也会自动回滚。

注意,事务命令只能用于 insert、delete、update 操作,而其他命令,比如建表、删表,会被自动提交。

总结一下:事务需要手动开启,手动提交;而且这种方式能提高操作效率。

实例

 

假设有如下表

?
1
2
3
4
5
6
7
8
9
id | name | age | address  | salary
----+-------+-----+-----------+--------
 1 | Paul | 32 | California| 20000
 2 | Allen | 25 | Texas   | 15000
 3 | Teddy | 23 | Norway  | 20000
 4 | Mark | 25 | Rich-Mond | 65000
 5 | David | 27 | Texas   | 85000
 6 | Kim  | 22 | South-Hall| 45000
 7 | James | 24 | Houston  | 10000

操作1:开始事务,从表中删除年龄为25的记录,最后用rollback撤销所有操作

?
1
2
3
runoobdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;

结果如下

?
1
2
3
4
5
6
7
8
9
id | name | age | address  | salary
----+-------+-----+-----------+--------
 1 | Paul | 32 | California| 20000
 2 | Allen | 25 | Texas   | 15000
 3 | Teddy | 23 | Norway  | 20000
 4 | Mark | 25 | Rich-Mond | 65000
 5 | David | 27 | Texas   | 85000
 6 | Kim  | 22 | South-Hall| 45000
 7 | James | 24 | Houston  | 10000

我们发现原表没有任何改变

操作2:开始事务,从表中删除年龄为25的记录,最后用commit提交事务

?
1
2
3
runoobdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

此时我们发现表中age为25的已删除。

Python 示例

?
1
2
3
4
5
6
7
8
9
10
11
time.clock()
conn = psycopg2.connect(host='172.16.89.80',user="postgres",password="postgres",database="postgres")
cur = conn.cursor()
cur.execute("BEGIN TRANSACTION")    # 开始事务
if __name__=='__main__':
  for i in range(0,1000):
    cur.execute('INSERT INTO test(a, b, c, d) VALUES (%d, %d, %d, %d);'%(i, i, i, i))
  cur.execute('commit')     # 提交事务
  cur.close()
  conn.close()
  print(time.clock())

执行成功,耗时约 2s

继续尝试

 

上面手动开始了事务,后面我做了如下尝试,发现耗时只有 1s      【commit 优化】

?
1
2
3
4
5
6
7
8
9
10
time.clock()
conn = psycopg2.connect(host='172.16.89.80',user="postgres",password="postgres",database="postgres")
cur = conn.cursor()
if __name__=='__main__':
  for i in range(0,1000):
    cur.execute('INSERT INTO test(a, b, c, d) VALUES (%d, %d, %d, %d);'%(i, i, i, i))
  conn.commit()
  cur.close()
  conn.close()
  print(time.clock())

执行了一系列sql,最后来个 commit,同样执行成功,且耗时更少,我猜测是python自动开始了事务,以 commit 命令提交,无需手动开始。【后续有空会验证下这个猜测】

以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。如有错误或未考虑完全的地方,望不吝赐教。

原文链接:https://blog.csdn.net/kiwi_kid/article/details/50881919