MYSQL事務與鎖表的問題？
成都創(chuàng)新互聯(lián)公司長期為上千多家客戶提供的網站建設服務，團隊從業(yè)經驗10年，關注不同地域、不同群體，并針對不同對象提供差異化的產品和服務；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網生態(tài)環(huán)境。為寶坻企業(yè)提供專業(yè)的成都網站設計、成都做網站，寶坻網站改版等技術服務。擁有10年豐富建站經驗和眾多成功案例,為您定制開發(fā)。
這個要看事務隔離級別，mysql默認是“可重復讀”，并且通過innodb引擎的多版本并發(fā)控制（MVCC，Multiversion Concurrency Control）機制防止了幻像讀，同樣，后面的select也不會被鎖定。
當事務隔離級別升到“串行化”時，后面只要前面的session沒有commit（包括select之后），那么后面的session的任何操作都會被鎖定。
當多個用戶訪問同一份數(shù)據(jù)時，一個用戶在更改數(shù)據(jù)的過程中，可能有其他用戶同時發(fā)起更改請求，為保證數(shù)據(jù)庫記錄的更新從一個一致性狀態(tài)變?yōu)榱硗庖粋€一致性狀態(tài)，使用事務處理是非常必要的，事務具有以下四個特性：
MySQL 提供了多種事務型存儲引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事務。為了支持事務，InnoDB 存儲引擎引入了與事務處理相關的 REDO 日志和 UNDO 日志，同時事務依賴于 MySQL 提供的鎖機制
事務執(zhí)行時需要將執(zhí)行的事務日志寫入日志文件，對應的文件為 REDO 日志。當每條 SQL 進行數(shù)據(jù)更新操作時，首先將 REDO 日志寫進日志緩沖區(qū)。當客戶端執(zhí)行 COMMIT 命令提交時，日志緩沖區(qū)的內容將被刷新到磁盤，日志緩沖區(qū)的刷新方式或者時間間隔可以通過參數(shù) innodb_flush_log_at_trx_commit 控制
REDO 日志對應磁盤上的 ib_logifleN 文件，該文件默認為 5MB，建議設置為 512MB，以便容納較大的事務。MySQL 崩潰恢復時會重新執(zhí)行 REDO 日志的記錄，恢復最新數(shù)據(jù)，保證已提交事務的持久性
與 REDO 日志相反，UNDO 日志主要用于事務異常時的數(shù)據(jù)回滾，具體內容就是記錄數(shù)據(jù)被修改前的信息到 UNDO 緩沖區(qū)，然后在合適的時間將內容刷新到磁盤
假如由于系統(tǒng)錯誤或者 rollback 操作而導致事務回滾，可以根據(jù) undo 日志回滾到沒修改前的狀態(tài)，保證未提交事務的原子性
與 REDO 日志不同的是，磁盤上不存在單獨的 UNDO 日志文件，所有的 UNDO 日志均存在表空間對應的 .ibd 數(shù)據(jù)文件中，即使 MySQL 服務啟動了獨立表空間
在 MySQL 中，可以使用 BEGIN 開始事務，使用 COMMIT 結束事務，中間可以使用 ROLLBACK 回滾事務。MySQL 通過 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等語句支持本地事務
MySQL 定義了四種隔離級別，指定事務中哪些數(shù)據(jù)改變其他事務可見、哪些數(shù)據(jù)該表其他事務不可見。低級別的隔離級別可以支持更高的并發(fā)處理，同時占用的系統(tǒng)資源更少
InnoDB 系統(tǒng)級事務隔離級別可以使用以下語句設置：
查看系統(tǒng)級事務隔離級別：
InnoDB 會話級事務隔離級別可以使用以下語句設置：
查看會話級事務隔離級別：
在該隔離級別，所有事務都可以看到其他未提交事務的執(zhí)行結果。讀取未提交的數(shù)據(jù)稱為臟讀（Dirty Read），即是：首先開啟 A 和 B 兩個事務，在 B 事務更新但未提交之前，A 事務讀取到了更新后的數(shù)據(jù)，但由于 B 事務回滾，導致 A 事務出現(xiàn)了臟讀現(xiàn)象
所有事務只能看見已經提交事務所做的改變，此級別可以解決臟讀，但也會導致不可重復讀（Nonrepeatable Read）：首先開啟 A 和 B 兩個事務，A事務讀取了 B 事務的數(shù)據(jù)，在 B 事務更新并提交后，A 事務又讀取到了更新后的數(shù)據(jù)，此時就出現(xiàn)了同一 A 事務中的查詢出現(xiàn)了不同的查詢結果
MySQL 默認的事務隔離級別，能確保同一事務的多個實例在并發(fā)讀取數(shù)據(jù)時看到同樣的數(shù)據(jù)行，理論上會導致一個問題，幻讀（Phontom Read）。例如，第一個事務對一個表中的數(shù)據(jù)做了修改，這種修改會涉及表中的全部數(shù)據(jù)行，同時第二個事務也修改這個表中的數(shù)據(jù)，這次的修改是向表中插入一行新數(shù)據(jù)，此時就會發(fā)生操作第一個事務的用戶發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行
InnoDB 通過多版本并發(fā)控制機制（MVCC）解決了該問題：InnoDB 通過為每個數(shù)據(jù)行增加兩個隱含值的方式來實現(xiàn)，這兩個隱含值記錄了行的創(chuàng)建時間、過期時間以及每一行存儲時間發(fā)生時的系統(tǒng)版本號，每個查詢根據(jù)事務的版本號來查詢結果
通過強制事務排序，使其不可能相互沖突，從而解決幻讀問題。簡而言之，就是在每個讀的數(shù)據(jù)行上加上共享鎖實現(xiàn)，這個級別會導致大量的超時現(xiàn)象和鎖競爭，一般不推薦使用
為了解決數(shù)據(jù)庫并發(fā)控制問題，如走到同一時刻客戶端對同一張表做更新或者查詢操作，需要對并發(fā)操作進行控制，因此產生了鎖
共享鎖的粒度是行或者元組（多個行），一個事務獲取了共享鎖以后，可以對鎖定范圍內的數(shù)據(jù)執(zhí)行讀操作
排他鎖的粒度與共享鎖相同，一個事務獲取排他鎖以后，可以對鎖定范圍內的數(shù)據(jù)執(zhí)行寫操作
有兩個事務 A 和 B，如果事務 A 獲取了一個元組的共享鎖，事務 B 還可以立即獲取這個元組的共享鎖，但不能獲取這個元組的排他鎖，必須等到事務 A 釋放共享鎖之后。如果事務 A 獲取了一個元組的排他鎖，事務 B 不能立即獲取這個元組的共享鎖，也不能立即獲取這個元組的排他鎖，必須等到 A 釋放排他鎖之后
意向鎖是一種表鎖，鎖定的粒度是整張表，分為意向共享鎖和意向排他鎖。意向共享鎖表示一個事務有意對數(shù)據(jù)上共享鎖或者排他鎖。有意表示事務想執(zhí)行操作但還沒真正執(zhí)行
鎖的粒度主要分為表鎖和行鎖
表鎖的開銷最小，同時允許的并發(fā)量也是最小。MyISAM 存儲引擎使用該鎖機制。當要寫入數(shù)據(jù)時，整個表記錄被鎖，此時其他讀/寫動作一律等待。一些特定的動作，如 ALTER TABLE 執(zhí)行時使用的也是表鎖
行鎖可以支持最大的并發(fā)，InnoDB 存儲引擎使用該鎖機制。如果要支持并發(fā)讀/寫，建議采用 InnoDB 存儲引擎
希望一下內容對你有所幫助
鎖定讀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可以鎖定用于當前線程的表。如果表被其它線程鎖定，則造成堵塞，直到可以獲取所有鎖定為止。UNLOCK TABLES可以釋放被當前線程保持的任何鎖定。當線程發(fā)布另一個LOCK TABLES時，或當與服務器的連接被關閉時，所有由當前線程鎖定的表被隱含地解鎖。
表鎖定只用于防止其它客戶端進行不正當?shù)刈x取和寫入。保持鎖定（即使是讀取鎖定）的客戶端可以進行表層級的操作，比如DROP TABLE。
修改方法
有兩種方法可以對配置了 systemd 的程序進行資源隔離：1. 命令行修改：通過執(zhí)行?systemctl set-property?命令實現(xiàn)，形式為?systemctl set-property?name parameter=value；修改默認即時生效。2. 手工修改文件：直接編輯程序的 systemd unit file 文件，完成之后需手工執(zhí)行?systemctl?daemon-reload?更新配置，并重啟服務?systemctl restart name.service。
systemd unit file 里支持的資源隔離配置項，如常見的：
CPUQuota=value
該參數(shù)表示服務可以獲取的最大 CPU 時間，value 為百分數(shù)形式，高于 100% 表示可使用?1 核以上的?CPU。與 cgroup cpu 控制器?cpu.cfs_quota_us?配置項對應。
MemoryLimit=value
該參數(shù)表示服務可以使用的最大內存量，value 可以使用 K, M, G, T 等后綴表示值的大小。與 cgroup?memory 控制器?memory.limit_in_bytes?配置項對應。
事務的4種隔離級別
READ UNCOMMITTED ? ? ? 未提交讀，可以讀取未提交的數(shù)據(jù)。
READ COMMITTED ? ? ? ? 已提交讀，對于鎖定讀(select with for update 或者 for share)、update 和 delete 語句，InnoDB 僅鎖定索引記錄，而不鎖定它們之間的間隙，因此允許在鎖定的記錄旁邊自由插入新記錄。 ? ? ? ? ? ? ? ? ? ?
Gap locking 僅用于外鍵約束檢查和重復鍵檢查。
REPEATABLE READ ? ? ? ?可重復讀，事務中的一致性讀取讀取的是事務第一次讀取所建立的快照。
SERIALIZABLE ? ? ? ? ? 序列化在了解了 4 種隔離級別的需求后，在采用鎖控制隔離級別的基礎上，我們需要了解加鎖的對象(數(shù)據(jù)本身間隙)，以及了解整個數(shù)據(jù)范圍的全集組成。
數(shù)據(jù)范圍全集組成
SQL 語句根據(jù)條件判斷不需要掃描的數(shù)據(jù)范圍（不加鎖）；
SQL 語句根據(jù)條件掃描到的可能需要加鎖的數(shù)據(jù)范圍；
以單個數(shù)據(jù)范圍為例，數(shù)據(jù)范圍全集包含：（數(shù)據(jù)范圍不一定是連續(xù)的值，也可能是間隔的值組成）
1、在mysql數(shù)據(jù)庫中如何鎖定一行數(shù)據(jù)，保證不被其他的操作影響。
2、從對數(shù)據(jù)的操作類型分為讀鎖和寫鎖。從對數(shù)據(jù)操作的粒度來分：表鎖和行鎖。
3、現(xiàn)在我們建立一個表來演示數(shù)據(jù)庫的行鎖講解。
4、行鎖基本演示如下圖所示。
5、如果兩個會話操作的是不同的行，就不會互相阻塞了。
加鎖情況與死鎖原因分析
為方便大家復現(xiàn)，完整表結構和數(shù)據(jù)如下：
CREATE TABLE `t3` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `c2` (`c2`)
) ENGINE=InnoDB
insert into t3 values(1,1),(15,15),(20,20);
在 session1 執(zhí)行 commit 的瞬間，我們會看到 session2、session3 的其中一個報死鎖。這個死鎖是這樣產生的：
1.?session1 執(zhí)行 delete ?會在唯一索引 c2 的 c2 = 15 這一記錄上加 X lock（也就是在MySQL 內部觀測到的：X Lock but not gap）；
2.?session2 和 session3 在執(zhí)行 insert 的時候，由于唯一約束檢測發(fā)生唯一沖突，會加 S Next-Key Lock，即對 (1,15] 這個區(qū)間加鎖包括間隙，并且被 seesion1 的 X Lock 阻塞，進入等待；
3.?session1 在執(zhí)行 commit 后，會釋放 X Lock，session2 和 session3 都獲得 S Next-Key Lock；
4.?session2 和 session3 繼續(xù)執(zhí)行插入操作，這個時候 INSERT INTENTION LOCK（插入意向鎖）出現(xiàn)了，并且由于插入意向鎖會被 gap 鎖阻塞，所以 session2 和 session3 互相等待，造成死鎖。
死鎖日志如下：
請點擊輸入圖片描述
INSERT INTENTION LOCK
在之前的死鎖分析第四點，如果不分析插入意向鎖，也是會造成死鎖的，因為插入最終還是要對記錄加 X Lock 的，session2 和 session3 還是會互相阻塞互相等待。
但是插入意向鎖是客觀存在的，我們可以在官方手冊中查到，不可忽略：
Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.
插入意向鎖其實是一種特殊的 gap lock，但是它不會阻塞其他鎖。假設存在值為 4 和 7 的索引記錄，嘗試插入值 5 和 6 的兩個事務在獲取插入行上的排它鎖之前使用插入意向鎖鎖定間隙，即在（4，7）上加 gap lock，但是這兩個事務不會互相沖突等待。
當插入一條記錄時，會去檢查當前插入位置的下一條記錄上是否存在鎖對象，如果下一條記錄上存在鎖對象，就需要判斷該鎖對象是否鎖住了 gap。如果 gap 被鎖住了，則插入意向鎖與之沖突，進入等待狀態(tài)（插入意向鎖之間并不互斥）?？偨Y一下這把鎖的屬性：
1. 它不會阻塞其他任何鎖；
2. 它本身僅會被 gap lock 阻塞。
在學習 MySQL 過程中，一般只有在它被阻塞的時候才能觀察到，所以這也是它常常被忽略的原因吧...
GAP LOCK
在此例中，另外一個重要的點就是 gap lock，通常情況下我們說到 gap lock 都只會聯(lián)想到 REPEATABLE-READ 隔離級別利用其解決幻讀。但實際上在 READ-COMMITTED 隔離級別，也會存在 gap lock ，只發(fā)生在：唯一約束檢查到有唯一沖突的時候，會加 S Next-key Lock，即對記錄以及與和上一條記錄之間的間隙加共享鎖。
通過下面這個例子就能驗證：
請點擊輸入圖片描述
這里 session1 插入數(shù)據(jù)遇到唯一沖突，雖然報錯，但是對 (15,20] 加的 S Next-Key Lock 并不會馬上釋放，所以 session2 被阻塞。另外一種情況就是本文開始的例子，當 session2 插入遇到唯一沖突但是因為被 X Lock 阻塞，并不會立刻報錯 “Duplicate key”，但是依然要等待獲取 S Next-Key Lock 。
有個困惑很久的疑問：出現(xiàn)唯一沖突需要加 S Next-Key Lock 是事實，但是加鎖的意義是什么？還是說是通過 S Next-Key Lock 來實現(xiàn)的唯一約束檢查，但是這樣意味著在插入沒有遇到唯一沖突的時候，這個鎖會立刻釋放，這不符合二階段鎖原則。這點希望能與大家一起討論得到好的解釋。
如果是在 REPEATABLE-READ，除以上所說的唯一約束沖突外，gap lock 的存在是這樣的：
普通索引（非唯一索引）的S/X Lock，都帶 gap 屬性，會鎖住記錄以及前1條記錄到后1條記錄的左閉右開區(qū)間，比如有[4,6,8]記錄，delete 6，則會鎖住[4,8）整個區(qū)間。
對于 gap lock，相信 DBA 們的心情是一樣一樣的，所以我的建議是：
1. 在絕大部分的業(yè)務場景下，都可以把 MySQL 的隔離界別設置為 READ-COMMITTED；
2. 在業(yè)務方便控制字段值唯一的情況下，盡量減少表中唯一索引的數(shù)量。
鎖沖突矩陣
前面我們說的 GAP LOCK 其實是鎖的屬性，另外我們知道 InnoDB 常規(guī)鎖模式有：S 和 X，即共享鎖和排他鎖。鎖模式和鎖屬性是可以隨意組合的，組合之后的沖突矩陣如下，這對我們分析死鎖很有幫助：
請點擊輸入圖片描述
重慶分公司
重慶分公司
