[转载] 数据库分析手记 —— InnoDB锁机制分析
?
作者：倪煜
? ? ? ? InnoDB鎖機(jī)制常常困擾大家，不同的條件下往往表現(xiàn)出不同的鎖競爭，在實(shí)際工作中經(jīng)常要分析各種鎖超時(shí)、死鎖的問題。本文通過不同條件下的實(shí)驗(yàn)，利用InnoDB系統(tǒng)給出的各種信息，分析了鎖的工作機(jī)制。通過本文可以幫助大家了解InnoDB鎖的基本原理，常見的沖突、死鎖，以及對InnoDB事務(wù)日志信息的解讀。
1.?索引基本原理
? ? ? ? InnoDB主要使用行級鎖（row lock），其行鎖是通過在索引項(xiàng)上加鎖而實(shí)現(xiàn)的，如果MySQL的執(zhí)行計(jì)劃沒有用到索引，那么行鎖也就無意義了，所以了解鎖之前需要了解一點(diǎn)索引原理。InnoDB索引是由改進(jìn)的B+樹實(shí)現(xiàn)，基本特點(diǎn)就是一顆快速查找樹同時(shí)葉子節(jié)點(diǎn)由雙向鏈表連接，索引項(xiàng)都在葉子節(jié)點(diǎn)上，而且分為兩種類型：
- 聚簇索引（clustered index?）（或主鍵索引）
- ?輔助索引（secondary index）（或二級索引、非聚簇索引）
? ? ? ? 聚簇索引的特點(diǎn)是葉子節(jié)點(diǎn)中除了存儲索引key值，還存儲了真實(shí)的記錄內(nèi)容，同時(shí)還會存儲事務(wù)ID和回滾指針。因此聚簇索引就等于表的真實(shí)內(nèi)容，所以每張表都會有一個(gè)聚簇索引。通常聚簇索引就是主鍵索引，如果建表時(shí)沒有顯示的定義主鍵，則會首先選擇“非空的唯一索引（unique not null）”作為聚簇索引。如果沒有的話則會自動創(chuàng)建一個(gè)6字節(jié)大小的隱藏主鍵作為主鍵索引值，隨著記錄增加而單調(diào)遞增。一張表只有一個(gè)聚簇索引，否則該多浪費(fèi)。其他的索引都屬于輔助索引，輔助索引只存儲輔助鍵和主鍵，查詢時(shí)要再通過主鍵索引二次查找定位記錄。由于輔助索引的鍵值是可以重復(fù)的，所以為了唯一標(biāo)識B+樹鍵值，需要重復(fù)存儲主鍵值。
? ? ? ? InnoDB按聚簇索引的形式存儲數(shù)據(jù)，大致如下：（《高性能MySQL》配圖）
?
? ? ? ? 聚簇索引中的每個(gè)葉子節(jié)點(diǎn)包含primary key的值、事務(wù)ID、回滾指針(rollback pointer)和余下的列。下文分析中會看到這幾個(gè)字段的具體展現(xiàn)。
? ? ? ? 輔助索引類似如下：
? ? ? ? 記錄的是輔助key值和主key值。
? ? ? ? 上面的示意圖相當(dāng)于索引的邏輯結(jié)構(gòu)，在實(shí)際中B+樹中所有葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)都是通過page結(jié)構(gòu)管理，一個(gè)page單元通常含有多個(gè)節(jié)點(diǎn)數(shù)據(jù)。每個(gè)page中有一個(gè)Infimum表示最小，Supremum表示最大。通常的一個(gè)主鍵索引詳細(xì)結(jié)構(gòu)類似于Jeremy Cole的博客中提供InnoDB結(jié)構(gòu)圖：
? ? ? ? （關(guān)于InnoDB索引的實(shí)現(xiàn)細(xì)節(jié)請參考另一篇wiki《InnoDB索引實(shí)現(xiàn)機(jī)制》）
?
2. InnoDB鎖的模式和類型
? ? ? ? InnoDB鎖有兩個(gè)緯度，一個(gè)是鎖模式，一個(gè)是鎖類型。
2.1 鎖模式：
-
S共享鎖：讀鎖（shared lock permits the transaction that holds the lock to read a row.）
? ? ? ? (select …where … lock in share mode)顯示的加S鎖。允許一個(gè)事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
- X排它鎖：寫鎖（exclusive lock permits the transaction that holds the lock to update or delete a row）
? ? ? ? (select … where … for update)顯示的加X鎖。允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。
? ? ? ?
? ? ? ? 為了允許行鎖和表鎖共存，實(shí)現(xiàn)多粒度鎖機(jī)制，同時(shí)還有兩種內(nèi)部使用的表級意向鎖（都是表鎖），所謂意向就是想做但沒真做。
- IS鎖：事務(wù)對記錄加S鎖之前必須先獲取表的IS鎖（Intention shared?(IS): Transaction?T?intends to set?S?locks on individual rows in table?t.）
? ? （Before a transaction can acquire an S lock on a row in table t, it must first acquire an IS or stronger lock on t. ）
?
- IX鎖：事務(wù)對記錄加X鎖之前必須先獲取表的IX鎖（Intention exclusive?(IX): Transaction?T?intends to set?X?locks on those rows.）
? ? （Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on t. ）
? ? ? ? 關(guān)于意向鎖的官方補(bǔ)充解釋：Thus, intention locks do not block anything except full table requests (for example,?LOCK TABLES ... WRITE). The main purpose of?IX?and?IS?locks is to show that someone is locking a row, or going to lock a row in the table.
? ? ? ? 意向鎖是InnoDB自動加的，不需用戶干預(yù)，對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及到的數(shù)據(jù)集加排他鎖X，對于普通SELECT語句，InnoDB不會加任何鎖。
? ? ? ??SELECT ... LOCK IN SHARE MODE?會先設(shè)置一個(gè)IS鎖
? ? ? ??SELECT ... FOR UPDATE?會先設(shè)置一個(gè)IX鎖
? ? ?不同的鎖有不同的兼容性。四種鎖的兼容矩陣如下：
| 請求模式 當(dāng)前模式 | X | IX | S | IS |
| X | 沖突 | 沖突 | 沖突 | 沖突 |
| IX | 沖突 | 兼容 | 沖突 | 兼容 |
| S | 沖突 | 沖突 | 兼容 | 兼容 |
| IS | 沖突 | 兼容 | 兼容 | 兼容 |
? ? ? ? 意向鎖之間沒有任何沖突，S和X鎖之間的關(guān)系顯而易見。只有X、S鎖和意向鎖之間的關(guān)系比較特殊，后文詳細(xì)舉例分析。
2.2 鎖類型
? ? ? ? 除了有鎖模式概念，還有鎖的類型，總體分為表級鎖和行級鎖。
2.2.1 表鎖
? ? ? ? lock table XXX read；對表XXX加S讀鎖。
? ? ? ? lock table XXX write；對表XXX加X寫鎖。
? ? ? ? 意向鎖就是表級鎖，會跟表鎖之間有沖突。
2.2.2 行鎖
- 間隙鎖(Gap Lock)，只鎖間隙。表現(xiàn)為鎖住一個(gè)區(qū)間（注意這里的區(qū)間都是開區(qū)間，也就是不包括邊界值）。
- 記錄鎖(Record Lock)，只鎖記錄。表現(xiàn)為僅僅鎖著單獨(dú)的一行記錄。
- Next-Key鎖(源碼中稱為Ordinary Lock)，同時(shí)鎖住記錄和間隙。從實(shí)現(xiàn)的角度為record lock+gap lock，而且兩種鎖有可能只成功一個(gè)，所以next-key是半開半閉區(qū)間，且是下界開，上界閉。一張表中的next-key鎖包括：(負(fù)無窮大，最小的第一條記錄]，(記錄之間]，(最大的一條記錄，正無窮大)。
- 插入意圖鎖(Insert Intention Lock)，插入操作時(shí)使用的鎖。在代碼中，插入意圖鎖實(shí)際上是Gap鎖上加了一個(gè)LOCK_INSERT_INTENTION的標(biāo)記。也就是說insert語句會對插入的行加一個(gè)X記錄鎖，但是在插入這個(gè)行的過程之前，會設(shè)置一個(gè)Insert intention的Gap鎖，叫做Insert intention鎖。
? ? ? ? 看一下官方定義：
? ? ? ? 行鎖在X鎖上做了一些精確的細(xì)分，在代碼中稱作Precise Mode。這些精確的模式使的鎖的粒度更細(xì)小，可以減少沖突。而且在事務(wù)級別RC或者innodb_locks_unsafe_for_binlog打開的情況下GAP鎖會失效。這個(gè)很重要，后面會說到。
3. 主鍵索引鎖分析
? ? ? ? 分析鎖之前一定要確認(rèn)前提條件。
| 鎖分析前提條件： 隔離級別為RR： tx_isolation = REPEATABLE-READ ? 關(guān)閉binlog不安全寫： innodb_locks_unsafe_for_binlog = OFF ? |
? ? ? ? 同時(shí)打開InnoDB監(jiān)控：create table innodb_lock_monitor(x int) engine=InnoDB;
? ? ? （關(guān)于監(jiān)控的官方文檔：http://dev.MySQL.com/doc/refman/5.1/en/innodb-standard-monitor.html）
3.1 主鍵索引鎖測試
? ? ? ? 下面的實(shí)驗(yàn)建立在一張簡單的表A上，我們通過實(shí)例觀察InnoDB鎖機(jī)制。假設(shè)我們有這樣一張表A：
?
? ? ? ? 建表語句和數(shù)據(jù)集如下：
| | A???? | CREATE TABLE `A` ( ? `id` int(11) NOT NULL, ? `name` varchar(1024) DEFAULT NULL, ? `t` int(11) DEFAULT NULL, ? PRIMARY KEY (`id`), ? KEY `i_name` (`name`(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | |
| MySQL>? select * from A; +----+------+ | id | name | +----+------+ |? 2 | aa?? | |? 6 | eee? | |? 7 | aa?? | |? 8 | adf? | |? 9 | aa?? | | 11 | a??? | | 12 | bbb? | +------+------+ 7 rows in set (0.00 sec) |
?
? ? ? ? 下面通過針對不同的where條件，觀察主鍵索引加鎖情況，注意彩色的內(nèi)容，先把要測試的條件列出來：
| =1 |
| <2 |
| =2 |
| <=2 |
| >2 and <6 |
| >=2 and <6 |
| >=2 and <=6 |
| =4 |
| =6 |
| >12 |
| >=12 |
| =12 |
| <=12 and >11 |
| <12 and >11 |
?
? ? ? ? 下面逐條分析：
| case1：=1 |
| MySQL> select * from A where id=1 for update; Empty set (0.00 sec) ------------ TRANSACTIONS ------------ Trx id counter 721 Purge done for trx's n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 71A, not started MySQL thread id 2, OS thread handle 0x41743960, query id 163 localhost root ---TRANSACTION 720, ACTIVE 3 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 178 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 720 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 720lock_mode X locks gap before rec?? //行鎖模式與類型 Record lock,?heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0?//上鎖的記錄 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 000000000714; asc?????? ;; ?2: len 7; hex 94000001960110; asc??????? ;; ?3: len 2; hex 6161; asc aa;; |
| 解析：
事務(wù)720對表A加IX鎖，如前面所述，對行加X鎖之前先對表加IX
對索引項(xiàng)加X鎖，類型為gap鎖
表示gap鎖加在哪個(gè)索引項(xiàng)上，19可以先理解為索引項(xiàng)的物理地址，InnoDB使用Page no. +Heap no.來做行的唯一識別。我們可以將Heap no.理解為頁面上的一個(gè)自增數(shù)值。每條物理記錄在被創(chuàng)建時(shí)，都會分配一個(gè)唯一的heap no。 鍵值可以理解為一個(gè)邏輯值，page no. + heap no. 可以理解為物理地址。 ? 從這里也可以看出gap鎖實(shí)際是加在索引項(xiàng)上的，不同的索引項(xiàng)之間并沒有其他數(shù)據(jù)結(jié)構(gòu)管理gap鎖。 |
? ? ? ? 一條行記錄可由(space_id, page_no, heap_no)唯一標(biāo)識，記錄項(xiàng)字段包含四個(gè)部分：
0: len 4; hex 80000002; asc ?聚簇值字段
1: len 6; hex 000000000714; asc? 事務(wù)ID：48位整型的ID值，由最近一次修改該字段的事務(wù)決定。
2: len 7; hex 94000001960110; asc? 回滾指針：包含最近一次修改該字段的undo記錄，長度為7字節(jié)（1-bit“is insert”標(biāo)記；7-bit回滾段ID；4字節(jié)頁號；2字節(jié)undo log的頁偏移）
3: len 2; hex 6161; asc aa;; 非主鍵字段：
?
?
?
| case2：<2 |
| select * from A where id<2 for update; |
| ---TRANSACTION 718, ACTIVE 36 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 134 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 718 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 718lock_mode X ??????//后面未標(biāo)明鎖類型的是默認(rèn)類型，在源碼中是LOCK_ORDINARY Record lock,?heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 000000000714; asc?????? ;; ?2: len 7; hex 94000001960110; asc??????? ;; ?3: len 2; hex 6161; asc aa;; |
| 解析：
在索引項(xiàng)2上加next-key鎖，其他兩處解釋同上 |
?
?
| case3：=2 |
| MySQL> select * from A where id=2 for update; +----+------+ | id | name | +----+------+ |? 2 | aa?? | +----+------+ 1 row in set (0.00 sec) |
| ------------ TRANSACTIONS ------------ Trx id counter 721 Purge done for trx's n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 71A, not started MySQL thread id 2, OS thread handle 0x41743960, query id 163 localhost root ---TRANSACTION 720, ACTIVE 3 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 178 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 720 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 720lock_mode X locks rec but not gap?? //X模式的記錄鎖 Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 000000000714; asc?????? ;; ?2: len 7; hex 94000001960110; asc??????? ;; ?3: len 2; hex 6161; asc aa;; |
| 分析：
只有一個(gè)對2這條記錄（heap no 19）的記錄鎖，符合常識。 |
?
?
?
?
| case4：<=2 |
| MySQL> select * from A where id<=2 for update;??????????? +----+------+ | id | name | +----+------+ |? 2 | aa?? | +----+------+ 1 row in set (0.00 sec) |
| ------------ TRANSACTIONS ------------ Trx id counter 71F Purge done for trx's n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 71A, not started MySQL thread id 2, OS thread handle 0x41743960, query id 163 localhost root ---TRANSACTION 71E, ACTIVE 2 sec 2 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 168 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 71E lock mode IX RECORD LOCKS?space id 0 page no 306?n bits 88 index `PRIMARY` of table `test`.`A` trx id 71Elock_mode X Record lock, heap no 14 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;;???????//主key值6 ?1: len 6; hex 000000000536; asc????? 6;; ?2: len 7; hex ae0000014f0110; asc???? O? ;; ?3: len 3; hex 656565; asc eee;;?????//記錄中非主建字段 ? Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 000000000714; asc?????? ;; ?2: len 7; hex 94000001960110; asc??????? ;; ?3: len 2; hex 6161; asc aa;; |
| 解析： 表示給heap no 14和heap no 19分別加一個(gè)X模式的next-key鎖。這里heap no 19是指key為2的記錄（看前面的hex值），heap no 14是指key為6的記錄，也就是說<=2時(shí)不僅會加鎖2，還會加鎖2后面的一條記錄6. 這個(gè)條件會導(dǎo)致后面的很多特殊的鎖沖突。原因暫時(shí)沒想到，應(yīng)該跟索引掃描有關(guān)。 ? |
| InnoDB鎖系統(tǒng)有1個(gè)全局對象lock_sys(type lock_sys_t)，而行鎖的hash table就存儲在其中 struct lock_sys_t { ??????? ib_mutex_t????? mutex;?? ??????? hash_table_t*?? rec_hash;?? --行鎖hash表，以(space_id, page_no)為hash key，即同一頁的所有鎖均在一個(gè)hash bucket上， ??????? ulint?????????? n_lock_max_wait_time; ??????? // more ... }; |
?
?
| case5：>2 and <6 |
| MySQL> select * from A where id>2 and id<6 for update;????????????? Empty set (0.00 sec) |
| ------------ TRANSACTIONS ------------ Trx id counter 723 Purge done for trx's n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 71A, not started MySQL thread id 2, OS thread handle 0x41743960, query id 163 localhost root ---TRANSACTION 722, ACTIVE 3 sec 2 lock struct(s), heap size 376, 1 row lock(s) //2個(gè)鎖結(jié)構(gòu)，1個(gè)行鎖 MySQL thread id 1, OS thread handle 0x415b9960, query id 188 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 722 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 722 lock_mode X Record lock, heap no 14 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 000000000536; asc????? 6;; ?2: len 7; hex ae0000014f0110; asc???? O? ;; ?3: len 3; hex 656565; asc eee;; |
| 只在記錄6上加了一個(gè)X的next-key鎖 |
?
?
| case6:：>=2 and <6 |
| MySQL> select * from A where id>=2 and id<6 for update; +----+------+ | id | name | +----+------+ |? 2 | aa?? | +----+------+ 1 row in set (0.00 sec) |
| TABLE LOCK table `test`.`A` trx id 71D lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 71Dlock_mode X locks rec but not gap Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 000000000714; asc?????? ;; ?2: len 7; hex 94000001960110; asc??????? ;; ?3: len 2; hex 6161; asc aa;; ? RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 71Dlock_mode X Record lock, heap no 14 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 000000000536; asc? ????6;; ?2: len 7; hex ae0000014f0110; asc???? O? ;; ?3: len 3; hex 656565; asc eee;; |
| 記錄2上加了一個(gè)X的記錄鎖；記錄6上加了一個(gè)X的next-key鎖 |
?
?
| case7: >=2 and <=6 |
| select * from A where id>=2 and id<=6 for update; |
| ------------ TRANSACTIONS ------------ Trx id counter 729 Purge done for trx's n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 727, not started MySQL thread id 2, OS thread handle 0x41743960, query id 207 localhost root ---TRANSACTION 728, ACTIVE 2 sec 3 lock struct(s), heap size 376, 3 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 212 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 728 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 728lock_mode X locks rec but not gap Record lock,?heap no 19?PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 000000000714; asc?????? ;; ?2: len 7; hex 94000001960110; asc??????? ;; ?3: len 2; hex 6161; asc aa;; ? RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 728lock_mode X Record lock,?heap no 14?PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 000000000536; asc????? 6;; ?2: len 7; hex ae0000014f0110; asc???? O? ;; ?3: len 3; hex 656565; asc eee;; ? Record lock,?heap no 18?PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000007; asc???? ;; ?1: len 6; hex 0000000005cd; asc?????? ;; ?2: len 7; hex a7000001900110; asc??????? ;; ?3: len 2; hex 6161; asc aa;; |
| 記錄2加了記錄鎖；記錄6和7加了next-key鎖 |
?
?
?
| case8: =4（記錄不存在） |
| MySQL> select * from A where id=4 for update;??? Empty set (0.01 sec) |
| ------------ TRANSACTIONS ------------ Trx id counter 720 Purge done for trx's n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 71A, not started MySQL thread id 2, OS thread handle 0x41743960, query id 163 localhost root ---TRANSACTION 71F, ACTIVE 3 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 172 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 71F lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 71Flock_mode X locks gap before rec Record lock,?heap no 14 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 000000000536; asc????? 6;; ?2: len 7; hex ae0000014f0110; asc???? O? ;; ?3: len 3; hex 656565; asc eee;; ? |
| 對一個(gè)不存在的記錄加鎖，鎖住間隙，所以在記錄6上加了一個(gè)X模式的gap鎖 |
?
?
?
| case9: =6 |
| MySQL> select * from A where id=6 for update; +----+------+ | id | name | +----+------+ |? 6 | eee? | +----+------+ 1 row in set (0.00 sec) |
| ------------ TRANSACTIONS ------------ Trx id counter 722 Purge done for trx's n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 71A, not started MySQL thread id 2, OS thread handle 0x41743960, query id 163 localhost root ---TRANSACTION 721, ACTIVE 2 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 182 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 721 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 721 lock_mode X locks rec but not gap Record lock, heap no 14 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 000000000536; asc????? 6;; ?2: len 7; hex ae0000014f0110; asc???? O? ;; ?3: len 3; hex 656565; asc eee;; |
| 只加一個(gè)記錄鎖 |
?
? ? ? ? 對于記錄的末尾會不會有什么不同嗎？我們繼續(xù)驗(yàn)證一下：
?
?
| case10：>12 |
| MySQL> select * from A where id>12 for update; Empty set (0.00 sec) |
| ------------ TRANSACTIONS ------------ Trx id counter 72A Purge done for trx's n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 727, not started MySQL thread id 2, OS thread handle 0x41743960, query id 207 localhost root ---TRANSACTION 729, ACTIVE 19 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 217 localhost root show engine InnoDB status Trx read view will not see trx with id >= 72A, sees < 72A TABLE LOCK table `test`.`A` trx id 729 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 729 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 ?0: len 8; hex 73757072656d756d; asc supremum;; |
| 不太一樣，對無窮大supremum加了一個(gè)X的next-key鎖 |
?
?
?
| case11：>=12 |
| MySQL> select * from A where id>=12 for update;?????????? +----+------+ | id | name | +----+------+ | 12 | bbb? | +----+------+ 1 row in set (0.00 sec) |
| ------------ TRANSACTIONS ------------ Trx id counter 72A Purge done for trx's n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 727, not started MySQL thread id 2, OS thread handle 0x41743960, query id 207 localhost root ---TRANSACTION 729, ACTIVE 62 sec 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 219 localhost root show engine InnoDB status Trx read view will not see trx with id >= 72A, sees < 72A TABLE LOCK table `test`.`A` trx id 729?lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 729 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 ?0: len 8; hex 73757072656d756d; asc supremum;; ? RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 729lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 8000000c; asc???? ;; ?1: len 6; hex 000000000542; asc????? B;; ?2: len 7; hex b900000156011c; asc???? V? ;; ?3: len 3; hex 626262; asc bbb;; |
| 除了等于的記錄12加記錄所，還要給sup加next-key |
?
?
?
| case12: =12 |
| MySQL> select * from A where id=12 for update; +----+------+ | id | name | +----+------+ | 12 | bbb? | +----+------+ 1 row in set (0.01 sec) |
| ------------ TRANSACTIONS ------------ Trx id counter 78E Purge done for trx's n:o < 78D undo n:o < 0 History list length 63 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 789, not started MySQL thread id 10, OS thread handle 0x415b9960, query id 510 localhost root ---TRANSACTION 78D, ACTIVE 17 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 11, OS thread handle 0x41743960, query id 522 localhost root show engine InnoDB status Trx read view will not see trx with id >= 78E, sees < 78E TABLE LOCK table `test`.`A` trx id 78D lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 78D lock_mode X locks rec but not gap /*后面值的操作由于我做了表內(nèi)容改動，所以heap no變了，但不影響結(jié)論*/ Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8000000c; asc???? ;; 1: len 6; hex 00000000077f; asc?????? ;; 2: len 7; hex ef000001750158; asc???? u X;; 3: len 3; hex 626262; asc bbb;; |
| 不多說 |
?
?
| case13: <=12 and >11 |
| MySQL> select * from A where id<=12 and id>11 for update; +----+------+ | id | name | +----+------+ | 12 | bbb? | +----+------+ 1 row in set (0.00 sec) |
| ------------ TRANSACTIONS ------------ Trx id counter 78F Purge done for trx's n:o < 78D undo n:o < 0 History list length 63 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 789, not started MySQL thread id 10, OS thread handle 0x415b9960, query id 510 localhost root ---TRANSACTION 78E, ACTIVE 9 sec 2 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 11, OS thread handle 0x41743960, query id 527 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 78E lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 78E lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 ?0: len 8; hex 73757072656d756d; asc supremum;; ? Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 8000000c; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef000001750158; asc???? u X;; ?3: len 3; hex 626262; asc bbb;; |
| sup和記錄12加X的next-key鎖 |
?
?
?
| case14: <12 and >11 |
| MySQL> select * from A where id<12 and id>11 for update; Empty set (0.00 sec) |
| ------------ TRANSACTIONS ------------ Trx id counter 790 Purge done for trx's n:o < 78D undo n:o < 0 History list length 63 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 789, not started MySQL thread id 10, OS thread handle 0x415b9960, query id 510 localhost root ---TRANSACTION 78F, ACTIVE 4 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 11, OS thread handle 0x41743960, query id 531 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 78F lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 78F lock_mode X Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 8000000c; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef000001750158; asc???? u X;; ?3: len 3; hex 626262; asc bbb;; |
| ? |
?
? ? ? ? 從上面的case實(shí)驗(yàn)可以得出一張表，表示不同條件下分別在哪些索引項(xiàng)上，加何種鎖：
? ? ? ? 左邊一列是where條件，G表是GAP鎖加在哪個(gè)索引項(xiàng)上，N是next-key鎖，R是記錄鎖。有了這個(gè)表，就可以知道在不同條件的事務(wù)并發(fā)下，哪些會產(chǎn)生鎖等待。
?
比如，如果有下面兩個(gè)并發(fā)事務(wù)發(fā)生：
| t1 | t2 |
| select * from A where id<2 for update; | select * from A where id=2 for update;? |
t1事務(wù)需要<2的，t2事務(wù)需要=2的，表面上兩個(gè)條件沒有重合之處，但是由于他們都是在索引key=2上加鎖，所以就會產(chǎn)生沖突：
| ------------ TRANSACTIONS ------------ Trx id counter 71A Purge done for trx's n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 719, ACTIVE 13 sec starting index read MySQL tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 2, OS thread handle 0x41743960, query id 133 localhost root statistics select * from A where id=2 for update ------- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 719 lock_mode X locks rec but not gap waiting Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 000000000714; asc?????? ;; ?2: len 7; hex 94000001960110; asc??????? ;; ?3: len 2; hex 6161; asc aa;; ? ------------------ TABLE LOCK table `test`.`A` trx id 719 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 719 lock_mode X locks rec but not gap waiting Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 000000000714; asc?????? ;; ?2: len 7; hex 94000001960110; asc??????? ;; ?3: len 2; hex 6161; asc aa;; ? ---TRANSACTION 718,?ACTIVE 36 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 134 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 718 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 718 lock_mode X Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 000000000714; asc?????? ;; ?2: len 7; hex 94000001960110; asc??????? ;; ?3: len 2; hex 6161; asc aa;; |
| 解析： 先執(zhí)行了TRANSACTION 718，鎖住heap no 19 PHYSICAL RECORD，后執(zhí)行的TRANSACTION 719就會發(fā)生lock_mode X locks rec but not gap waiting。注意觀察上面鎖等待的信息 |
?
從上表還可知，同樣的事情還會發(fā)生在下面類似的例子：
| t1 | t2 |
| select * from A where id<=2 for update; | select * from A where id>2 and id<6 for update; |
?
3.2 鎖類型的精確模式
? ? ? ? 那么對于下面這種呢：
| t1 | t2 |
| select * from A where id<2 for update; | select * from A where id=1 for update; |
? ? ? ? 按照表來說兩個(gè)事務(wù)都會對key=2加鎖，而且都是for update的X鎖，應(yīng)該會有沖突，我們看下結(jié)果。
| ------------ TRANSACTIONS ------------ Trx id counter 71A Purge done for trx's n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 719, ACTIVE 216 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 2, OS thread handle 0x41743960, query id 136 localhost root TABLE LOCK table `test`.`A` trx id 719 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 719 lock_mode X locks gap before rec Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 000000000714; asc?????? ;; ?2: len 7; hex 94000001960110; asc??????? ;; ?3: len 2; hex 6161; asc aa;; ? ---TRANSACTION 718, ACTIVE 239 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 137 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 718 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 718 lock_mode X Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 000000000714; asc?????? ;; ?2: len 7; hex 94000001960110; asc??????? ;; ?3: len 2; hex 6161; asc aa;; |
| 結(jié)果毫無沖突，兩個(gè)X鎖都成功了，而且都鎖住heap no 19，為什么呢？ |
?
? ? ? ??為了解釋上面的case就要引入一種行鎖的精確模式，我先貼一下源碼中對于4中鎖類型的注釋：
| #define LOCK_S? 4 /* shared */ #define LOCK_X? 5 /* exclusive */ ... /* Waiting lock flag */ ? #define LOCK_WAIT 256 /* this wait bit should be so high that it can be ORed to the lock mode and type; when this bit is set, it means that the lock has not yet been granted, it is just waiting for its turn in the wait queue */ ... /* Precise modes */ ? #define LOCK_ORDINARY 0 /* this flag denotes an ordinary next-key lock in contrast to LOCK_GAP or LOCK_REC_NOT_GAP */ ??#define LOCK_GAP 512 /* this gap bit should be so high that it can be ORed to the other flags; when this bit is set, it means that the lock holds only on the gap before the record; for instance, an x-lock on the gap does not give permission to modify the record on which the bit is set; locks of this type are created when records are removed from the index chain of records */ ??#define LOCK_REC_NOT_GAP 1024 /* this bit means that the lock is only on the index record and does NOT block inserts to the gap before the index record; this is used in the case when we retrieve a record with a unique key, and is also used in locking plain SELECTs (not part of UPDATE or DELETE) when the user has set the READ COMMITTED isolation level */ ??#define LOCK_INSERT_INTENTION 2048 /* this bit is set when we place a waiting gap type record lock request in order to let an insert of an index record to wait until there are no conflicting locks by other transactions on the gap; note that this flag remains set when the waiting lock is granted, or if the lock is inherited to a neighboring record */ |
? ? ? ? 精確模式就是從源碼中導(dǎo)出的。大家都知到S鎖和X鎖的兼容關(guān)系，但這只是鎖的模式，上面說的InnoDB行鎖有四種類型：G(gap鎖)、R(記錄鎖)、N(next-key鎖)、I(插入意向鎖)。那么對于不同類型的鎖在X模式下有怎樣的兼容關(guān)系呢？（S模式下沒有什么沖突，不用解釋）
? ? ? ? 有人從源碼發(fā)掘出一個(gè)行鎖兼容矩陣，這個(gè)在官方文檔中并沒有。
| 兼容性 | G | I | R | N | 當(dāng)前持有的X鎖類型 |
| G | + | + | + | + | ? 要加的X鎖類型 |
| I | - | + | + | - | |
| R | + | + | - | - | |
| N | + | + | - | - |
? ? ? ? + 代表兼容， -代表不兼容。S鎖和S鎖是完全兼容的，因此在判別兼容性時(shí)不需要對比精確模式，精確模式的檢測，用在S、X和X、X之間。從這個(gè)精確模式可以看出，<2是N鎖，=1是G鎖，這兩種鎖匙完全兼容的，所以即使都是X鎖也沒有沖突，而=2是R鎖，N和R是不兼容的，所以<2和=2沖突。同時(shí)大家要注意這個(gè)矩陣不是對稱的，這點(diǎn)在I鎖的兼容性上，大家可以通過類似實(shí)驗(yàn)驗(yàn)證。其實(shí)上表中的N鎖應(yīng)該分解成G+R鎖來看會好理解一些。
? ? ? ? 這種新的兼容性是為了帶來更好的事務(wù)并發(fā)性，但也會帶來一些其他問題呢？比如下面的例子：
| t1 | t2 |
| MySQL> select * from A where id>2 and id<6 for update; Empty set (0.00 sec) | MySQL> select * from A where id=4 for update; Empty set (0.00 sec) |
| 兩個(gè)事務(wù)都加鎖成功： ------------ TRANSACTIONS ------------ Trx id counter 792 Purge done for trx's n:o < 78D undo n:o < 0 History list length 63 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 791, ACTIVE 58 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 11, OS thread handle 0x41743960, query id 539 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 791 lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 791 lock_mode X locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef00000175011c; asc???? u? ;; ?3: len 3; hex 656565; asc eee;; ? ---TRANSACTION 790, ACTIVE 95 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 10, OS thread handle 0x415b9960, query id 537 localhost root TABLE LOCK table `test`.`A` trx id 790 lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 790 lock_mode X Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef00000175011c; asc???? u? ;; ?3: len 3; hex 656565; asc eee;; | |
| MySQL> insert into A values(3,'abc'); | ? |
| 出現(xiàn)鎖等待： ------------ TRANSACTIONS ------------ Trx id counter 792 Purge done for trx's n:o < 78D undo n:o < 0 History list length 63 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 791, ACTIVE 226 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 11, OS thread handle 0x41743960, query id 543 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 791 lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 791 lock_mode X locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef00000175011c; asc???? u? ;; ?3: len 3; hex 656565; asc eee;; ? ---TRANSACTION 790, ACTIVE 263 sec inserting MySQL tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 10, OS thread handle 0x415b9960, query id 542 localhost root update insert into A values(3,'abc') ------- TRX HAS BEEN WAITING 22 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 790 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef00000175011c; asc???? u? ;; ?3: len 3; hex 656565; asc eee;; ? ------------------ TABLE LOCK table `test`.`A` trx id 790 lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 790 lock_mode X Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef00000175011c; asc???? u? ;; ?3: len 3; hex 656565; asc eee;; ? RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 790 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef00000175011c; asc???? u? ;; ?3: len 3; hex 656565; asc eee;; ? 原因很明顯，I鎖和G鎖不兼容，需要等待，你雖然通過條件>2 and <6加了for update鎖，但是并沒有真正鎖住區(qū)間，這時(shí)insert 3時(shí)會先加I鎖，于是要等待t2的G鎖沖突了 | |
| ? | 這時(shí)如果t2認(rèn)為鎖住了4記錄，然后執(zhí)行 MySQL> insert into A values(4,'abc'); 會怎樣呢？ 直接死鎖了。 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction ? |
| 很明顯，因?yàn)閠1等待t2的G鎖，t2等待t1的N鎖。 下面是死鎖日志： ------------------------ LATEST DETECTED DEADLOCK ------------------------ 141216 14:54:55 *** (1) TRANSACTION: TRANSACTION 790, ACTIVE 556 sec inserting MySQL tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s) MySQL thread id 10, OS thread handle 0x415b9960, query id 544 localhost root update insert into A values(3,'abc') *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 790lock_mode X locks gap before rec insert intention waiting Record lock,?heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef00000175011c; asc???? u? ;; ?3: len 3; hex 656565; asc eee;; ? *** (2) TRANSACTION: TRANSACTION 791, ACTIVE 519 sec inserting MySQL tables in use 1, locked 1 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 11, OS thread handle 0x41743960, query id 545 localhost root update insert into A values(4,'abc') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 791 lock_mode X locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef00000175011c; asc???? u? ;; ?3: len 3; hex 656565; asc eee;; ? *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 791 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef00000175011c; asc???? u? ;; ?3: len 3; hex 656565; asc eee;; ? *** WE ROLL BACK TRANSACTION (2) | |
?
? ? ? ? 同樣的原因還會有下面這種死鎖的例子：
| t1 | t2 |
| select * from A where id=3 for update; insert into A values(3,'abc'); | select * from A where id=4 for update; insert into A values(4,'def'); |
?
? ? ? ? “InnoDB locks all index records found by the WHERE clause with an exclusive lock and the gaps between them with a shared gap lock.”?網(wǎng)上會有上面這種說法，其實(shí)是因?yàn)閄的gap鎖之間是兼容的，其底層實(shí)現(xiàn)可能是通過S鎖的方式實(shí)現(xiàn)的。
? ? ? ? 精確模式在InnoDB中非常重要，從中也可以觀察到一些特性：
- GAP鎖基本上跟所有鎖都兼容
- Next-key鎖和Record鎖之間都沖突
- 持有Insert鎖的記錄可以兼容所有鎖，但是Insert鎖卻不能加到GAP和Next-key鎖上。
? ? ? ? 想想這些特性為什么。
?
?
4. 無索引鎖
? ? ? ? 對于無索引的表，由于無法利用索引，因此會對所有記錄加Next-key鎖，可以觀察一下實(shí)驗(yàn)結(jié)果：
| 建一個(gè)無索引的表B： | B???? | CREATE TABLE `B` ( ? `id` int(11) NOT NULL, ? `name` varchar(1024) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | MySQL> select * from B; +----+------+ | id | name | +----+------+ |? 3 | dd?? | |? 4 | t??? | |? 5 | dd?? | |? 4 | t??? | +----+------+ 4 rows in set (0.00 sec) |
| 執(zhí)行下面語句： MySQL> select * from A where id=2 for update; +----+------+ | id | name | +----+------+ |? 2 | aa?? | +----+------+ 1 row in set (0.00 sec) 由于沒有索引，會鎖全部索引項(xiàng)。而且全都是N鎖。（4條記錄和一個(gè)無窮大） |
| ------------ TRANSACTIONS ------------ Trx id counter 72F Purge done for trx's n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 72C, not started MySQL thread id 1, OS thread handle 0x415b9960, query id 248 localhost root show engine InnoDB status ---TRANSACTION 72E, ACTIVE 3 sec 2 lock struct(s), heap size 376, 5 row lock(s) MySQL thread id 2, OS thread handle 0x41743960, query id 247 localhost root TABLE LOCK table `test`.`B` trx id 72E?lock mode IX RECORD LOCKS space id 1 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`B` trx id 72E lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 ?0: len 8; hex 73757072656d756d; asc supremum;; ? Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ?0: len 6; hex 000000000203; asc?? ????;;?? //InnoDB自動生成的6字節(jié)ID（索引相關(guān)文章有介紹） ?1: len 6; hex 0000000005be; asc?????? ;; ?2: len 7; hex 1a0000018d0110; asc??????? ;; ?3: len 4; hex 80000003; asc???? ;;????? //記錄字段 ?4: len 2; hex 6464; asc dd;; ? Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ?0: len 6; hex 000000000204; asc?????? ;; ?1: len 6; hex 0000000005c5; asc?????? ;; ?2: len 7; hex 200000018e0110; asc??????? ;; ?3: len 4; hex 80000004; asc???? ;; ?4: len 1; hex 74; asc t;; ? Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ?0: len 6; hex 000000000205; asc?????? ;; ?1: len 6; hex 0000000005be; asc?????? ;; ?2: len 7; hex 1a0000018d0154; asc?????? T;; ?3: len 4; hex 80000005; asc???? ;; ?4: len 2; hex 6464; asc dd;; ? Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ?0: len 6; hex 000000000206; asc?????? ;; ?1: len 6; hex 0000000005c5; asc?????? ;; ?2: len 7; hex 200000018e0130; asc?????? 0;; ?3: len 4; hex 80000004; asc???? ;; ?4: len 1; hex 74; asc t;; |
? ? ? ? 還有執(zhí)行計(jì)劃中沒有使用到索引的也是類似上面這種情形，可以自行驗(yàn)證。
?
?
5. 輔助索引鎖
? ? ? ? ?下面再觀察一下輔助索引的情況：
| ---------+ | transfer | CREATE TABLE `transfer` ( ? `id` int(11) NOT NULL AUTO_INCREMENT, ? `trans_id` int(11) NOT NULL, ? `name` varchar(256) NOT NULL, ? PRIMARY KEY (`id`), ? KEY `trans_id` (`trans_id`), ? KEY `name` (`name`(255)) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 | ? MySQL> select * from transfer; +----+----------+------+ | id | trans_id | name | +----+----------+------+ |? 1 |????? 101 | aaa? | |? 4 |?? ???103 | bbb? | | 10 |????? 104 | ddd? | +----+----------+------+ 3 rows in set (0.01 sec) |
? ? ? ? 針對上表做一下一種條件的測試：
case1：=103
| MySQL> select * from transfer where trans_id=103 for update; +----+----------+------+ | id | trans_id | name | +----+----------+------+ |? 4 |????? 103 | bbb? | +----+----------+------+ 1 row in set (0.00 sec) |
| --TRANSACTION 771, ACTIVE 4 sec 4 lock struct(s), heap size 1248, 3 row lock(s) MySQL thread id 6, OS thread handle 0x415fa960, query id 421 localhost root TABLE LOCK table `test`.`transfer` trx id 771 lock mode IX RECORD LOCKS space id 22?page no 4?n bits 72 index `trans_id` of table `test`.`transfer` trx id 771lock_mode X? //輔助索引page 4。加的是N鎖，不同于主鍵索引哦 Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ?0: len 4; hex 80000067; asc??? g;;????????????????? 輔助key?? hex67 == 103 ?1: len 4; hex 80000004; asc???? ;;??????????????????????????????????????????? 主key????? hex4? ==? 4 ? RECORD LOCKS space id 22?page no 3?n bits 72 index `PRIMARY` of table `test`.`transfer` trx id 771lock_mode X locks rec but not gap //主索引page 3 ，加了記錄鎖 Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ?0: len 4; hex 80000004; asc???? ;;??????????? ????????????????????????? 主key ?1: len 6; hex 0000000005e0; asc?????? ;;????????????????????????????? trx_id ?2: len 7; hex b8000001930110; asc??????? ;;?????????????????????? roll_ptr ?3: len 4; hex 80000067; asc??? g;;????????????????????????????????????????????????????? 對應(yīng)的輔助key ?4: len 3; hex 626262; asc bbb;;?????????????????????? ??????? name 字段? bbb ? RECORD LOCKS space id 22?page no 4?n bits 72 index `trans_id` of table `test`.`transfer` trx id 771lock_mode X locks gap before rec Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ?0: len 4; hex 80000068; asc??? h;;???????? 在103后面的一條記錄上加了gap鎖 ?1: len 4; hex 8000000a; asc???? ;; ? 這里給104又加了一個(gè)gap鎖，因?yàn)檩o助鍵是可能重復(fù)的，所以可能會在其后插入相同的記錄，因此這里要對其后的間隙加gap鎖 如果在這里繼續(xù)給101加鎖，會不會也給103加一個(gè)gap鎖呢？ |
| 繼續(xù)： MySQL> select * from transfer where trans_id=101 for update; +----+----------+------+ | id | trans_id | name | +----+----------+------+ |? 1 |????? 101 | aaa? | +----+----------+------+ 1 row in set (0.00 sec) |
| ---TRANSACTION 771, ACTIVE 403 sec 4 lock struct(s), heap size 1248, 5 row lock(s) MySQL thread id 6, OS thread handle 0x415fa960, query id 423 localhost root TABLE LOCK table `test`.`transfer` trx id 771 lock mode IX RECORD LOCKS space id 22 page no 4 n bits 72 index `trans_id` of table `test`.`transfer` trx id 771 lock_mode X Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ?0: len 4; hex 80000065; asc??? e;; ?1: len 4; hex 80000001; asc???? ;; ? Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ?0: len 4; hex 80000067; asc??? g;; ?1: len 4; hex 80000004; asc???? ;; ? RECORD LOCKS space id 22 page no 3 n bits 72 index `PRIMARY` of table `test`.`transfer` trx id 771 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ?0: len 4; hex 80000001; asc???? ;; ?1: len 6; hex 0000000005d3; asc?????? ;; ?2: len 7; hex ac000001910110; asc??????? ;; ?3: len 4; hex 80000065; asc??? e;; ?4: len 3; hex 616161; asc aaa;; ? Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ?0: len 4; hex 80000004; asc???? ;; ?1: len 6; hex 0000000005e0; asc?????? ;; ?2: len 7; hex b8000001930110; asc??????? ;; ?3: len 4; hex 80000067; asc??? g;; ?4: len 3; hex 626262; asc bbb;; ? RECORD LOCKS space id 22 page no 4 n bits 72 index `trans_id` of table `test`.`transfer` trx id 771 lock_mode X locks gap before rec Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ?0: len 4; hex 80000068; asc??? h;; ?1: len 4; hex 8000000a; asc???? ;; ? 沒有！因?yàn)?03上已經(jīng)有N鎖了，而N鎖比G鎖級別高，所以沒有必要再加個(gè)G鎖了。 所以如果是單獨(dú)給101加鎖，就肯定會給103加gap鎖了，驗(yàn)證一下： |
| ? 單獨(dú)加鎖101 MySQL> select * from transfer where trans_id=101 for update; +----+----------+------+ | id | trans_id | name | +----+----------+------+ |? 1 |????? 101 | aaa? | +----+----------+------+ 1 row in set (0.00 sec) ? ---TRANSACTION 772, ACTIVE 4 sec 4 lock struct(s), heap size 1248, 3 row lock(s) MySQL thread id 6, OS thread handle 0x415fa960, query id 428 localhost root TABLE LOCK table `test`.`transfer` trx id 772 lock mode IX RECORD LOCKS space id 22 page no 4 n bits 72 index `trans_id` of table `test`.`transfer` trx id 772 lock_mode X Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ?0: len 4; hex 80000065; asc??? e;; ?1: len 4; hex 80000001; asc???? ;; ? RECORD LOCKS space id 22 page no 3 n bits 72 index `PRIMARY` of table `test`.`transfer` trx id 772 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ?0: len 4; hex 80000001; asc???? ;; ?1: len 6; hex 0000000005d3; asc?????? ;; ?2: len 7; hex ac000001910110; asc??????? ;; ?3: len 4; hex 80000065; asc??? e;; ?4: len 3; hex 616161; asc aaa;; ? RECORD LOCKS space id 22 page no 4 n bits 72 index `trans_id` of table `test`.`transfer` trx id 772 lock_mode X locks gap before rec Record lock,?heap no 3?PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ?0: len 4; hex 80000067; asc??? g;; ?1: len 4; hex 80000004; asc???? ;; ? 的確會給后面一條記錄加G鎖。 ? |
| 加鎖不存在記錄102，在103記錄上加gap鎖，主索引無鎖 ?---TRANSACTION 773, ACTIVE 3 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 6, OS thread handle 0x415fa960, query id 434 localhost root TABLE LOCK table `test`.`transfer` trx id 773 lock mode IX RECORD LOCKS space id 22 page no 4 n bits 72 index `trans_id` of table `test`.`transfer` trx id 773 lock_mode X locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ?0: len 4; hex 80000067; asc??? g;; ?1: len 4; hex 80000004; asc???? ;; |
| 加鎖<101，粒度比=101要少一個(gè)后一條記錄的G鎖，很好理解 MySQL> select * from transfer where trans_id<101 for update;??? Empty set (0.00 sec) ?---TRANSACTION 774, ACTIVE 3 sec 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 6, OS thread handle 0x415fa960, query id 440 localhost root TABLE LOCK table `test`.`transfer` trx id 774 lock mode IX RECORD LOCKS space id 22 page no 4 n bits 72 index `trans_id` of table `test`.`transfer` trx id 774 lock_mode X Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ?0: len 4; hex 80000065; asc??? e;; ?1: len 4; hex 80000001; asc???? ;; ? RECORD LOCKS space id 22 page no 3 n bits 72 index `PRIMARY` of table `test`.`transfer` trx id 774 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ?0: len 4; hex 80000001; asc???? ;; ?1: len 6; hex 0000000005d3; asc?????? ;; ?2: len 7; hex ac000001910110; asc??????? ;; ?3: len 4; hex 80000065; asc??? e;; ?4: len 3; hex 616161; asc aaa;; |
?
?
| <=101 把101和103主輔索引都鎖住了 ?MySQL> select * from transfer where trans_id<=101 for update; +----+----------+------+ | id | trans_id | name | +----+----------+------+ |? 1 |????? 101 | aaa? | +----+----------+------+ 1 row in set (0.00 sec) ?---TRANSACTION 775, ACTIVE 17 sec 3 lock struct(s), heap size 376, 4 row lock(s) MySQL thread id 6, OS thread handle 0x415fa960, query id 444 localhost root TABLE LOCK table `test`.`transfer` trx id 775 lock mode IX RECORD LOCKS space id 22 page no 4 n bits 72 index `trans_id` of table `test`.`transfer` trx id 775 lock_mode X Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ?0: len 4; hex 80000065; asc??? e;; ?1: len 4; hex 80000001; asc???? ;; ? Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ?0: len 4; hex 80000067; asc??? g;; ?1: len 4; hex 80000004; asc???? ;; ? RECORD LOCKS space id 22 page no 3 n bits 72 index `PRIMARY` of table `test`.`transfer` trx id 775 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ?0: len 4; hex 80000001; asc???? ;; ?1: len 6; hex 0000000005d3; asc?????? ;; ?2: len 7; hex ac000001910110; asc??????? ;; ?3: len 4; hex 80000065; asc??? e;; ?4: len 3; hex 616161; asc aaa;; ? Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ?0: len 4; hex 80000004; asc???? ;; ?1: len 6; hex 0000000005e0; asc?????? ;; ?2: len 7; hex b8000001930110; asc??????? ;; ?3: len 4; hex 80000067; asc??? g;; ?4: len 3; hex 626262; asc bbb;; |
?
? ? ? ? 所以，如果有下面兩個(gè)并發(fā)，會沖突，雖然id=4跟trans_id<=101沒有什么關(guān)系：
| MySQL> select * from transfer where trans_id<=101 for update; +----+----------+------+ | id | trans_id | name | +----+----------+------+ |? 1 |????? 101 | aaa? | +----+----------+------+ 1 row in set (0.00 sec) | MySQL> update transfer set name='ccc' where id=4; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
?
6. 表級鎖
? ? ? ? 前面在說鎖模式時(shí)提到IX和IS鎖，官方定義是表級鎖，而且IX和IS之間是全兼容，所以對于IX、IS和X、S鎖之間的關(guān)系比較好奇，官方定義的兼容性：
?
|
| X | S | IX | IS |
| X | Conflict | Conflict | Conflict | Conflict |
| S | Conflict | Compatible | Conflict | Compatible |
| IX | Conflict | Conflict | Compatible | Compatible |
| IS | Conflict | Compatible | Compatible | Compatible |
?
? ? ? ? S，X之間和IS，IX之間很好理解，重點(diǎn)關(guān)注的就是黃色區(qū)域的關(guān)系。
? ? ? ? 構(gòu)造兩個(gè)事務(wù)的狀態(tài)：
| ---TRANSACTION 76C, not started MySQL thread id 2, OS thread handle 0x41743960, query id 371 localhost root ---TRANSACTION 76A, ACTIVE 838 sec 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 375 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 76A lock mode IX ? 76A事務(wù)目前持有A表的IX鎖 |
| IX和S關(guān)系 事務(wù)76C對A加表級S鎖： lock table A read; 無沖突。（官方說有沖突） ---TRANSACTION 76C, not started MySQL tables in use 1, locked 1 MySQL thread id 2, OS thread handle 0x41743960, query id 378 localhost root ---TRANSACTION 76A, ACTIVE 925 sec 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 379 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 76A lock mode IX ? 注意：上面實(shí)驗(yàn)由于沒有設(shè)置autocommit=0,必須為0時(shí)才生效！重做實(shí)驗(yàn)后： MySQL> set autocommit=0; Query OK, 0 rows affected (0.00 sec) ? MySQL> show variables like '%autocommit%';?????????? +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit??? | OFF?? | +---------------+-------+ 1 row in set (0.00 sec) MySQL> lock table A read; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 有沖突，符合官方定義 ---TRANSACTION 7A2, ACTIVE 7 sec setting table lock LOCK WAIT 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 10, OS thread handle 0x415b9960, query id 584 localhost root System lock lock table A read ------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `test`.`A` trx id 7A2 lock mode S waiting ------------------ TABLE LOCK table `test`.`A` trx id 7A2 lock mode S waiting ---TRANSACTION 7A1,?ACTIVE 164 sec 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 11, OS thread handle 0x41743960, query id 585 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 7A1 lock mode IX ? ? |
| IX和X關(guān)系 事務(wù)76C對A加表級X鎖： lock table A write; 有沖突 ---TRANSACTION 76C, not started MySQL thread id 2, OS thread handle 0x41743960, query id 380 localhost root Waiting for table metadata lock lock table A write ---TRANSACTION 76A, ACTIVE 970 sec 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 381 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 76A lock mode IX |
| IS 和 S關(guān)系 無沖突 ---TRANSACTION 76E, not started MySQL tables in use 1, locked 1 MySQL thread id 2, OS thread handle 0x41743960, query id 405 localhost root ---TRANSACTION 76F, ACTIVE 727 sec 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 406 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 76F lock mode IS |
| IS 和 X關(guān)系 有沖突 ---TRANSACTION 76E, not started MySQL thread id 2, OS thread handle 0x41743960, query id 400 localhost root Waiting for table metadata lock lock table A write ---TRANSACTION 76F, ACTIVE 651 sec 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 401 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 76F lock mode IS |
?
? ? ? ? 對于delete、update等各種操作以及l(fā)ock in share mode下或各種查詢條件下鎖的情況都可以用上面的方法通過實(shí)驗(yàn)分析。
?
?
附錄
A. InnoDB幾項(xiàng)常用行鎖變量：
MySQL> show status like 'innodb_row%';??????????????
+-------------------------------+--------+
| Variable_name???????????????? | Value? |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0????? |
| Innodb_row_lock_time????????? | 191673 |累計(jì)鎖等待時(shí)間（毫秒）
| Innodb_row_lock_time_avg????? | 31945? |平均行鎖等待時(shí)間
| Innodb_row_lock_time_max????? | 50965? |最大行鎖等待時(shí)間
| Innodb_row_lock_waits???????? | 6????? |
| Innodb_rows_deleted?????????? | 0????? |
| Innodb_rows_inserted????????? | 17???? |
| Innodb_rows_read????????????? | 45???? |
| Innodb_rows_updated?????????? | 2????? |
+-------------------------------+--------+
| Innodb_lock_wait_timeout????? | 50???? |鎖等待超時(shí)時(shí)間（秒）
?
B. 問題案例
舉兩個(gè)實(shí)際環(huán)境中的案例，由于鎖的沖突而導(dǎo)致的故障
案例一：
首先把某實(shí)際系統(tǒng)中的數(shù)據(jù)庫操作shell腳本簡化如下，省略號略去一些無關(guān)信息：
| MySQL.core -q -e " ??? insert into?bfb_analytics.yc_dashboard_cust_temp ????? ……; ? " & ? MySQL.core -q -e "????? ??? drop table if exists bfb_analytics.yc_dashboard_cust_cat_${i_date}; ??? create table bfb_analytics.yc_dashboard_cust_cat_${i_date}( ????? select f_buyer_user_id …… ????? from?bfb_analytics.yc_dashboard_cust_temp ??? ); ? " |
大致邏輯是第一條語句往yc_dashboard_cust_temp表中插入數(shù)據(jù)，但會放入后臺運(yùn)行。第二條語句從yc_dashboard_cust_temp表select數(shù)據(jù)導(dǎo)入新表。但在運(yùn)行過程中該腳本頻繁出現(xiàn)新表yc_dashboard_cust_cat_${i_date}創(chuàng)建失敗，同時(shí)會有對該表的lock wait超時(shí)。從通常的InnoDB鎖分析看select f_buyer_user_id ,…… from bfb_analytics.yc_dashboard_cust_temp這種語句應(yīng)該會通過MVCC方式讀取表的快照，而不會對表加鎖。那么這里為何會加鎖呢？首先要確認(rèn)一下當(dāng)前MySQL環(huán)境：
| +---------------+-----------------+ | Variable_name | Value?????????? | +---------------+-----------------+ | tx_isolation? | REPEATABLE-READ | +---------------+-----------------+ ? +--------------------------------+-------+ | Variable_name????????????????? | Value | +--------------------------------+-------+ | innodb_locks_unsafe_for_binlog | OFF?? | +--------------------------------+-------+ ? +---------------+-----------+ | Variable_name | Value???? | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ ? | log_bin ??| ON ?| ? |
上面幾個(gè)變量值會對InnoDB鎖行為有所影響。
原因分析：
在RR隔離級別下，同時(shí)開啟了bin-log時(shí)，系統(tǒng)首先認(rèn)為你是需要進(jìn)行數(shù)據(jù)恢復(fù)和主從同步的。 為了保證事務(wù)在主從數(shù)據(jù)一致，對于create … B select * from A;這種情況必須對A表加鎖，否則可能會存在另一個(gè)事務(wù)在對A做update操作，當(dāng)這兩個(gè)事務(wù)寫入bin-log時(shí)就會由于事務(wù)完成時(shí)間的不確定而寫入 順序不同，那么當(dāng)同步或者恢復(fù)時(shí)就會造成數(shù)據(jù)不一致。
?
案例二：
另外一個(gè)案例跟上面差不多，有類似下面的shell腳本：
| create table bfb_analytics.yc_dashboard_rec_${i_date}( ????? select f_trans_id …… ????? from bfb_db.t_recvables ????? where f_create_time<=’2014-10-11’ and f_create_time>’2014-09-01’ ??? ); |
| 同時(shí)每天會有bfb_db.t_recvables表的主從同步在進(jìn)行。 |
該 腳本的運(yùn)行幾乎每天都會發(fā)生因?yàn)殒i等待超時(shí)而導(dǎo)致主從同步停止，所以一定是上面操作bfb_db.t_recvables的語句問題。按案例一中的分析可 知create … select …類型需要對select的查詢表加鎖，但是因?yàn)檫@里加了where限定條件，而且recvables表在f_create_time上也有索引，所以鎖 應(yīng)該是加在where限定范圍內(nèi)的。主從同步的時(shí)間都是當(dāng)前時(shí)間，跟腳本中的時(shí)間范圍跟主從同步的時(shí)間沒有任何交集，為何會鎖等待呢？
原因分析：
???????? 通過查看該語句的執(zhí)行計(jì)劃（explain）才知，由于結(jié)果集數(shù)據(jù)量較大，當(dāng)夸天超過15天時(shí)就已經(jīng)不再使用f_create_time上的索引了，當(dāng)InnoDB不能使用索引時(shí)就只能鎖全部記錄，這樣就演變成案例一中的情形了。
?
上面的兩個(gè)case可以通過下面的測試驗(yàn)證：
| select * from A where id=2 for update; | create table D select * from A; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
| ------------ TRANSACTIONS ------------ Trx id counter 79B Purge done for trx's n:o < 796 undo n:o < 0 History list length 65 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 79A, ACTIVE 3 sec starting index read MySQL tables in use 2, locked 2 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 10, OS thread handle 0x415b9960, query id 557 localhost root Sending data create table D select * from A ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 79A?lock mode S waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef000001750110; asc???? u? ;; ?3: len 2; hex 6161; asc aa;; ? ------------------ TABLE LOCK table `test`.`A` trx id 79A lock mode IS RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 79A lock mode S waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef000001750110; asc???? u? ;; ?3: len 2; hex 6161; asc aa;; ? ---TRANSACTION 798, ACTIVE 18 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 11, OS thread handle 0x41743960, query id 558 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 798 lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 798 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef000001750110; asc???? u? ;; ?3: len 2; hex 6161; asc aa;; | |
確實(shí)是要加鎖，原因正如前面所述，由于binlog記錄是有序的，要保證數(shù)據(jù)恢復(fù)和同步必須加鎖。但如果將innodb_locks_unsafe_for_binlog設(shè)置off，也就是不要binlog的安全功能了，這里就不會加鎖了。當(dāng)然如果關(guān)閉binlog也不用加鎖。InnoDB在默認(rèn)情況下不得不設(shè)置鎖定，因?yàn)樵趶囊粋€(gè)備份的回滾恢復(fù)中，每個(gè)SQL語句不得不以與它最初被執(zhí)行的方式完全同樣的方式執(zhí)行。
?
C. LOG中事務(wù)ID的解釋
順便提一下開頭提到的聚簇索引的結(jié)構(gòu)，主鍵key值下面是TID，這個(gè)值記錄的是最后一次修改該字段的事務(wù)ID。
| ------------ TRANSACTIONS ------------ Trx id counter 7A6 Purge done for trx's n:o < 79D undo n:o < 0 History list length 66 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 7A4, not started MySQL thread id 16, OS thread handle 0x415fa960, query id 604 localhost root ---TRANSACTION 7A5, ACTIVE 9 sec??? //當(dāng)前的事務(wù)ID 2 lock struct(s), heap size 376, 8 row lock(s) MySQL thread id 17, OS thread handle 0x41743960, query id 610 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 7A5 lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 7A5 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 ?0: len 8; hex 73757072656d756d; asc supremum;; ? Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;;????????? //主key值 ?1: len 6; hex 00000000077f; asc?????? ;;??? //事務(wù)ID，其實(shí)是產(chǎn)生這條記錄的事務(wù)ID，類似于一個(gè)數(shù)據(jù)的版本號，用于MVCC中。 ?2: len 7; hex ef000001750110; asc???? u? ;; //回滾LOG指針 ?3: len 2; hex 6161; asc aa;;??????????????? //其他字段值 ? Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;;???? //此時(shí)所有數(shù)據(jù)的事務(wù)ID都是一樣的。 ?2: len 7; hex ef00000175011c; asc???? u? ;; ?3: len 3; hex 656565; asc eee;; ? Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000007; asc???? ;; ?1: len 6; hex 00000000077f; asc????? ?;; ?2: len 7; hex ef000001750128; asc???? u (;; ?3: len 2; hex 6161; asc aa;; ? Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000008; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef000001750134; asc???? u 4;; ?3: len 3; hex 616466; asc adf;; ? Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000009; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef000001750140; asc???? u @;; ?3: len 2; hex 6161; asc aa;; ? Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 8000000b; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef00000175014c; asc???? u L;; ?3: len 1; hex 61; asc a;; ? Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 8000000c; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef000001750158; asc???? u X;; ?3: len 3; hex 626262; asc bbb;; |
| 上面所有數(shù)據(jù)的事務(wù)ID都是一樣的，如果我們這時(shí)候修改某條數(shù)據(jù)，他的ID應(yīng)該會變成當(dāng)前事務(wù)ID MySQL> update A set name='new' where id=8; |
| ------------ TRANSACTIONS ------------ Trx id counter 7A6 Purge done for trx's n:o < 79D undo n:o < 0 History list length 66 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 7A4, not started MySQL thread id 16, OS thread handle 0x415fa960, query id 604 localhost root ---TRANSACTION 7A5, ACTIVE 75 sec 2 lock struct(s), heap size 376, 8 row lock(s), undo log entries 1 MySQL thread id 17, OS thread handle 0x41743960, query id 613 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 7A5 lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 7A5 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 ?0: len 8; hex 73757072656d756d; asc supremum;; ? Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000002; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef000001750110; asc???? u? ;; ?3: len 2; hex 6161; asc aa;; ? Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000006; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef00000175011c; asc???? u? ;; ?3: len 3; hex 656565; asc eee;; ? Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000007; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef000001750128; asc???? u (;; ?3: len 2; hex 6161; asc aa;; ? Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000008; asc???? ;; ?1: len 6; hex 0000000007a5; asc?????? ;;?? //變成當(dāng)前事務(wù)ID了 ?2: len 7; hex 0f0000018801ca; asc??????? ;; ?3: len 3; hex 6e6577; asc new;; ? Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 80000009; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef000001750140; asc???? u @;; ?3: len 2; hex 6161; asc aa;; ? Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 8000000b; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef00000175014c; asc???? u L;; ?3: len 1; hex 61; asc a;; ? Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ?0: len 4; hex 8000000c; asc???? ;; ?1: len 6; hex 00000000077f; asc?????? ;; ?2: len 7; hex ef000001750158; asc???? u X;; ?3: len 3; hex 626262; asc bbb;; |
轉(zhuǎn)載于:https://www.cnblogs.com/zhengran/p/4611616.html
總結(jié)
以上是生活随笔為你收集整理的[转载] 数据库分析手记 —— InnoDB锁机制分析的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： MVC URL参数传递+变为空格解决方法
- 下一篇： 处理json中影响解析的多余引号