【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=#
若有轉載,請標明出處:
https://blog.csdn.net/CharlesYuangc/article/details/149165365