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