【7】PostgreSQL 事務

前言
使用事務
事務內錯誤處理
事務保存點
DDL 事務

前言

在 PostgreSQL 中，每一個操作都是一個事務。即使一個簡單的查詢(select)，這也是一個事務。
例如：

postgres=# select now();now              
-------------------------------2025-07-07 09:08:11.218095+08
(1 row)postgres=#

上述的 select 語句將是一個單獨的事物。

使用事務

在 PostgreSQL 中，如果想要讓多個語句作為同一個事務的一部分，需使用 begin 子句。

begin 語法如下：

postgres=# \h begin;
Command:     BEGIN
Description: start a transaction block
Syntax:
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]where transaction_mode is one of:ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }READ WRITE | READ ONLY[ NOT ] DEFERRABLE
postgres=#

begin 子句將多個 SQL 命令包裝到一個事務中，示例：

postgres=# begin;
BEGIN
postgres=# 
postgres=# select now();now              
-------------------------------2025-07-07 09:15:03.335124+08
(1 row)postgres=#   ### 這里等待了好多秒，
postgres=#   ### 再次執行 select now() 查詢的時間一致。
postgres=#
postgres=# select now();now              
-------------------------------2025-07-07 09:15:03.335124+08
(1 row)postgres=#

上述的 begin 語句示例中，需要注意的 兩次 select 查詢之間其實等待了好多秒（即：第一個 select 查詢后，等待好多秒后，再次執行第二個 select 語句）。

在同一個事務中，兩次 select 查詢之間即使等待好多秒，兩次查詢結果也是一致的。

commit 語法如下：
要結束一個事務，可使用 commit 語句

postgres=# \h commit
Command:     COMMIT
Description: commit the current transaction
Syntax:
COMMIT [ WORK | TRANSACTION ]postgres=#

commit 子句提交一個事務：

postgres=# commit;
COMMIT
postgres=#

另外，在 PostgreSQL 中除了使用 commit 語句提交事務外，還可使用 end 語句。

end 語法如下：

postgres=# \h end;
Command:     END
Description: commit the current transaction
Syntax:
END [ WORK | TRANSACTION ]postgres=# 
postgres=# 
postgres=# ### 和 commit 子句做一個對比，語法是一樣的。
postgres=# \h commit;
Command:     COMMIT
Description: commit the current transaction
Syntax:
COMMIT [ WORK | TRANSACTION ]postgres=#

與提交(commit | end) 對應的命令，有個 rollback 。

rollback 語法如下：

postgres=# \h rollback;
Command:     ROLLBACK
Description: abort the current transaction
Syntax:
ROLLBACK [ WORK | TRANSACTION ]postgres=#

注意

rollback 并不是成功的結束一個事務，僅會停止事務 而不把事務中的部分對其他事務可見。【見描述：abort the current transaction】
與 rollback 含義相同的還有一個 abort 語句

abort 語法如下：

postgres=# \h abort
Command:     ABORT
Description: abort the current transaction
Syntax:
ABORT [ WORK | TRANSACTION ]postgres=#

事務內錯誤處理

在 PostgreSQL 中，只有 沒有發生錯誤的事務才能被提交。

如下示例：
訪問一個不存在的函數 non_existent_function() 故意制作報錯，查看數據庫表現。

postgres=# CREATE TABLE tb_test (
postgres(#     id INT
postgres(# );
CREATE TABLE
postgres=# 
postgres=# begin;
BEGIN
postgres=# insert into tb_test(id) values(1001);
INSERT 0 1
postgres=# 
postgres=# select * from tb_test;id  
------1001
(1 row)
postgres=# 
postgres=#  ### 這里訪問一個不存在的函數，故意制作報錯。
postgres=# SELECT non_existent_function();
ERROR:  function non_existent_function() does not exist
LINE 1: SELECT non_existent_function();^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
postgres=# 
postgres=# 
postgres=# select * from tb_test;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres=# 
postgres=# commit;
ROLLBACK
postgres=# 
postgres=# select * from tb_test;id 
----
(0 rows)postgres=#

上述同樣的操作步驟，看下在 MySQL 中的表現

MySQL 數據庫版本：

[root@localhost][testdb]> select version();
+------------+
| version()  |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.00 sec)

在 MySQL 數據庫中的驗證 SQL 語句：

[root@localhost][testdb]> CREATE TABLE tb_test (->     id INT-> );
Query OK, 0 rows affected (0.01 sec)[root@localhost][testdb]> begin;
Query OK, 0 rows affected (0.00 sec)[root@localhost][testdb]> insert into tb_test(id) values(1001);
Query OK, 1 row affected (0.00 sec)[root@localhost][testdb]> select * from tb_test;
+------+
| id   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)[root@localhost][testdb]> SELECT non_existent_function(); 
ERROR 1305 (42000): FUNCTION testdb.non_existent_function does not exist
[root@localhost][testdb]> 
[root@localhost][testdb]> select * from tb_test;
+------+
| id   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)[root@localhost][testdb]> commit;
Query OK, 0 rows affected (0.00 sec)[root@localhost][testdb]> 
[root@localhost][testdb]> select * from tb_test;
+------+
| id   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)[root@localhost][testdb]>

從上述的驗證結果中，可知：
與 MySQL 不同，PostgreSQL 數據庫在出現錯誤后，即使后續語句在語法和語義上完全正確，也不會再接受任何語句。即便此時發出 COMMIT 語句，PostgreSQL 也會回滾整個事務。

事務保存點

在 PostgreSQL 數據庫中，保存點使用 savepoint 子句，語法如下：

postgres=# \h savepoint;
Command:     SAVEPOINT
Description: define a new savepoint within the current transaction
Syntax:
SAVEPOINT savepoint_namepostgres=#

演示- 示例(01)：
如下示例中，即使有保存點，當遇到報錯時，進行提交。
此時也不會從保存點保存數據，因為：在 PostgreSQL 中，只有 沒有發生錯誤的事務才能被提交。

postgres=# select * from tb_test;id 
----
(0 rows)postgres=# begin;
BEGIN
postgres=# insert into tb_test values(1001);
INSERT 0 1
postgres=# 
postgres=# select * from tb_test;id  
------1001
(1 row)postgres=# savepoint step01;
SAVEPOINT
postgres=# 
postgres=# select 1/0;
ERROR:  division by zero
postgres=# 
postgres=# select * from tb_test;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres=# 
postgres=# commit;
ROLLBACK
postgres=# 
postgres=# select * from tb_test;id 
----
(0 rows)postgres=#

演示- 示例(02)：
如下示例中，有保存點，當遇到報錯時，回退到保存點 進行提交。
這種情況下，事務可以提交成功。

postgres=# select * from tb_test;id 
----
(0 rows)postgres=# begin;
BEGIN
postgres=# insert into tb_test values(1001);
INSERT 0 1
postgres=# savepoint step01;
SAVEPOINT
postgres=# select * from tb_test;id  
------1001
(1 row)postgres=# select 1/0;
ERROR:  division by zero
postgres=# 
postgres=# select * from tb_test;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres=# 
postgres=# rollback to savepoint step01;
ROLLBACK
postgres=# 
postgres=# select * from tb_test;id  
------1001
(1 row)postgres=# commit;
COMMIT
postgres=# 
postgres=# select * from tb_test;id  
------1001
(1 row)postgres=#

演示- 示例(03)：
如下示例中，事務提交后，再嘗試**回退到保存點** 。

postgres=# select * from tb_test;id  
------1001
(1 row)postgres=# 
postgres=# begin;
BEGIN
postgres=# insert into tb_test values(1002);
INSERT 0 1
postgres=# savepoint step01;
SAVEPOINT
postgres=# select * from tb_test;id  
------10011002
(2 rows)postgres=# commit;
COMMIT
postgres=# rollback to savepoint step01;
ERROR:  ROLLBACK TO SAVEPOINT can only be used in transaction blocks
postgres=#

在事務已經被結束之后，將無法再次返回到一個之前的保存點。

DDL 事務

在 PostgreSQL 數據庫中，除了少量（drop database | create tablespace/drop tablespace 等），PostgreSQL中所有的 DDL 都是事務性的。

例如：
在一個事務塊中運行 DDL（改變數據結構命令），在MySQL中，當前事務中的 DDL 將會被隱式提交。但在 PostgreSQL 數據庫中，可回滾。

MySQL 數據庫，示例驗證：

[root@localhost][testdb]> desc tb_test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)[root@localhost][testdb]> 
[root@localhost][testdb]> begin;
Query OK, 0 rows affected (0.00 sec)[root@localhost][testdb]> ALTER TABLE tb_test -> MODIFY COLUMN id CHAR(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0[root@localhost][testdb]> rollback;
Query OK, 0 rows affected (0.00 sec)[root@localhost][testdb]> 
[root@localhost][testdb]> desc tb_test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)[root@localhost][testdb]>

PostgreSQL 數據庫，示例驗證：

postgres=# \d tb_test;Table "public.tb_test"Column |  Type   | Modifiers 
--------+---------+-----------id     | integer | postgres=# 
postgres=# begin;
BEGIN
postgres=# 
postgres=# ALTER TABLE tb_test 
postgres-# ALTER COLUMN id TYPE CHAR(10);
ALTER TABLE
postgres=# 
postgres=# \d tb_test;Table "public.tb_test"Column |     Type      | Modifiers 
--------+---------------+-----------id     | character(10) | postgres=# 
postgres=# rollback;
ROLLBACK
postgres=# 
postgres=# \d tb_test;Table "public.tb_test"Column |  Type   | Modifiers 
--------+---------+-----------id     | integer | postgres=#