基于Postgresql 事务的提交与回滚解析
用过oracle或mysql的人都知道在sqlplus或mysql中,做一个dml语句,如果发现做错了,还可以rollback;掉,但在PostgreSQL的psql中,如果执行一个dml,没有先运行begin;的话,一执行完就马上提交了,不能回滚,这样容易导致误操作的发生,有没有什么办法避免这个风险呢?
当然有,在psql中默认是打开自动提交的,我们可以关闭自动提交,方法如下:
设置\set AUTOCOMMIT off
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事务一样是可以回滚的,并没有隐式提交的概念,这儿我们需要注意下
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 操作,而其他命令,比如建表、删表,会被自动提交。
总结一下:事务需要手动开启,手动提交;而且这种方式能提高操作效率。
实例
假设有如下表
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; DELETE FROM COMPANY WHERE AGE = 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; DELETE FROM COMPANY WHERE AGE = 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("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 优化】
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 命令提交,无需手动开始。【后续有空会验证下这个猜测】
以上为个人经验,希望能给大家一个参考,也希望大家多多支持猪先飞。如有错误或未考虑完全的地方,望不吝赐教。
相关文章
- 这篇文章主要介绍了PostgreSQL判断字符串是否包含目标字符串的多种方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-02-23
- 这篇文章主要介绍了PostgreSQL TIMESTAMP类型 时间戳操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-26
- 这篇文章主要介绍了postgresql 实现多表关联删除操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-02
- 这篇文章主要介绍了Postgresl 如何选择正确的关闭模式,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-18
- 这篇文章主要介绍了postgresql数据添加两个字段联合唯一的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04
- artDialog是一个基于javascript编写的对话框组件,它拥有精致的界面与友好的接口。本文给大家介绍JS中artdialog弹出框控件之提交表单思路详解,对本文感兴趣的朋友一起学习吧...2016-04-19
- 这篇文章主要介绍了PostgreSQL 字符串处理与日期处理操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-01
- 因此,正确的原子操作是真正被执行过的。是物理执行。在当前事务中确实能看到插入的记录。最后只不过删除了。但是AUTO_INCREMENT不会应删除而改变值。1、为什么auto_increament没有回滚?因为innodb的auto_increament的...2014-05-31
- 这篇文章主要介绍了postgresql重置序列起始值,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-04
- 这篇文章主要介绍了postgresql 中的时间处理小技巧(推荐),本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-03-29
- 通过合理的设计,可以将选择一定的规则,将大表切分多个不重不漏的子表,这就是传说中的partitioning。比如,我们可以按时间切分,每天一张子表,比如我们可以按照某其他字段分割,总之了就是化整为零,提高查询的效能...2020-07-11
- 这篇文章主要介绍了基于postgresql数据库锁表问题的解决,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-30
- 这篇文章主要介绍了angularjs $http实现form表单提交示例,非常具有实用价值,需要的朋友可以参考下 ...2017-06-15
- 这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25
- 这篇文章主要介绍了PostgreSQL 中的单引号与双引号用法说明,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-01
- 这篇文章主要介绍了postgresql 利用xlog进行热备操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-16
- 这篇文章主要介绍了vue项目,代码提交至码云,iconfont的用法说明,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-07-30
- 这篇文章主要介绍了Postgresql数据库之创建和修改序列的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04
- 这篇文章主要介绍了Postgresql中xlog生成和清理逻辑操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-16
- 这篇文章主要介绍了postgresql影子用户实践场景分析,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-03-06