1.1 表鎖
1.2 行鎖
1.3 頁鎖
1.4 引擎與鎖
將一張表的數據insert 到另一個表、1.5 查詢表鎖爭用情況
檢查table_locks_waited和table_locks_immediate狀態變量分析
table_locks_waited 的值越高，則說明存在嚴重的表級鎖的爭用情況
ＭySQL的表鎖有兩種模式
2.1 表鎖兼容性
sql根據一個表更新另一個表？鎖模式的兼容如下表
是否兼容請求none請求讀鎖請求寫鎖當前處于讀鎖是是否當前處于寫鎖是否否
可見，對ＭyISAM表的讀操作,不會阻塞其他用戶對同一表的讀請求,但會阻塞對同一表的寫請求;
對ＭyISAM表的寫操作,則會阻塞其他用戶對同一表的讀和寫請求;
ＭyISAM表的讀和寫操作之間,以及寫和寫操作之間是串行的!(當某一線程獲得對一個表的寫鎖后,只有持有鎖的線程可以對表進行更新操作.其他線程的讀、寫操作都會等待,直到鎖被釋放為止)
將一個表的字段更新到另一個表、2.2 如何加表鎖
對于 MyISAM 引擎
不需要用戶直接顯式用lock table命令
對于給MyISAM顯式加鎖，一般是為了在一定程度上模擬事務操作，實現對某一個時間點多個表一致性讀取
2.2.1 實例
mysql查詢是否存在。假設我們需要檢查這兩個表的金額合計是否相符,可能就需要執行如下兩條SQL
圖片上傳失敗...(image-3017e3-1547370332969)
這時,如果不先給這兩個表加鎖,就可能產生錯誤的結果;
因為第一條語句執行過程中,order_detail表可能已經發生了改變.
因此,正確寫法應該如下
oracle從一個表查數據插到另一張表，圖片上傳失敗...(image-8081d7-1547370332969)
2.2.2 注意點
session1session2獲得表 film_text 的讀鎖 lock table film_text read;?可select * from film_text可select * from film_text不能查詢沒有鎖定的表 :select * from film可查詢/更新未鎖定的表: select * from film插入或更新鎖定表會提示錯誤 update...from film_text更新鎖定表會等待 update...from film_text釋放鎖 unlock tables等待?獲得鎖，更新成功
##2.3 tips
當使用lock tables時,不僅需要一次鎖定用到的所有表
mysql數據庫的調優和部署，且同一表在SQL語句中出現多少次,就要通過與SQL語句中別名鎖多少次
lock table actor read會提示錯誤
select a.first_name.....需要對別名分別鎖定
lock table actor as a read,actor as b read;在一定條件下，MyISAM也支持并發插入和讀取
3.1 系統變量 : concurrent_insert
控制其并發插入的行為,其值分別可以為
可以利用MyISAM的并發插入特性，來解決應用中對同表查詢和插入的鎖爭用
例如,將concurrent_insert系統變量設為2，總是允許并發插入;
同時,通過定期在系統空閑時段執行OPTIONMIZE TABLE語句來整理空間碎片，收到因刪除記錄而產生的中間空洞
刪除操作不會重整整個表,只是把 行 標記為刪除,在表中留下空洞
MyISAM傾向于在可能時填滿這些空洞,插入時就會重用這些空間,無空洞則把新行插到表尾
3.2 MyISAM的鎖調度
MyISAM的讀和寫鎖互斥,讀操作串行的
這是因為MySQL認為寫請求一般比讀請求重要
這也正是MyISAM表不適合有大量更新 / 查詢操作應用的原因
大量的更新操作會造成查詢操作很難獲得讀鎖,從而可能永遠阻塞
幸好,我們可以通過一些設置來調節MyISAM的調度行為
雖然上面3種方法都是要么更新優先，要么查詢優先，但還是可以用其來解決查詢相對重要的應用(如用戶登錄系統)中，讀鎖等待嚴重的問題
另外，MySQL也提供了一種折中的辦法來調節讀寫沖突;
即給系統參數max_write_lock_count設置一個合適的值;
當一個表的讀鎖達到這個值后，MySQL便暫時將寫請求的優先級降低，給讀進程一定獲得鎖的機會
* * *
InnoDB與MyISAM的最大不同有兩點
行級鎖和表級鎖本來就有許多不同之處，另外，事務的引入也帶來了一些新問題
4.1 事務
一組SQL語句組成的邏輯處理單元
4.2 事務的問題
相對于串行處理來說，并發事務處理能大大增加數據庫資源的利用率，提高數據庫系統的事務吞吐量，從而可以支持可以支持更多的用戶
但并發事務處理也會帶來一些問題，主要包括以下幾種情況
4.3 事務隔離級別
在并發事務的問題中，“更新丟失”通常應該是完全避免的;
但防止更新丟失,并不能單靠數據庫事務控制器來解決,需要應用程序對要更新的數據加必要的鎖來解決,因此，防止更新丟失應該是應用的責任
“臟讀”、“不可重復讀”和“幻讀”,其實都是數據庫讀一致性問題,必須由數據庫提供一定的事務隔離機制來解決
數據庫實現事務隔離的方式，基本可以分為以下兩種
數據庫的事務隔離級別越嚴格,并發副作用越小,但付出的代價也越大
因為事務隔離實質上就是使事務在一定程度上“串行化”進行,這顯然與“并發”矛盾
為了解決“隔離”與“并發”的矛盾，ANSI SQL定義了４種隔離級別
隔離級別/讀數據一致性及允許的并發副作用讀數據一致性臟讀不可重復讀幻讀未提交讀(Read uncommitted)最低級別，只能保證不讀取物理上損壞的數據是是是已提交度(Read committed)語句級否是是可重復讀(Repeatable read)事務級否否是可序列化(Serializable)最高級別，事務級否否否
查看Innodb行鎖爭用情況
如果發現爭用比較嚴重，如Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比較高
查詢information_schema相關表來查看鎖情況
設置Innodb monitors
進一步觀察發生鎖沖突的表，數據行等，并分析鎖爭用的原因
停止監視器
默認情況每15秒會向日志中記錄監控的內容;
如果長時間打開會導致.err文件變得非常巨大;
所以確認原因后,要刪除監控表關閉監視器,或者通過使用--console選項來啟動服務器以關閉寫日志功能
4.4 InnoDB的行鎖
InnoDB支持以下兩種類型的行鎖
MySQL InnoDB默認行級鎖
行級鎖都是基于索引的,若一條SQL語句用不到索引是不會使用行級鎖的,會使用表級鎖把整張表鎖住
為了允許行/表鎖共存,實現多粒度鎖機制,InnoDB還有兩種內部使用的意向鎖(Intention Locks)
這兩種意向鎖都是表鎖
當前鎖/是否兼容/請求鎖XIXSISX沖突沖突沖突沖突IX沖突兼容沖突兼容S沖突沖突兼容兼容IS沖突兼容兼容兼容
如果一個事務請求的鎖模式與當前鎖兼容，InnoDB就請求的鎖授予該事務;
反之，如果兩者兩者不兼容，該事務就要等待鎖釋放
意向鎖是InnoDB自動加的，不需用戶干預.
對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及及數據集加排他鎖(Ｘ);
對于普通SELECT語句，InnoDB不會加任何鎖.
對于SELECT語句,可以通過以下語句顯式地給記錄加讀/寫鎖
共享鎖語句主要用在需要數據依存關系時確認某行記錄是否存在;
并確保沒有人對這個記錄UPDATE或DELETE.
但如果當前事務也需要對該記錄進行更新,則很有可能造成死鎖;
對于鎖定行記錄后需要進行更新操作的應用,應該使用排他鎖語句.
4.5 實例
4.5.1 Innodb共享鎖
session_1session_2set autocommit=0,select * from actor where id =1set autocommit=0,select * from actor where id =1當前seesion對id為1的記錄加入共享鎖 select * from actor where id =1 lock in share mode??其他seesion仍然可以查詢，并對該記錄加入 select * from actor where id =1 lock in share mode當前session對鎖定的記錄進行更新，等待鎖 update。。。where id=1??當前session對鎖定記錄進行更新，則會導致死鎖退出 update。。。where id=1
| 獲得鎖，更新成功 |
4.5.2 Innodb排他鎖
session_1session_2set autocommit=0,select * from actor where id =1set autocommit=0,select * from actor where id =1當前seesion對id為1的記錄加入for update 共享鎖 select * from actor where id =1 for update??可查詢該記錄select from actor where id =1,但是不能再記錄共享鎖，會等待獲得鎖select from actor where id =1 for update更新后釋放鎖 update。。。 commit??其他session，獲得鎖，得到其他seesion提交的記錄
4.6 行鎖的實現
行鎖是通過給索引上的索引項加鎖來實現
如果沒有索引，InnoDB將通過隱藏的聚簇索引來對記錄加鎖
行鎖實現特點意味著：
如果不通過索引條件檢索數據,那么Innodb將對表的所有記錄加鎖，和表鎖一樣
間隙鎖(Next-Key鎖)
當我們用范圍條件而不是相等條件檢索數據,并請求共享或排他鎖時,InnoDB會給符合條件的已有數據的索引項加鎖;
對于鍵值在條件范圍內但并不存在的記錄,叫做“間隙(GAP)”,InnoDB也會對這個“間隙”加鎖,這種鎖機制就是所謂的間隙鎖(Next-Key鎖).
舉例來說，假如emp表中只有101條記錄，其empid的值分別是1,2,...,100,101，下面的SQL：
InnoDB 不僅會對符合條件的 empid 值為 101 的記錄加鎖;
也會對 empid大于101(這些記錄并不存在)的“間隙”加鎖
間隙鎖的目的
4.7 when 使用表鎖
對于InnoDB,在絕大部分情況下都應該使用行鎖
因為事務,行鎖往往是我們選擇InnoDB的理由
但在個別特殊事務中,也可以考慮使用表鎖
當然，應用中這兩種事務不能太多，否則，就應該考慮使用ＭyISAＭ
在InnoDB下 ,使用表鎖要注意
ＭyISAM表鎖是deadlock free的，這是因為ＭyISAM總是一次性獲得所需的全部鎖，要么全部滿足，要么等待，因此不會出現死鎖
但在InnoDB中，除單個SQL組成的事務外，鎖是逐步獲得的，這就決定了InnoDB發生死鎖是可能的
發生死鎖后，InnoDB一般都能自動檢測到，并使一個事務釋放鎖并退回，另一個事務獲得鎖，繼續完成事務
通常來說，死鎖都是應用設計的問題，通過調整業務流程、數據庫對象設計、事務大小、以及訪問數據庫的SQL語句，絕大部分都可以避免
下面就通過實例來介紹幾種死鎖的常用方法。
如果出現死鎖，可以用SHOW INNODB STATUS命令來確定最后一個死鎖產生的原因和改進措施。
6.1 MyISAM的表鎖
6.2 對于InnoDB表
在了解InnoDB的鎖特性后，用戶可以通過設計和SQL調整等措施減少鎖沖突和死鎖
MySQL中的鎖(表鎖、行鎖)
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
