深入理解PostgreSQL 事務處理
簡介
PostgreSQL 事務處理（Transaction Processing）是指在數(shù)據(jù)庫中執(zhí)行一系列 SQL 語句，使其成為一個不可分割的操作單元，即 要么全部執(zhí)行成功，要么全部回滾，以確保數(shù)據(jù)的一致性和完整性
準備工作
- 創(chuàng)建演示表
CREATE TABLE "public"."users" ( "user_account" varchar(32) COLLATE "pg_catalog"."default" NOT NULL, "username" varchar(32) COLLATE "pg_catalog"."default", "user_avatar" varchar(64) COLLATE "pg_catalog"."default", "user_profile" varchar(512) COLLATE "pg_catalog"."default", "hashed_password" varchar COLLATE "pg_catalog"."default" NOT NULL );
基本操作
- 提交事務
BEGIN; -- 開啟事務
-- SQL 語句
INSERT INTO users (user_account, hashed_password) VALUES ('Alice', 'xxxx');
COMMIT; -- 提交事務
- 回滾事務
BEGIN DELETE FROM users; ROLLBACK;
- 設置回滾點
SAVEPOINT 允許在事務內部創(chuàng)建回滾點，部分 SQL 語句可以回滾，而不影響其他 SQL
BEGIN;
INSERT INTO users (user_account, hashed_password) VALUES ('Alice', 'xxxx');
SAVEPOINT sp1; -- 創(chuàng)建回滾點
INSERT INTO users (user_account, hashed_password) VALUES ('Alice2', 'xxxx');
SAVEPOINT sp2;
INSERT INTO users (user_account, hashed_password) VALUES ('Alice3', 'xxxx');
ROLLBACK TO sp2;
COMMIT; -- 提交事務
事務隔離級別
簡介
在數(shù)據(jù)庫中，事務隔離級別（Transaction Isolation Levels）用于控制多個事務并發(fā)執(zhí)行時的可見性，避免數(shù)據(jù)不一致的問題。PostgreSQL 遵循 ACID（原子性、一致性、隔離性、持久性） 原則，并提供四種事務隔離級別
| 隔離級別 | 臟讀 | 不可重復讀 | 幻讀 |
|---|---|---|---|
| 讀未提交 | ? 可能發(fā)生 | ? 可能發(fā)生 | ? 可能發(fā)生 |
| 讀已提交（默認） | ? 不會發(fā)生 | ? 可能發(fā)生 | ? 可能發(fā)生 |
| 可重復讀 | ? 不會發(fā)生 | ? 不會發(fā)生 | ? 可能發(fā)生 |
| 可串行化 | ? 不會發(fā)生 | ? 不會發(fā)生 | ? 不會發(fā)生 |
下面我們來逐一介紹
讀未提交/讀已提交
PostgreSQL 不真正支持 讀未提交 這個級別，而是當作 **讀已提交 **處理
即，就算你設置了這個級別，PG 數(shù)據(jù)庫還是會使用 讀已提交 級別事務隔離
臟讀示例（PG 不支持）
BEGIN; -- 事務1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; UPDATE users SET hashed_password = 'new_hash' WHERE user_account = 'Alice'; SELECT txid_current(); -- 查看當前事務id -- 保持事務未提交 BEGIN; -- 事務2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT * FROM users WHERE user_account = 'Alice'; SELECT txid_current(); ROLLBACK; -- 釋放鎖，事務 2 的查詢繼續(xù)執(zhí)行
注意：我的 navacat17 版本，一個查詢頁面執(zhí)行的語句是同一個事務，所以想讓上面語句生效，你可能需要開啟兩個查詢頁面，分別執(zhí)行事務 1 和 2
不可重復讀
- 事務 1
BEGIN; SELECT hashed_password FROM users;
- 事務 2
BEGIN; UPDATE users SET hashed_password = 'xxxx'; COMMIT;
- 事務 1
SELECT hashed_password FROM users;
問題：事務 1 在第一次 SELECT 時看到的是 hashed_password 與 第二次查詢時hashed_password 不一致，這就是不可重復讀
幻讀
- 事務 1
BEGIN; SELECT COUNT(*) FROM users; -- 假設是3
- 事務 2
BEGIN;
INSERT INTO users (user_account, hashed_password) VALUES ('Alice4', 'xxxx');
COMMIT; -- 增加到4
- 事務 1
SELECT COUNT(*) FROM users; -- 增加到4
問題：事務 1 在開始時認為 users 里數(shù)據(jù)為 3，但在事務進行中，別的事務插入了一條數(shù)據(jù)，事務 1 重新查詢時，發(fā)現(xiàn)數(shù)據(jù)數(shù)量變了，這就是幻讀！
其余隔離級別，有需求可自行查詢~
設置事務隔離級別
在事務中設置
BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- SQL 語句 COMMIT;
在會話級別設置
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
- 這會影響當前會話中的所有事務
在 PostgreSQL 配置文件 postgresql.conf 設置（全局）
default_transaction_isolation = 'read committed'
- 影響所有數(shù)據(jù)庫的默認隔離級別
自動提交
PostgreSQL 默認開啟自動提交模式，即每條 SQL 語句都會被自動提交。如果要手動管理事務，需要顯式使用 BEGIN
SET AUTOCOMMIT TO OFF;
到此這篇關于深入理解PostgreSQL 事務處理的文章就介紹到這了,更多相關PostgreSQL 事務處理內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了PostgreSQL游標與索引選擇優(yōu)化案例，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習吧2022-09-09
PostgreSQL數(shù)據(jù)DML誤操作恢復方法
PostgreSQL是一種開源的對象關系型數(shù)據(jù)庫管理系統(tǒng),其DML（Data Manipulation Language）負責數(shù)據(jù)的操作和管理,那么如何DML誤操作了如何恢復,所以本文介紹了PostgreSQL數(shù)據(jù)DML誤操作恢復方法,需要的朋友可以參考下2024-12-12
使用docker compose啟動postgresql的示例代碼
要在啟動 PostgreSQL 容器時執(zhí)行特定的初始化文件,可以使用 Docker 的 docker-entrypoint-initdb.d 目錄,這個目錄下的 SQL 文件會在容器啟動時被自動執(zhí)行,下面是如何修改 Docker Compose 配置文件,以便在啟動時執(zhí)行初始化 SQL 腳本,需要的朋友可以參考下2024-10-10
解決postgresql 數(shù)字轉換成字符串前面會多出一個空格的問題
這篇文章主要介紹了解決postgresql 數(shù)字轉換成字符串前面會多出一個空格的問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了PostgreSQL之連接失敗的問題及解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-05-05
在使用Mysql時,創(chuàng)建表結構時可以通過關鍵字auto_increment來指定主鍵是否自增,但在Postgresql數(shù)據(jù)庫中,雖然可以實現(xiàn)字段的自增,但從本質上來說卻并不支持Mysql那樣的自增,所以本文給大家介紹了PostgreSQL如何設置主鍵自增,需要的朋友可以參考下2024-08-08
使用PostgreSQL創(chuàng)建高級搜索引擎的代碼示例
本文我們將探索PostgreSQL中的全文搜索功能，并研究我們能夠復制多少典型搜索引擎功能，文中有詳細的代碼示例供大家參考,需要的朋友可以參考下2023-07-07
PostgreSQL無法查看表中數(shù)據(jù)問題的原因和解決方法
在 PostgreSQL 中,恢復數(shù)據(jù)庫后,使用 DBeaver 等工具可以看到數(shù)據(jù)庫和表名,但無法查詢到表中數(shù)據(jù),可能有很多原因,本文給大家分析了PostgreSQL無法查看表中數(shù)據(jù)問題的原因和解決方法,需要的朋友可以參考下2025-04-04
PostgreSQL創(chuàng)建新用戶所遇見的權限問題以及解決辦法
這篇文章主要給大家介紹了關于PostgreSQL創(chuàng)建新用戶所遇見的權限問題以及解決辦法, 在PostgreSQL中創(chuàng)建一個新用戶非常簡單,但可能會遇到權限問題,需要的朋友可以參考下2023-09-09
PostgreSQL對GROUP BY子句使用常量的特殊限制詳解
這篇文章主要介紹了PostgreSQL對GROUP BY子句使用常量的特殊限制詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02