Innodb事务和锁
事務概念
數據庫操作的最小工作單元,是作為單個邏輯工作單元執(zhí)行的一系列操作,經典的事務場景是轉賬,A(id為3)給B(id為1)轉賬:
update user_account set balance = balance - 1000 where user_id = 3; update user_account set balance = balance + 1000 where user_id = 1;這兩個sql要保證必須同時成功或同時失敗,否則數據將出現不一致的情況。
mysql中的事務
查看mysql事務開啟狀態(tài):
show variables like 'autocommit'默認是ON。
mysql中開啟事務
會話級別
set session autocommit = on/off;這個是對當前會話設置自動提交,對其他會話不起作用,如果設置為off,這時候執(zhí)行完sql后,當前會話都要手動加上commit才能提交事務。
手動開啟
手動執(zhí)行sql:
開啟事務:begin / start transaction; 提交或回滾事務:commit / rollbackJDBC編程中:
connection.setAutoCommit(boolean); connection.commit();Spring事務AOP編程:
expression=execution(com.gpedu.dao.*.*(..))mysql中默認是自動提交事務的。也就是在你執(zhí)行sql語句的時候,它會自動在你sql前邊加上begin或start transaction;在后邊自動加上commit;從而事務就會自動提交。
當手動使用begin或start transaction時,mysql就會取消自動加事務,例如:
begin; update user set name="faith" where id=1;執(zhí)行后,數據庫id為1的記錄并不會改變,因為這時候mysql不會自動提交,當手動執(zhí)行commit之后才會進行提交。
而因為mysql自動提交事務,所以如下兩個sql實際上是在兩個事務中的:
update user_account set balance = balance - 1000 where userID = 3; update user_account set balance = balance +1000 where userID = 1;那么為了保證原子性,我們需要做如下操作:
begin; update user_account set balance = balance - 1000 where userID = 3; update user_account set balance = balance +1000 where userID = 1; commit;這樣就把這兩個sql放到一個事務中去了。
在jdbc中將兩個sql放到一個事務中,如下:
connection.setAutoCommit(boolean); update user_account set balance = balance - 1000 where userID = 3; update user_account set balance = balance +1000 where userID = 1; connection.commit();Spring事務AOP編程,實際上也是做了手動開啟操作:
expression=execution(com.faith.dao.*.*(..))這里設置了一個切面,基于這個切面的所有方法都會被攔截,這些方法配置事務的傳播性質,攔截之后,spring會在方法之前加一個切面,設置會話手動提交,例如:
connection.setAutoCommit(boolean);然后在方法之后加一個切面,設置會話提交,例如:
connection.commit();當catch到異常的時候,就執(zhí)行
connection.rollback();事務的特性
原子性(Atomicity)
事務是最小的工作單元,事務中的sql要么一起提交成功,要么全部失敗回滾。
一致性(Consistency)
事務中操作的數據及狀態(tài)改變是一致的,更新的數據必須完全符合預設的規(guī)則,不會因為事務或系統(tǒng)等原因導致狀態(tài)的不一致。
隔離性(Isolation)
一個事務所操作的數據在提交之前,對其他事務的可見性設定。如果事務并發(fā)且相互不隔離,會導致臟讀、不可重復讀、幻讀等系列問題。
持久性(Durability)
事務所做的修改會永久保存,不會因為系統(tǒng)意外導致數據的丟失。
原子性和一致性是兩個不同的概念。對于原子性來說,如下的兩條語句:
update user_account set balance = balance - 1000 where userID = 3; update user_account set balance = balance +10000 where userID = 1;只要同時執(zhí)行成功或失敗就是符合原子性的。
而對于一致性來說是不成立的,因為實際給1轉賬1000,但是1的賬戶上多了10000,不符合轉賬的規(guī)則,導致了數據的不一致性。
事務的隔離級別
SQL92 ANSI/ISO標準
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt Read Uncommitted(讀未提交)--未解決任何并發(fā)問題,可以讀到其他事務未提交的數據,會導致臟讀(dirty read)。Read Committed(讀已提交)--解決臟讀問題,一個事務開始之后,只能看到己提交的事務所做的修改,但是未解決不可重復讀(nonrepeatable read)。Repeatable Read (可重復讀)--解決不可重復讀問題,在同一個事務中多次讀取同樣的數據結果是一樣的,這種隔離級別未定義解決幻讀的問題。Serializable(串行化)--解決所有問題,最高的隔離級別,通過強制事務的串行執(zhí)行。innodb對隔離級別的支持
| 讀未提交 | 可能 | 可能 | 可能 | 
| 讀已提交 | 不可能 | 不可能 | 可能 | 
| 可重復讀 | 不可能 | 不可能 | ==不可能== | 
| 串行化 | 不可能 | 不可能 | 不可能 | 
在92標準中,可重復讀級別可以不解決幻讀問題,但是innodb存儲引擎的可重復讀解決了幻讀問題。
鎖
鎖用來管理不同事務對共享資源的并發(fā)訪問。
表鎖與行鎖:
鎖定粒度:表鎖 > 行鎖表鎖直接鎖定表,行鎖只鎖定一行。加鎖效率:表鎖 > 行鎖直接對表加鎖塊,而行鎖需要在表中找到指定的行記錄。沖突概率:表鎖 > 行鎖,表鎖鎖定的記錄更多,更容易產生沖突。并發(fā)性能:表鎖 < 行鎖InnoDB存儲引擎只有行鎖,沒有表鎖,但它也能實現表鎖的效果,因為它的表鎖是把表中所有的行都鎖一遍,就成了表鎖。這個只是實現了表鎖的效果,但是和真正的表鎖效率相比要低下很多。
innodb的鎖類型
InnoDB默認select語句不加任何鎖類型,但是delete、update、insert 默認會加上X鎖。
innodb共有八種鎖:
共享鎖(行鎖):Shared Locks排它鎖(行鎖):Exclusive Locks意向鎖共享鎖(表鎖):Intention Shared Locks意向鎖排它鎖(表鎖):Intention Exclusive Locks自增鎖:AUTO-INC Locks關于行鎖的鎖:記錄鎖 Record Locks間隙鎖 Gap Locks臨鍵鎖 Next-key Locks共享鎖
又稱為讀鎖,簡稱S鎖,多個事務對于同一數據可以共享一把共享鎖,持有共享鎖的事務都能訪問到數據,但是只能讀不能修改。
共享鎖示例:
begin; select * from user WHERE id=1 LOCK IN SHARE MODE;不執(zhí)行commit操作,這時候在另一個窗口執(zhí)行:
select * from user WHERE id=1; update user set name='2' where id=1;select操作可以查到數據,但是update會被阻塞,直到最開始申請到共享鎖的事務執(zhí)行commit或rollback來釋放享鎖,之后update才會繼續(xù)執(zhí)行。
排他鎖
又稱為寫鎖,簡稱X鎖,排他鎖不能與其他鎖并存,如一個事務獲取了某條記錄的排他鎖,其他事務就不能再獲取該行的鎖(共享鎖、排他鎖),即不能讀不能寫,只有持有排他鎖的事務才可以對記錄進行讀取和修改(其他事務要讀取數據可來自于快照)。
排它鎖示例:
begin; update user set name='2' where id=1;不執(zhí)行commit操作,這時候在另一個窗口執(zhí)行:
select * from user WHERE id=1 LOCK IN SHARE MODE; update user set name='3' where id=1;這兩條操作都會被阻塞,直至持有排它鎖的事務commit或rollback之后才能繼續(xù)執(zhí)行。
意向共享鎖(IS)
表示事務準備給數據行加入共享鎖,即一個數據行加共享鎖前必須先取得該表的IS鎖,意向共享鎖之間是可以相互兼容的。
意向排它鎖(IX)
表示事務準備給數據行加入排他鎖,即一個數據行加排他鎖前必須先取得該表的IX鎖,意向排它鎖之間也是可以相互兼容的。
意向鎖(IS 、IX)是InnoDB在數據操作之前自動加的,不需要用戶干預,我們編碼時不需要對意向鎖進行特殊處理。
意向鎖是表級鎖,之間是相互兼容的,也就是說多個持有鎖的線程,可以同時持有意向鎖。比如update id=1,update id=2,他們可以同時持有意向鎖。
意向鎖存在的意義:
只有當事務想去進行鎖表時,意向鎖才會發(fā)揮作用,事務會先判斷意向鎖是否存在,如果存在,說明此時肯定有行鎖存在,這時候不能進行表鎖,則可快速返回該表不能啟用表鎖,省略了進入底層掃描全表的數據。
自增鎖
針對自增列自增長的一個特殊的表級別鎖,可以使用如下語句查看自增鎖的模式:
show variables like 'innodb_autoinc_lock_mode';此參數可取的值有三個:0、1、2,默認取值1。
取值0:傳統(tǒng)方式,串行自增,并且是連續(xù)的。這種模式下需要語句執(zhí)行完成才釋放自增鎖,所以性能最低。例如:1、2、3、4、5、6,沒有人為刪除情況下,表中id字段一定是連續(xù)的。
取值1:連續(xù)方式,自增的,并且是連續(xù)的。當語句申請到自增鎖就釋放自增鎖,自增鎖就可以給其它語句使用,性能會好很多。但因為不會等待語句事務執(zhí)行完畢就釋放了自增鎖,可能該事務回滾了,所以id可能會出現斷續(xù)的情況,例如:1、2、6,8,10
2:交錯方式,多語句插入數據時,有可能自增的序列號和執(zhí)行先后順不一致,并且中間可能有斷裂。一次分配一批自增值,然后下個語句就再進行分配一批自增值,阻塞很小,性能很高。例如:1、2、3、6、5。
設置為2時,需要確認表是否需要連續(xù)的自增值,如果需要,就不要使用這個值。
臨鍵鎖(Next-key locks)
當sql執(zhí)行按照索引進行檢索,查詢條件為范圍查找(between and、<、>等)并且有數據命中,則此時SQL語句加上的鎖為Next-key locks,鎖住索引范圍為記錄的本區(qū)間 + 本區(qū)間下一個區(qū)間(左開右閉)。
mysql會對記錄自動劃分出區(qū)間,如下:
如果為1,2,4,7,10,區(qū)間則為(-&,1],(1,2],(2,4],(4,7],(7,10],(10,+&)。劃分區(qū)間是依據B+樹節(jié)點之間的間隙來劃分的,1和2之間沒有間隙,但是在樹中,是兩個不同的節(jié)點,它們之間是有間隙的。
update user set name=1 where id>5 and id<10上面的sql選中的記錄是id=7,這時候鎖住的區(qū)間是(4,7],(7,10],(4,7]是本區(qū)間,而(7,10]是本區(qū)間的下一個區(qū)間。
鎖住本區(qū)間和相鄰區(qū)間就是防止幻讀,例如這里的>5和<9條件,肯定是要鎖定(4,7],(7,10]區(qū)間才能實現的,也就是要鎖住本區(qū)間和相鄰區(qū)間。
鎖住區(qū)間是因為B+樹的特性,如果把這個例子中的id換成age就更好理解了。
因為innodb默認隔離級別是可重復讀,而前邊說了innodb的可重復讀還捎帶解決了幻讀問題,而幻讀問題就是臨鍵鎖配合mvcc一起解決的。
間隙鎖(Gap locks)
當sql執(zhí)行按照索引進行檢索,查詢條件為范圍查找并且查詢的數據不存在,這時SQL語句加上的鎖即為Gap locks,鎖住索引不存在的區(qū)間(左開右開)。
只在可重復讀隔離級別存在是因為innodb的可重復讀解決了幻讀問題。
Record locks
當sql執(zhí)行按照唯一性(Primary key、Unique key)索引進行檢索,查詢條件為精準等值匹
配且查詢的數據是存在,這時SQL語句加上的鎖即為記錄鎖Record locks,這種情況只針對唯一索引,所以對應的是const或equ_ref級別的查詢。
innodb的行鎖鎖住了哪些內容
得出結論之前先做個測試。例如user表中,id,name,age,create_time字段,id和name有索引,create_time沒有加索引。
測試1
begin; select * from user where id=1;不執(zhí)行commit或rollback。然后在其他線程執(zhí)行:
select * from user where id=1; 阻塞 select * from user where id=2; 非阻塞 select * from user where id=3; 非阻塞測試2
begin; select * from user where name=‘1’;不執(zhí)行commit或rollback。然后在其他線程執(zhí)行:
select * from user where name=‘1’; 阻塞 select * from user where name=‘2’; 非阻塞 select * from user where name=‘3’; 非阻塞測試3
begin; select * from user where create_time=1;不執(zhí)行commit或rollback。然后在其他線程執(zhí)行:
select * from user where create_time=1; 阻塞 select * from user where create_time=2; 阻塞 select * from user where create_time=3; 阻塞結論:InnoDB的行鎖是通過給索引樹中的索引項加鎖來實現的,如果是聚集索引,那么直接鎖住聚集索引的索引項,如果是非聚集索引,那么會鎖住當前索引的索引項,以及對應的聚集索引中的索引項。
也就是說對于非聚集索引,會在兩棵索引樹中分別上鎖。只有通過索引條件進行數據檢索,InnoDB才使用行級鎖,否則,InnoDB將使用表鎖(鎖住索引的所有記錄)。
表鎖是非常耗費性能的,所以要避免表鎖。這種特性可以給平時寫sql帶來一些啟發(fā),例如:
delete from user where create_time;導致表鎖 delete from user where id=1;行鎖;寫刪改的sql時候,要考慮where條件是否命中了索引,要避免表鎖出現,刪一條記錄,卻導致整張表被鎖住了,這是一件很郁悶的事。
鎖如何解決并發(fā)問題
臟讀
加上X鎖可以解決臟讀。
不可重復讀
模擬不可重復讀:
select name from user where id=1;// name='1' update user set name='2' where id=1; // 執(zhí)行成功 select name from user where id=1;// name='2'上面兩個select的結果不同,導致了不可重復讀。解決方法是給這兩個select加上S鎖:
select name from user where id=1 LOCK IN SHARE MODE;// name='1' update user set name='2' where id=1; // 阻塞 select name from user where id=1 LOCK IN SHARE MODE;// name='1'這樣在當前事務執(zhí)行完畢之前,不可能被其他事務更改值,從而解決了不可重復讀的問題。
幻讀
加臨鍵鎖可以解決幻讀。
死鎖
多個并發(fā)事務每個事務都持有鎖,每個事務都需要再繼續(xù)持有其他事務持有的鎖,但誰都不釋放自己手中的鎖,產生鎖的循環(huán)等待,這就形成了死鎖。
死鎖的避免
類似的業(yè)務邏輯以固定的順序訪問表和行。大事務拆小。大事務更傾向于死鎖,如果業(yè)務允許,將大事務拆小。在同一個事務中,盡可能做到一次鎖定所需要的所有資源,減少死鎖概率。降低隔離級別,如果業(yè)務允許,將隔離級別調低也是較好的選擇為表添加合理的索引。可以看到如果不走索引將會為表的每一行記錄添加上鎖(或者說是表鎖),表鎖造成的鎖沖突比行鎖要嚴重的多總結
以上是生活随笔為你收集整理的Innodb事务和锁的全部內容,希望文章能夠幫你解決所遇到的問題。
                            
                        - 上一篇: [菜鸟SpringCloud实战入门]第
 - 下一篇: 第6章 面向对象(下)