?譯者 | 布加迪

審校 | 孫淑娟

一說起關(guān)系數(shù)據(jù)庫，人們通常會(huì)先想到MySQL。MySQL使用InnoDB作為其存儲(chǔ)引擎，而可重復(fù)讀取隔離級(jí)別（在事務(wù)開始之前查看數(shù)據(jù)）最常見。

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

MySQL開發(fā)人員應(yīng)了解可能存在的陷阱，并且能夠采取適當(dāng)?shù)淖龇?，避免丟失更新和幻讀等問題。本文介紹MySQL開發(fā)人員如何解決幻讀可能導(dǎo)致的“寫偏”。

導(dǎo)致幻讀的場景

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

比如，假設(shè)在搜索特定范圍后采取的操作是UPDATE或DELETE。在這種情況下，MySQL開發(fā)人員可以使用獨(dú)占鎖來避免“寫偏”。然后，開發(fā)人員可以在“SELECT”操作開始時(shí)使用FOR UPDATE，之后他們可以強(qiáng)制兩個(gè)同時(shí)的事務(wù)依次完成。因此，這兩個(gè)同時(shí)的事務(wù)在競態(tài)條件下，規(guī)避了“寫偏”。

但是，如果我們假設(shè)根據(jù)特定范圍的搜索結(jié)果采取的操作是CREATE，上述解決方案就是不完整的：不存在開發(fā)人員可以用SELECT鎖定的對(duì)應(yīng)行，這意味著稍后形成一行。

使用CREATE時(shí)，如何解決幻讀

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

想象人們可以預(yù)訂會(huì)議室的系統(tǒng)；在有人使用該系統(tǒng)預(yù)訂房間后，新數(shù)據(jù)添加到表中。該系統(tǒng)讓用戶可以根據(jù)他們想要預(yù)訂的時(shí)間段查詢某個(gè)房間是否可以預(yù)訂。一旦有人創(chuàng)建了新的預(yù)訂條目，所有其他用戶就能避免時(shí)段沖突。

然而，當(dāng)兩個(gè)人同時(shí)預(yù)訂同一房間時(shí)，就會(huì)出現(xiàn)問題。兩個(gè)用戶都能夠通過最初的SELECT驗(yàn)證，這意味著理論上他們都可以預(yù)訂同一時(shí)段的房間，從而導(dǎo)致時(shí)段沖突。比如說，如果有多個(gè)需要使用該預(yù)訂系統(tǒng)的用戶通過VPN連接到遠(yuǎn)程SQL服務(wù)器，該問題可能更復(fù)雜。即使MySQL開發(fā)人員添加了獨(dú)占鎖，這個(gè)問題也無法避免，因?yàn)樗麄儫o法在開始的SELECT驗(yàn)證時(shí)鎖定行。

用唯一約束索引來解決

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

開發(fā)人員可以使用唯一約束索引來定位與房號(hào)和會(huì)議開始時(shí)間對(duì)應(yīng)的房間預(yù)訂表的列。該解決方案可以防止有人預(yù)訂別人已經(jīng)預(yù)訂的時(shí)間段，開發(fā)人員可以做到這一點(diǎn)，實(shí)現(xiàn)沒有人可以預(yù)訂超過一小時(shí)的房間。

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

通過物化沖突來解決

解決我們所討論的幻讀的正確方法是，發(fā)現(xiàn)表隱藏起來的沖突。開發(fā)人員可以使用協(xié)調(diào)并發(fā)操作的數(shù)據(jù)集，預(yù)先填充一個(gè)全新的表。如果我們以會(huì)議室系統(tǒng)為例，可以想象創(chuàng)建一個(gè)新表來指定時(shí)間段，并提前顯示所有可用的時(shí)間段。

有了這個(gè)新表，開發(fā)人員現(xiàn)在將在指定可用時(shí)間段的列上執(zhí)行SELECT，并添加FOR UPDATE，因?yàn)閿?shù)據(jù)已經(jīng)存在。開發(fā)人員需要在初始SELECT之前運(yùn)行這個(gè)SELECT FOR UPDATE。

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

結(jié)論

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

任何使用數(shù)據(jù)庫的人都必須了解相應(yīng)數(shù)據(jù)庫的功能以及解決方案。否則，將無法預(yù)測該數(shù)據(jù)庫的哪些行為可能危及數(shù)據(jù)庫設(shè)計(jì)和開發(fā)工作。

此外，了解如何適當(dāng)?shù)靥幚頋撛陲L(fēng)險(xiǎn)同樣重要。雖然我們?cè)诒疚闹型ㄟ^時(shí)間預(yù)訂系統(tǒng)所描述的用例與其他用例不一樣，但展示出來的模式非常相似，因此了解如何解決它們有助于將來更容易處理其他情形。

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