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

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

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

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

$pdo = new PDO('mysql:host=127.0.0.1;port=3306; dbname=test','root','123456');
$pdo->beginTransaction();//開啟事務(wù)
$sql="select `number` from storage where id=1 *for UPDATE* ";//利用for update 開啟行鎖
$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ù)隔離級別的實現(xiàn)原理：鎖

表級鎖&行級鎖

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

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

注：

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

排它鎖和共享鎖

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

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

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

排它鎖（Exclusive），又稱為X鎖，寫鎖

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

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

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

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

InnoDB行級鎖

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

行級鎖

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

間隙鎖（gap lock）（串行化隔離級別怎么解決幻讀問題？）

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

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

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

幻讀場景：

第一類條件：范圍查詢

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

第二類條件：等值查詢引入上圖場景所用表進行解讀

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

間隙鎖和next-key lock：

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

意向共享鎖和意向排他鎖

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

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

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

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

InnoDB表級鎖

在絕大部分情況下都應(yīng)該使用行鎖，因為事務(wù)和行鎖往往是選擇InnoDB的理由，但個別情況下也使用 表級鎖；

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

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

如：

LOCK TABLE user READ；讀鎖鎖表
LOCK TABLE user WRITE; 寫鎖鎖表

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

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

死鎖

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

但在 InnoDB 中，除單個 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

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

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

鎖的優(yōu)化建議

1.盡量使用較低的隔離級別

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

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

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

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

6.不要申請超過實際需要的鎖級別

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

總結(jié)

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

最新評論