1、確定mysql有鎖表的情況則使用以下命令查看鎖表進(jìn)程
為宜賓等地區(qū)用戶提供了全套網(wǎng)頁(yè)設(shè)計(jì)制作服務(wù),及宜賓網(wǎng)站建設(shè)行業(yè)解決方案。主營(yíng)業(yè)務(wù)為成都網(wǎng)站制作、成都網(wǎng)站建設(shè)、宜賓網(wǎng)站設(shè)計(jì),以傳統(tǒng)方式定制建設(shè)網(wǎng)站,并提供域名空間備案等一條龍服務(wù),秉承以專業(yè)、用心的態(tài)度為用戶提供真誠(chéng)的服務(wù)。我們深信只要達(dá)到每一位用戶的要求,就會(huì)得到認(rèn)可,從而選擇與我們長(zhǎng)期合作。這樣,我們也可以走得更遠(yuǎn)!
2、殺掉查詢結(jié)果中已經(jīng)鎖表的trx_mysql_thread_id
擴(kuò)展:
1、查看鎖的事務(wù)
2、查看等待鎖的事務(wù)
3、查詢是否鎖表:
4、查詢進(jìn)程
for update 的作用是在查詢的時(shí)候?yàn)樾屑由吓潘i,當(dāng)一個(gè)事務(wù)的操作未完成時(shí)候,其他事務(wù)可以讀取但是不能寫入或更新。
它的典型使用場(chǎng)景是 高并發(fā)并且對(duì)于數(shù)據(jù)的準(zhǔn)確性有很高要求 ,比如金錢、庫(kù)存等,一般這種操作都是很長(zhǎng)一串并且開啟事務(wù)的,假如現(xiàn)在要對(duì)庫(kù)存進(jìn)行操作,在剛開始讀的時(shí)候是1,然后馬上另外一個(gè)進(jìn)程將庫(kù)存更新為0了,但事務(wù)還沒結(jié)束,會(huì)一直用1進(jìn)行后續(xù)的邏輯,就會(huì)有問題,所以需要用for upate 加鎖防止出錯(cuò)。
行鎖的具體實(shí)現(xiàn)算法有三種:record lock、gap lock以及next-key lock。
只在可重復(fù)讀或以上隔離級(jí)別下的特定操作才會(huì)取得 gap lock 或 next-key lock,在 Select、Update 和 Delete 時(shí),除了基于唯一索引的查詢之外,其它索引查詢時(shí)都會(huì)獲取 gap lock 或 next-key lock,即鎖住其掃描的范圍。主鍵索引也屬于唯一索引,所以主鍵索引是不會(huì)使用 gap lock 或 next-key lock
for update 僅適用于InnoDB,并且必須開啟事務(wù),在begin與commit之間才生效。
select 語(yǔ)句默認(rèn)不獲取任何鎖,所以是可以讀被其它事務(wù)持有排它鎖的數(shù)據(jù)的!
InnoDB 既實(shí)現(xiàn)了行鎖,也實(shí)現(xiàn)了表鎖。
當(dāng)有明確指定的主鍵/索引時(shí)候,是行級(jí)鎖,否則是表級(jí)鎖
假設(shè)表 user,存在有id跟name字段,id是主鍵,有5條數(shù)據(jù)。
明確指定主鍵,并且有此記錄,行級(jí)鎖
無(wú)主鍵/索引,表級(jí)鎖
主鍵/索引不明確,表級(jí)鎖
明確指定主鍵/索引,若查無(wú)此記錄,無(wú)鎖
參考博文:
鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制,在數(shù)據(jù)庫(kù)中,除傳統(tǒng)的計(jì)算資源(CPU、RAM、I/O)爭(zhēng)用外,數(shù)據(jù)也是一種供許多用戶共享的資源,如何保證數(shù)據(jù)并發(fā)訪問的一致性,有效性是所有數(shù)據(jù)庫(kù)必須解決的一個(gè)問題,鎖沖突也是影響數(shù)據(jù)庫(kù)并發(fā)訪問性能的一個(gè)重要因素,從這個(gè)角度來說,鎖對(duì)數(shù)據(jù)庫(kù)而言是尤其重要,也更加復(fù)雜。MySQL中的鎖,按照鎖的粒度分為:1、全局鎖,就鎖定數(shù)據(jù)庫(kù)中的所有表。2、表級(jí)鎖,每次操作鎖住整張表。3、行級(jí)鎖,每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù)。
全局鎖就是對(duì)整個(gè)數(shù)據(jù)庫(kù)實(shí)例加鎖,加鎖后整個(gè)實(shí)例就處于只讀狀態(tài),后續(xù)的DML的寫語(yǔ)句,DDL語(yǔ)句,已經(jīng)更新操作的事務(wù)提交語(yǔ)句都將阻塞。其典型的使用場(chǎng)景就是做全庫(kù)的邏輯備份,對(duì)所有的表進(jìn)行鎖定,從而獲取一致性視圖,保證數(shù)據(jù)的完整性。但是對(duì)數(shù)據(jù)庫(kù)加全局鎖是有弊端的,如在主庫(kù)上備份,那么在備份期間都不能執(zhí)行更新,業(yè)務(wù)會(huì)受影響,第二如果是在從庫(kù)上備份,那么在備份期間從庫(kù)不能執(zhí)行主庫(kù)同步過來的二進(jìn)制日志,會(huì)導(dǎo)致主從延遲。
解決辦法是在innodb引擎中,備份時(shí)加上--single-transaction參數(shù)來完成不加鎖的一致性數(shù)據(jù)備份。
添加全局鎖: flush tables with read lock; 解鎖 unlock tables。
表級(jí)鎖,每次操作會(huì)鎖住整張表.鎖定粒度大,發(fā)送鎖沖突的概率最高,并發(fā)讀最低,應(yīng)用在myisam、innodb、BOB等存儲(chǔ)引擎中。表級(jí)鎖分為: 表鎖、元數(shù)據(jù)鎖(meta data lock, MDL)和意向鎖。
表鎖又分為: 表共享讀鎖 read lock、表獨(dú)占寫鎖write lock
語(yǔ)法: 1、加鎖 lock tables 表名 ... read/write
2、釋放鎖 unlock tables 或者關(guān)閉客戶端連接
注意: 讀鎖不會(huì)阻塞其它客戶端的讀,但是會(huì)阻塞其它客戶端的寫,寫鎖既會(huì)阻塞其它客戶端的讀,又會(huì)阻塞其它客戶端的寫。大家可以拿一張表來測(cè)試看看。
元數(shù)據(jù)鎖,在加鎖過程中是系統(tǒng)自動(dòng)控制的,無(wú)需顯示使用,在訪問一張表的時(shí)候會(huì)自動(dòng)加上,MDL鎖主要作用是維護(hù)表元數(shù)據(jù)的數(shù)據(jù)一致性,在表上有活動(dòng)事務(wù)的時(shí)候,不可以對(duì)元數(shù)據(jù)進(jìn)行寫入操作。為了避免DML和DDL沖突,保證讀寫的正確性。
在MySQL5.5中引入了MDL,當(dāng)對(duì)一張表進(jìn)行增刪改查的時(shí)候,加MDL讀鎖(共享);當(dāng)對(duì)表結(jié)構(gòu)進(jìn)行變更操作時(shí),加MDL寫鎖(排他).
查看元數(shù)據(jù)鎖:
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;
意向鎖,為了避免DML在執(zhí)行時(shí),加的行鎖與表鎖的沖突,在innodb中引入了意向鎖,使得表鎖不用檢查每行數(shù)據(jù)是否加鎖,使用意向鎖來減少表鎖的檢查。意向鎖分為,意向共享鎖is由語(yǔ)句select ... lock in share mode添加。意向排他鎖ix,由insert,update,delete,select。。。for update 添加。
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock;
行級(jí)鎖,每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù),鎖定粒度最小,發(fā)生鎖沖突的概率最高,并發(fā)讀最高,應(yīng)用在innodb存儲(chǔ)引擎中。
innodb的數(shù)據(jù)是基于索引組織的,行鎖是通過對(duì)索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的,而不是對(duì)記錄加的鎖,對(duì)于行級(jí)鎖,主要分為以下三類:
1、行鎖或者叫record lock記錄鎖,鎖定單個(gè)行記錄的鎖,防止其他事物對(duì)次行進(jìn)行update和delete操作,在RC,RR隔離級(jí)別下都支持。
2、間隙鎖Gap lock,鎖定索引記錄間隙(不含該記錄),確保索引記錄間隙不變,防止其他事物在這個(gè)間隙進(jìn)行insert操作,產(chǎn)生幻讀,在RR隔離級(jí)別下都支持。
3、臨鍵鎖Next-key-lock,行鎖和間隙鎖組合,同時(shí)鎖住數(shù)據(jù),并鎖住數(shù)據(jù)前面的間隙Gap,在RR隔離級(jí)別下支持。
innodb實(shí)現(xiàn)了以下兩種類型的行鎖
1、共享鎖 S: 允許一個(gè)事務(wù)去讀一行,阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
2、排他鎖 X: 允許獲取排他鎖的事務(wù)更新數(shù)據(jù),阻止其他事務(wù)獲得相同數(shù)據(jù)集的共享鎖和排他鎖。
insert 語(yǔ)句 排他鎖 自動(dòng)添加的
update語(yǔ)句 排他鎖 自動(dòng)添加
delete 語(yǔ)句 排他鎖 自動(dòng)添加
select 正常查詢語(yǔ)句 不加鎖 。。。
select 。。。lock in share mode 共享鎖 需要手動(dòng)在select 之后加lock in share mode
select 。。。for update 排他鎖 需要手動(dòng)在select之后添加for update
默認(rèn)情況下,innodb在repeatable read事務(wù)隔離級(jí)別運(yùn)行,innodb使用next-key鎖進(jìn)行搜索和索引掃描,以防止幻讀。
間隙鎖唯一目的是防止其它事務(wù)插入間隙,間隙鎖可以共存,一個(gè)事務(wù)采用的間隙鎖不會(huì)阻止另一個(gè)事務(wù)在同一間隙上采用的間隙鎖。
MySQL8.x 中新增了一個(gè)輕量級(jí)的備份鎖,它允許在 online 備份的時(shí)候進(jìn)行 DML 操作,同時(shí)可防止快照不一致。這個(gè)鎖禁止的操作很少,它禁止的操作包括:
文件的創(chuàng)建、刪除、改名
賬戶的管理
REPAIR TABLE
TRUNCATE TABLE
OPTIMIZE TABLE
備份鎖由?lock instance for backup?和?unlock instance?語(yǔ)法組成。使用這些語(yǔ)句需要 BACKUP_ADMIN 權(quán)限。
如果兩個(gè)程序都向表中寫數(shù)據(jù)顯然會(huì)造成很大的麻煩,甚至?xí)幸馔馇闆r發(fā)生。如果表正由一個(gè)程序?qū)懭?,同時(shí)進(jìn)行讀取的另一個(gè)程序也會(huì)產(chǎn)生混亂的結(jié)果。 鎖定表的方法 防止客戶機(jī)的請(qǐng)求互相干擾或者服務(wù)器與維護(hù)程序相互干擾的方法主要有多種。如果你關(guān)閉數(shù)據(jù)庫(kù),就可以保證服務(wù)器 和myisamchk和isamchk之間沒有交互作用。但是停止服務(wù)器的運(yùn)行并不是一個(gè)好注意,因?yàn)檫@樣做會(huì)使得沒有故障的數(shù)據(jù)庫(kù)和表也不可用。本節(jié)主 要討論的過程,是避免服務(wù)器和myisamchk或isamchk之間的交互作用。實(shí)現(xiàn)這種功能的方法是對(duì)表進(jìn)行鎖定。 服務(wù)器由兩種表的鎖定方法: 1.內(nèi)部鎖定 內(nèi)部鎖定可以避免客戶機(jī)的請(qǐng)求相互干擾——例如,避免客戶機(jī)的SELECT查詢被另一個(gè)客戶機(jī)的UPDATE查詢所干擾。也可以利用內(nèi)部鎖定機(jī)制防止服務(wù)器在利用myisamchk或isamchk檢查或修復(fù)表時(shí)對(duì)表的訪問。 語(yǔ)法:鎖定表:LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…] 解鎖表:UNLOCKTABLESLOCKTABLES為當(dāng)前線程鎖定表。UNLOCK TABLES釋放被當(dāng)前線程持有的任何鎖。當(dāng)線程發(fā)出另外一個(gè)LOCK TABLES時(shí),或當(dāng)服務(wù)器的連接被關(guān)閉時(shí),當(dāng)前線程鎖定的所有表自動(dòng)被解鎖。 如果一個(gè)線程獲得在一個(gè)表上的一個(gè)READ鎖,該線程(和所有其他線程)只能從表中讀。如果一個(gè)線程獲得一個(gè)表上的一個(gè)WRITE鎖,那么只有持鎖的線程READ或WRITE表,其他線程被阻止。 每個(gè)線程等待(沒有超時(shí))直到它獲得它請(qǐng)求的所有鎖。 WRITE鎖通常比READ鎖有更高的優(yōu)先級(jí),以確保更改盡快被處理。這意味著,如果一個(gè)線程獲得READ鎖,并且然后另外一個(gè)線程請(qǐng)求一個(gè)WRITE鎖, 隨后的READ鎖請(qǐng)求將等待直到WRITE線程得到了鎖并且釋放了它。 顯然對(duì)于檢查,你只需要獲得讀鎖。再者鐘情跨下,只能讀取表,但不能修改它,因此他也允許其它客戶機(jī)讀取表。對(duì)于修復(fù),你必須獲得些所以防止任何客戶機(jī)在你對(duì)表進(jìn)行操作時(shí)修改它。 2.外部鎖定 服務(wù)器還可以使用外部鎖定(文件級(jí)鎖)來防止其它程序在服務(wù)器使用表時(shí)修改文件。通常,在表的檢查操作中服務(wù)器 將外部鎖定與myisamchk或isamchk作合使用。但是,外部鎖定在某些系統(tǒng)中是禁用的,因?yàn)樗荒芸煽康倪M(jìn)行工作。對(duì)運(yùn)行myisamchk或 isamchk所選擇的過程取決于服務(wù)器是否能使用外部鎖定。如果不使用,則必修使用內(nèi)部鎖定協(xié)議。 如果服務(wù)器用--skip-locking選項(xiàng)運(yùn)行,則外部鎖定禁用。該選項(xiàng)在某些系統(tǒng)中是缺省的,如Linux??梢酝ㄟ^運(yùn)行mysqladmin variables命令確定服務(wù)器是否能夠使用外部鎖定。檢查skip_locking變量的值并按以下方法進(jìn)行: ◆ 如果skip_locking為off,則外部鎖定有效您可以繼續(xù)并運(yùn)行人和一個(gè)實(shí)用程序來檢查表。服務(wù)器和實(shí)用程序?qū)⒑献鲗?duì)表進(jìn)行訪問。但是,運(yùn)行任何 一個(gè)實(shí)用程序之前,應(yīng)該使用mysqladmin flush-tables。為了修復(fù)表,應(yīng)該使用表的修復(fù)鎖定協(xié)議。 ◆ 如果skip_locaking為on,則禁用外部鎖定,所以在myisamchk或isamchk檢查修復(fù)表示服務(wù)器并不知道,最好關(guān)閉服務(wù)器。如果堅(jiān) 持是服務(wù)器保持開啟狀態(tài),月確保在您使用此表示沒有客戶機(jī)來訪問它。
第一步,創(chuàng)建數(shù)據(jù)庫(kù)表writer和查看表結(jié)構(gòu),利用SQL語(yǔ)句:
create table writer(
wid int(10),
wno int(10),
wname varchar(20),
wsex varchar(2),
wage int(2)
第二步,向數(shù)據(jù)庫(kù)表writer插入五條數(shù)據(jù),插入后查看表里數(shù)據(jù)
第三步,利用鎖定語(yǔ)句鎖定數(shù)據(jù)庫(kù)表writer,利用SQL語(yǔ)句:
lock table writer read;
讓數(shù)據(jù)庫(kù)表只讀不能進(jìn)行寫
第四步,為了驗(yàn)證鎖定效果,可以查看數(shù)據(jù)庫(kù)表數(shù)據(jù),利用SQL語(yǔ)句:
select * from writer;
第五步,利用update語(yǔ)句對(duì)id=5進(jìn)行更新,SQL語(yǔ)句為:
update writer set wname = '胡思思' where id = 5;
第六步,利用unlock進(jìn)行解鎖,SQL語(yǔ)句為:
unlock tables;