InnoDB和MyISAM

Mysql在5.5之前默認(rèn)使用MyISAM存儲引擎，之后使用InnoDB。

查看當(dāng)前存儲引擎：

show variables like ‘%storage_engine%';

MyISAM操作數(shù)據(jù)都是使用的表鎖，你更新一條記錄就要鎖整個(gè)表，導(dǎo)致性能較低，并發(fā)不高。當(dāng)然它也不會存在死鎖問題。

InnoDB與M有ISAM的最大不同有兩點(diǎn)

• InnoDB支持事務(wù)
• InnoDB采用了行級鎖。也就是你需要修改哪行，就可以只鎖定哪行。

在Mysql中，行級鎖并不是直接鎖記錄，而是鎖索引。

索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，Mysql就會鎖定這條主鍵索引；如果一條語句操作了非主鍵索引，Mysql會先鎖定該非主鍵索引，再鎖定相關(guān)的主鍵索引。

InnoDB行鎖是通過給索引加鎖實(shí)現(xiàn)的，如果沒有索引，InnoDB會通過因此的聚簇索引來對記錄加鎖。

也就是說：如果不通過索引條件檢索數(shù)據(jù)，那么InnoDB將對表中所有數(shù)據(jù)加鎖，實(shí)際效果跟表鎖一樣。因?yàn)闆]有了索引，找到記錄就得掃描全表，要掃描全表，就得鎖定表。

共享鎖與排他鎖

首先說明：InnoDB引擎默認(rèn)對update，delete，insert加排它鎖，select語句默認(rèn)不加鎖

共享鎖

共享鎖shared locks（S鎖）也稱讀鎖：用于不更改或不更新數(shù)據(jù)的操作（只讀操作），可以查看但無法修改和刪除的一種數(shù)據(jù)鎖，如select語句。

如果事務(wù)T對數(shù)據(jù)A加上共享鎖后，則其他事務(wù)只能對數(shù)據(jù)A再加共享鎖，不能加排他鎖。獲準(zhǔn)共享鎖的事務(wù)只能讀數(shù)據(jù)，不能修改數(shù)據(jù)。共享鎖下其它用戶可以并發(fā)讀取，查詢數(shù)據(jù)。但不能修改，增加，刪除數(shù)據(jù)。資源共享。

加鎖方式

select ... lock in share mode

注意：

• 對于使用共享鎖的事務(wù)，其他事務(wù)只能讀，不可寫
• 如果執(zhí)行了更新操作則會一直等待，直到當(dāng)前事務(wù)commit或者rollback
• 如果當(dāng)前事務(wù)也執(zhí)行了其他事務(wù)處于等待的那條sql語句，當(dāng)前事務(wù)將會執(zhí)行成功，而其他事務(wù)會報(bào)死鎖
• 允許其他鎖共存

排它鎖

排它鎖Exclusive Locks（X鎖）也稱寫鎖、獨(dú)占鎖：用于數(shù)據(jù)修改操作，例如insert、update或delete。確保不會同時(shí)對同一資源進(jìn)行多重更新。

如果事務(wù)T對數(shù)據(jù)A加上排它鎖后，則其他事務(wù)不能在對A加任何類型的鎖。獲準(zhǔn)排他鎖的事務(wù)既能讀數(shù)據(jù)，又能修改數(shù)據(jù)。我們在操作數(shù)據(jù)庫的時(shí)候，可能會由于并發(fā)問題而引起的數(shù)據(jù)的不一致性（數(shù)據(jù)沖突）

加鎖方式

select ... for update

**for update：**InnoDB默認(rèn)是行級別的鎖，當(dāng)有明確指定的主鍵是，使用的是行鎖；否則使用表鎖。使用情況詳細(xì)如下：

明確指定主鍵，并且存在此記錄，行級鎖。例如：

-- id是主鍵
select name,age from table_user where id = '1' for update;

明確指定主鍵，若查無記錄，無鎖。例如：

-- id是主鍵，單不存在id = 1的數(shù)據(jù)
select name,age from table_user where id = '1' for update;

無主鍵，表級鎖。例如：

-- age是普通字段
select name,age from table_user where age = 12 for update;

主鍵不明確，表級鎖。例如：

-- id是主鍵，age不是，但數(shù)據(jù)庫
select name,age from table_user where age = 12 and id = '1' for update;

注意：

• 對于排它鎖的事務(wù)，其它事務(wù)可讀，但不可進(jìn)行更新操作
• for update僅使用與InnoDB，并且必須開啟事務(wù)，在begin和commit之間才生效
• 當(dāng)一個(gè)事務(wù)進(jìn)行for update的時(shí)候，另一個(gè)事務(wù)也有for update時(shí)會一直等待，直到之前的事務(wù)commit或rollback或斷開連接釋放鎖才能拿到鎖進(jìn)行后面的操作（排它鎖不能共存）
• InnoDB引擎默認(rèn)對update，delete，insert加排它鎖，select語句默認(rèn)不加鎖
• 加過排他鎖的數(shù)據(jù)行在其他事務(wù)中是不能修改數(shù)據(jù)的，也不能通過for update和lock in share mode鎖的方式查詢數(shù)據(jù)，但可以直接通過select … from …查詢數(shù)據(jù)，因?yàn)槠胀ú樵儧]有任何鎖機(jī)制

樂觀鎖與悲觀鎖

首先說明：樂觀鎖和悲觀鎖都是針對讀（select）來說的。

樂觀鎖

樂觀鎖不是數(shù)據(jù)庫自帶的，需要我們自己去實(shí)現(xiàn)。

樂觀鎖是指操作數(shù)據(jù)庫時(shí)（更新操作），想法很樂觀，認(rèn)為這次的操作不會導(dǎo)致沖突，在操作數(shù)據(jù)時(shí)，并不進(jìn)行任何其他的特殊處理（也就是不加鎖），而在進(jìn)行更新后，再去判斷是否有沖突了。

悲觀鎖

悲觀鎖就是在操作數(shù)據(jù)時(shí)，認(rèn)為此操作會出現(xiàn)數(shù)據(jù)沖突，所以在進(jìn)行每次操作時(shí)都要通過獲取鎖才能進(jìn)行對相同數(shù)據(jù)的操作，這點(diǎn)跟Java中的synchronized很相似，所以悲觀鎖需要耗費(fèi)較多的時(shí)間。

另外與樂觀鎖相對應(yīng)，悲觀鎖是由數(shù)據(jù)庫自己實(shí)現(xiàn)了的，要用的時(shí)候，我們直接調(diào)用數(shù)據(jù)庫的相關(guān)語句就可以了。

由悲觀鎖涉及到的另外兩個(gè)鎖概念，就是共享鎖與排他鎖。共享鎖和排他鎖是悲觀鎖的不同實(shí)現(xiàn)，它倆都屬于悲觀鎖的范疇。

案例

某商品，用戶購買后庫存應(yīng)-1，而某兩個(gè)或多個(gè)用戶同時(shí)購買，此時(shí)三個(gè)執(zhí)行程序均同時(shí)讀得庫存為“n”，之后進(jìn)行了一些操作，最后將均執(zhí)行update table set 庫存書 = n - 1，那么，很顯然這是錯(cuò)誤的。

解決

1.使用悲觀鎖（也就是排他鎖）

• 程序A在查詢庫存數(shù)時(shí)使用排他鎖（select * from table where id = 10 for update）
• 然后進(jìn)行后續(xù)操作，包括更新庫存數(shù)，最后提交事務(wù)
• 程序B在查詢庫存數(shù)時(shí)，如果A還未釋放排他鎖，程序B將等待。。。
• 程序C同B。。。

2.使用樂觀鎖（靠表設(shè)計(jì)和代碼來實(shí)現(xiàn)）

一般是在該商品表添加version版本字段或者timestamp時(shí)間戳字段

程序A查詢后，執(zhí)行更新變成了：

update table set num = num - 1 and version = 23

這樣，保證了修改的數(shù)據(jù)是和它查詢出來的數(shù)據(jù)是一致的（其他執(zhí)行程序肯定未進(jìn)行修改）。當(dāng)然，如果更新失敗，表示在更新操作之前，有其他執(zhí)行程序已經(jīng)更新了該庫存數(shù)，那么就可以嘗試重試來保證更新成功。為了盡可能避免更新失敗，可以合理調(diào)整重試次數(shù)（阿里巴巴開發(fā)手冊規(guī)定重試次數(shù)不低于三次）。

樂觀鎖和悲觀鎖的區(qū)別

悲觀鎖實(shí)際使用了排他鎖來實(shí)現(xiàn)**（select … for update）**。InnoDB加行鎖的前提是：必須是通過索引條件來檢索數(shù)據(jù)，否則會切換為表鎖。

因此，悲觀鎖在未通過索引條件檢索數(shù)據(jù)時(shí)，會鎖定整張表。導(dǎo)致其他程序不允許**“加鎖的查詢操作”**，影響吞吐。因此，如果在查詢居多的情況下，推薦使用樂觀鎖。

加鎖的查詢操作：加過排他鎖的數(shù)據(jù)行在其他事務(wù)中是不能修改的，也不能通過for update或lock in share mode的加鎖方式查詢，但可以直接通過select … from …查詢數(shù)據(jù)，因?yàn)槠胀ú樵儧]有任何鎖機(jī)制。

樂觀鎖更新有可能會失敗，甚至是更新幾次都失敗，這是有風(fēng)險(xiǎn)的。所以如果寫入居多，對吞吐要求不高，可使用悲觀鎖。

結(jié)尾：讀用樂觀鎖，寫用悲觀鎖。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。