PostgreSQL:鎖
鎖問題
-
髒讀:一個事務可以讀到另一個事務中未提交的資料
-
不可重複讀:在一個事務沒有結束時,另外一個事務也訪問該同一資料集合,並做了 DML 操作。因此,在第一個事務兩次讀資料之間,讀到的資料可能是不一樣的
-
幻讀(丟失更新):一個事務的更新操作會被另一個事務的更新操作所覆蓋,從而導致資料的不一致。
隔離級別
-
READ UNCOMMITTED
-
允許事務檢視其他事務所進行的未提交更改
-
允許“髒”讀、 不可重複讀和虛讀。
-
READ COMMITTED
-
允許事務檢視其他事務所進行的已提交更改
-
不允許“髒”讀。 但允許不可重複讀和虛讀。
-
只支援二進位制格式為 ROW 情況下
-
REPEATABLE READ
-
確保每個事務的 SELECT 輸出一致
-
SERIALIZABLE
-
將一個事務的結果與其他事務完全隔離, 即一個事務所選的行不能由其他事務更改, 直到該事務完成為止。
-
分散式事務常用。
鎖機制
-
表級鎖模式
鎖模式 描述 ACCESS SHARE SELECT 命令將在所引用的表上加此型別的鎖。通常只讀取表而不修改表的查詢都會請求這種鎖模式 ROW SHARE SELECT FOR UPDATE 和 SELECT FOR SHARE 命令會在目標表上加此型別的鎖 ROW EXCLUSIVE UPDATE、DELETE、INSERT 命令會自動在所修改的表上請求加這個鎖。通常,修改表中資料的命令都是加這種鎖 SHARE UPDATE EXCLUSIVE 這個模式保護一個表,在模式改變和執行 VACUUM 併發、ANALYZE/CREATE INDEX CONCURR-ENTLY 命令請求這樣的鎖 SHARE 這個模式可避免表的併發資料修改。CREATE INDEX (不帶 CONCURRENTLY 選項)語句要求這樣的鎖模式 SHARE ROW EXCLUSIVE 任何 PostgreSQL 命令都不會自動請求這個鎖模式 EXCLUSIVE 這個模式只允許併發 ACCESS SHARE 鎖,也就是說,只有對錶的度動作可以和持有這個鎖的事務併發執行。任何 PostgreSQL 命令都不會使用者表上自動請求這個鎖模式。不過,在執行某些操作時,會在某些系統上請求這個鎖。 ACCESS EXCLUSIVE 這個模式只能保證只有一個人訪問此表。ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL 命令要求這樣的鎖。在 LOCK TABLE 命令中沒有明確宣告需要的鎖模式時,它是預設鎖模式 -
行級鎖模式:只有兩種,即“共享鎖”和“排他鎖”,或者可以說是“讀鎖”或“寫鎖”。而在 PostgreSQL 中不稱其為“讀鎖”的原因是,由於有多版本的實現,所以實際讀取資料時,並不會在行上執行任何鎖。
表級鎖命令
LOCK [ TABLE ] [ ONLY ] name [, ...] [ IN lockmode MODE ] [ NOWAIT ]
-
name:表名
-
lockmode:八種鎖模式
-
NOWAIT:如果沒有 NOWAIT 這個關鍵字時,當無法獲得鎖時,會一直等待,而如果加了 NOWAIT 關鍵字,在無法立即獲取該鎖時,此命令會立即退出並且發出一個錯誤資訊。
行級鎖命令
SELECT ...... FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...]
-
[ OF table_name [, ...] ]:如果明確了表名字,那麼只有這些被指定的表會被鎖定,其他在 SELECT 中使用的表將不會被鎖定。
-
若主查詢中引用了 WITH 查詢時,WITH 查詢中的表並不會被鎖定。如果想鎖定 WITH 查詢內的錶行,需要在 WITH 查詢內指定 FOR UPDATE 或 FOR SHARE 關鍵字。
鎖的檢視
pg_locks 解釋
列名稱 | 列型別 | 引用 | 描述 |
---|---|---|---|
locktype | text | 被鎖定的物件型別 | |
database | oid | pg_database.oid | 鎖定物件之資料庫的 OID,如果物件是一個共享物件,不屬於任何一個資料庫,此值為 0,如果物件是“transaction ID”,此值為空 |
relation | oid | pg_class.oid | 如果物件不是表或只是表的一部分,則此值為 null,否則此值是表的 OID |
page | integer | 表中的頁號,如果物件不是錶行 (tuple) 或表頁 (relation page),則此值為 null | |
tuple | smallint | 頁內的行號 (tuple),如果物件不是錶行 (tuple),則此值為空 | |
virtualxid | text | 是一個虛擬事務 ID (ID of a transaction),如果物件不是虛擬事務,則此值為 null | |
transactionid | xid | 事務 ID (ID of a transaction),如果物件不是事務 ID,此值則為 null | |
classid | oid | pg_class.oid | 包含該物件的系統目錄 (system catalog) 的 OID,如果物件不是通常的資料庫物件,則此值為空 |
objid | oid | any OID column | 物件在系統目錄(system catalog) 中的 OID,如果物件不是通常的資料庫物件,則此值為空。對於 advisory locks,此欄位用於區別兩類 key 空間(1 表示 int8 的 key,2 表示 two int4 的 key) |
objsubid | smallint | 如果物件是表列 (table column),此列的值為列號,這時“classid”和“objid”指向表,在其他的資料庫型別中,此值為 0;如果不是資料庫物件,則此值為 null | |
virtualtransaction | text | 持有或等待這把鎖的虛擬事務的 ID (Virtual ID of the transaction) | |
pid | integer | 持有或等待這把鎖的服務程式的 pid。如果此鎖是被一個兩階段的事務持有,則此值為 null | |
mode | text | 鎖的模式名稱 | |
granted | boolean | 如果鎖已被持有,此之為“True”;如果等待獲得此鎖,則此值為“False” |
事務 ID 欄位詳解
-
transactionid-->xid:普通事務 ID
-
virtualxid-->vxid:只讀事務 ID
-
virtualtransaction:表示持有鎖或等待鎖 session 的虛擬事務 ID。
常用語句
SELECT locktype, relation::regclass as rel, page||','||tuple as ctid, virtualxid as vxid, transactionid as xid, virtualtransaction as vxid2, pid, mode, granted FROM pg_locks;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31490526/viewspace-2742209/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- PostgreSQL死鎖相關SQL
- PostgreSQL 死鎖異常SQL
- PostgreSQL 行鎖解讀SQL
- PostgreSQL的四種程式間鎖SQL
- PostgreSQL之鎖監控指令碼SQL指令碼
- postgresql資料庫鎖介紹SQL資料庫
- PostgreSQL 跟蹤checkpointer出現死鎖SQL
- PostgreSQL10GIN索引鎖優化SQL索引優化
- PostgreSQL 併發控制機制(2):表級鎖和行級鎖SQL
- Oracle vs PostgreSQL,研發注意事項(1)-查詢鎖表OracleSQL
- PostgreSQL鎖等待監控珍藏級SQL-誰堵塞了誰SQL
- PostgreSQL模擬兩個update語句死鎖-利用掃描方法SQL
- 使用PostgreSQL替代Redis實現佇列、分散式鎖和釋出/訂閱SQLRedis佇列分散式
- 一天學會PostgreSQL應用開發與管理-6事務和鎖SQL
- MySQL鎖(讀鎖、共享鎖、寫鎖、S鎖、排它鎖、獨佔鎖、X鎖、表鎖、意向鎖、自增鎖、MDL鎖、RL鎖、GL鎖、NKL鎖、插入意向鎖、間隙鎖、頁鎖、悲觀鎖、樂觀鎖、隱式鎖、顯示鎖、全域性鎖、死鎖)MySql
- PostgreSQL DBA(45) - Hypothetical Indexes in PostgreSQLSQLIndex
- PostgreSQL:WITHSQL
- PostgreSQLSQL
- Postgresql關於Vacuum的作用和操作方法,Vacuum full鎖表並生成新的relfilenodeSQL
- PostgreSQL10.0preview功能增強-序列隔離級別預加鎖閾值可控SQLView
- PostgreSQL-PostgreSQL中的public(九)SQL
- 自旋鎖、阻塞鎖、可重入鎖、悲觀鎖、樂觀鎖、讀寫鎖、偏向所、輕量級鎖、重量級鎖、鎖膨脹、物件鎖和類鎖物件
- 【鎖機制】共享鎖、排它鎖、悲觀鎖、樂觀鎖、死鎖等等
- Java鎖?分散式鎖?樂觀鎖?行鎖?Java分散式
- Java鎖最全詳解:樂觀鎖/悲觀鎖+公平鎖/非公平鎖+獨享鎖/共享鎖Java
- PostgreSQL DBA(133) - Extension(postgresql_anonymizer)SQL
- 【PostgreSQL 】PostgreSQL 15對distinct的優化SQL優化
- PostgreSQL:Redhat 8.5 + PostgreSQL 14.5 安裝SQLRedhat
- Java 中15種鎖的介紹:公平鎖,可重入鎖,獨享鎖,互斥鎖,樂觀鎖,分段鎖,自旋鎖等等Java
- 全域性鎖、表鎖、行鎖
- MySQL鎖(樂觀鎖、悲觀鎖、多粒度鎖)MySql
- java 中的鎖 -- 偏向鎖、輕量級鎖、自旋鎖、重量級鎖Java
- InnoDB常用鎖總結(行鎖、間隙鎖、臨鍵鎖、表鎖)
- MySQL/InnoDB中,樂觀鎖、悲觀鎖、共享鎖、排它鎖、行鎖、表鎖、死鎖概念的理解MySql
- PostgreSQL:EXPLAINSQLAI
- PostgreSQL:RULESQL
- PostgreSQL:表SQL
- PostgreSQL:INDEXSQLIndex