Postgresql
UNIQUE 約束的交易策略?
如何使用基於交易的策略來確保同一地點不能在同一天被多次預訂?
有人向我建議,每個隔離級別都會有所不同。你能為他們每個人添加一個例子嗎?(
read committed
和repeatable read
)serializable
。我想了解他們中的每一個。以下是表格和測試數據:
CREATE TABLE place ( place_id INT PRIMARY KEY, Name CHARACTER VARYING(50) NOT NULL, Type CHARACTER VARYING(50) NOT NULL ); CREATE TABLE visit ( visit_id SERIAL PRIMARY KEY, place_id INT NOT NULL, place_dt TIMESTAMP NOT NULL, FOREIGN KEY (place_id) REFERENCES place(place_id) ); INSERT INTO place(place_id, Name, Type ) VALUES (1, 'Denali', 'mountain'), (2, 'Brindley', 'mountain'), (3, 'St. Louis Cathedral', 'church') ; INSERT INTO visit(place_id, place_dt ) VALUES (1, '2019-01-02 10:00'), (2, '2019-01-02 11:00'), (3, '2019-01-03 14:09') ;
我想要的是,對於每個隔離級別,尋找基於事務的策略,以確保同一地點不能在同一天被多次預訂。
鑑於在這種情況下,策略可能對於各種隔離級別都是最佳的。
勞倫茲所說的。
UNIQUE
請注意,Postgres 檢查(必須檢查)跨事務隔離邊界的唯一違規(索引中的重複條目引發的異常)。這些檢查是“絕對的”。手冊中“索引唯一性檢查”一章中的詳細資訊。
但是使用這個優化的等效解決方案,同時使用它:
CREATE UNIQUE INDEX ON visit (place_id, cast(place_dt AS date));
place_id
是integer
(4 個字節)。
date_trunc()
返回timestamp
輸入timestamp
(8 個字節)。
date
只佔用 4 個字節。空間通常以 8 字節的倍數分配。
每個索引元組的索引
(integer, timestamp)
實際上佔用 28 個字節。每個索引元組的索引
(integer, date)
實際上佔用 20 個字節。所以另一個是40%,沒有任何收益。更多細節:
visit (place_id)
旁白:當你把它place_id
放在第一位時,索引還有助於 FK 約束的性能。
為此,您只需
visit
以唯一索引的形式設置約束:CREATE UNIQUE INDEX ON visit (place_id, date_trunc('day', place_dt));
這將保證唯一性,無論隔離級別是什麼。