基于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撤销所有操作
runoobdb=#BEGIN; DELETEFROMCOMPANYWHEREAGE=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命令提交,无需手动开始。【后续有空会验证下这个猜测】
以上为个人经验,希望能给大家一个参考,也希望大家多多支持毛票票。如有错误或未考虑完全的地方,望不吝赐教。