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

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

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

设置\setAUTOCOMMIToff

test=#createtabletest1(xint);
CREATETABLE
Time:0.593ms
test=#select*fromtest1;
x
---
(0rows)
Time:0.309ms
test=#rollback;
ROLLBACK
Time:1.501ms
test=#select*fromtest1;
ERROR:relation"test1"doesnotexist
LINE1:select*fromtest1;
^
Time:0.376ms

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

test=#\d
Listofrelations
Schema|Name|Type|Owner
--------+------+-------+-------
public|foo|table|kiwi
public|test|table|kiwi
(2rows)

补充：PostgreSQL-事务与commit优化

基本概念

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

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

属性

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

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

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

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

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

适用场景

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

1.消费者账户减少100元

2.商家账户增加100元

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

事务控制

使用如下命令控制事务

begin或者begintransaction：开始一个事务

commit或者endtransaction：提交事务，执行一系列sql

rollback：事务回滚

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

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

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

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

实例

假设有如下表

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撤销所有操作

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提交事务

runoobdb=#BEGIN;
DELETEFROMCOMPANYWHEREAGE=25;
COMMIT;

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

Python示例

time.clock()
conn=psycopg2.connect(host='172.16.89.80',user="postgres",password="postgres",database="postgres")
cur=conn.cursor()
cur.execute("BEGINTRANSACTION")#开始事务
if__name__=='__main__':
foriinrange(0,1000):
cur.execute('INSERTINTOtest(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优化】

time.clock()
conn=psycopg2.connect(host='172.16.89.80',user="postgres",password="postgres",database="postgres")
cur=conn.cursor()
if__name__=='__main__':
foriinrange(0,1000):
cur.execute('INSERTINTOtest(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命令提交，无需手动开始。【后续有空会验证下这个猜测】

Tags：

转载：感谢您对杨青青个人博客网站平台的认可，以及对我们原创作品以及文章的青睐，非常欢迎各位朋友分享到个人站长或者朋友圈，但转载请说明文章出处“来源杨青个人博客”。http://www.yangqq.com/jstt/bj/421.html

很赞哦！ ()