在程序員的職業(yè)生涯中，總會遇到數(shù)據(jù)庫表被鎖的情況，前些天就又撞見一次。由于業(yè)務(wù)突發(fā)需求，各個(gè)部門都在批量操作、導(dǎo)出數(shù)據(jù)，而數(shù)據(jù)庫又未做讀寫分離，結(jié)果就是：數(shù)據(jù)庫的某張表被鎖了！
網(wǎng)站建設(shè)哪家好，找成都創(chuàng)新互聯(lián)！專注于網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、成都微信小程序、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了聶榮免費(fèi)建站歡迎大家使用！
用戶反饋系統(tǒng)部分功能無法使用，緊急排查，定位是數(shù)據(jù)庫表被鎖，然后進(jìn)行緊急處理。這篇文章給大家講講遇到類似緊急狀況的排查及解決過程，建議點(diǎn)贊收藏，以備不時(shí)之需。
用戶反饋某功能頁面報(bào)502錯(cuò)誤，于是第一時(shí)間看服務(wù)是否正常，數(shù)據(jù)庫是否正常。在控制臺看到數(shù)據(jù)庫CPU飆升，堆積大量未提交事務(wù)，部分事務(wù)已經(jīng)阻塞了很長時(shí)間，基本定位是數(shù)據(jù)庫層出現(xiàn)問題了。
查看阻塞事務(wù)列表，發(fā)現(xiàn)其中有鎖表現(xiàn)象，本想利用控制臺直接結(jié)束掉阻塞的事務(wù)，但控制臺賬號權(quán)限有限，于是通過客戶端登錄對應(yīng)賬號將鎖表事務(wù)kill掉，才避免了情況惡化。
下面就聊聊，如果當(dāng)突然面對類似的情況，我們該如何緊急響應(yīng)？
想象一個(gè)場景，當(dāng)然也是軟件工程師職業(yè)生涯中會遇到的一種場景：原本運(yùn)行正常的程序，某一天突然數(shù)據(jù)庫的表被鎖了，業(yè)務(wù)無法正常運(yùn)轉(zhuǎn)，那么我們該如何快速定位是哪個(gè)事務(wù)鎖了表，如何結(jié)束對應(yīng)的事物？
首先最簡單粗暴的方式就是：重啟MySQL。對的，網(wǎng)管解決問題的神器——“重啟”。至于后果如何，你能不能跑了，要你自己三思而后行了！
重啟是可以解決表被鎖的問題的，但針對線上業(yè)務(wù)很顯然不太具有可行性。
下面來看看不用跑路的解決方案：
遇到數(shù)據(jù)庫阻塞問題，首先要查詢一下表是否在使用。
如果查詢結(jié)果為空，那么說明表沒在使用，說明不是鎖表的問題。
如果查詢結(jié)果不為空，比如出現(xiàn)如下結(jié)果：
則說明表（test）正在被使用，此時(shí)需要進(jìn)一步排查。
查看數(shù)據(jù)庫當(dāng)前的進(jìn)程，看看是否有慢SQL或被阻塞的線程。
執(zhí)行命令：
該命令只顯示當(dāng)前用戶正在運(yùn)行的線程，當(dāng)然，如果是root用戶是能看到所有的。
在上述實(shí)踐中，阿里云控制臺之所以能夠查看到所有的線程，猜測應(yīng)該使用的就是root用戶，而筆者去kill的時(shí)候，無法kill掉，是因?yàn)榈卿浀挠脩舴莚oot的數(shù)據(jù)庫賬號，無法操作另外一個(gè)用戶的線程。
如果情況緊急，此步驟可以跳過，主要用來查看核對：
如果情況緊急，此步驟可以跳過，主要用來查看核對：
看事務(wù)表INNODB_TRX中是否有正在鎖定的事務(wù)線程，看看ID是否在show processlist的sleep線程中。如果在，說明這個(gè)sleep的線程事務(wù)一直沒有commit或者rollback，而是卡住了，需要手動kill掉。
搜索的結(jié)果中，如果在事務(wù)表發(fā)現(xiàn)了很多任務(wù)，最好都kill掉。
執(zhí)行kill命令：
對應(yīng)的線程都執(zhí)行完kill命令之后，后續(xù)事務(wù)便可正常處理。
針對緊急情況，通常也會直接操作第一、第二、第六步。
這里再補(bǔ)充一些MySQL鎖相關(guān)的知識點(diǎn)：數(shù)據(jù)庫鎖設(shè)計(jì)的初衷是處理并發(fā)問題，作為多用戶共享的資源，當(dāng)出現(xiàn)并發(fā)訪問的時(shí)候，數(shù)據(jù)庫需要合理地控制資源的訪問規(guī)則，而鎖就是用來實(shí)現(xiàn)這些訪問規(guī)則的重要數(shù)據(jù)結(jié)構(gòu)。
根據(jù)加鎖的范圍，MySQL里面的鎖大致可以分成全局鎖、表級鎖和行鎖三類。MySQL中表級別的鎖有兩種：一種是表鎖，一種是元數(shù)據(jù)鎖（metadata lock，MDL)。
表鎖是在Server層實(shí)現(xiàn)的，ALTER TABLE之類的語句會使用表鎖，忽略存儲引擎的鎖機(jī)制。表鎖通過lock tables… read/write來實(shí)現(xiàn)，而對于InnoDB來說，一般會采用行級鎖。畢竟鎖住整張表影響范圍太大了。
另外一個(gè)表級鎖是MDL（metadata lock），用于并發(fā)情況下維護(hù)數(shù)據(jù)的一致性，保證讀寫的正確性，不需要顯式的使用，在訪問一張表時(shí)會被自動加上。
常見的一種鎖表場景就是有事務(wù)操作處于：Waiting for table metadata lock狀態(tài)。
MySQL在進(jìn)行alter table等DDL操作時(shí)，有時(shí)會出現(xiàn)Waiting for table metadata lock的等待場景。
一旦alter table TableA的操作停滯在Waiting for table metadata lock狀態(tài)，后續(xù)對該表的任何操作（包括讀）都無法進(jìn)行，因?yàn)樗鼈円矔贠pening tables的階段進(jìn)入到Waiting for table metadata lock的鎖等待隊(duì)列。如果核心表出現(xiàn)了鎖等待隊(duì)列，就會造成災(zāi)難性的后果。
通過show processlist可以看到表上有正在進(jìn)行的操作（包括讀），此時(shí)alter table語句無法獲取到metadata 獨(dú)占鎖，會進(jìn)行等待。
通過show processlist看不到表上有任何操作，但實(shí)際上存在有未提交的事務(wù)，可以在information_schema.innodb_trx中查看到。在事務(wù)沒有完成之前，表上的鎖不會釋放，alter table同樣獲取不到metadata的獨(dú)占鎖。
處理方法：通過 select * from information_schema.innodb_trxG, 找到未提交事物的sid，然后kill掉，讓其回滾。
通過show processlist看不到表上有任何操作，在information_schema.innodb_trx中也沒有任何進(jìn)行中的事務(wù)。很可能是因?yàn)樵谝粋€(gè)顯式的事務(wù)中，對表進(jìn)行了一個(gè)失敗的操作（比如查詢了一個(gè)不存在的字段），這時(shí)事務(wù)沒有開始，但是失敗語句獲取到的鎖依然有效，沒有釋放。從performance_schema.events_statements_current表中可以查到失敗的語句。
處理方法：通過performance_schema.events_statements_current找到其sid，kill 掉該session，也可以kill掉DDL所在的session。
總之，alter table的語句是很危險(xiǎn)的（核心是未提交事務(wù)或者長事務(wù)導(dǎo)致的），在操作之前要確認(rèn)對要操作的表沒有任何進(jìn)行中的操作、沒有未提交事務(wù)、也沒有顯式事務(wù)中的報(bào)錯(cuò)語句。
如果有alter table的維護(hù)任務(wù)，在無人監(jiān)管的時(shí)候運(yùn)行，最好通過lock_wait_timeout設(shè)置好超時(shí)時(shí)間，避免長時(shí)間的metedata鎖等待。
關(guān)于MySQL的鎖表其實(shí)還有很多其他場景，我們在實(shí)踐的過程中盡量避免鎖表情況的發(fā)生，當(dāng)然這需要一定經(jīng)驗(yàn)的支撐。但更重要的是，如果發(fā)現(xiàn)鎖表我們要能夠快速的響應(yīng)，快速的解決問題，避免影響正常業(yè)務(wù)，避免情況進(jìn)一步惡化。所以，本文中的解決思路大家一定要收藏或記憶一下，做到有備無患，避免突然狀況下抓瞎。
MySQL 事務(wù)
什么是事務(wù)？
MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。比如說，在人員管理系統(tǒng)中，你刪除一個(gè)人員，你既需要刪除人員的基本資料，也要刪除和該人員相關(guān)的信息，如信箱，文章等等，這樣，這些數(shù)據(jù)庫操作語句就構(gòu)成一個(gè)事務(wù)！
在 MySQL 中只有使用了 Innodb 數(shù)據(jù)庫引擎的數(shù)據(jù)庫或表才支持事務(wù)。
事務(wù)處理可以用來維護(hù)數(shù)據(jù)庫的完整性，保證成批的 SQL 語句要么全部執(zhí)行，要么全部不執(zhí)行。
事務(wù)用來管理 insert,update,delete 語句
一般來說，事務(wù)是必須滿足4個(gè)條件（ACID）：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨(dú)立性）、持久性（Durability）。
原子性：一個(gè)事務(wù)（transaction）中的所有操作，要么全部完成，要么全部不完成，不會結(jié)束在中間某個(gè)環(huán)節(jié)。事務(wù)在執(zhí)行過程中發(fā)生錯(cuò)誤，會被回滾（Rollback）到事務(wù)開始前的狀態(tài)，就像這個(gè)事務(wù)從來沒有執(zhí)行過一樣。
一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預(yù)設(shè)規(guī)則，這包含資料的精確度、串聯(lián)性以及后續(xù)數(shù)據(jù)庫可以自發(fā)性地完成預(yù)定的工作。
隔離性：數(shù)據(jù)庫允許多個(gè)并發(fā)事務(wù)同時(shí)對其數(shù)據(jù)進(jìn)行讀寫和修改的能力，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同級別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（Serializable）。
持久性：事務(wù)處理結(jié)束后，對數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會丟失。
在 MySQL 命令行的默認(rèn)設(shè)置下，事務(wù)都是自動提交的，即執(zhí)行 SQL 語句后就會馬上執(zhí)行 COMMIT 操作。因此要顯式地開啟一個(gè)事務(wù)務(wù)須使用命令 BEGIN 或 START TRANSACTION，或者執(zhí)行命令 SET AUTOCOMMIT=0，用來禁止使用當(dāng)前會話的自動提交。
from 樹懶學(xué)堂 - 一站式數(shù)據(jù)知識平臺
解決方案之一，就是把你的所有操作放在一個(gè)連接中執(zhí)行完畢。mysql
-h${HOSTNAME}
-P${PORT}
-u${USERNAME}
-p${PASSWORD}
${DBNAME}
x.sqlset
AUTOCOMMIT=0;start
transaction;在X.sql
中你可以寫入語句。中間部分是一些數(shù)據(jù)庫的操作，如移表，刪除等commit;
1、用begin,rollback,commit來實(shí)現(xiàn)
begin 開始一個(gè)事務(wù)
rollback 事務(wù)回滾
commit 事務(wù)確認(rèn)
2、直接用set來改變mysql的自動提交模式
MYSQL默認(rèn)是自動提交的，也就是你提交一個(gè)QUERY，它就直接執(zhí)行！我們可以通過
set autocommit=0 禁止自動提交
set autocommit=1 開啟自動提交
來實(shí)現(xiàn)事務(wù)的處理。
我們經(jīng)常會遇到操作一張大表，發(fā)現(xiàn)操作時(shí)間過長或影響在線業(yè)務(wù)了，想要回退大表操作的場景。在我們停止大表操作之后，等待回滾是一個(gè)很漫長的過程，盡管你可能對知道一些縮短時(shí)間的方法，處于對生產(chǎn)環(huán)境數(shù)據(jù)完整性的敬畏，也會選擇不做介入。最終選擇不作為的原因大多源于對操作影響的不確定性。實(shí)踐出真知，下面針對兩種主要提升事務(wù)回滾速度的方式進(jìn)行驗(yàn)證，一種是提升操作可用內(nèi)存空間，一種是通過停實(shí)例，禁用 redo 回滾方式進(jìn)行進(jìn)行驗(yàn)證。
仔細(xì)閱讀過官方手冊的同學(xué)，一定留意到了對于提升大事務(wù)回滾效率，官方提供了兩種方法：一是增加 innodb_buffer_pool_size 參數(shù)大小，二是合理利用 innodb_force_recovery=3 參數(shù)，跳過事務(wù)回滾過程。第一種方式比較溫和，innodb_buffer_pool_size 參數(shù)是可以動態(tài)調(diào)整的，可行性也較高。第二種方式相較之下較暴力，但效果較好。
兩種方式各有自己的優(yōu)點(diǎn)，第一種方式對線上業(yè)務(wù)系統(tǒng)影響較小，不會中斷在線業(yè)務(wù)。第二種方式效果更顯著，會短暫影響業(yè)務(wù)連續(xù)，回滾所有沒有提交的事務(wù)。
千萬行數(shù)據(jù)，表數(shù)據(jù)巨大。
? ? ? ? ? ? 1、慢查詢的產(chǎn)生：很難在一定時(shí)間內(nèi)過濾出所需要的數(shù)據(jù)。
? ? ? ? ? ? 2、建立索引需要更長的時(shí)間。
Mysql版本5.5 建立索引會鎖表
Mysql版本=5.5 誰讓不會鎖表，但會引起主從延遲。
? ? ? ? ? ? 3、修改表結(jié)構(gòu)需要長時(shí)間鎖定表
? ? ? ? ? ? 4、會造成長時(shí)間的主從延遲。
? ? ? ? ? ? 5、影響正常的數(shù)據(jù)操作。
1、分庫分表把一張大表分為多個(gè)小表
難點(diǎn)：
分表主鍵的選擇
分表后跨分區(qū)數(shù)據(jù) 的查詢和統(tǒng)計(jì)
2、大表的歷史數(shù)據(jù)歸檔
? ? ? ? ? ? ? ?好處：
減少對前后端業(yè)務(wù)的影響。
? ? ? ? ? ? ? ?難點(diǎn)：
歸檔時(shí)間點(diǎn)的選擇
如何進(jìn)行歸檔操作。
運(yùn)行時(shí)間比較長，操作的數(shù)據(jù)比較多的事務(wù)。
鎖定太多的數(shù)據(jù)，造成大量的阻塞和鎖超時(shí)。
? ? ? ? 回滾時(shí)間比較長
執(zhí)行時(shí)間比較長，容易造成主從延遲。
避免一次處理太多的數(shù)據(jù)。
移除不必要在事務(wù)中操作的select操作
重慶分公司
重慶分公司
