1. 用sql語句，怎麼解決mysql資料庫死鎖
MySQL死鎖問題的相關知識是本文我們主要要介紹的內容，接下來我們就來一一介紹這部分內容，希望能夠對您有所幫助。
1、MySQL常用存儲引擎的鎖機制
MyISAM和MEMORY採用表級鎖(table-level locking)
BDB採用頁面鎖(page-level locking)或表級鎖，默認為頁面鎖
InnoDB支持行級鎖(row-level locking)和表級鎖，默認為行級鎖
2、各種鎖特點
表級鎖：開銷小，加鎖快;不會出現死鎖;鎖定粒度大，發生鎖沖突的概率最高，並發度最低
行級鎖：開銷大，加鎖慢;會出現死鎖;鎖定粒度最小，發生鎖沖突的概率最低，並發度也最高
頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間;會出現死鎖;鎖定粒度界於表鎖和行鎖之間，並發度一般
3、各種鎖的適用場景
表級鎖更適合於以查詢為主，只有少量按索引條件更新數據的應用，如Web應用
行級鎖則更適合於有大量按索引條件並發更新數據，同時又有並發查詢的應用，如一些在線事務處理系統
4、死鎖
是指兩個或兩個以上的進程在執行過程中，因爭奪資源而造成的一種互相等待的現象，若無外力作用，它們都將無法推進下去。
表級鎖不會產生死鎖。所以解決死鎖主要還是針對於最常用的InnoDB。
5、死鎖舉例分析
在MySQL中，行級鎖並不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引;如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引。
在UPDATE、DELETE操作時，MySQL不僅鎖定WHERE條件掃描過的所有索引記錄，而且會鎖定相鄰的鍵值，即所謂的next-key locking。
例如，一個表db。tab_test，結構如下：
id：主鍵;
state：狀態;
time：時間;
索引：idx_1(state，time)
出現死鎖日誌如下：
?***(1) TRANSACTION:
?TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OSthread id 278546 starting index read
?mysql tables in use 1, locked 1
?LOCK WAIT 3 lock struct(s), heap size 320
?MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update
?update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute) (任務1的sql語句)
?***(1) WAITING FOR THIS LOCK TO BE GRANTED: (任務1等待的索引記錄)
?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833455 _mode X locks rec but not gap waiting
?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
?*** (2) TRANSACTION:
?TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499
?mysql tables in use 1, locked 1
?3 lock struct(s), heap size 320, undo log entries 1
?MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating update tab_test set state=1067,time=now () where id in (9921180) (任務2的sql語句)
?*** (2) HOLDS THE LOCK(S): (任務2已獲得的鎖)
?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap
?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
?*** (2) WAITING FOR THIS LOCK TO BE GRANTED: (任務2等待的鎖)
?RECORD LOCKS space id 0 page no 843102 n bits 600 index `idx_1` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap waiting
?Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
?0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;;
?*** WE ROLL BACK TRANSACTION (1)
?(回滾了任務1，以解除死鎖)
原因分析：
當「update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)」執行時，MySQL會使用idx_1索引，因此首先鎖定相關的索引記錄，因為idx_1是非主鍵索引，為執行該語句，MySQL還會鎖定主鍵索引。
假設「update tab_test set state=1067,time=now () where id in (9921180)」幾乎同時執行時，本語句首先鎖定主鍵索引，由於需要更新state的值，所以還需要鎖定idx_1的某些索引記錄。
這樣第一條語句鎖定了idx_1的記錄，等待主鍵索引，而第二條語句則鎖定了主鍵索引記錄，而等待idx_1的記錄，這樣死鎖就產生了。
6、解決辦法
拆分第一條sql，先查出符合條件的主鍵值，再按照主鍵更新記錄：
?select id from tab_test where state=1061 and time < date_sub(now(), INTERVAL 30 minute);
?update tab_test state=1064,time=now() where id in(......);
2. 資料庫死鎖處理方法
mysql資料庫死鎖解決方法如下：
1、對於按鈕等控制項，點擊後使其立刻失效，不讓用戶重復點擊，避免對同時對同一條記錄操作。
2、使用樂觀鎖進行控制。樂觀鎖大多是基於數據版本（Version）記錄機制實現。即為數據增加一個版本標識，在基於資料庫表的版本解決方案中，一般是 通過為資料庫表增加一個「version」欄位來實現。讀取出數據時，將此版本號一同讀出，之後更新時，對此版本號加一。此時，將提交數據的版本數據與數 據庫表對應記錄的當前版本信息進行比對，如果提交的數據版本號大於資料庫表當前版本號，則予以更新，否則認為是過期數據。樂觀鎖機制避免了長事務中的數據 庫加鎖開銷（用戶A和用戶B操作過程中，都沒有對資料庫數據加鎖），大大提升了大並發量下的系統整體性能表現。Hibernate 在其數據訪問引擎中內置了樂觀鎖實現。需要注意的是，由於樂觀鎖機制是在系統中實現，來自外部系統的用戶更新操作不受系統的控制，因此可能會造 成臟數據被更新到資料庫中。
3. oracle資料庫實例被鎖如何解決
在pl/sql Developer工具的的菜單「tools」裡面的「sessions」可以查詢現在存在的會話，但是我們很難找到那個會話被鎖定了，想找到所以被鎖的會話就更難了，下面這叫查詢語句可以查詢出所以被鎖的會話。如下：
SELECT sn.username, m.SID,sn.SERIAL#, m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) lmode,
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) request,
m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.SID = m.SID AND m.request != 0) --存在鎖請求，即被阻塞
OR ( sn.SID = m.SID --不存在鎖請求，但是鎖定的對象被其他會話請求鎖定
AND m.request = 0
AND lmode != 4
AND (id1, id2) IN (
SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0 AND s.id1 = m.id1
AND s.id2 = m.id2)
)
ORDER BY id1, id2, m.request;
通過以上查詢知道了sid和 SERIAL#就可以開殺了
alter system kill session 'sid,SERIAL#';
希望對你有所幫助。我也是不太懂，是在網上了解的。努力學習ing～～～
4. MySQL資料庫表被鎖、解鎖，刪除事務
在程序員的職業生涯中，總會遇到資料庫表被鎖的情況，前些天就又撞見一次。由於業務突發需求，各個部門都在批量操作、導出數據，而資料庫又未做讀寫分離，結果就是：資料庫的某張表被鎖了！
用戶反饋系統部分功能無法使用，緊急排查，定位是資料庫表被鎖，然後進行緊急處理。這篇文章給大家講講遇到類似緊急狀況的排查及解決過程，建議點贊收藏，以備不時之需。
用戶反饋某功能頁面報502錯誤，於是第一時間看服務是否正常，資料庫是否正常。在控制台看到資料庫CPU飆升，堆積大量未提交事務，部分事務已經阻塞了很長時間，基本定位是資料庫層出現問題了。
查看阻塞事務列表，發現其中有鎖表現象，本想利用控制台直接結束掉阻塞的事務，但控制台賬號許可權有限，於是通過客戶端登錄對應賬號將鎖表事務kill掉，才避免了情況惡化。
下面就聊聊，如果當突然面對類似的情況，我們該如何緊急響應？
想像一個場景，當然也是軟體工程師職業生涯中會遇到的一種場景：原本運行正常的程序，某一天突然資料庫的表被鎖了，業務無法正常運轉，那麼我們該如何快速定位是哪個事務鎖了表，如何結束對應的事物？
首先最簡單粗暴的方式就是：重啟MySQL。對的，網管解決問題的神器——「重啟」。至於後果如何，你能不能跑了，要你自己三思而後行了！
重啟是可以解決表被鎖的問題的，但針對線上業務很顯然不太具有可行性。
下面來看看不用跑路的解決方案：
遇到資料庫阻塞問題，首先要查詢一下表是否在使用。
如果查詢結果為空，那麼說明表沒在使用，說明不是鎖表的問題。
如果查詢結果不為空，比如出現如下結果：
則說明表（test）正在被使用，此時需要進一步排查。
查看資料庫當前的進程，看看是否有慢SQL或被阻塞的線程。
執行命令：
該命令只顯示當前用戶正在運行的線程，當然，如果是root用戶是能看到所有的。
在上述實踐中，阿里雲控制台之所以能夠查看到所有的線程，猜測應該使用的就是root用戶，而筆者去kill的時候，無法kill掉，是因為登錄的用戶非root的資料庫賬號，無法操作另外一個用戶的線程。
如果情況緊急，此步驟可以跳過，主要用來查看核對：
如果情況緊急，此步驟可以跳過，主要用來查看核對：
看事務表INNODB_TRX中是否有正在鎖定的事務線程，看看ID是否在show processlist的sleep線程中。如果在，說明這個sleep的線程事務一直沒有commit或者rollback，而是卡住了，需要手動kill掉。
搜索的結果中，如果在事務表發現了很多任務，最好都kill掉。
執行kill命令：
對應的線程都執行完kill命令之後，後續事務便可正常處理。
針對緊急情況，通常也會直接操作第一、第二、第六步。
這里再補充一些MySQL鎖相關的知識點：資料庫鎖設計的初衷是處理並發問題，作為多用戶共享的資源，當出現並發訪問的時候，資料庫需要合理地控制資源的訪問規則，而鎖就是用來實現這些訪問規則的重要數據結構。
根據加鎖的范圍，MySQL裡面的鎖大致可以分成全局鎖、表級鎖和行鎖三類。MySQL中表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（metadata lock，MDL)。
表鎖是在Server層實現的，ALTER TABLE之類的語句會使用表鎖，忽略存儲引擎的鎖機制。表鎖通過lock tables… read/write來實現，而對於InnoDB來說，一般會採用行級鎖。畢竟鎖住整張表影響范圍太大了。
另外一個表級鎖是MDL（metadata lock），用於並發情況下維護數據的一致性，保證讀寫的正確性，不需要顯式的使用，在訪問一張表時會被自動加上。
常見的一種鎖表場景就是有事務操作處於：Waiting for table metadata lock狀態。
MySQL在進行alter table等DDL操作時，有時會出現Waiting for table metadata lock的等待場景。
一旦alter table TableA的操作停滯在Waiting for table metadata lock狀態，後續對該表的任何操作（包括讀）都無法進行，因為它們也會在Opening tables的階段進入到Waiting for table metadata lock的鎖等待隊列。如果核心表出現了鎖等待隊列，就會造成災難性的後果。
通過show processlist可以看到表上有正在進行的操作（包括讀），此時alter table語句無法獲取到metadata 獨占鎖，會進行等待。
通過show processlist看不到表上有任何操作，但實際上存在有未提交的事務，可以在information_schema.innodb_trx中查看到。在事務沒有完成之前，表上的鎖不會釋放，alter table同樣獲取不到metadata的獨占鎖。
處理方法：通過 select * from information_schema.innodb_trxG, 找到未提交事物的sid，然後kill掉，讓其回滾。
通過show processlist看不到表上有任何操作，在information_schema.innodb_trx中也沒有任何進行中的事務。很可能是因為在一個顯式的事務中，對表進行了一個失敗的操作（比如查詢了一個不存在的欄位），這時事務沒有開始，但是失敗語句獲取到的鎖依然有效，沒有釋放。從performance_schema.events_statements_current表中可以查到失敗的語句。
處理方法：通過performance_schema.events_statements_current找到其sid，kill 掉該session，也可以kill掉DDL所在的session。
總之，alter table的語句是很危險的（核心是未提交事務或者長事務導致的），在操作之前要確認對要操作的表沒有任何進行中的操作、沒有未提交事務、也沒有顯式事務中的報錯語句。
如果有alter table的維護任務，在無人監管的時候運行，最好通過lock_wait_timeout設置好超時時間，避免長時間的metedata鎖等待。
關於MySQL的鎖表其實還有很多其他場景，我們在實踐的過程中盡量避免鎖表情況的發生，當然這需要一定經驗的支撐。但更重要的是，如果發現鎖表我們要能夠快速的響應，快速的解決問題，避免影響正常業務，避免情況進一步惡化。所以，本文中的解決思路大家一定要收藏或記憶一下，做到有備無患，避免突然狀況下抓瞎。