***** ## 數據庫鎖 - 表鎖 - 行鎖 - 間隙鎖 鎖是計算機協調多個進程或線程并發訪問某一資源的機制 ### 表鎖(偏讀) 偏向MyISAM存儲引擎,開銷小,加鎖快;無死鎖,鎖定粒度大,發送鎖沖突的概率最高,并發度低 #### 表鎖案例 ~~~ create table mylock( id int not null primary key auto_increment, name varchar(20) )engine myisam; insert into mylock(name) values('a'); insert into mylock(name) values('b'); insert into mylock(name) values('c'); insert into mylock(name) values('d'); insert into mylock(name) values('e'); ~~~ 手動增加表鎖 ~~~ lock table 表名字 read(write),表名字2 read(write); ~~~ 釋放表鎖 ~~~ unlock tables; ~~~ ### 表鎖總結 MyISAM在執行查詢語句(select)前,會自動給涉及的所有表加讀鎖,在執行增刪改操作前,會自動給涉及的表加寫鎖 * 對MyISAM表的讀操作(加讀鎖),不會阻塞其他進程對同一表的讀請求,但會阻塞對同一表的寫請求。只有當讀鎖釋放后,才會執行其他進行的寫操作 * 對MyISAM表的寫操作(加寫鎖),會阻塞其他進程對同一表的讀和寫操作,只有當寫鎖釋放后,才會執行其他進程的讀寫操作 ### 行鎖(偏寫) 偏向InnoDB存儲引擎,開銷大,加鎖慢,會出現死鎖。鎖定粒度最小,發生鎖沖突的概率最低,并發度也最高 <br>InnoDB與MyISAM的最大不同點,支持事務,采用了行級鎖 #### 行鎖案例 ~~~ create table test_innodb_lock(a int(11),b varchar(16))engine=innodb; insert into test_innodb_lock values(1,'b2'); insert into test_innodb_lock values(3,'3'); insert into test_innodb_lock values(4,'4000'); insert into test_innodb_lock values(5,'5000'); create index idx_test_innodb_a on test_innodb_lock(a); create index idx_test_innodb_b on test_innodb_lock(b); ~~~ #### 如何分析行鎖定 ~~~ 通過檢查innodb_row_lock狀態變量來分析系統上的行鎖爭奪情況 show status like 'innodb_row_lock%'; ~~~ 各個狀態量的說明 ~~~ Innodb_row_lock_current_waits 當前正在等待鎖定的數量 * Innodb_row_lock_time 從系統啟動到現在鎖定的總時間長度 * Innodb_row_lock_time_avg 每次等待所花費平均時間 Innodb_row_lock_time_max 從系統啟動到現在等待最長的一次所花費的時間 * Innodb_row_lock_waits 系統啟動后到現在總共等待的次數 ~~~ ### 什么是間隙鎖 當我們用范圍條件而不是相等條件檢索數據,并請求共享或排他鎖時,innodb會給符合條件的已有數據記錄的索引項加鎖, 對于鍵值在條件范圍內但并不存在的記錄,叫做"間隙" <br>innodb也會對這個"間隙"加鎖,這種鎖機制就是所謂的間隙鎖 ### 間隙鎖的危害 因為SQL執行過程中通過范圍查找的話,他會鎖定整個范圍內所有的索引值,即使這個鍵值并不存在 <br>間隙鎖有一個比較致命的弱點,就是當鎖定以為范圍鍵值之后,即使某些不存在的鍵值也會被無辜的鎖定,而造成在鎖定的時候無法插入鎖定鍵值范圍內的任何數據。在某些場景下這可能會對性能造成很大的危害 ### 如何鎖定一行 ~~~ select * from test_innodb_lock where a = 8 for update; ~~~