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

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

創(chuàng)新互聯(lián)公司成立與2013年，先為大方等服務(wù)建站，大方等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為大方企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(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 等語(yǔ)句支持本地事務(wù)

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

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

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

InnoDB 會(huì)話級(jí)事務(wù)隔離級(jí)別可以使用以下語(yǔ)句設(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ù)庫(kù)并發(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鎖表和解鎖操作

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

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

擴(kuò)展：

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

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

3、查詢是否鎖表：

4、查詢進(jìn)程

如何對(duì)MySQL數(shù)據(jù)庫(kù)表進(jì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},…]解鎖表：UNLOCK TABLESLOCK TABLES為當(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ī)來訪問它。必須使用卡黨的鎖定協(xié)議告訴服務(wù)器是該表不被其他客戶機(jī)訪問。檢查表的鎖定協(xié)議本節(jié)只介紹如果使用表的內(nèi)部鎖定。對(duì)于檢查表的鎖定協(xié)議，此過程只針對(duì)表的檢查，不針對(duì)表的修復(fù)。1.調(diào)用mysql發(fā)布下列語(yǔ)句：$mysql –u root –p db_namemysqlLOCK TABLE tbl_name READ;mysqlFLUSH TABLES; 該鎖防止其它客戶機(jī)在檢查時(shí)寫入該表和修改該表。FLUSH語(yǔ)句導(dǎo)致服務(wù)器關(guān)閉表的文件，它將刷新仍在告訴緩存中的任何為寫入的改變。2.執(zhí)行檢查過程$myisamchk tbl_name$ isamchk tbl_name 3.釋放表鎖mysqlUNLOCK TABLES; 如果myisamchk或isamchk指出發(fā)現(xiàn)該表的問題，將需要執(zhí)行表的修復(fù)。修復(fù)表的鎖定協(xié)議這里只介紹如果使用表的內(nèi)部鎖定。修復(fù)表的鎖定過程類似于檢查表的鎖定過程，但有兩個(gè)區(qū)別。第一，你必須得到寫鎖而非讀鎖。由于你需要修改表，因此根本不允許客戶機(jī)對(duì)其進(jìn)行訪問。第二，必須在執(zhí)行修復(fù)之后發(fā)布FLUSH TABLE語(yǔ)句，因?yàn)閙yisamchk和isamchk建立的新的索引文件，除非再次刷新改表的高速緩存，否則服務(wù)器不會(huì)注意到這個(gè)改變。本例同樣適合優(yōu)化表的過程。1.調(diào)用mysql發(fā)布下列語(yǔ)句：$mysql –u root –p db_namemysqlLOCK TABLE tbl_name WRITE;mysqlFLUSH TABLES; 2.做數(shù)據(jù)表的拷貝，然后運(yùn)行myisamchk和isamchk：$cp tbl_name.* /some/other/dir$myisamchk --recover tbl_name$ isamchk --recover tbl_name --recover選項(xiàng)只是針對(duì)安裝而設(shè)置的。這些特殊選項(xiàng)的選擇將取決與你執(zhí)行修復(fù)的類型。

mysql 的鎖以及間隙鎖

mysql 為并發(fā)事務(wù)同時(shí)對(duì)一條記錄進(jìn)行讀寫時(shí)，提出了兩種解決方案：

1）使用 mvcc 的方法，實(shí)現(xiàn)多事務(wù)的并發(fā)讀寫，但是這種讀只是“快照讀”，一般讀的是歷史版本數(shù)據(jù)，還有一種是“當(dāng)前讀”，一般加鎖實(shí)現(xiàn)“當(dāng)前讀”，或者 insert、update、delete 也是當(dāng)前讀。

2）使用加鎖的方法，鎖分為共享鎖（讀鎖），排他鎖（寫鎖）

快照讀：就是select

當(dāng)前讀：特殊的讀操作，插入/更新/刪除操作，屬于當(dāng)前讀，處理的都是當(dāng)前的數(shù)據(jù)，需要加鎖。

mysql 在 RR 級(jí)別怎么處理幻讀的呢？一般來說，RR 級(jí)別通過 mvcc 機(jī)制，保證讀到低于后面事務(wù)的數(shù)據(jù)。但是 select for update 不會(huì)觸發(fā) mvcc，它是當(dāng)前讀。如果后面事務(wù)插入數(shù)據(jù)并提交，那么在 RR 級(jí)別就會(huì)讀到插入的數(shù)據(jù)。所以，mysql 使用 行鎖 + gap 鎖（簡(jiǎn)稱 next-key 鎖）來防止當(dāng)前讀的時(shí)候插入。

Gap Lock在InnoDB的唯一作用就是防止其他事務(wù)的插入操作，以此防止幻讀的發(fā)生。

Innodb自動(dòng)使用間隙鎖的條件：

MySQL白菜教程（Level 10 - 意向鎖&記錄鎖&間隙鎖）

意向鎖（Intention Locks; table-level lock）

意向鎖是一種特殊的表級(jí)鎖，意向鎖是為了讓 InnoDB 多粒度的鎖能共存而設(shè)計(jì)的。取得行的共享鎖和排他鎖之前需要先取得表的意向共享鎖（IS）和意向排他鎖（IX），意向共享鎖和意向排他鎖都是系統(tǒng)自動(dòng)添加和自動(dòng)釋放的，整個(gè)過程無需人工干預(yù)

意向鎖就是指未來的某一個(gè)時(shí)刻事務(wù)可能要加共享鎖或者排它鎖，提前聲明一個(gè)意向，分為兩種：

意向共享鎖（Intention Shared Lock） IS

事務(wù)有意向?qū)Ρ碇械哪承┬屑庸蚕礞i（S鎖）

意向排它鎖（Intention Exclusive Lock）IX

事務(wù)有意向?qū)Ρ碇械哪承┬屑优潘i（X鎖）

記錄鎖（Record Locks）

官方原文

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 這一行則是使用了記錄鎖，不允許其他事務(wù)進(jìn)行增，刪，改

但是 SELECT c1 FROM t WHERE c1 = 10; 是沒有鎖的，走的是快照讀，上文已經(jīng)闡明過了

記錄鎖本身不是鎖定記錄數(shù)據(jù)本身而是鎖定索引記錄，如果要鎖的列沒有索引，則會(huì)進(jìn)行全表記錄加鎖

間隙鎖（Gap Locks）

官方原文

比如 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE ;

插入 c1 為 15 的記錄會(huì)被鎖定不可執(zhí)行

這種默認(rèn)存在于可重復(fù)讀的事務(wù)隔離級(jí)別中的鎖，鎖定被圈定的范圍不允許 insert，防止不可重復(fù)讀，上文說了我們的事務(wù)隔離級(jí)別都是讀已提交，默認(rèn)會(huì)產(chǎn)生不可重復(fù)讀的問題