?譯者 | 布加迪

審校 | 孫淑娟

一說起關系數據庫，人們通常會先想到MySQL。MySQL使用InnoDB作為其存儲引擎，而可重復讀取隔離級別（在事務開始之前查看數據）最常見。

不過與PostgreSQL不同，InnoDB中的可重復讀取隔離級別無法處理丟失更新和幻讀（phantom read）之類的問題，而您不需要額外的技巧就能解決PostgreSQL中的丟失更新。您可以使用幾個技巧來執行幻讀，比如范圍類型及其他機制。

MySQL開發人員應了解可能存在的陷阱，并且能夠采取適當的做法，避免丟失更新和幻讀等問題。本文介紹MySQL開發人員如何解決幻讀可能導致的“寫偏”。

導致幻讀的場景

不同的場景可能導致幻讀。一般來說，這些場景都遵循類似的模式。最初搜索MySQL數據庫中的特定范圍，然后根據已搜索范圍的結果執行CREATE（創建）、UPDATE（更新）或DELETE（刪除）等操作。之后，執行的操作直接影響從已搜索的范圍中獲取的結果。

比如，假設在搜索特定范圍后采取的操作是UPDATE或DELETE。在這種情況下，MySQL開發人員可以使用獨占鎖來避免“寫偏”。然后，開發人員可以在“SELECT”操作開始時使用FOR UPDATE，之后他們可以強制兩個同時的事務依次完成。因此，這兩個同時的事務在競態條件下，規避了“寫偏”。

但是，如果我們假設根據特定范圍的搜索結果采取的操作是CREATE，上述解決方案就是不完整的：不存在開發人員可以用SELECT鎖定的對應行，這意味著稍后形成一行。

使用CREATE時，如何解決幻讀

  我們將介紹一種實際場景，以便更好地理解使用CREATE時，解決幻讀導致的問題。

想象人們可以預訂會議室的系統；在有人使用該系統預訂房間后，新數據添加到表中。該系統讓用戶可以根據他們想要預訂的時間段查詢某個房間是否可以預訂。一旦有人創建了新的預訂條目，所有其他用戶就能避免時段沖突。

然而，當兩個人同時預訂同一房間時，就會出現問題。兩個用戶都能夠通過最初的SELECT驗證，這意味著理論上他們都可以預訂同一時段的房間，從而導致時段沖突。比如說，如果有多個需要使用該預訂系統的用戶通過VPN連接到遠程SQL服務器，該問題可能更復雜。即使MySQL開發人員添加了獨占鎖，這個問題也無法避免，因為他們無法在開始的SELECT驗證時鎖定行。

用唯一約束索引來解決

MySQL開發人員無法通過使用獨占鎖將并發操作變成順序操作。因此，他們需要通過為表添加唯一約束讓一項操作失敗。

開發人員可以使用唯一約束索引來定位與房號和會議開始時間對應的房間預訂表的列。該解決方案可以防止有人預訂別人已經預訂的時間段，開發人員可以做到這一點，實現沒有人可以預訂超過一小時的房間。

然而，如果兩個用戶的會議時間重疊，該解決方案也會阻止唯一約束發揮效果。為了正確解決問題，開發人員必須改用物化沖突。

通過物化沖突來解決

解決我們所討論的幻讀的正確方法是，發現表隱藏起來的沖突。開發人員可以使用協調并發操作的數據集，預先填充一個全新的表。如果我們以會議室系統為例，可以想象創建一個新表來指定時間段，并提前顯示所有可用的時間段。

有了這個新表，開發人員現在將在指定可用時間段的列上執行SELECT，并添加FOR UPDATE，因為數據已經存在。開發人員需要在初始SELECT之前運行這個SELECT FOR UPDATE。

通過物化上述例子中的沖突，開發人員可以使用獨占鎖，阻止任何兩個預訂的時間段彼此重疊，從而迫使一個時間段在另一個時間段之前或之后添加。由于已完成第一個時間段，后面的時間段將立即失效。

結論

雖然物化沖突是一種難度大、不直觀的解決方案，但在使用MySQL數據庫時，有必要避免性能出現大幅下降。遺憾的是，MySQL的InnoDB隔離級別不可序列化，因此開發人員需要犧牲一定程度的復雜性，以獲得可接受的性能級別。

任何使用數據庫的人都必須了解相應數據庫的功能以及解決方案。否則，將無法預測該數據庫的哪些行為可能危及數據庫設計和開發工作。

此外，了解如何適當地處理潛在風險同樣重要。雖然我們在本文中通過時間預訂系統所描述的用例與其他用例不一樣，但展示出來的模式非常相似，因此了解如何解決它們有助于將來更容易處理其他情形。

原文標題：??The Definitive Guide to Solving the Phantom Read in MySQL???，作者：Nahla Davies?