深入淺出MySQL事務(wù)處理和鎖機(jī)制

?

2015-01-13?架構(gòu)師之旅 1. 事務(wù)處理和并發(fā)性 1.1. 基礎(chǔ)知識(shí)和相關(guān)概念 1 ）全部的表類型都可以使用鎖，但是只有 InnoDB 和 BDB 才有內(nèi)置的事務(wù)功能。 2 ）使用 begin 開(kāi)始事務(wù)，使用 commit 結(jié)束事務(wù)，中間可以使用 rollback 回滾事務(wù)。 3 ）在默認(rèn)情況下， InnoDB 表支持一致讀。 SQL 標(biāo)準(zhǔn)中定義了 4 個(gè)隔離級(jí)別： read uncommited ， read commited ， repeatable read ， serializable 。 read uncommited 即臟讀，一個(gè)事務(wù)修改了一行，另一個(gè)事務(wù)也可以讀到該行。 如果第一個(gè)事務(wù)執(zhí)行了回滾，那么第二個(gè)事務(wù)讀取的就是從來(lái)沒(méi)有正式出現(xiàn)過(guò)的值。 ? read commited 即一致讀，試圖通過(guò)只讀取提交的值的方式來(lái)解決臟讀的問(wèn)題，但是這又引起了不可重復(fù)讀取的問(wèn)題。 一個(gè)事務(wù)執(zhí)行一個(gè)查詢，讀取了大量的數(shù)據(jù)行。在它結(jié)束讀取之前，另一個(gè)事務(wù)可能完成了對(duì)數(shù)據(jù)行的更改。當(dāng)?shù)谝粋€(gè)事務(wù)試圖再次執(zhí)行同一個(gè)查詢，服務(wù)器就會(huì)返回不同的結(jié)果。 repeatable read 即可重復(fù)讀，在一個(gè)事務(wù)對(duì)數(shù)據(jù)行執(zhí)行讀取或?qū)懭氩僮鲿r(shí)鎖定了這些數(shù)據(jù)行。 但是這種方式又引發(fā)了幻想讀的問(wèn)題。 因?yàn)橹荒苕i定讀取或?qū)懭氲男?#xff0c;不能阻止另一個(gè)事務(wù)插入數(shù)據(jù)，后期執(zhí)行同樣的查詢會(huì)產(chǎn)生更多的結(jié)果。 serializable 模式中，事務(wù)被強(qiáng)制為依次執(zhí)行。這是 SQL 標(biāo)準(zhǔn)建議的默認(rèn)行為。 4 ）如果多個(gè)事務(wù)更新了同一行，就可以通過(guò)回滾其中一個(gè)事務(wù)來(lái)解除死鎖。 5 ） MySQL 允許利用 set transaction 來(lái)設(shè)置隔離級(jí)別。 6 ）事務(wù)只用于 insert 和 update 語(yǔ)句來(lái)更新數(shù)據(jù)表，不能用于對(duì)表結(jié)構(gòu)的更改。執(zhí)行一條更改表結(jié)構(gòu)或 begin 則會(huì)立即提交當(dāng)前的事務(wù)。 7 ）所有表類型都支持表級(jí)鎖，但是 MyISAM 只支持表級(jí)鎖。 8 ）有兩種類型的表級(jí)鎖：讀鎖和寫(xiě)鎖。 讀鎖是共享鎖，支持并發(fā)讀，寫(xiě)操作被鎖。 寫(xiě)鎖是獨(dú)占鎖，上鎖期間其他線程不能讀表或?qū)懕怼?8 ）如果要支持并發(fā)讀寫(xiě)，建議采用 InnoDB 表，因?yàn)樗遣捎眯屑?jí)鎖，可以獲得更多的更新性能。 9 ）很多時(shí)候，可以通過(guò)經(jīng)驗(yàn)來(lái)評(píng)估什么樣的鎖對(duì)應(yīng)用程序更合適，不過(guò)通常很難說(shuō)一個(gè)鎖比別的更好，這全都要依據(jù)應(yīng)用程序來(lái)決定，不同的地方可能需要不同的鎖。當(dāng)前 MySQL 已經(jīng)支持 ISAM, MyISAM, MEMORY (HEAP) 類型表的表級(jí)鎖了， BDB 表支持頁(yè)級(jí)鎖， InnoDB 表支持行級(jí)鎖。 10 ） MySQL 的表級(jí)鎖都是寫(xiě)鎖優(yōu)先，而且是采用排隊(duì)機(jī)制，這樣不會(huì)出現(xiàn)死鎖的情況。對(duì)于 InnoDB 和 BDB 存儲(chǔ)引擎來(lái)說(shuō)，是可能產(chǎn)生死鎖的。這是因?yàn)?InnoDB 會(huì)自動(dòng)捕獲行鎖， BDB 會(huì)在執(zhí)行 SQL 語(yǔ)句時(shí)捕獲頁(yè)鎖的，而不是在事務(wù)的開(kāi)始就這么做。 1.2. 不同鎖的優(yōu)缺點(diǎn)及選擇 行級(jí)鎖的優(yōu)點(diǎn)及選擇 ： 1 ）在很多線程請(qǐng)求不同記錄時(shí)減少?zèng)_突鎖。 2 ）事務(wù)回滾時(shí)減少改變數(shù)據(jù)。 3 ）使長(zhǎng)時(shí)間對(duì)單獨(dú)的一行記錄加鎖成為可能。 行級(jí)鎖的缺點(diǎn) ： 1 ）比頁(yè)級(jí)鎖和表級(jí)鎖消耗更多的內(nèi)存。 2 ）當(dāng)在大量表中使用時(shí)，比頁(yè)級(jí)鎖和表級(jí)鎖更慢，因?yàn)樗枰?qǐng)求更多的所資源。 3 ）當(dāng)需要頻繁對(duì)大部分?jǐn)?shù)據(jù)做 GROUP BY 操作或者需要頻繁掃描整個(gè)表時(shí)，就明顯的比其它鎖更糟糕。 4 ）使用更高層的鎖的話，就能更方便的支持各種不同的類型應(yīng)用程序，因?yàn)檫@種鎖的開(kāi)銷(xiāo)比行級(jí)鎖小多了。 5 ）可以用應(yīng)用程序級(jí)鎖來(lái)代替行級(jí)鎖，例如 MySQL 中的 GET_LOCK() 和 RELEASE_LOCK() 。但它們是勸告鎖（原文： These are advisory locks ），因此只能用于安全可信的應(yīng)用程序中。 6 ）對(duì)于 InnoDB 和 BDB 表， MySQL 只有在指定用 LOCK TABLES 鎖表時(shí)才使用表級(jí)鎖。在這兩種表中，建議最好不要使用 LOCK TABLES ，因?yàn)?InnoDB 自動(dòng)采用行級(jí)鎖， BDB 用頁(yè)級(jí)鎖來(lái)保證事務(wù)的隔離。 表鎖的優(yōu)點(diǎn)及選擇： 1 ）很多操作都是讀表。 2 ）在嚴(yán)格條件的索引上讀取和更新，當(dāng)更新或者刪除可以用單獨(dú)的索引來(lái)讀取得到時(shí)： UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;DELETE FROM tbl_name WHERE unique_key_col=key_value; 3 ） SELECT 和 INSERT 語(yǔ)句并發(fā)的執(zhí)行，但是只有很少的 UPDATE 和 DELETE 語(yǔ)句。 4 ）很多的掃描表和對(duì)全表的 GROUP BY 操作，但是沒(méi)有任何寫(xiě)表。 表鎖的缺點(diǎn)： 1 ）一個(gè)客戶端提交了一個(gè)需要長(zhǎng)時(shí)間運(yùn)行的 SELECT 操作。 2 ）其他客戶端對(duì)同一個(gè)表提交了 UPDATE 操作，這個(gè)客戶端就要等到 SELECT 完成了才能開(kāi)始執(zhí)行。 3 ）其他客戶端也對(duì)同一個(gè)表提交了 SELECT 請(qǐng)求。由于 UPDATE 的優(yōu)先級(jí)高于 SELECT ，所以 SELECT 就會(huì)先等到 UPDATE 完成了之后才開(kāi)始執(zhí)行，它也在等待第一個(gè) SELECT 操作。 1.3. 如何避免鎖的資源競(jìng)爭(zhēng) 1 ）讓 SELECT 速度盡量快，這可能需要?jiǎng)?chuàng)建一些摘要表。 2 ）啟動(dòng) mysqld 時(shí)使用參數(shù) --low-priority-updates 。這就會(huì)讓更新操作的優(yōu)先級(jí)低于 SELECT 。 這種情況下，在上面的假設(shè)中，第二個(gè) SELECT 就會(huì)在 INSERT 之前執(zhí)行了，而且也無(wú)需等待第一個(gè) SELECT 了。 3 ）可以執(zhí)行 SET LOW_PRIORITY_UPDATES=1 命令，指定所有的更新操作都放到一個(gè)指定的鏈接中去完成。 4 ）用 LOW_PRIORITY 屬性來(lái)降低 INSERT ， UPDATE ， DELETE 的優(yōu)先級(jí)。 5 ）用 HIGH_PRIORITY 來(lái)提高 SELECT 語(yǔ)句的優(yōu)先級(jí)。 6 ）從 MySQL 3.23.7 開(kāi)始，可以在啟動(dòng) mysqld 時(shí)指定系統(tǒng)變量 max_write_lock_count 為一個(gè)比較低的值，它能強(qiáng)制臨時(shí)地提高表的插入數(shù)達(dá)到一個(gè)特定值后的所有 SELECT 操作的優(yōu)先級(jí)。它允許在 WRITE 鎖達(dá)到一定數(shù)量后有 READ 鎖。 7 ）當(dāng) INSERT 和 SELECT 一起使用出現(xiàn)問(wèn)題時(shí)，可以轉(zhuǎn)而采用 MyISAM 表，它支持并發(fā)的 SELECT 和 INSERT 操作。 8 ）當(dāng)在同一個(gè)表上同時(shí)有插入和刪除操作時(shí)， INSERT DELAYED 可能會(huì)很有用。 9 ）當(dāng) SELECT 和 DELETE 一起使用出現(xiàn)問(wèn)題時(shí)， DELETE 的 LIMIT 參數(shù)可能會(huì)很有用。 10 ）執(zhí)行 SELECT 時(shí)使用 SQL_BUFFER_RESULT 有助于減短鎖表的持續(xù)時(shí)間。 11 ）可以修改源代碼 `mysys/thr_lock.c' ，只用一個(gè)所隊(duì)列。這種情況下，寫(xiě)鎖和讀鎖的優(yōu)先級(jí)就一樣了，這對(duì)一些應(yīng)用可能有幫助。 posted on 2018-08-11 21:42 micwin 閱讀(...) 評(píng)論(...) ?編輯 收藏

轉(zhuǎn)載于:https://www.cnblogs.com/chinanetwind/articles/9461213.html

總結(jié)

以上是生活随笔為你收集整理的深入浅出MySQL事务处理和锁机制的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。