mysql怎么釋放事務(wù) mysql釋放磁盤空間

一文詳解-MySQL 事務(wù)和鎖

當(dāng)多個(gè)用戶訪問同一份數(shù)據(jù)時(shí)，一個(gè)用戶在更改數(shù)據(jù)的過程中，可能有其他用戶同時(shí)發(fā)起更改請(qǐng)求，為保證數(shù)據(jù)庫記錄的更新從一個(gè)一致性狀態(tài)變?yōu)榱硗庖粋€(gè)一致性狀態(tài)，使用事務(wù)處理是非常必要的，事務(wù)具有以下四個(gè)特性：

成都創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供薩迦網(wǎng)站建設(shè)、薩迦做網(wǎng)站、薩迦網(wǎng)站設(shè)計(jì)、薩迦網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)與制作、薩迦企業(yè)網(wǎng)站模板建站服務(wù)，十多年薩迦做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

MySQL 提供了多種事務(wù)型存儲(chǔ)引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事務(wù)。為了支持事務(wù)，InnoDB 存儲(chǔ)引擎引入了與事務(wù)處理相關(guān)的 REDO 日志和 UNDO 日志，同時(shí)事務(wù)依賴于 MySQL 提供的鎖機(jī)制

事務(wù)執(zhí)行時(shí)需要將執(zhí)行的事務(wù)日志寫入日志文件，對(duì)應(yīng)的文件為 REDO 日志。當(dāng)每條 SQL 進(jìn)行數(shù)據(jù)更新操作時(shí)，首先將 REDO 日志寫進(jìn)日志緩沖區(qū)。當(dāng)客戶端執(zhí)行 COMMIT 命令提交時(shí)，日志緩沖區(qū)的內(nèi)容將被刷新到磁盤，日志緩沖區(qū)的刷新方式或者時(shí)間間隔可以通過參數(shù) innodb_flush_log_at_trx_commit 控制

REDO 日志對(duì)應(yīng)磁盤上的 ib_logifleN 文件，該文件默認(rèn)為 5MB，建議設(shè)置為 512MB，以便容納較大的事務(wù)。MySQL 崩潰恢復(fù)時(shí)會(huì)重新執(zhí)行 REDO 日志的記錄，恢復(fù)最新數(shù)據(jù)，保證已提交事務(wù)的持久性

與 REDO 日志相反，UNDO 日志主要用于事務(wù)異常時(shí)的數(shù)據(jù)回滾，具體內(nèi)容就是記錄數(shù)據(jù)被修改前的信息到 UNDO 緩沖區(qū)，然后在合適的時(shí)間將內(nèi)容刷新到磁盤

假如由于系統(tǒng)錯(cuò)誤或者 rollback 操作而導(dǎo)致事務(wù)回滾，可以根據(jù) undo 日志回滾到?jīng)]修改前的狀態(tài)，保證未提交事務(wù)的原子性

與 REDO 日志不同的是，磁盤上不存在單獨(dú)的 UNDO 日志文件，所有的 UNDO 日志均存在表空間對(duì)應(yīng)的 .ibd 數(shù)據(jù)文件中，即使 MySQL 服務(wù)啟動(dòng)了獨(dú)立表空間

在 MySQL 中，可以使用 BEGIN 開始事務(wù)，使用 COMMIT 結(jié)束事務(wù)，中間可以使用 ROLLBACK 回滾事務(wù)。MySQL 通過 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等語句支持本地事務(wù)

MySQL 定義了四種隔離級(jí)別，指定事務(wù)中哪些數(shù)據(jù)改變其他事務(wù)可見、哪些數(shù)據(jù)該表其他事務(wù)不可見。低級(jí)別的隔離級(jí)別可以支持更高的并發(fā)處理，同時(shí)占用的系統(tǒng)資源更少

InnoDB 系統(tǒng)級(jí)事務(wù)隔離級(jí)別可以使用以下語句設(shè)置：

查看系統(tǒng)級(jí)事務(wù)隔離級(jí)別：

InnoDB 會(huì)話級(jí)事務(wù)隔離級(jí)別可以使用以下語句設(shè)置：

查看會(huì)話級(jí)事務(wù)隔離級(jí)別：

在該隔離級(jí)別，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。讀取未提交的數(shù)據(jù)稱為臟讀（Dirty Read），即是：首先開啟 A 和 B 兩個(gè)事務(wù)，在 B 事務(wù)更新但未提交之前，A 事務(wù)讀取到了更新后的數(shù)據(jù)，但由于 B 事務(wù)回滾，導(dǎo)致 A 事務(wù)出現(xiàn)了臟讀現(xiàn)象

所有事務(wù)只能看見已經(jīng)提交事務(wù)所做的改變，此級(jí)別可以解決臟讀，但也會(huì)導(dǎo)致不可重復(fù)讀（Nonrepeatable Read）：首先開啟 A 和 B 兩個(gè)事務(wù)，A事務(wù)讀取了 B 事務(wù)的數(shù)據(jù)，在 B 事務(wù)更新并提交后，A 事務(wù)又讀取到了更新后的數(shù)據(jù)，此時(shí)就出現(xiàn)了同一 A 事務(wù)中的查詢出現(xiàn)了不同的查詢結(jié)果

MySQL 默認(rèn)的事務(wù)隔離級(jí)別，能確保同一事務(wù)的多個(gè)實(shí)例在并發(fā)讀取數(shù)據(jù)時(shí)看到同樣的數(shù)據(jù)行，理論上會(huì)導(dǎo)致一個(gè)問題，幻讀（Phontom Read）。例如，第一個(gè)事務(wù)對(duì)一個(gè)表中的數(shù)據(jù)做了修改，這種修改會(huì)涉及表中的全部數(shù)據(jù)行，同時(shí)第二個(gè)事務(wù)也修改這個(gè)表中的數(shù)據(jù)，這次的修改是向表中插入一行新數(shù)據(jù)，此時(shí)就會(huì)發(fā)生操作第一個(gè)事務(wù)的用戶發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行

InnoDB 通過多版本并發(fā)控制機(jī)制（MVCC）解決了該問題：InnoDB 通過為每個(gè)數(shù)據(jù)行增加兩個(gè)隱含值的方式來實(shí)現(xiàn)，這兩個(gè)隱含值記錄了行的創(chuàng)建時(shí)間、過期時(shí)間以及每一行存儲(chǔ)時(shí)間發(fā)生時(shí)的系統(tǒng)版本號(hào)，每個(gè)查詢根據(jù)事務(wù)的版本號(hào)來查詢結(jié)果

通過強(qiáng)制事務(wù)排序，使其不可能相互沖突，從而解決幻讀問題。簡(jiǎn)而言之，就是在每個(gè)讀的數(shù)據(jù)行上加上共享鎖實(shí)現(xiàn)，這個(gè)級(jí)別會(huì)導(dǎo)致大量的超時(shí)現(xiàn)象和鎖競(jìng)爭(zhēng)，一般不推薦使用

為了解決數(shù)據(jù)庫并發(fā)控制問題，如走到同一時(shí)刻客戶端對(duì)同一張表做更新或者查詢操作，需要對(duì)并發(fā)操作進(jìn)行控制，因此產(chǎn)生了鎖

共享鎖的粒度是行或者元組（多個(gè)行），一個(gè)事務(wù)獲取了共享鎖以后，可以對(duì)鎖定范圍內(nèi)的數(shù)據(jù)執(zhí)行讀操作

排他鎖的粒度與共享鎖相同，一個(gè)事務(wù)獲取排他鎖以后，可以對(duì)鎖定范圍內(nèi)的數(shù)據(jù)執(zhí)行寫操作

有兩個(gè)事務(wù) A 和 B，如果事務(wù) A 獲取了一個(gè)元組的共享鎖，事務(wù) B 還可以立即獲取這個(gè)元組的共享鎖，但不能獲取這個(gè)元組的排他鎖，必須等到事務(wù) A 釋放共享鎖之后。如果事務(wù) A 獲取了一個(gè)元組的排他鎖，事務(wù) B 不能立即獲取這個(gè)元組的共享鎖，也不能立即獲取這個(gè)元組的排他鎖，必須等到 A 釋放排他鎖之后

意向鎖是一種表鎖，鎖定的粒度是整張表，分為意向共享鎖和意向排他鎖。意向共享鎖表示一個(gè)事務(wù)有意對(duì)數(shù)據(jù)上共享鎖或者排他鎖。有意表示事務(wù)想執(zhí)行操作但還沒真正執(zhí)行

鎖的粒度主要分為表鎖和行鎖

表鎖的開銷最小，同時(shí)允許的并發(fā)量也是最小。MyISAM 存儲(chǔ)引擎使用該鎖機(jī)制。當(dāng)要寫入數(shù)據(jù)時(shí)，整個(gè)表記錄被鎖，此時(shí)其他讀/寫動(dòng)作一律等待。一些特定的動(dòng)作，如 ALTER TABLE 執(zhí)行時(shí)使用的也是表鎖

行鎖可以支持最大的并發(fā)，InnoDB 存儲(chǔ)引擎使用該鎖機(jī)制。如果要支持并發(fā)讀/寫，建議采用 InnoDB 存儲(chǔ)引擎

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

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

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

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

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

下面就聊聊，如果當(dāng)突然面對(duì)類似的情況，我們?cè)撊绾尉o急響應(yīng)？

想象一個(gè)場(chǎng)景，當(dāng)然也是軟件工程師職業(yè)生涯中會(huì)遇到的一種場(chǎng)景：原本運(yùn)行正常的程序，某一天突然數(shù)據(jù)庫的表被鎖了，業(yè)務(wù)無法正常運(yùn)轉(zhuǎn)，那么我們?cè)撊绾慰焖俣ㄎ皇悄膫€(gè)事務(wù)鎖了表，如何結(jié)束對(duì)應(yīng)的事物？

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

重啟是可以解決表被鎖的問題的，但針對(duì)線上業(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í)踐中，阿里云控制臺(tái)之所以能夠查看到所有的線程，猜測(cè)應(yīng)該使用的就是root用戶，而筆者去kill的時(shí)候，無法kill掉，是因?yàn)榈卿浀挠脩舴莚oot的數(shù)據(jù)庫賬號(hào)，無法操作另外一個(gè)用戶的線程。

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

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

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

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

執(zhí)行kill命令：

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

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

這里再補(bǔ)充一些MySQL鎖相關(guān)的知識(shí)點(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里面的鎖大致可以分成全局鎖、表級(jí)鎖和行鎖三類。MySQL中表級(jí)別的鎖有兩種：一種是表鎖，一種是元數(shù)據(jù)鎖（metadata lock，MDL)。

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

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

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

MySQL在進(jìn)行alter table等DDL操作時(shí)，有時(shí)會(huì)出現(xiàn)Waiting for table metadata lock的等待場(chǎng)景。

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

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

通過show processlist看不到表上有任何操作，但實(shí)際上存在有未提交的事務(wù)，可以在information_schema.innodb_trx中查看到。在事務(wù)沒有完成之前，表上的鎖不會(huì)釋放，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ù)中，對(duì)表進(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ù)或者長(zhǎng)事務(wù)導(dǎo)致的），在操作之前要確認(rèn)對(duì)要操作的表沒有任何進(jìn)行中的操作、沒有未提交事務(wù)、也沒有顯式事務(wù)中的報(bào)錯(cuò)語句。

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

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

mysql 事務(wù)如何釋放

事務(wù)能保證你做的一系列動(dòng)作，要么全部成功。如果有一個(gè)操作失敗，就回退到修改前。 比如你要做下面幾個(gè)操作， 1.刪除表A中的某些記錄 2.向B添加一些記錄。 3.修改C表中的一些數(shù)據(jù)。 使用事務(wù)，如果1,2都成功了，3卻失敗了。就會(huì)回退到第1步執(zhí)行前的樣子，ABC表都沒被修改。

網(wǎng)站名稱：mysql怎么釋放事務(wù) mysql釋放磁盤空間
本文路徑：http://muchs.cn/article20/dohoeco.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站營(yíng)銷、搜索引擎優(yōu)化、服務(wù)器托管、動(dòng)態(tài)網(wǎng)站、微信公眾號(hào)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)