mysql行鎖解決高并發(fā)

for update 必須在事務(wù)中執(zhí)行

（避免高并發(fā)時(shí)庫(kù)存為負(fù)數(shù)）

where條件有主鍵是行鎖 否則是表鎖

$pdo = new PDO('mysql:host=127.0.0.1;port=3306; dbname=test','root','123456');
$pdo->beginTransaction();//開(kāi)啟事務(wù)
$sql="select `number` from storage where id=1 *for UPDATE* ";//利用for update 開(kāi)啟行鎖
$res = $pdo->query($sql)->fetch();
$number = $res['number'];
if($number>0)
{
  $sql ="insert into `order` VALUES (null,$number)";
  $order_id = $pdo->query($sql);
  if($order_id)
  {
    $sql="update storage set `number`=`number`-1 WHERE id=1";
    if($pdo->query($sql))
    {
      $pdo->commit();//提交事務(wù)
    }
    else
    {
      $pdo->rollBack();//回滾
    }
  }
  else
  {
    $pdo->rollBack();//回滾
  }
}

mysql行鎖、表鎖&間隙鎖

事務(wù)隔離級(jí)別的實(shí)現(xiàn)原理：鎖

表級(jí)鎖&行級(jí)鎖

表級(jí)鎖：對(duì)整張表加鎖。開(kāi)銷小，加鎖快，不會(huì)出現(xiàn)死鎖；鎖粒度大，發(fā)生鎖沖突的概率高，并發(fā)度低。

行級(jí)鎖：對(duì)某行記錄加鎖。開(kāi)銷大，加鎖慢，會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度高。

注：

• 對(duì)于InnoDB引擎，絕大部分情況應(yīng)該使用行鎖
• 使用表鎖中，表比較大，事務(wù)需要更新全部或大部分?jǐn)?shù)據(jù)
• 事務(wù)涉及到多個(gè)表，比較復(fù)雜，可能引起死鎖，造成大量的事務(wù)回滾

排它鎖和共享鎖

共享鎖（Shared），又稱為S鎖，讀鎖

共享鎖鎖定的資源可以被其他用戶讀取，但不能修改

在進(jìn)行SELECT的時(shí)候，會(huì)將對(duì)象進(jìn)行共享鎖鎖定，當(dāng)數(shù)據(jù)讀取完畢之后，就會(huì)釋放共享鎖，這樣就可以保證數(shù)據(jù)在讀取時(shí)不被修改。

排它鎖（Exclusive），又稱為X鎖，寫(xiě)鎖

排它鎖鎖定的數(shù)據(jù)只允許進(jìn)行鎖定操作的事務(wù)使用，其他事務(wù)無(wú)法對(duì)已鎖定的數(shù)據(jù)進(jìn)行查詢或修改

X鎖和S鎖之間有以下的關(guān)系：SS(讀-讀)可以兼容的，SX(讀-寫(xiě))、XX(寫(xiě)-寫(xiě))之間是互斥的

• 一個(gè)事務(wù)對(duì)數(shù)據(jù)對(duì)象O加了S鎖，可以對(duì)O進(jìn)行讀取操作，但不能進(jìn)行更新操作。加鎖期間其他事務(wù)能對(duì)O加S鎖但不能加X(jué)鎖
• 一個(gè)事務(wù)對(duì)數(shù)據(jù)對(duì)象O加了X鎖，就可以對(duì)O進(jìn)行讀取和更新。加速期間其他事務(wù)不能對(duì)O加任何鎖。

//對(duì)某一行加上共享鎖
select uid from student where uid=1 lock in share mode; 
//對(duì)某個(gè)數(shù)據(jù)行上添加排它鎖
select uid from student where uid=1 for update;

InnoDB行級(jí)鎖

InnoDB存儲(chǔ)引擎支持事務(wù)處理，表支持行級(jí)鎖定，并發(fā)能力更好

行級(jí)鎖

• InnoDB的行鎖是通過(guò)給在索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的，是給索引在加鎖，并不是給單純表的行記錄在加鎖；索引若過(guò)濾條件沒(méi)有索引的話，使用的就是表鎖，而不是行鎖?。?！
• 由于InnoDB的行鎖實(shí)現(xiàn)是針對(duì)索引字段添加的鎖，不是針對(duì)行記錄加的鎖，因此雖然訪問(wèn)的是InnoDB引擎下表的不同行，但若使用相同的索引字段作為過(guò)濾條件，依然會(huì)發(fā)生鎖沖突，只能串行進(jìn)行，不能并發(fā)進(jìn)行
• 即使SQL中使用了索引，但是經(jīng)過(guò)MySQL的優(yōu)化器后，若認(rèn)為全表掃描比使用索引效率更高，此時(shí)會(huì)放棄使用索引，因此也不會(huì)使用行鎖，而是使用表鎖，比如對(duì)一些很小的表，MySQL就不會(huì)去使用索引。

間隙鎖（gap lock）（串行化隔離級(jí)別怎么解決幻讀問(wèn)題？）

間隙鎖是專門用于解決幻讀這種問(wèn)題的鎖，它鎖的是行與行之間的間隙，能夠阻塞新插入的操作

間隙鎖的引入也帶來(lái)了一些新的問(wèn)題，比如：降低并發(fā)度，可能導(dǎo)致死鎖。

注意：讀讀不互斥，讀寫(xiě)/寫(xiě)讀/寫(xiě)寫(xiě)實(shí)互斥的，但是間隙鎖之間是不沖突的，間隙鎖會(huì)阻塞插入操作。另外，間隙鎖在可重復(fù)讀級(jí)別下才是有效的。

幻讀場(chǎng)景：

第一類條件：范圍查詢

注：當(dāng)使用索引時(shí)，經(jīng)過(guò)MySQL優(yōu)化器，認(rèn)為全盤掃描比使用索引效率高，則變成表級(jí)鎖，當(dāng)前只能插入表頭之前或表尾之后。

第二類條件：等值查詢引入上圖場(chǎng)景所用表進(jìn)行解讀

注：若age是主鍵索引和唯一索引(值是不允許重復(fù)的)，那就只有行鎖

間隙鎖和next-key lock：

行鎖和間隙鎖合稱為next-key lock，這個(gè)鎖是左開(kāi)右閉的區(qū)。

意向共享鎖和意向排他鎖

1、意向鎖是由InnoDB存儲(chǔ)引擎獲取行鎖之前自己獲取的

2、意向鎖之間都是兼容的，不會(huì)產(chǎn)生沖突

3、意向鎖存在的意義是為了更高效的獲取表鎖（表格中的X和S指的是表鎖，不是行鎖?。。。?/p>

4、意向鎖是表級(jí)鎖，協(xié)調(diào)表鎖和行鎖的共存關(guān)系。主要目的是顯示事務(wù)正在鎖定某行或者試圖鎖定某行。

InnoDB表級(jí)鎖

在絕大部分情況下都應(yīng)該使用行鎖，因?yàn)槭聞?wù)和行鎖往往是選擇InnoDB的理由，但個(gè)別情況下也使用 表級(jí)鎖；

1）事務(wù)需要更新大部分或全部數(shù)據(jù)，表又比較大，如果使用默認(rèn)的行鎖，不僅這個(gè)事務(wù)執(zhí)行效率低，而且可能造成其他事務(wù)長(zhǎng)時(shí)間等待和 鎖沖突；

2）事務(wù)涉及多個(gè)表，比較復(fù)雜，很可能引起死鎖，造成大量事務(wù)回滾。

如：

LOCK TABLE user READ；讀鎖鎖表
LOCK TABLE user WRITE; 寫(xiě)鎖鎖表

事務(wù)執(zhí)行…

COMMIT/ROLLBACK; 事務(wù)提交或者回滾
UNLOCK TABLES; 本身自帶提交事務(wù)，釋放線程占用的所有表鎖

死鎖

MyISAM 表鎖是 deadlock free 的， 這是因?yàn)?MyISAM 總是一次獲得所需的全部鎖，要么全部滿足，要么等待，因此不會(huì)出現(xiàn)死鎖。

但在 InnoDB 中，除單個(gè) SQL 組成的事務(wù)外，鎖是逐步獲得的，即鎖的粒度比較小，這就決定了在 InnoDB 中發(fā)生死鎖是可能的。

mysql> select * from test_dead_lock where id=1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死鎖問(wèn)題一般都是我們自己的應(yīng)用造成的，和多線程編程的死鎖情況相似，大部分都是由于我們多個(gè)線程在獲取多個(gè)鎖資源的時(shí)候，獲取的順序不同而導(dǎo)致的死鎖問(wèn)題。

因此我們應(yīng)用在對(duì)數(shù)據(jù)庫(kù)的多個(gè)表做更新的時(shí)候，不同的代碼段，應(yīng)對(duì)這些表按相同的順序進(jìn)行更新操作，以防止鎖沖突導(dǎo)致死鎖問(wèn)題。

鎖的優(yōu)化建議

1.盡量使用較低的隔離級(jí)別

2.設(shè)計(jì)合理的索引并盡量使用索引訪問(wèn)數(shù)據(jù)，使加鎖更加準(zhǔn)確，減少鎖沖突的機(jī)會(huì)提高并發(fā)能力

3.選擇合理的事務(wù)大小，小事務(wù)發(fā)生鎖沖突的概率小

4.不同的程序訪問(wèn)一組表時(shí)，應(yīng)盡量約定以相同的順序訪問(wèn)各表，對(duì)一個(gè)表而言，盡可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機(jī)會(huì)

5.盡量用相等條件訪問(wèn)數(shù)據(jù)，這樣可以避免間隙鎖對(duì)并發(fā)插入的影響

6.不要申請(qǐng)超過(guò)實(shí)際需要的鎖級(jí)別

7.除非必須，查詢時(shí)不要顯示加鎖

總結(jié)

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

最新評(píng)論