mysql事務(wù)隔離級別和鎖
點(diǎn)擊上方藍(lán)色字體，選擇“標(biāo)星公眾號(hào)”
優(yōu)質(zhì)文章，第一時(shí)間送達(dá)
作者 | 白露非霜
來源 | urlify.cn/fqmY7v
1.數(shù)據(jù)庫的鎖
從性能上分為樂觀鎖和悲觀鎖：樂觀鎖是利用版本號(hào)，比如數(shù)據(jù)字段新增一個(gè)版本號(hào)字段，操作的時(shí)候進(jìn)行版本的比對，需要開發(fā)者自己實(shí)現(xiàn)；悲觀鎖就是在操作數(shù)據(jù)時(shí)，認(rèn)為此操作會(huì)出現(xiàn)數(shù)據(jù)沖突，所以在進(jìn)行每次操作時(shí)都要通過獲取鎖才能進(jìn)行對相同數(shù)據(jù)的操作，這點(diǎn)跟java中的synchronized很相似，因此悲觀鎖需要耗費(fèi)較多的時(shí)間。悲觀鎖是由數(shù)據(jù)庫自己實(shí)現(xiàn)了的，執(zhí)行CRUD操作都會(huì)涉及到。
從對對數(shù)據(jù)庫的操作類型上面可以分為讀鎖和寫鎖，都屬于悲觀鎖：讀鎖也叫共享鎖，針對同一份數(shù)據(jù)，多個(gè)讀操作可以同時(shí)進(jìn)行，但是寫操作不允許。寫鎖也叫排它鎖，當(dāng)寫操作完成時(shí)，讀寫都不允許
從數(shù)據(jù)庫的操作粒度上可以分為表鎖和行鎖：InnoDB支持行鎖，myISAM不支持行鎖。
表鎖每次操作鎖住整張表。開銷小，加鎖快（直接鎖住整張表）；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖 突的概率最高，并發(fā)度最低；
行鎖每次操作鎖住一行數(shù)據(jù)。開銷大，加鎖慢（需要定位到那條數(shù)據(jù)）；會(huì)出現(xiàn)死鎖（兩個(gè)事務(wù)分別操作AB兩條數(shù)據(jù)，事務(wù)一先操作B，再操作A，事務(wù)二先操作A，再操作B，這個(gè)時(shí)候就可能出現(xiàn)死鎖）；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度最高。
加表鎖：lock table 表1 read(write), 表2 read/write;
查看表鎖: show open tables; 結(jié)果中In_use字段為1，表示加了表鎖
釋放當(dāng)前會(huì)話加的表鎖unlock tables;
MyISAM在執(zhí)行查詢語句(SELECT)前,會(huì)自動(dòng)給涉及的所有表加讀鎖,在執(zhí)行增刪改操作前,會(huì)自動(dòng)給涉及的表加寫鎖。InnoDB支持事務(wù)，因此它的鎖和事務(wù)的隔離級別有一定關(guān)系。
2.數(shù)據(jù)庫事務(wù)和隔離級別
2.1 事務(wù)
InnoDB支持事務(wù)而MyISAM是不支持事務(wù)的。
我們都知道事務(wù)具有四大屬性——ACID。
原子性Atomicity：事務(wù)看做是一個(gè)原子操作，因此其對數(shù)據(jù)的修改，要么全都執(zhí)行成功，要么都不成功。
一致性Consistent：事務(wù)開始和結(jié)束時(shí)的數(shù)據(jù)都是一致的 。
隔離性Isolation：事務(wù)處理中的數(shù)據(jù)狀態(tài)對外部是不可見的，反之也無法獲取到其他事務(wù)處理中的數(shù)據(jù)狀態(tài)。
持久性Durable：事務(wù)提交之后，對數(shù)據(jù)的修改時(shí)永久性的。
2.2并發(fā)事務(wù)帶來的問題
更新丟失：當(dāng)多個(gè)事務(wù)更新同一行數(shù)據(jù)時(shí)，因?yàn)楦綦x性的存在，彼此都不知道其他的事務(wù)的存在，就會(huì)導(dǎo)致最后一個(gè)提交的事務(wù)，覆蓋了其他事務(wù)提交的數(shù)據(jù)。
臟讀：事務(wù)A正在對一條記錄修改，事務(wù)B讀了A正在修改的數(shù)據(jù)。但是此時(shí)事務(wù)A并未提交。后續(xù)因?yàn)閱栴}事務(wù)A可能回滾，那么事務(wù)B讀到的數(shù)據(jù)就是無效的臟數(shù)據(jù)。同時(shí)也破壞了事務(wù)的一致性的要求。
不可能重復(fù)讀：在一次事務(wù)中，多次執(zhí)行同樣的查詢條件，獲取到的結(jié)果不一致，也就是讀到了其他事務(wù)的修改的數(shù)據(jù)。不符合事務(wù)的隔離性
幻讀：和不可重復(fù)讀不一樣的是幻讀是讀取到了新增的數(shù)據(jù)。
正因?yàn)榇嬖谝陨蠁栴}，所以就就需要數(shù)據(jù)庫提供一定的機(jī)制來解決這些問題。
2.3事務(wù)隔離級別
| 事務(wù)隔離級別 | 臟讀 | 不可重復(fù)讀 | 幻讀 |
| 讀未提交 | 可能 | 可能 | 可能 |
| 讀已提交 | 不可能 | 可能 | 可能 |
| 可重復(fù)讀 | 不可能 | 不可能 | 可能 |
| 串行化 | 不可能 | 不可能 | 不可能 |
數(shù)據(jù)庫的事務(wù)隔離越嚴(yán)格,并發(fā)副作用越小,但付出的代價(jià)也就越大,因?yàn)槭聞?wù)隔離實(shí)質(zhì)上就是使事務(wù)在一定程度上“串行化”進(jìn)行,這顯然與“并發(fā)”是矛盾的。同時(shí),不同的應(yīng)用對讀一致性和事務(wù)隔離程度的要求也是不同的,比如許多應(yīng)用 對“不可重復(fù)讀"和“幻讀”并不敏感,可能更關(guān)心數(shù)據(jù)并發(fā)訪問的能力，所以大多數(shù)時(shí)候我們都會(huì)采用的可重復(fù)讀的事務(wù)隔離級別。
3.InnoDB的行鎖
前面有說MyISAM會(huì)根據(jù)執(zhí)行的操作對數(shù)據(jù)加表鎖（讀鎖/寫鎖）。InnoDB有所不同，在非串行化的隔離級別下面select語句不會(huì)加鎖，但是update，insert，delete會(huì)根據(jù)條件加行鎖。且行鎖是加在索引上面的，如果這些語句沒有走索引，那么也會(huì)加表鎖。
如果條件是范圍，那么該范圍內(nèi)的所有行，包括每行記錄所在的間隙區(qū)間都會(huì)被加上鎖，就算該行數(shù)據(jù)還未被插入也會(huì)被加鎖，這就是所謂的間隙鎖，間隙鎖在可重復(fù)讀隔離級別下面才會(huì)生效。
比如說A表現(xiàn)有的id是1,2,3,4,5,10,20；那么間隙區(qū)間就有5-10,10-20,20-正無窮三個(gè)區(qū)間。我們執(zhí)行update A set XXX= 'XXX' where id > 6 and id <16; 首先[6,16]這個(gè)范圍的數(shù)據(jù)會(huì)被加鎖，然6是落在5-10這個(gè)間隙區(qū)間的，這個(gè)區(qū)間的數(shù)據(jù)也會(huì)加鎖，16是落在10-20這個(gè)間隙區(qū)間，這個(gè)區(qū)間的記錄也會(huì)被加鎖，所以執(zhí)行上述sql時(shí)(5,16]左開又閉，這個(gè)區(qū)間的數(shù)據(jù)都會(huì)被加鎖。如果執(zhí)行的update A set XXX= 'XXX' where id > 6 and id <21；那么(5,正無窮) 都會(huì)被加鎖，所以這個(gè)點(diǎn)還需要注意一下。
因此我們應(yīng)該：
1.盡可能讓所有數(shù)據(jù)查找，修改都通過索引來完成，避免無索引行鎖升級為表鎖
2.盡可能減少條件范圍，縮小鎖的范圍，盡量避免間隙鎖
3.盡量控制事務(wù)大小，減少鎖定資源量和時(shí)間長度，涉及事務(wù)加鎖的sql盡量放在事務(wù)最后執(zhí)行
