PGSQL併發控制

併發控制簡介

PostgreSQL提供了多種方式以控制對資料的併發訪問。在資料庫內部，資料的一致性使用多版本模式（多版本併發控制（Multiversion Concurrency Control），即MVCC）維護。這意味著每個SQL語句查詢到的資料，是查詢開始時間節點的快照（一個資料版本），而與查詢期間資料狀態無關。此機制確保語句不會查詢到由併發事務對同一行資料進行修改而產生的不一致資料，從而為每個資料庫會話提供了事務隔離特性。MVCC通過避免傳統資料庫系統中的鎖定方法，最大程度上減小了在多使用者併發場景下的鎖爭用，從而提高了效能。

在PG中，mvvc只適用於讀已提交、可重複度兩個隔離級別。

事務隔離

四種現象

髒讀

事務讀取到另一併發事務未提交的資料。

不可重複讀

同一事務內，讀取之前讀過的資料時，發現讀取到了開始查詢時間點之後其他事務修改後的資料。

幻讀

同一事務，重複執行查詢出來的符合條件行數，受另一最近提交的事務影響。

序列化異常

一次提交一組事務，與隨機順序單個事務執行的結果不同。

事務隔離級別

隔離級別	髒讀	不可重複讀	幻讀	序列化異常
讀未提交	允許，但PG中不允許	可能	可能	可能
讀已提交	不可能	可能	可能	可能
可重複讀	不可能	不可能	允許，但PG中不允許	可能
序列化（序列化）	不可能	不可能	不可能	不可能

讀未提交：一個事務可以讀取另一個事務未提交的內容。pg中不存在該級別，pg的mvcc機制使讀未提交和讀已提交表現一致。

讀已提交：一個事務可以讀取另一個已提交事務的內容，這可能導致同一個事務中兩次查詢得到的結果不一致。

可重複讀：一個事務可以讀取另一個已提交事務的資料（列表層面）。

序列化：序列執行

PG中的鎖

表級鎖

以下列出了可用的鎖模式，以及在何種場景下PostgreSQL會自動使用它們。也可以使用LOCK命令顯式地獲取這些鎖。請謹記，所有這些鎖模式均為表級鎖，即使鎖名中包含“行”（這種命名是歷史遺留問題）。名稱在某種程度上反應了每個鎖模式的典型用法--但語義都相同。各種鎖模式之間的唯一真正區別就是鎖模式之間的衝突。兩個事務不能同時在同一張表獲取相互衝突的鎖模式。（不過，事務本身無衝突，例如，對於同一張表，它可以先獲得ACCESS EXCLUSIVE鎖，隨後又獲得ACCESS SHARE鎖。）多個事務可以並行獲取不衝突的鎖模式。請注意，有些鎖模式，與自身會產生衝突（例如，同時僅可以有一個事務獲得ACCESS EXCLUSIVE鎖）；有些卻不會（例如，多個事務可同時獲取ACCESS SHARE鎖）。

表級鎖模式

ACCESS SHARE（訪問共享）

僅與ACCESS EXCLUSIVE鎖模式衝突。

SELECT命令會在相關表上獲取該鎖。一般情況下，所有僅讀取表而不更改表的查詢均會獲取該鎖模式。

 

ROW SHARE（行共享）

與EXCLUSIVE和ACCESS EXCLUSIVE鎖模式衝突。

SELECT FOR UPDATE和SELECT FOR SHARE命令在目標表上獲取該鎖模式（在其他相關但未選擇為FOR UPDATE/FOR SHARE的表上獲取ACCESS SHARE鎖）。

 

ROW EXCLUSIVE（行排他）

與SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE鎖模式衝突。

UPDATE，DELETE和INSERT命令在目標表上獲取此鎖模式（在其他相關表上獲取ACCESS SHARE鎖）。一般情況下，所有更改表資料的命令均會獲取該鎖模式。

 

SHARE UPDATE EXCLUSIVE（共享更新排他）

與SHARE UPDATE EXCLUSIVE，SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE鎖模式衝突。此模式可防止表發生併發模式（schema）更改和VACUUM。

由以下命令獲取：VACUUM（無FULL）、ANALYZE、CREATE INDEX CONCURRENTYLY、REINDEX CONCURRENTLY、CREATE STATISTICS以及特殊的ALTER INDEX和ALTER TABLE變體（更多新詳情請參見命令ALTER INDEX和ALTER TABLE）。

 

SHARE

與ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE鎖模式衝突。該模式可防止表發生併發資料變更。

由命令CREATE INDEX（無CONCURRENTLY）獲取。

 

SHARE ROW EXCLUSIVE

與ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE，SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE鎖模式衝突。該模式可防止表發生併發資料更改，且是自排他，所以可保證每次僅一個會話可持有該鎖。

由命令CREATE TRIGGER和ALTER TABLE的某些格式獲取。

 

EXCLUSIVE

與ROW SHARE，ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE，SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE鎖模式衝突。該模式僅允許併發的ACCESS SHARE鎖，即，若事務持有該鎖，那麼僅可並行執行讀取表資料的操作。

由REFRESH MATERIALIZED VIEW CONCURRENTLY命令獲取。

 

ACCESS EXCLUSIVE

與所有鎖模式衝突（ACCESS SHARE，ROW SHARE，ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE，SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE）。該模式確保僅持有該鎖的事務訪問該目標表。

由命令DROP TABLE，TRUNCATE，REINDEX，CLUSTER，VACUUM FULL和REFRESH MATERIALIZED VIEW（無CONCURRENTLY）獲取。ALTER INDEX和ALTER TABLE的一些模式同樣獲取此級別的鎖模式。這也是LOCK TABLE語句的預設鎖模式。

衝突模式

Requested Lock Mode	Current Lock Mode
	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE UPDATE EXCLUSIVE	SHARE	SHARE ROW EXCLUSIVE	EXCLUSIVE	ACCESS EXCLUSIVE
ACCESS SHARE								X
ROW SHARE							X	X
ROW EXCLUSIVE					X	X	X	X
SHARE UPDATE EXCLUSIVE				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCLUSIVE			X	X	X	X	X	X
EXCLUSIVE		X	X	X	X	X	X	X
ACCESS EXCLUSIVE	X	X	X	X	X	X	X	X

行級鎖

除了表級鎖，還有行級鎖，以下列出了行級鎖及在什麼情況下PostgreSQL會自動的使用它們。請注意，在不同的子事務中，事務可以在同一行上獲得衝突的鎖；但除此之外，兩個事務永遠不能在同一行上獲得衝突的鎖。行級鎖不會影響檢索資料；它們僅會阻塞對同一行的寫入及鎖。如表級鎖一樣，行級鎖僅會在事務結束或快照回滾時釋放。

行級鎖模式

FOR UPDATE

FOR UPDATE將SELECT語句返回的行鎖定用於更新。這可以防止這些行在事務結束之前被其他事務鎖定、修改或刪除。也就是說，其他嘗試針對這些行執行UPDATE，DELETE，SELECT FOR UPDATE，SELECT FOR NO KEY UPDATE，SELECT FOR SHARE或者SELECT FOR KEY SHARE的事務在當前事務結束之前會一直被阻塞；相反的，SELECT FOR UPDATE將會等待在同一行執行這些命令的並行事務，然後會返回更新後的行（如果這些行被刪除了，那麼不會返回行）。不過，在可重複讀或序列化事務中，如果被鎖定的行在事務開始後被修改了，那麼會丟擲錯誤。

DELETE行以及UPDATE某一列值，也會獲取FOR UPDATE鎖模式。當前，UPDATE情況下考慮的為可以在其上有唯一索引、可用於外來鍵的列（所以不考慮部分索引和表示式索引），不過將來這種機制可能會改變。

 

FOR NO KEY UPDATE

行為與FOR UPDATE類似，不過鎖級別低一些；該鎖不會阻塞在同一行上的SELECT FOR KEY SHARE命令。那些不獲取FOR UPDATE鎖的UPDATE命令均獲取該鎖。

 

FOR SHARE

行為與FOR NO KEY UPDATE類似，不過它是在檢索到的行上加共享鎖而不是排它鎖。共享鎖會阻塞在相同行上執行的UPDATE，DELETE，SELECT FOR UPDATE或者SELECT FOR NO KEY UPDATE命令，但不會阻塞SELECT FOR SHARE或者SELECT FOR KEY SHARE命令。

 

FOR KEY SHARE

行為與FOR SHARE類似，但鎖級別更低一些：會阻塞SELECT FOR UPDATE，但不會阻塞SELECT FOR NO KEY UPDATE。該鎖會阻塞其他事務執行DELETE或那些改變鍵值的UPDATE操作，但不會阻塞其他UPDATE操作，也不會阻塞SELECT FOR NO KEY UPDATE，SELECT FOR SHARE或SELECT FOR KEY SHARE。

 

PostgreSQL並不會在記憶體中記錄變更行的資訊，所以對於同一時間鎖定的行數沒有限制。不過，鎖定行可能會導致磁碟寫，例如：SELECT FOR UPDATE修改選取的行以將其標記為鎖定，從而會導致磁碟寫。

 

行級鎖衝突

quested Lock Mode	Current Lock Mode
	FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE				X
FOR SHARE			X	X
FOR NO KEY UPDATE		X	X	X
FOR UPDATE	X	X	X	X

頁級鎖

除了表級鎖和行級鎖，還有頁級共享/排他鎖用以控制對於共享緩衝池中表頁的讀/寫。這些鎖在行被獲取或更新後立馬釋放。應用程式開發者一般無需關係此類鎖。

諮詢鎖

一種顯式可重入鎖策略，完全由應用程式控制。

諮詢鎖函式

Name	Return Type	Description
pg_advisory_lock(key bigint)	void	Obtain exclusive session level advisory lock
pg_advisory_lock(key1 int, key2 int)	void	Obtain exclusive session level advisory lock
pg_advisory_lock_shared(key bigint)	void	Obtain shared session level advisory lock
pg_advisory_lock_shared(key1 int, key2 int)	void	Obtain shared session level advisory lock
pg_advisory_unlock(key bigint)	boolean	Release an exclusive session level advisory lock
pg_advisory_unlock(key1 int, key2 int)	boolean	Release an exclusive session level advisory lock
pg_advisory_unlock_all()	void	Release all session level advisory locks held by the current session
pg_advisory_unlock_shared(key bigint)	boolean	Release a shared session level advisory lock
pg_advisory_unlock_shared(key1 int, key2 int)	boolean	Release a shared session level advisory lock
pg_advisory_xact_lock(key bigint)	void	Obtain exclusive transaction level advisory lock
pg_advisory_xact_lock(key1 int, key2 int)	void	Obtain exclusive transaction level advisory lock
pg_advisory_xact_lock_shared(key bigint)	void	Obtain shared transaction level advisory lock
pg_advisory_xact_lock_shared(key1 int, key2 int)	void	Obtain shared transaction level advisory lock
pg_try_advisory_lock(key bigint)	boolean	Obtain exclusive session level advisory lock if available
pg_try_advisory_lock(key1 int, key2 int)	boolean	Obtain exclusive session level advisory lock if available
pg_try_advisory_lock_shared(key bigint)	boolean	Obtain shared session level advisory lock if available
pg_try_advisory_lock_shared(key1 int, key2 int)	boolean	Obtain shared session level advisory lock if available
pg_try_advisory_xact_lock(key bigint)	boolean	Obtain exclusive transaction level advisory lock if available
pg_try_advisory_xact_lock(key1 int, key2 int)	boolean	Obtain exclusive transaction level advisory lock if available
pg_try_advisory_xact_lock_shared(key bigint)	boolean	Obtain shared transaction level advisory lock if available
pg_try_advisory_xact_lock_shared(key1 int, key2 int)	boolean	Obtain shared transaction level advisory lock if available

諮詢鎖用法

庫級：

select pg_advisory_lock(1)
select pg_advisory_unlock(1)

表級：

select pg_advisory_lock(1) from users
select pg_advisory_unlock(1) from users

行級：

select pg_advisory_lock(tableoid::int,id),tableoid,* from users where id = 1;
select pg_advisory_unlock(tableoid::int,id),* from users where id = 1;

select pg_advisory_lock(id) from users
select pg_advisory_unlock(id) from users

 

MVCC

Multi-Version Concurrency Control 多版本併發控制，MVCC 是一種併發控制的方法，一般在資料庫管理系統中，實現對資料庫的併發訪問；在程式語言中實現事務記憶體。（MVCC：百度百科）

在PG提供的三種隔離級別中，MVCC機制只工作在讀已提交和可重複讀兩個隔離級別中，序列化使用鎖控制事務序列化執行。

例項

測試表：

CREATE TABLE users (
  id int4 NOT NULL,
  age int4
);
ALTER TABLE users ADD CONSTRAINT "user_pkey" PRIMARY KEY ("id");

MVCC驗證

驗證資料庫預設事務隔離級別為提交讀

show transaction_isolation;

--result:
read committed

 

事務1執行：

BEGIN;
SELECT * from users;

id	age
1	3

 

事務2執行：

BEGIN;
UPDATE users set age = 4 where "id" = 1;
SELECT * from users;

id	age
1	4

 

在事務1中再次執行SELECT * from users

查詢結果不變：

id	age
1	3

可見，事務1和事務2的可見性不同，所以sqlSELECT * from users查詢結果也不同。證明多版本的存在。

髒讀

PG中不存在未提交讀隔離級別，顯示使用未提交讀隔離級別時，PG內部會使用為提交讀隔離級別處理，髒讀在PG中不會發生。

不可重複讀現象驗證

繼續 MVCC驗證 的測試。在事務2中執行COMMIT提交事務。

事務1中再次執行SELECT * from users

查詢結果：

id	age
1	4

事務1的查詢結果發生了改變，即發生了“不可重複讀”現象，在同一個事務（事務1）中，兩次查詢返回的結果不一致。

消除不可重複讀驗證

解說上述所有事務。

事務1執行：

BEGIN;
set default_transaction_isolation='repeatable read';
SELECT * from users;

id	age
1	6

 

事務2執行：

BEGIN;
set default_transaction_isolation='repeatable read';
UPDATE users set age = 7 where "id" = 1;
SELECT * from users;

id	age
1	7

 

執行COMMIT提交事務2，並在事務1中再次查詢SELECT * from users

查詢結果：

id	age
1	6

事務1沒有發生不可重複讀現象，age仍然是6。證明在repeatable read事務隔離級別中解決了不可重複讀現象。

 

mvvc解決不可重複讀的方式：這個級別與Read Committed不同，可重複讀事務中的查詢在事務中第一個非事務控制語句開始時確定快照，而不是在事務中當前語句開始時確定快照。因此，單個事務中的連續SELECT命令會看到相同的資料，也就是說，它們不會看到在自己的事務啟動後提交的其他事務所做的更改

 

幻讀

PG的MVCC機制在可重複讀隔離級別中解決了幻讀問題。資料插入時或更新時，會在相應資料行中設定xmin,xmax,xmin,xmax，PG會根據這些輔助列控制事務的可見性，實現效果類似InnoDB的間隙鎖。在提交讀中，會發生幻讀現象，在可重複讀中，因為一直使用事務開始時資料庫快照，所以消除了幻讀。

 

隱藏列

PostgreSQL中，對於每一行資料（稱為一個tuple），包含有多個隱藏欄位。

查詢命令：select * from pg_attribute where attrelid in (select oid from pg_class where relname = 'users');

attrelid	attname	atttypid	attstattarget	attlen	attnum	attndims	attcacheoff	atttypmod	attbyval	attstorage	attalign	attnotnull	atthasdef	atthasmissing	attidentity	attgenerated	attisdropped	attislocal	attinhcount	attcollation	attacl	attoptions	attfdwoptions	attmissingval
56034	tableoid	26	0	4	-6	0	-1	-1	t	p	i	t	f	f			f	t	0	0
56034	cmax	29	0	4	-5	0	-1	-1	t	p	i	t	f	f			f	t	0	0
56034	xmax	28	0	4	-4	0	-1	-1	t	p	i	t	f	f			f	t	0	0
56034	cmin	29	0	4	-3	0	-1	-1	t	p	i	t	f	f			f	t	0	0
56034	xmin	28	0	4	-2	0	-1	-1	t	p	i	t	f	f			f	t	0	0
56034	ctid	27	0	6	-1	0	-1	-1	f	p	s	t	f	f			f	t	0	0
56034	id	23	-1	4	1	0	-1	-1	t	p	i	t	f	f			f	t	0	0
56034	........pg.dropped.2........	0	0	-1	2	0	-1	259	f	x	i	f	f	f			t	t	0	100
56034	age	23	-1	4	3	0	-1	-1	t	p	i	f	f	f			f	t	0	0

xmin ：在建立（insert）記錄（tuple）時，記錄此值為插入tuple的事務ID。
xmax ：預設值為0.在刪除tuple時，記錄此值。
cmin和cmax ：標識在同一個事務中多個語句命令的序列值，從0開始，用於同一個事務中實現版本可見性判斷。

 

oid：行的物件識別符號（物件 ID）。這個欄位只有在建立表的時候使用了 WITH OIDS，或者是設定了配置引數 default_with_oids 時出現。 這個欄位的型別是 oid（和欄位同名）； 參閱Section 8.12 獲取有關這種型別的更多資訊。

tableoid： 包含本行的表的 OID。這個欄位對那些從繼承層次中選取的查詢特別有用（參閱 Section 5.8）， 因為如果沒有它的話，我們就很難說明一行來自哪個獨立的表。 tableoid 可以和pg_class 的 oid 欄位連線起來獲取表名字。

ctid： 一個行版本在它所處的表內的物理位置。請注意，儘管 ctid 可以用於非常快速地定位行版本，但每次 VACUUM FULL 之後， 一個行的 ctid 都會被更新或者移動。 因此 ctid 是不能作為長期的行識別符號的。 應該使用OID，或者更好是使用者定義的序列號，來標識一個邏輯行。

 

MVCC與鎖

MVCC主要是為解決讀寫併發加鎖的問題，最終是為了減少鎖的使用，提高查詢效率。在消除不可重複讀驗證用例中，寫對讀沒有影響。但是在一些必須的場景中，在查詢時不允許變更，比如在盤點庫存時，不允許出入庫，此時可以顯式使用for update加上更高階別的鎖，從而阻止更新語句併發訪問。

 

參見：

csdn-blog:併發控制

PG官方文件：併發控制

PG官方文件：諮詢鎖函式