mysql鎖
根據(jù)加鎖的范圍、MySQL里邊的鎖可以分為全局鎖论寨、表級鎖和行鎖三類
全局鎖
全局鎖: 就是要對整個db實例加鎖. mysql提供了一個全局鎖支持:
flush tables with read lock;(FTWRL)
此時: update、ddl爽茴、dml語句會被阻塞
典型場景: 全庫邏輯備份
但是讓整個db處于只讀狀態(tài)葬凳、比較危險:
若是操作主庫、則在整個備份期間室奏、都不能執(zhí)行更新】整個業(yè)務基本上處于停滯狀態(tài)
若操作從庫火焰、則備份期間從庫不能執(zhí)行主庫同步過來的binlog、會導致主從延遲
那么胧沫、如果實現(xiàn)不影響業(yè)務的備份呢 昌简？
1. innodb引擎可以使用一致性讀
mysql自帶備份工具mysqldump使用參數(shù) --single-transaction 時、mysql就好啟動一個事務琳袄、來確保拿到一致性視圖
2. 為何不選擇 set global readonly=true ?
1) 在某些系統(tǒng)中江场、readonly可能被用來做其它邏輯、比如用來判斷db是主還是備庫 窖逗？
2) 在異常處理上的差異
若執(zhí)行FTWRL之后址否、mysql異常斷開、mysql會自動釋放這個全局鎖碎紊、整個庫可以回到正常更新的狀態(tài)
設(shè)置readonly佑附、若client異常、則db會一直保持readonly狀態(tài)仗考、會導致整個庫長時間不可寫音同、風險較高
3) MDL 在slave庫上對super權(quán)限無效
表級鎖
mysql的表級鎖有2種: mysql表鎖 和 元數(shù)據(jù)鎖(meta data lock, MDL)
表鎖的語法是: lock tables .. read/write. 與FTWRL類似、使用unlock tables主動釋放鎖秃嗜、也可以在client斷開時自動釋放
既限制別的線程权均、也限制本線程
eg. 某個線程A中執(zhí)行 lock tables t1 read, t2 write;
則其它線程寫t1, 讀寫t2 都會被阻塞顿膨、同時、線程A在執(zhí)行unlock tables之前叽赊、也只能執(zhí)行讀t1, 讀寫t2的操作
但innodb可以支持行鎖恋沃、一般就不使用 lock tables了、畢竟影響還是很大的
元數(shù)據(jù)鎖:
不需要顯式使用必指、在訪問一個表的時候會被自動加上囊咏、MDL的作用是 保證讀寫的正確性
eg. 正在遍歷某表的數(shù)據(jù)、在執(zhí)行期間另外一個線程對這個表的結(jié)構(gòu)做了變更塔橡、減少了一列梅割、查詢結(jié)果就會有問題
在mysql5.5的版本中引入了MDL、當對表curd操作時葛家、會加MDL讀鎖户辞、對表結(jié)構(gòu)變更時、會加MDL寫鎖
* 讀鎖之間不互斥惦银、可以同時對一張表增刪改查
* 寫鎖咆课、讀寫鎖之間互斥、用來保證表結(jié)構(gòu)變更操作的安全性
so. 兩個線程同時對一個表做結(jié)構(gòu)變更時扯俱、其中一個要等另外一個執(zhí)行完才開始執(zhí)行
案例分析
case: 給一個小表加字段书蚪、卻導致db掛掉
note: 給表加字段或者修改字段或者加索引、都會導致全表掃描數(shù)據(jù)
在對大表操作時迅栅、都會特別小心殊校、避免對線上造成影響、小表一般認為很快結(jié)束读存、會比較大意为流、其實、小表操作不當也會造成db掛掉
實驗環(huán)境: mysql5.6 t是小表
image.png
可以看到: session A先啟動让簿、這時會對t加MDL讀鎖敬察、session B操作不被影響
session C需要MDL寫鎖、blocked尔当、此時db表現(xiàn)為 不可讀寫
若: 此時db的寫十分頻繁莲祸、且client有重試機制、超時后起一個新的session椭迎、這些session很快就會把連接打滿
Q: 那么如何安全的給表加字段呢 锐帜？
首先要解決長事務、事務不提交畜号、就會一直占著MDL鎖. 在mysql的information_schema庫的innodb_trx表中缴阎、可以看到當前
正在執(zhí)行的事務、若要做ddl變更的表剛好有長事務在執(zhí)行简软、要考慮先暫停DDL或者kill掉事務
若要變更的是熱點表蛮拔、請求頻繁述暂、雖然數(shù)據(jù)量不大、但請求特別頻繁语泽、而不得不加字段贸典、如何做 ?
此時kill未必管用视卢、因為新的請求馬上就進來了踱卵、此時比較理想的機制是:
在alter table語句里設(shè)定等待時間、若在這個等待時間里能拿到MDL寫鎖最好据过、拿不到也不要阻塞后邊的業(yè)務惋砂、先放棄
之后再重復
語法:
alter table table nowait add column...
alter table table wait n add column...
查看鎖表情況
show status like 'table%';
table_locks_immediate 指能夠立即獲取表級鎖的次數(shù)
table_locks_waited 指的是不能立即獲取表級鎖而需要等待的次數(shù)、num越大绳锅、鎖等待越多西饵、有鎖爭用的情況
查看正在被鎖定的表
show open tables where in_use>0;
鎖涉及表說明
information_shcema庫
innodb_trx 當前innodb內(nèi)核中的活躍事務
innodb_locks 當前狀態(tài)下產(chǎn)生的innodb鎖、僅在有鎖等待時打印
innodb_lock_waits 當前狀態(tài)產(chǎn)生的innodb鎖等待 僅在有鎖等待時打印
innodb_trx表結(jié)構(gòu)說明
字段名 說明
trx_id innodb 存儲引擎內(nèi)部唯一的事物ID
trx_state 當前事務狀態(tài)（running和lock wait兩種狀態(tài)）
trx_started 事務的開始時間
trx_requested_lock_id 等待事務的鎖ID鳞芙，如trx_state的狀態(tài)為Lock wait眷柔，那么該值帶表當前事物等待之前事物占用資源的ID，若trx_state不是Lock wait 則該值為NULL
trx_wait_started 事務等待的開始時間
trx_weight 事務的權(quán)重原朝，在innodb存儲引擎中驯嘱，當發(fā)生死鎖需要回滾的時，innodb存儲引擎會選擇該值最小的進行回滾
trx_mysql_thread_id mysql中的線程id, 即show processlist顯示的結(jié)果
trx_query 事務運行的SQL語句
innodb_locks表結(jié)構(gòu)說明
字段名 說明
lock_id 鎖的ID
lock_trx_id 事務的ID
lock_mode 鎖的模式（S鎖與X鎖兩種模式）
lock_type 鎖的類型 表鎖還是行鎖（RECORD）
lock_table 要加鎖的表
lock_index 鎖住的索引
lock_space 鎖住對象的space id
lock_page 事務鎖定頁的數(shù)量喳坠，若是表鎖則該值為NULL
lock_rec 事務鎖定行的數(shù)量鞠评，若是表鎖則該值為NULL
lock_data 事務鎖定記錄主鍵值，若是表鎖則該值為NULL（此選項不可信)
innodb_lock_waits表結(jié)構(gòu)說明
字段名 說明
requesting_trx_id 申請鎖資源的事物ID
requested_lock_id 申請的鎖的ID
blocking_trx_id 阻塞其他事物的事物ID
blocking_lock_id 阻塞其他鎖的鎖ID
加鎖的原則
1. 基本單位 nex-key-lock
2. 查找過程中訪問到的對象才加鎖
3. 索引上的等值查詢壕鹉、給唯一錯音加鎖的時候剃幌、next-key lock退化為行鎖
4. 索引上的等值查詢、向右遍歷時且最后一個值不滿足等值條件時晾浴、退化為間隙鎖
5. 唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止
