用過oracle或mysql的人都知道在sqlplus或mysql中，做一個dml語句，如果發(fā)現(xiàn)做錯了，還可以rollback;掉，但在PostgreSQL的psql中，如果執(zhí)行一個dml，沒有先運行begin;的話，一執(zhí)行完就馬上提交了，不能回滾，這樣容易導致誤操作的發(fā)生，有沒有什么辦法避免這個風險呢？
當然有，在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優(yōu)化
基本概念
事務 Transaction 是 數(shù)據(jù)庫管理系統(tǒng)DBMS 執(zhí)行過程中的一個邏輯單元，是一個 sql命令組成的序列。
其特點在于，當事務被提交DBMS后，DBMS需要確保所有的操作被完成；如果事務中有的操作沒有成功完成，那么所有操作都將回滾，回滾到事務提交之前的狀態(tài)
屬性
事務具有以下四個標準屬性
原子性：事務作為一個整體被執(zhí)行，相當于一個原子
一致性：確保修改前后數(shù)據(jù)庫都滿足約束
隔離性：多個事務能并發(fā)執(zhí)行，互不影響
持久性：已被提交的事務對數(shù)據(jù)庫的修改應該永久保存在數(shù)據(jù)庫中
適用場景
某人在商店使用電子貨幣支付100元，包括以下兩個操作：
1. 消費者賬戶減少100元
2. 商家賬戶增加100元
事務的作用就是保證這兩個操作要么都發(fā)生，要么都不發(fā)生，否則可能出現(xiàn)100元憑空消失。
事務控制
使用如下命令控制事務
begin 或者 begin transaction：開始一個事務
commit 或者 end transaction：提交事務，執(zhí)行一系列sql
rollback：事務回滾
在開始一個事務后，除非遇到 commit 或者 rollback 命令，事務才會被執(zhí)行；
如果還沒遇到 commit 或者 rollback，數(shù)據(jù)庫發(fā)生異常，也會自動回滾。
注意，事務命令只能用于 insert、delete、update 操作，而其他命令，比如建表、刪表，會被自動提交。
總結(jié)一下：事務需要手動開啟，手動提交；而且這種方式能提高操作效率。
實例
假設有如下表
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;
結(jié)果如下
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
我們發(fā)現(xiàn)原表沒有任何改變
操作2：開始事務，從表中刪除年齡為25的記錄，最后用commit提交事務
runoobdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;
此時我們發(fā)現(xiàn)表中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())
執(zhí)行成功，耗時約 2s
繼續(xù)嘗試
上面手動開始了事務，后面我做了如下嘗試，發(fā)現(xiàn)耗時只有 1s 【commit 優(yōu)化】
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())
執(zhí)行了一系列sql，最后來個 commit，同樣執(zhí)行成功，且耗時更少，我猜測是python自動開始了事務，以 commit 命令提交，無需手動開始。【后續(xù)有空會驗證下這個猜測】
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- postgresql通過索引優(yōu)化查詢速度操作
- Postgresql在mybatis中報錯:操作符不存在:character varying == unknown的問題
- postgresql 如何關閉自動提交
- Postgresql 解決pg掉電后無法重啟的問題
- PostgreSQL 恢復誤刪數(shù)據(jù)的操作
- PostgreSQL 日志文件的所在位置
- PostgreSQL 打印日志信息所在的源文件和行數(shù)的實例
- PostgreSQL的B-tree索引用法詳解
