MySQL系列之-“事務(wù)”與“鎖”

IT那活兒發(fā)布于2023-01-11 13:20 / 2479人閱讀

點(diǎn)擊上方“IT那活兒”，關(guān)注后了解更多精彩內(nèi)容?。?！

事務(wù)的四大特性

No.1 原子性（Atomicity）

原子性是指事務(wù)包含的所有操作要么全部成功，要么全部失敗回滾，因此事務(wù)的操作如果成功就必須要完全應(yīng)用到數(shù)據(jù)庫(kù)，如果操作失敗則不能對(duì)數(shù)據(jù)庫(kù)有任何影響。

No.2 一致性（Consistency）

一致性是指事務(wù)必須使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變換到另一個(gè)一致性狀態(tài)，也就是說(shuō)一個(gè)事務(wù)執(zhí)行之前和執(zhí)行之后都必須處于一致性狀態(tài)。

在事務(wù)開(kāi)始和完成時(shí)，數(shù)據(jù)都必須保持一致?tīng)顟B(tài)。

No.3 隔離性（Isolation）

數(shù)據(jù)庫(kù)系統(tǒng)提供一定的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的 "獨(dú)立"環(huán)境下運(yùn)行。

No.4 持久性（Durability）

持久性是指一個(gè)事務(wù)一旦被提交了，那么對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)的改變就是永久性的，即便是在數(shù)據(jù)庫(kù)系統(tǒng)遇到故障的情況下也不會(huì)丟失提交事務(wù)的操作。

事務(wù)完成之后，對(duì)于數(shù)據(jù)的修改是永久的。

并發(fā)引發(fā)的數(shù)據(jù)庫(kù)問(wèn)題

No.1 數(shù)據(jù)丟失更新(update lost)

當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行，最初的事務(wù)修改的值，會(huì)被后面的事務(wù)修改的值覆蓋。

No.2 臟讀(dirty reads)

當(dāng)一個(gè)事務(wù)正在訪(fǎng)問(wèn)數(shù)據(jù)，并且對(duì)數(shù)據(jù)進(jìn)行了修改，而這種修改還沒(méi)有提交到數(shù)據(jù)庫(kù)中，這時(shí)，另外一個(gè)事務(wù)也訪(fǎng)問(wèn)這個(gè)數(shù)據(jù)，然后使用了這個(gè)數(shù)據(jù)。

No.3 不可重復(fù)讀(NonRepeatableReads)

一個(gè)事務(wù)在讀取某些數(shù)據(jù)后的某個(gè)時(shí)間，再次讀取以前讀過(guò)的數(shù)據(jù)，卻發(fā)現(xiàn)和以前讀出的數(shù)據(jù)不一致。

No.4 幻讀(Phantom Reads)

一個(gè)事務(wù)按照相同的查詢(xún)條件重新讀取以前查詢(xún)過(guò)的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿(mǎn)足其查詢(xún)條件的新數(shù)據(jù)。

通過(guò)事務(wù)隔離級(jí)別解決事務(wù)并發(fā)

No.1 未提交讀READ-UNCOMMITTED

造成臟讀現(xiàn)象,存在臟讀、不可重復(fù)讀、幻讀的問(wèn)題,通過(guò)鎖解決數(shù)據(jù)丟失更新的問(wèn)題。

Session 1:

Session2:

No.2 讀已提交READ-COMMITTED

已提交讀隔離級(jí)別解決了臟讀的問(wèn)題，但是出現(xiàn)了不可重復(fù)讀的問(wèn)題，即事務(wù)B在兩次查詢(xún)的數(shù)據(jù)不一致，解決臟讀的問(wèn)題，存在不可重復(fù)讀、幻讀的問(wèn)題。

T2時(shí)刻無(wú)法讀到T1時(shí)刻未交提的數(shù)據(jù)，T4時(shí)刻能讀到T3時(shí)刻的數(shù)據(jù)。

No.3 可重復(fù)讀(REPEATABLE READ)

mysql 默認(rèn)級(jí)別，解決臟讀、不可重復(fù)讀的問(wèn)題，存在幻讀的問(wèn)題。使用 MMVC機(jī)制實(shí)現(xiàn)可重復(fù)讀。

No.4 串行化(SERIALIZABLE)

mysql中事務(wù)隔離級(jí)別為serializable時(shí)會(huì)鎖表，因此不會(huì)出現(xiàn)幻讀的情況，這種隔離級(jí)別并發(fā)性極低，開(kāi)發(fā)中很少會(huì)用到解決臟讀、不可重復(fù)讀、幻讀，可保證事務(wù)安全，但完全串行執(zhí)行，性能最低。

通過(guò)鎖解決并發(fā)事務(wù)數(shù)據(jù)一致性問(wèn)題

No1. 事務(wù)鎖策略

1）樂(lè)觀鎖

在關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)里，樂(lè)觀并發(fā)控制（又名“樂(lè)觀鎖”，Optimistic Concurrency Control，縮寫(xiě)“OCC”）是一種并發(fā)控制的方法。它假設(shè)多用戶(hù)并發(fā)的事務(wù)在處理時(shí)不會(huì)彼此互相影響，各事務(wù)能夠在不產(chǎn)生鎖的情況下處理各自影響的那部分?jǐn)?shù)據(jù)。在提交數(shù)據(jù)更新之前，每個(gè)事務(wù)會(huì)先檢查在該事務(wù)讀取數(shù)據(jù)后，有沒(méi)有其他事務(wù)又修改了該數(shù)據(jù)。如果其他事務(wù)有更新的話(huà)，正在提交的事務(wù)會(huì)進(jìn)行回滾。

樂(lè)觀并發(fā)控制相信事務(wù)之間的數(shù)據(jù)競(jìng)爭(zhēng)(data race)的概率是比較小的，因此盡可能直接做下去，直到提交的時(shí)候才去鎖定，所以不會(huì)產(chǎn)生任何鎖和死鎖。但如果直接簡(jiǎn)單這么做，還是有可能會(huì)遇到不可預(yù)期的結(jié)果，例如兩個(gè)事務(wù)都讀取了數(shù)據(jù)庫(kù)的某一行，經(jīng)過(guò)修改以后寫(xiě)回?cái)?shù)據(jù)庫(kù)，這時(shí)就遇到了問(wèn)題。

使用數(shù)據(jù)版本（Version）記錄機(jī)制實(shí)現(xiàn)，這是樂(lè)觀鎖最常用的一種實(shí)現(xiàn)方式。何謂數(shù)據(jù)版本？即為數(shù)據(jù)增加一個(gè)版本標(biāo)識(shí)，一般是通過(guò)為數(shù)據(jù)庫(kù)表增加一個(gè)數(shù)字類(lèi)型的 “version” 字段來(lái)實(shí)現(xiàn)。當(dāng)讀取數(shù)據(jù)時(shí)，將version字段的值一同讀出，數(shù)據(jù)每更新一次，對(duì)此version值加一。

2）悲觀鎖

在關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)里，悲觀并發(fā)控制（又名“悲觀鎖”，Pessimistic Concurrency Control，縮寫(xiě)“PCC”）是一種并發(fā)控制的方法。它可以阻止一個(gè)事務(wù)以影響其他用戶(hù)的方式來(lái)修改數(shù)據(jù)。如果一個(gè)事務(wù)執(zhí)行的操作都某行數(shù)據(jù)應(yīng)用了鎖，那只有當(dāng)這個(gè)事務(wù)把鎖釋放，其他事務(wù)才能夠執(zhí)行與該鎖沖突的操作。

悲觀并發(fā)控制主要用于數(shù)據(jù)爭(zhēng)用激烈的環(huán)境，以及發(fā)生并發(fā)沖突時(shí)使用鎖保護(hù)數(shù)據(jù)的成本要低于回滾事務(wù)的成本的環(huán)境中。

悲觀并發(fā)控制實(shí)際上是“先取鎖再訪(fǎng)問(wèn)”的保守策略，為數(shù)據(jù)處理的安全提供了保證。但是在效率方面，處理加鎖的機(jī)制會(huì)讓數(shù)據(jù)庫(kù)產(chǎn)生額外的開(kāi)銷(xiāo)，還有增加產(chǎn)生死鎖的機(jī)會(huì)；另外，在只讀型事務(wù)處理中由于不會(huì)產(chǎn)生沖突，也沒(méi)必要使用鎖，這樣做只能增加系統(tǒng)負(fù)載；還有會(huì)降低了并行性，一個(gè)事務(wù)如果鎖定了某行數(shù)據(jù)，其他事務(wù)就必須等待該事務(wù)處理完才可以處理那行數(shù)。

No2. 鎖

1）Myisam引擎鎖總結(jié)

對(duì)MyISAM 表的讀操作，不會(huì)阻塞其他用戶(hù)對(duì)同一表的讀請(qǐng)求，但會(huì)阻塞對(duì)同一表的寫(xiě)請(qǐng)求；

對(duì)MyISAM 表的寫(xiě)操作，則會(huì)阻塞其他用戶(hù)對(duì)同一表的讀和寫(xiě)操作；

簡(jiǎn)而言之，就是讀鎖會(huì)阻塞寫(xiě)，但是不會(huì)阻塞讀。而寫(xiě)鎖，則既會(huì)阻塞讀，又會(huì)阻塞寫(xiě)。

此外，MyISAM 的讀寫(xiě)鎖調(diào)度是寫(xiě)優(yōu)先，這也是MyISAM不適合做寫(xiě)為主的表的存儲(chǔ)引擎的原因。因?yàn)閷?xiě)鎖后，其他線(xiàn)程不能做任何操作，大量的更新會(huì)使查詢(xún)很難得到鎖，從而造成永遠(yuǎn)阻塞。在mysql中，Myisam引擎開(kāi)始只支持表鎖。

查詢(xún)表爭(zhēng)用情況

2）Innodb引擎鎖總結(jié)

Innodb鎖的二種鎖定方式：

共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他鎖（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE

Innodb行鎖的演示

無(wú)索引表行鎖升級(jí)為表鎖

可重復(fù)讀的間隙鎖以及next-key鎖

當(dāng)我們用范圍條件，而不是使用相等條件檢索數(shù)據(jù)，并請(qǐng)求共享或排他鎖時(shí)，InnoDB會(huì)給符合條件的已有數(shù)據(jù)進(jìn)行加鎖；對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做 "間隙（GAP）" ，InnoDB也會(huì)對(duì)這個(gè) "間隙" 加鎖，這種鎖機(jī)制就是所謂的間隙鎖（Next-Key鎖）

mysql> show status like innodb_row_lock%;
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 24021 |
| Innodb_row_lock_time_avg | 6005 |
| Innodb_row_lock_time_max | 6007  |
| Innodb_row_lock_waits | 4 |
+-------------------------------+-------+

Innodb_row_lock_current_waits: 當(dāng)前正在等待鎖定的數(shù)量

Innodb_row_lock_time: 從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)間長(zhǎng)度

Innodb_row_lock_time_avg:每次等待所花平均時(shí)長(zhǎng)

Innodb_row_lock_time_max:從系統(tǒng)啟動(dòng)到現(xiàn)在等待最長(zhǎng)的一次所花的時(shí)間

Innodb_row_lock_waits: 系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的次數(shù)

當(dāng)?shù)却拇螖?shù)很高，而且每次等待的時(shí)長(zhǎng)也不小的時(shí)候，我們就需要分析系統(tǒng)中為什么會(huì)有如此多的等待，然后根據(jù)分析結(jié)果著手制定優(yōu)化計(jì)劃。

3）鎖的優(yōu)化建議

盡可能讓所有數(shù)據(jù)檢索都能通過(guò)索引來(lái)完成，避免無(wú)索引行鎖升級(jí)為表鎖。
合理設(shè)計(jì)索引，盡量縮小鎖的范圍
盡可能減少索引條件，及索引范圍，避免間隙鎖
盡量控制事務(wù)大小，減少鎖定資源量和時(shí)間長(zhǎng)度
盡可使用低級(jí)別事務(wù)隔離（但是需要業(yè)務(wù)層面滿(mǎn)足需求）

END

更多精彩干貨分享

點(diǎn)擊下方名片關(guān)注

IT那活兒

GPU云服務(wù)器云服務(wù)器系列之系列之二架構(gòu)之坑系列 insert鎖事務(wù)innodbmysql

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/129730.html

發(fā)表評(píng)論

登陸后可評(píng)論

0條評(píng)論

IT那活兒

男|高級(jí)講師

我要關(guān)注我要私信

TA的文章

消息中間件故障分析一例

閱讀 1356·2023-01-11 13:20
RAC雙節(jié)點(diǎn)crash回復(fù)一例

閱讀 1707·2023-01-11 13:20
ORA-600處理一例

閱讀 1215·2023-01-11 13:20
雙節(jié)點(diǎn)RAC實(shí)例2 HANG 故障分析一例

閱讀 1906·2023-01-11 13:20
RAC集群節(jié)點(diǎn)1重啟分析一例

閱讀 4165·2023-01-11 13:20
CRS啟動(dòng)報(bào)錯(cuò)CRS-1656處理分享

閱讀 2757·2023-01-11 13:20
oracle 12CR2打補(bǔ)丁報(bào)錯(cuò)處理一例

閱讀 1402·2023-01-11 13:20
分布式緩存組件故障分析及監(jiān)控優(yōu)化

閱讀 3671·2023-01-11 13:20

資訊專(zhuān)欄INFORMATION COLUMN

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來(lái)選購(gòu)！

MySQL系列之-“事務(wù)”與“鎖”

No.1 原子性（Atomicity）

No.2 一致性（Consistency）

No.3 隔離性（Isolation）

No.4 持久性（Durability）

No.2 臟讀(dirty reads)

No.3 不可重復(fù)讀(NonRepeatableReads)

No.4 幻讀(Phantom Reads)

No.2 讀已提交READ-COMMITTED

No.3 可重復(fù)讀(REPEATABLE READ)

No.4 串行化(SERIALIZABLE)

1）樂(lè)觀鎖

2）悲觀鎖

No2. 鎖

1）Myisam引擎鎖總結(jié)

2）Innodb引擎鎖總結(jié)

Innodb鎖的二種鎖定方式：

Innodb行鎖的演示

無(wú)索引表行鎖升級(jí)為表鎖

可重復(fù)讀的間隙鎖以及next-key鎖

3）鎖的優(yōu)化建議

相關(guān)文章

發(fā)表評(píng)論

0條評(píng)論

男|高級(jí)講師

TA的文章

最新活動(dòng)

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來(lái)選購(gòu)！