mysql表鎖怎么設(shè)置,mysql表鎖怎么實現(xiàn)

mysql備份數(shù)據(jù)庫 怎么鎖表

MySQL8.x 中新增了一個輕量級的備份鎖，它允許在 online 備份的時候進行 DML 操作，同時可防止快照不一致。這個鎖禁止的操作很少，它禁止的操作包括：

創(chuàng)新互聯(lián)建站自2013年創(chuàng)立以來，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項目成都網(wǎng)站設(shè)計、成都網(wǎng)站制作網(wǎng)站策劃，項目實施與項目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元玉林做網(wǎng)站,已為上家服務(wù),為玉林各地企業(yè)和個人服務(wù),聯(lián)系電話:13518219792

文件的創(chuàng)建、刪除、改名

賬戶的管理

REPAIR TABLE

TRUNCATE TABLE

OPTIMIZE TABLE

備份鎖由?lock instance for backup?和?unlock instance?語法組成。使用這些語句需要 BACKUP_ADMIN 權(quán)限。

MySQL數(shù)據(jù)庫如何鎖定和解鎖數(shù)據(jù)庫表

第一步，創(chuàng)建數(shù)據(jù)庫表writer和查看表結(jié)構(gòu)，利用SQL語句：

create table writer(

wid int(10),

wno int(10),

wname varchar(20),

wsex varchar(2),

wage int(2)

第二步，向數(shù)據(jù)庫表writer插入五條數(shù)據(jù)，插入后查看表里數(shù)據(jù)

第三步，利用鎖定語句鎖定數(shù)據(jù)庫表writer，利用SQL語句：

lock table writer read;

讓數(shù)據(jù)庫表只讀不能進行寫

第四步，為了驗證鎖定效果，可以查看數(shù)據(jù)庫表數(shù)據(jù)，利用SQL語句：

select * from writer;

第五步，利用update語句對id=5進行更新，SQL語句為：

update writer set wname = '胡思思' where id = 5;

第六步，利用unlock進行解鎖，SQL語句為：

unlock tables;

如何給mysql表上鎖

希望一下內(nèi)容對你有所幫助

鎖定讀SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE

LOCK TABLES和UNLOCK TABLES語法

LOCK TABLES

tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...

UNLOCK TABLES

LOCK TABLES可以鎖定用于當(dāng)前線程的表。如果表被其它線程鎖定，則造成堵塞，直到可以獲取所有鎖定為止。UNLOCK TABLES可以釋放被當(dāng)前線程保持的任何鎖定。當(dāng)線程發(fā)布另一個LOCK TABLES時，或當(dāng)與服務(wù)器的連接被關(guān)閉時，所有由當(dāng)前線程鎖定的表被隱含地解鎖。

表鎖定只用于防止其它客戶端進行不正當(dāng)?shù)刈x取和寫入。保持鎖定（即使是讀取鎖定）的客戶端可以進行表層級的操作，比如DROP TABLE。

MySQL鎖表和解鎖操作

1、確定mysql有鎖表的情況則使用以下命令查看鎖表進程

2、殺掉查詢結(jié)果中已經(jīng)鎖表的trx_mysql_thread_id

擴展：

1、查看鎖的事務(wù)

2、查看等待鎖的事務(wù)

3、查詢是否鎖表：

4、查詢進程

MySQL數(shù)據(jù)庫表鎖定的幾種方法實現(xiàn)

如果兩個程序都向表中寫數(shù)據(jù)顯然會造成很大的麻煩，甚至?xí)幸馔馇闆r發(fā)生。如果表正由一個程序?qū)懭?，同時進行讀取的另一個程序也會產(chǎn)生混亂的結(jié)果。

鎖定表的方法

防止客戶機的請求互相干擾或者服務(wù)器與維護程序相互干擾的方法主要有多種。如果你關(guān)閉數(shù)據(jù)庫，就可以保證服務(wù)器

和myisamchk和isamchk之間沒有交互作用。但是停止服務(wù)器的運行并不是一個好注意，因為這樣做會使得沒有故障的數(shù)據(jù)庫和表也不可用。本節(jié)主

要討論的過程，是避免服務(wù)器和myisamchk或isamchk之間的交互作用。實現(xiàn)這種功能的方法是對表進行鎖定。

服務(wù)器由兩種表的鎖定方法：

1.內(nèi)部鎖定

內(nèi)部鎖定可以避免客戶機的請求相互干擾——例如，避免客戶機的SELECT查詢被另一個客戶機的UPDATE查詢所干擾。也可以利用內(nèi)部鎖定機制防止服務(wù)器在利用myisamchk或isamchk檢查或修復(fù)表時對表的訪問。

語法：鎖定表：LOCK TABLES

tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]

解鎖表：UNLOCKTABLESLOCKTABLES為當(dāng)前線程鎖定表。UNLOCK TABLES釋放被當(dāng)前線程持有的任何鎖。當(dāng)線程發(fā)出另外一個LOCK

TABLES時，或當(dāng)服務(wù)器的連接被關(guān)閉時，當(dāng)前線程鎖定的所有表自動被解鎖。

如果一個線程獲得在一個表上的一個READ鎖，該線程(和所有其他線程)只能從表中讀。如果一個線程獲得一個表上的一個WRITE鎖，那么只有持鎖的線程READ或WRITE表，其他線程被阻止。

每個線程等待(沒有超時)直到它獲得它請求的所有鎖。

WRITE鎖通常比READ鎖有更高的優(yōu)先級，以確保更改盡快被處理。這意味著，如果一個線程獲得READ鎖，并且然后另外一個線程請求一個WRITE鎖,

隨后的READ鎖請求將等待直到WRITE線程得到了鎖并且釋放了它。

顯然對于檢查，你只需要獲得讀鎖。再者鐘情跨下，只能讀取表，但不能修改它，因此他也允許其它客戶機讀取表。對于修復(fù)，你必須獲得些所以防止任何客戶機在你對表進行操作時修改它。

2.外部鎖定

服務(wù)器還可以使用外部鎖定(文件級鎖)來防止其它程序在服務(wù)器使用表時修改文件。通常，在表的檢查操作中服務(wù)器

將外部鎖定與myisamchk或isamchk作合使用。但是，外部鎖定在某些系統(tǒng)中是禁用的，因為他不能可靠的進行工作。對運行myisamchk或

isamchk所選擇的過程取決于服務(wù)器是否能使用外部鎖定。如果不使用，則必修使用內(nèi)部鎖定協(xié)議。

如果服務(wù)器用--skip-locking選項運行，則外部鎖定禁用。該選項在某些系統(tǒng)中是缺省的，如Linux。可以通過運行mysqladmin

variables命令確定服務(wù)器是否能夠使用外部鎖定。檢查skip_locking變量的值并按以下方法進行：

◆

如果skip_locking為off，則外部鎖定有效您可以繼續(xù)并運行人和一個實用程序來檢查表。服務(wù)器和實用程序?qū)⒑献鲗Ρ磉M行訪問。但是，運行任何

一個實用程序之前，應(yīng)該使用mysqladmin flush-tables。為了修復(fù)表，應(yīng)該使用表的修復(fù)鎖定協(xié)議。

◆

如果skip_locaking為on，則禁用外部鎖定，所以在myisamchk或isamchk檢查修復(fù)表示服務(wù)器并不知道，最好關(guān)閉服務(wù)器。如果堅

持是服務(wù)器保持開啟狀態(tài)，月確保在您使用此表示沒有客戶機來訪問它。

MySQL數(shù)據(jù)庫表被鎖、解鎖，刪除事務(wù)

在程序員的職業(yè)生涯中，總會遇到數(shù)據(jù)庫表被鎖的情況，前些天就又撞見一次。由于業(yè)務(wù)突發(fā)需求，各個部門都在批量操作、導(dǎo)出數(shù)據(jù)，而數(shù)據(jù)庫又未做讀寫分離，結(jié)果就是：數(shù)據(jù)庫的某張表被鎖了！

用戶反饋系統(tǒng)部分功能無法使用，緊急排查，定位是數(shù)據(jù)庫表被鎖，然后進行緊急處理。這篇文章給大家講講遇到類似緊急狀況的排查及解決過程，建議點贊收藏，以備不時之需。

用戶反饋某功能頁面報502錯誤，于是第一時間看服務(wù)是否正常，數(shù)據(jù)庫是否正常。在控制臺看到數(shù)據(jù)庫CPU飆升，堆積大量未提交事務(wù)，部分事務(wù)已經(jīng)阻塞了很長時間，基本定位是數(shù)據(jù)庫層出現(xiàn)問題了。

查看阻塞事務(wù)列表，發(fā)現(xiàn)其中有鎖表現(xiàn)象，本想利用控制臺直接結(jié)束掉阻塞的事務(wù)，但控制臺賬號權(quán)限有限，于是通過客戶端登錄對應(yīng)賬號將鎖表事務(wù)kill掉，才避免了情況惡化。

下面就聊聊，如果當(dāng)突然面對類似的情況，我們該如何緊急響應(yīng)？

想象一個場景，當(dāng)然也是軟件工程師職業(yè)生涯中會遇到的一種場景：原本運行正常的程序，某一天突然數(shù)據(jù)庫的表被鎖了，業(yè)務(wù)無法正常運轉(zhuǎn)，那么我們該如何快速定位是哪個事務(wù)鎖了表，如何結(jié)束對應(yīng)的事物？

首先最簡單粗暴的方式就是：重啟MySQL。對的，網(wǎng)管解決問題的神器——“重啟”。至于后果如何，你能不能跑了，要你自己三思而后行了！

重啟是可以解決表被鎖的問題的，但針對線上業(yè)務(wù)很顯然不太具有可行性。

下面來看看不用跑路的解決方案：

遇到數(shù)據(jù)庫阻塞問題，首先要查詢一下表是否在使用。

如果查詢結(jié)果為空，那么說明表沒在使用，說明不是鎖表的問題。

如果查詢結(jié)果不為空，比如出現(xiàn)如下結(jié)果：

則說明表（test）正在被使用，此時需要進一步排查。

查看數(shù)據(jù)庫當(dāng)前的進程，看看是否有慢SQL或被阻塞的線程。

執(zhí)行命令：

該命令只顯示當(dāng)前用戶正在運行的線程，當(dāng)然，如果是root用戶是能看到所有的。

在上述實踐中，阿里云控制臺之所以能夠查看到所有的線程，猜測應(yīng)該使用的就是root用戶，而筆者去kill的時候，無法kill掉，是因為登錄的用戶非root的數(shù)據(jù)庫賬號，無法操作另外一個用戶的線程。

如果情況緊急，此步驟可以跳過，主要用來查看核對：

如果情況緊急，此步驟可以跳過，主要用來查看核對：

看事務(wù)表INNODB_TRX中是否有正在鎖定的事務(wù)線程，看看ID是否在show processlist的sleep線程中。如果在，說明這個sleep的線程事務(wù)一直沒有commit或者rollback，而是卡住了，需要手動kill掉。

搜索的結(jié)果中，如果在事務(wù)表發(fā)現(xiàn)了很多任務(wù)，最好都kill掉。

執(zhí)行kill命令：

對應(yīng)的線程都執(zhí)行完kill命令之后，后續(xù)事務(wù)便可正常處理。

針對緊急情況，通常也會直接操作第一、第二、第六步。

這里再補充一些MySQL鎖相關(guān)的知識點：數(shù)據(jù)庫鎖設(shè)計的初衷是處理并發(fā)問題，作為多用戶共享的資源，當(dāng)出現(xiàn)并發(fā)訪問的時候，數(shù)據(jù)庫需要合理地控制資源的訪問規(guī)則，而鎖就是用來實現(xiàn)這些訪問規(guī)則的重要數(shù)據(jù)結(jié)構(gòu)。

根據(jù)加鎖的范圍，MySQL里面的鎖大致可以分成全局鎖、表級鎖和行鎖三類。MySQL中表級別的鎖有兩種：一種是表鎖，一種是元數(shù)據(jù)鎖（metadata lock，MDL)。

表鎖是在Server層實現(xiàn)的，ALTER TABLE之類的語句會使用表鎖，忽略存儲引擎的鎖機制。表鎖通過lock tables… read/write來實現(xiàn)，而對于InnoDB來說，一般會采用行級鎖。畢竟鎖住整張表影響范圍太大了。

另外一個表級鎖是MDL（metadata lock），用于并發(fā)情況下維護數(shù)據(jù)的一致性，保證讀寫的正確性，不需要顯式的使用，在訪問一張表時會被自動加上。

常見的一種鎖表場景就是有事務(wù)操作處于：Waiting for table metadata lock狀態(tài)。

MySQL在進行alter table等DDL操作時，有時會出現(xiàn)Waiting for table metadata lock的等待場景。

一旦alter table TableA的操作停滯在Waiting for table metadata lock狀態(tài)，后續(xù)對該表的任何操作（包括讀）都無法進行，因為它們也會在Opening tables的階段進入到Waiting for table metadata lock的鎖等待隊列。如果核心表出現(xiàn)了鎖等待隊列，就會造成災(zāi)難性的后果。

通過show processlist可以看到表上有正在進行的操作（包括讀），此時alter table語句無法獲取到metadata 獨占鎖，會進行等待。

通過show processlist看不到表上有任何操作，但實際上存在有未提交的事務(wù)，可以在information_schema.innodb_trx中查看到。在事務(wù)沒有完成之前，表上的鎖不會釋放，alter table同樣獲取不到metadata的獨占鎖。

處理方法：通過 select * from information_schema.innodb_trxG, 找到未提交事物的sid，然后kill掉，讓其回滾。

通過show processlist看不到表上有任何操作，在information_schema.innodb_trx中也沒有任何進行中的事務(wù)。很可能是因為在一個顯式的事務(wù)中，對表進行了一個失敗的操作（比如查詢了一個不存在的字段），這時事務(wù)沒有開始，但是失敗語句獲取到的鎖依然有效，沒有釋放。從performance_schema.events_statements_current表中可以查到失敗的語句。

處理方法：通過performance_schema.events_statements_current找到其sid，kill 掉該session，也可以kill掉DDL所在的session。

總之，alter table的語句是很危險的（核心是未提交事務(wù)或者長事務(wù)導(dǎo)致的），在操作之前要確認對要操作的表沒有任何進行中的操作、沒有未提交事務(wù)、也沒有顯式事務(wù)中的報錯語句。

如果有alter table的維護任務(wù)，在無人監(jiān)管的時候運行，最好通過lock_wait_timeout設(shè)置好超時時間，避免長時間的metedata鎖等待。

關(guān)于MySQL的鎖表其實還有很多其他場景，我們在實踐的過程中盡量避免鎖表情況的發(fā)生，當(dāng)然這需要一定經(jīng)驗的支撐。但更重要的是，如果發(fā)現(xiàn)鎖表我們要能夠快速的響應(yīng)，快速的解決問題，避免影響正常業(yè)務(wù)，避免情況進一步惡化。所以，本文中的解決思路大家一定要收藏或記憶一下，做到有備無患，避免突然狀況下抓瞎。

本文題目：mysql表鎖怎么設(shè)置,mysql表鎖怎么實現(xiàn)
文章位置：http://m.jiaotiyi.com/article/dssgcdh.html