問(wèn)題描述

在開發(fā)或生產(chǎn)環(huán)境中，我們經(jīng)常會(huì)遇到數(shù)據(jù)庫(kù)鎖表的情況，一旦發(fā)生鎖表，對(duì)業(yè)務(wù)將會(huì)產(chǎn)生很大的影響，本篇主要介紹如何判斷數(shù)據(jù)庫(kù)鎖表和鎖表后的處理。

mysql鎖表處理

一、導(dǎo)致鎖表的原因

1、鎖表發(fā)生在insert update 、delete 中；

2、鎖表的原理是 數(shù)據(jù)庫(kù)使用獨(dú)占式封鎖機(jī)制，當(dāng)執(zhí)行上面的語(yǔ)句時(shí)，對(duì)表進(jìn)行鎖住，直到發(fā)生commite 或者 回滾 或者退出數(shù)據(jù)庫(kù)用戶；

3、鎖表的原因 ：

1)、A程序執(zhí)行了對(duì) tableA 的 insert ，并還未 commite時(shí)，B程序也對(duì)tableA 進(jìn)行insert 則此時(shí)會(huì)發(fā)生資源正忙的異常 就是鎖表；

2)、鎖表常發(fā)生于并發(fā)而不是并行（并行時(shí)，一個(gè)線程操作數(shù)據(jù)庫(kù)時(shí)，另一個(gè)線程是不能操作數(shù)據(jù)庫(kù)的，cpu 和i/o 分配原則）

4、減少鎖表的概率：
減少insert 、update 、delete 語(yǔ)句執(zhí)行 到 commite 之間的時(shí)間。
具體點(diǎn)批量執(zhí)行改為單個(gè)執(zhí)行、優(yōu)化sql自身的非執(zhí)行速度
如果異常對(duì)事物進(jìn)行回滾。

二、mysql鎖表的解決

#查看進(jìn)程id，然后用kill id殺掉進(jìn)程

1)processlist展示了對(duì)應(yīng)的數(shù)據(jù)庫(kù)有哪些線程在運(yùn)行，如果有些語(yǔ)句執(zhí)行速度慢，可以在里面看到

show processlist;

#根據(jù)不同的賬號(hào)權(quán)限可以看到所有用戶的，當(dāng)前用戶的對(duì)應(yīng)的連接。只有100條

show full processlist:

展示所有的連接數(shù)據(jù)

SELECT * FROM information_schema.PROCESSLIST；

#查詢正在執(zhí)行的進(jìn)程

SELECT * FROM information_schema.PROCESSLIST where length(info) >0 ;

字段說(shuō)明

ID 連接標(biāo)識(shí)符，這個(gè)ID和show processlist 中ID是一樣的，也和Performance Schema 中threads表的PROCESSLIST_ID 欄位是一樣的，也和CONNECTION_ID()函數(shù)返回的是一樣的
USER 連接的用戶，其中system user代表系統(tǒng)用戶，非用戶連接，unauthenticated user代表是用戶連接，但是未完成認(rèn)證，event_scheduler 用戶代表的是監(jiān)控定時(shí)任務(wù)的用戶
HOST 連接的主機(jī)名，如果用戶是system user，則HOST為空
DB 連接的數(shù)據(jù)庫(kù)，如果未選擇數(shù)據(jù)庫(kù)，則為NULL
COMMAND 線程執(zhí)行的命令類型
TIME 線程在當(dāng)前狀態(tài)持續(xù)的時(shí)間，以秒為單位
STATE 線程當(dāng)前的狀態(tài)，如果該狀態(tài)持續(xù)很久，說(shuō)明有問(wèn)題，如果是SHOW PROCESSLIST 命令，則狀態(tài)為NULL
INFO 線程執(zhí)行的具體命令，如果執(zhí)行的是call procedure，這里可能顯示的是內(nèi)容的語(yǔ)句，如select

#查詢是否鎖表

show OPEN  TABLES where In_use > 0;

字段說(shuō)明

Database 含有該表的數(shù)據(jù)庫(kù)。
Table 表名稱。
In_use 表當(dāng)前被查詢使用的次數(shù)。如果該數(shù)為零，則表是打開的，但是當(dāng)前沒(méi)有被使用。
Name_locked 表名稱是否被鎖定。名稱鎖定用于取消表或?qū)Ρ磉M(jìn)行重命名等操作

#查看被鎖住的

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

innodb_locks提供有關(guān)InnoDB 事務(wù)已請(qǐng)求但尚未獲取的每個(gè)鎖的信息，以及事務(wù)持有的阻止另一個(gè)事務(wù)的每個(gè)鎖。

字段說(shuō)明

lock_id：鎖 ID。
lock_trx_id：擁有鎖的事務(wù) ID?？梢院?INNODB_TRX 表 JOIN 得到事務(wù)的詳細(xì)信息。
lock_mode：鎖的模式。有如下鎖類型：行級(jí)鎖包括：S、X、IS、IX，分別代表：共享鎖、排它鎖、意向共享鎖、意向排它鎖。表級(jí)鎖包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分別代表共享間隙鎖、排它間隙鎖、意向共享間隙鎖、意向排它間隙鎖和自動(dòng)遞增鎖。
lock_type：鎖的類型。RECORD 代表行級(jí)鎖，TABLE 代表表級(jí)鎖。
lock_table：被鎖定的或者包含鎖定記錄的表的名稱。
lock_index：當(dāng) LOCK_TYPE=’RECORD’ 時(shí)，表示索引的名稱；否則為 NULL。
lock_space：當(dāng) LOCK_TYPE=’RECORD’ 時(shí)，表示鎖定行的表空間 ID；否則為 NULL。
lock_page：當(dāng) LOCK_TYPE=’RECORD’ 時(shí)，表示鎖定行的頁(yè)號(hào)；否則為 NULL。
lock_rec：當(dāng) LOCK_TYPE=’RECORD’ 時(shí)，表示一堆頁(yè)面中鎖定行的數(shù)量，亦即被鎖定的記錄號(hào)；否則為 NULL。
lock_data：當(dāng) LOCK_TYPE=’RECORD’ 時(shí)，表示鎖定行的主鍵；否則為NULL。

#等待鎖定

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

innodb_lock_waits包含每個(gè)被阻止InnoDB 事務(wù)的一個(gè)或多個(gè)行，指示它已請(qǐng)求的鎖以及阻止該請(qǐng)求的任何鎖。

字段說(shuō)明

requesting_trx_id：請(qǐng)求事務(wù)的 ID。
requested_lock_id：事務(wù)所等待的鎖定的 ID?？梢院?INNODB_LOCKS 表 JOIN。
blocking_trx_id：阻塞事務(wù)的 ID。
blocking_lock_id：某一事務(wù)的鎖的 ID，該事務(wù)阻塞了另一事務(wù)的運(yùn)行?？梢院?INNODB_LOCKS 表 JOIN

#殺掉鎖表進(jìn)程

kill 5601

達(dá)夢(mèng)數(shù)據(jù)庫(kù)鎖表處理

1. 定義

阻塞：多個(gè)線程之間的相互影響，等待臨界資源；
死鎖：多個(gè)線程之間互相等待，形成等待環(huán)；

對(duì)于死鎖，達(dá)夢(mèng)數(shù)據(jù)庫(kù)會(huì)自動(dòng)檢測(cè)，并選擇犧牲掉其中的一個(gè)事務(wù)，保證其它事務(wù)正常運(yùn)行。死鎖一般是由于應(yīng)用程序bug導(dǎo)致的，當(dāng)有修改多表的事務(wù)時(shí)，應(yīng)盡量保證每個(gè)事務(wù)以相同的順序鎖定表。可以通過(guò)V$DEADLOCK_HISTORY查詢死鎖歷史，其中記錄了產(chǎn)生死鎖后被犧牲掉的事務(wù)的事務(wù)ID、會(huì)話ID、執(zhí)行的SQL語(yǔ)句以及死鎖發(fā)生時(shí)間（沒(méi)有記錄造成死鎖的其它事務(wù)）。對(duì)于阻塞，達(dá)夢(mèng)數(shù)據(jù)庫(kù)不會(huì)自動(dòng)處理，被阻塞的事務(wù)會(huì)一直掛起，直到獲取到所需的資源。阻塞一般是由應(yīng)用程序bug造成的，在應(yīng)用編寫中，應(yīng)盡量形成短事務(wù)，快速提交。

2. 什么情況下會(huì)形成阻塞？

在達(dá)夢(mèng)數(shù)據(jù)庫(kù)中，默認(rèn)采用讀提交的機(jī)制，查詢永遠(yuǎn)不會(huì)被阻塞。查詢一般情況下也不會(huì)阻塞增刪改操作，SELECT FOR UPDATE的情況除外。
INSERT語(yǔ)句被阻塞的情況：多個(gè)事務(wù)同時(shí)向有主鍵或唯一約束的表中插入相同的數(shù)據(jù)；
刪、改語(yǔ)句被阻塞的情況：所需要操作的數(shù)據(jù)被其它事務(wù)修改過(guò)，且一直沒(méi)有提交或回滾；

3.阻塞的排查

當(dāng)阻塞發(fā)生時(shí)，我們可以通過(guò) v$lock 視圖查到當(dāng)前數(shù)據(jù)庫(kù)中鎖的狀態(tài)

select * from v$lock;

結(jié)果中我們可以看到，事務(wù) 1459 被阻塞了，阻塞他的事務(wù)為 1450，同樣我們也可以通過(guò) V$TRXWAIT 視圖排查阻塞，

select * from v$trxwait;

得出同樣的結(jié)果，等待時(shí)間是1814645 毫秒。
接下來(lái)，通過(guò) V$SESSIONS 視圖查找兩個(gè)事務(wù)對(duì)應(yīng)的會(huì)話，可以知道是哪些 SQL 語(yǔ)句產(chǎn)生的阻塞。

select sess_id,sql_text,state,trx_id from v$sessions where trx_id in('1459','1450');

4.阻塞的解決方法

根據(jù)需求，可以有兩種解決方案。

4.1 提交或回滾產(chǎn)生阻塞的事務(wù)。

根據(jù)上文，我們可知產(chǎn)生阻塞的事務(wù)會(huì)話 ID 為 140702994469648。此時(shí)，我們只需要在該會(huì)話下提交或回滾事務(wù)，鎖自然會(huì)被釋放，阻塞解決。

4.2 關(guān)閉產(chǎn)生阻塞的會(huì)話

同樣，我們也可以使用系統(tǒng)過(guò)程 SP_CLOSE_SESSION(SESS_ID)來(lái)關(guān)閉對(duì)應(yīng)的會(huì)話

此時(shí)，鎖被釋放，delete 操作也可以順利進(jìn)行下去。

也可以用一條語(yǔ)句查看出被阻塞的操作和事務(wù)會(huì)話 ID（SESS_ID）

select a.*, b.NAME, c.SESS_ID from v$lock a
left join sysobjects b on b.ID=a.TABLE_ID
left join v$sessions c on a.TRX_ID=c.TRX_ID;

然后直接執(zhí)行SP_CLOSE_SESSION(SESS_ID) 來(lái)關(guān)閉對(duì)應(yīng)的會(huì)話

SQL Server數(shù)據(jù)庫(kù)鎖表處理

第一步：查詢死鎖語(yǔ)句

1： 條件是 blocked <> 0

select dbid,* from sys.sysprocesses
where 1=1
and spid >50
and blocked <> 0
--and spid= 62

查詢結(jié)果如下圖所示：

以上查詢得出:

受影響的數(shù)據(jù)庫(kù)ID 是 5;
受到阻塞的會(huì)話有1301、1306、869、109；
阻塞會(huì)話是961；

第二步：查詢發(fā)生阻塞或死鎖的當(dāng)前數(shù)據(jù)庫(kù)

1：通過(guò)第一步查詢，知道數(shù)據(jù)庫(kù)ID 為 DBID=5。

2：sp_helpdb 或 Select name,dbid from master.sys.sysdatabases 查詢數(shù)據(jù)庫(kù)名稱

第三步：查詢發(fā)生阻塞或死鎖的SQL語(yǔ)句

輸出死鎖的執(zhí)行的語(yǔ)句：

dbcc inputbuffer(961)

語(yǔ)句如下：

select o.tp_Login,o.tp_Title, CONVERT(varchar(10),a.TimeCreated,120)  as accessingdate,count(*) as docnum, 'sps' as type  from UserData u,AllDocs a, userinfo o ,webs w where a.ListId = u.tp_ListId and a.Id = u.tp_docID  and w.Id = a.WebId and w.SiteId = a.SiteId and w.SiteId = o.tp_SiteID and

第四步：殺死鎖

kill  961

之后再?gòu)牡谝徊竭M(jìn)行查詢，看是否還存在死鎖；

從第二步、第三步，查詢出了出問(wèn)題的鎖死數(shù)據(jù)庫(kù)及SQL語(yǔ)句，便于分析徹底解決死鎖問(wèn)題。

ORACEL數(shù)據(jù)庫(kù)鎖表處理

oracle表在什么情況下會(huì)被鎖住

DML鎖又可以分為，行鎖、表鎖、死鎖

行鎖： 當(dāng)事務(wù)執(zhí)行數(shù)據(jù)庫(kù)插入、更新、刪除操作時(shí)，該事務(wù)自動(dòng)獲得操作表中操作行的排它鎖。

表級(jí)鎖： 當(dāng)事務(wù)獲得行鎖后，此事務(wù)也將自動(dòng)獲得該行的表鎖(共享鎖),以防止其它事務(wù)進(jìn)行DDL語(yǔ)句影響記錄行的更新。事務(wù)也可以在進(jìn)行過(guò)程中獲得共享鎖或排它鎖，只有當(dāng)事務(wù)顯示使用LOCK TABLE語(yǔ)句顯示的定義一個(gè)排它鎖時(shí)，事務(wù)才會(huì)獲得表上的排它鎖,也可使用LOCK TABLE顯示的定義一個(gè)表級(jí)的共享鎖(LOCK TABLE具體用法請(qǐng)參考相關(guān)文檔)。

死鎖： 當(dāng)兩個(gè)事務(wù)需要一組有沖突的鎖，而不能將事務(wù)繼續(xù)下去的話，就出現(xiàn)死鎖。
如事務(wù)1在表A行記錄#3中有一排它鎖，并等待事務(wù)2在表A中記錄#4中排它鎖的釋放，而事務(wù)2在表A記錄行#4中有一排它鎖，并等待事務(wù)1在表A中記錄#3中排它鎖的釋放，事務(wù)1與事務(wù)2彼此等待，因此就造成了死鎖。死鎖一般是因拙劣的事務(wù)設(shè)計(jì)而產(chǎn)生。
死鎖只能使用SQL下:alter system kill session “sid,serial#”；或者使用相關(guān)操作系統(tǒng)kill進(jìn)程的命令，如UNIX下kill -9 sid,或者使用其它工具殺掉死鎖進(jìn)程。

DDL鎖又可以分為：排它DDL鎖、共享DDL鎖、分析鎖

排它DDL鎖：創(chuàng)建、修改、刪除一個(gè)數(shù)據(jù)庫(kù)對(duì)象的DDL語(yǔ)句獲得操作對(duì)象的 排它鎖。如使用alter table語(yǔ)句時(shí)，為了維護(hù)數(shù)據(jù)的完成性、一致性、合法性，該事務(wù)獲得一排它DDL鎖。

共享DDL鎖：需在數(shù)據(jù)庫(kù)對(duì)象之間建立相互依賴關(guān)系的DDL語(yǔ)句通常需共享獲得DDL鎖。
如創(chuàng)建一個(gè)包，該包中的過(guò)程與函數(shù)引用了不同的數(shù)據(jù)庫(kù)表，當(dāng)編譯此包時(shí)，該事務(wù)就獲得了引用表的共享DDL鎖。

分析鎖：ORACLE使用共享池存儲(chǔ)分析與優(yōu)化過(guò)的SQL語(yǔ)句及PL/SQL程序，使運(yùn)行相同語(yǔ)句的應(yīng)用速度更快。一個(gè)在共享池中緩存的對(duì)象獲得它所引用數(shù)據(jù)庫(kù)對(duì)象的分析鎖。分析鎖是一種獨(dú)特的DDL鎖類型，ORACLE使用它追蹤共享池對(duì)象及它所引用數(shù)據(jù)庫(kù)對(duì)象之間的依賴關(guān)系。當(dāng)一個(gè)事務(wù)修改或刪除了共享池持有分析鎖的數(shù)據(jù)庫(kù)對(duì)象時(shí)，ORACLE使共享池中的對(duì)象作廢，下次在引用這條SQL/PLSQL語(yǔ)句時(shí)，ORACLE重新分析編譯此語(yǔ)句。

Oracle鎖表查詢和解鎖

第一步：通過(guò)管理員權(quán)限用戶查詢被鎖表信息

如果懷疑表被鎖了，或者事務(wù)未被正常關(guān)閉，在Oracle數(shù)據(jù)庫(kù)中我們可以通過(guò)以下語(yǔ)句進(jìn)行查詢獲取相關(guān)信息：

 select t2.username,    t2.sid,    t2.serial#,    t3.object_name,  t2.OSUSER,  t2.MACHINE,
        t2.PROGRAM,  t2.LOGON_TIME,  t2.COMMAND,   t2.LOCKWAIT,
        t2.SADDR,   t2.PADDR, t2.TADDR,   t2.SQL_ADDRESS,   t1.LOCKED_MODE
   from v$locked_object t1, v$session t2, dba_objects t3
  where t1.session_id = t2.sid
    and t1.object_id = t3.object_id
  order by t2.logon_time;

大家發(fā)現(xiàn)，上面這條SQL語(yǔ)句用到了Oracle的兩個(gè)視圖和一個(gè)表，分別是vsession、dba_objects：
vsession 視圖記錄了所有session的相關(guān)信息。
dba_objects 為oracle用戶對(duì)象及系統(tǒng)對(duì)象的集合，通過(guò)關(guān)聯(lián)這張表能夠獲取被鎖定對(duì)象的詳細(xì)信息。

eg：現(xiàn)在我通過(guò)scott用戶執(zhí)行DML語(yǔ)句（eg：select * from emp for update;或者update scott.emp set sal = ‘2000’ where empno=‘7788’; ）之后一直不進(jìn)行提交，然后通過(guò)system用戶執(zhí)行上面的查詢oracle中被鎖表的sql語(yǔ)句，就會(huì)找到如下記錄：

說(shuō)明：

username：oracle用戶名
sid：進(jìn)程號(hào)
serial#：序列號(hào)
object_name：表名
osuser：操作系統(tǒng)用戶名
machine：機(jī)器名
program：操作工具
logon_time：登陸時(shí)間
lockwait：表示當(dāng)前這張表是否正在等待其他用戶解鎖這張表
locked_mode：鎖表模式（下面詳細(xì)說(shuō)明）

注意：這時(shí)候如果通過(guò)system用戶執(zhí)行select * from scott.emp for update;語(yǔ)句就無(wú)法成功執(zhí)行。

第二步：通過(guò)擁有管理員權(quán)限的用戶解除數(shù)據(jù)庫(kù)中被鎖住的表(SID,SERIAL)

通過(guò)第一步查出來(lái)的信息找到被鎖的表之后執(zhí)行如下語(yǔ)句解鎖該表：

alter system kill session ‘sid,seial#’;
注意：sid和seial#就是第一步中查詢出來(lái)的進(jìn)程號(hào)和序列號(hào)。

eg：解除第一步中表的鎖

alter system kill session ‘10,15’;
現(xiàn)在通過(guò)system再次執(zhí)行DML語(yǔ)句（eg：select * from scott.emp for update;或update scott.emp set sal = ‘2000’ where empno=‘7788’;）就可以了。

鎖的模式
v$locked_object中的LOCKED_MODE字段表示鎖的模式，oracle中鎖的模式有如下幾種:
0：none
1：null 空
2：Row-S 行共享(RS)：共享表鎖，sub share
3：Row-X 行獨(dú)占(RX)：用于行的修改，sub exclusive
4：Share 共享鎖(S)：阻止其他DML操作，share
5：S/Row-X 共享行獨(dú)占(SRX)：阻止其他事務(wù)操作，share/sub exclusive
6：exclusive 獨(dú)占(X)：獨(dú)立訪問(wèn)使用，exclusive

數(shù)字越大鎖級(jí)別越高, 影響的操作越多。

1級(jí)鎖有： Select，有時(shí)會(huì)在v$locked_object出現(xiàn)。
2級(jí)鎖有： Select for update,Lock For Update,Lock Row Share
select for update當(dāng)對(duì)話使用for update子串打開一個(gè)游標(biāo)時(shí)，所有返回集中的數(shù)據(jù)行都將處于行級(jí)(Row-X)獨(dú)占式鎖定，其他對(duì)象只能查詢這些數(shù)據(jù)行，不能進(jìn)行update、delete或select for update操作。
3級(jí)鎖有： Insert, Update, Delete, Lock Row Exclusive
沒(méi)有commit之前插入同樣的一條記錄會(huì)沒(méi)有反應(yīng), 因?yàn)楹笠粋€(gè)3的鎖會(huì)一直等待上一個(gè)3的鎖, 我們必須釋放掉上一個(gè)才能繼續(xù)工作。
4級(jí)鎖有： Create Index, Lock Share
locked_mode為2,3,4不影響DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作會(huì)提示ora-00054錯(cuò)誤。
00054, 00000, “resource busy and acquire with NOWAIT specified”
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.
5級(jí)鎖有： Lock Share Row Exclusive
具體來(lái)講有主外鍵約束時(shí)update / delete … ; 可能會(huì)產(chǎn)生4,5的鎖。
6級(jí)鎖有： Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

還有一條比較實(shí)用的sql：

–查某session 正在執(zhí)行的sql語(yǔ)句，從而可以快速定位到哪些操作或者代碼導(dǎo)致事務(wù)一直進(jìn)行沒(méi)有結(jié)束等.

 SELECT /*+ ORDERED */ 
  sql_text
   FROM v$sqltext a
  WHERE (a.hash_value, a.address) IN
        (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
                DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
           FROM v$session b
          WHERE b.sid = '67')  /* 此處67 為SID*/
  ORDER BY piece ASC;

參考鏈接：
https://blog.csdn.net/snowfoxmonitor/article/details/7182948
https://www.cnblogs.com/pugang/p/16531352.html
https://blog.csdn.net/qq_26987043/article/details/127640853
http://t.zoukankan.com/smellpawn-p-10799174.html文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-403943.html

到了這里，關(guān)于數(shù)據(jù)庫(kù)鎖表和解鎖的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！