Mysql的行級鎖定是什麼意思？

我們首先需要知道的一個大前提是：mysql的鎖是由特定的儲存引擎實現的。所以像Mysql的預設引擎MyISAM和第三方外掛引擎InnoDB的鎖定實現機制是有差別的。

Mysql有三個等級的鎖定：表級鎖定、頁級鎖定、行級鎖定

一、定義

#每次鎖定的是一行資料的鎖定機制就是行級鎖定(row-level)。行級鎖定不是MySQL自己實作的鎖定方式，而是由其他儲存引擎本身所實現的

二、優缺點

1. 優點

• 由於鎖定粒度小，爭用率低，並發高。

2. 缺點

• 實作複雜，開銷大。

• 加上鎖慢、容易出現死鎖

#三、支援儲存引擎

• 使用行級鎖定的主要有InnoDB儲存引擎，以及MySQL的分散式儲存引擎NDBCluster

四、行級鎖定類型

InnoDB的行級鎖定同樣分為兩種：共享鎖定和排他鎖，而在鎖定機制的實現過程中為了讓行級鎖定和表級鎖定共存，InnoDB也同樣使用了意向鎖（表級鎖定）的概念，也就有了意向共享鎖和意向排他鎖這兩種。

意向鎖的作用就是當一個事務在需要取得資源鎖定的時候，如果遇到自己需要的資源已經被排他鎖佔用的時候，該事務可以需要鎖定行的表上面添加一個合適的意向鎖。如果自己需要一個共享鎖，那麼就在表上面加上一個意向共享鎖。而如果自己需要的是某行（或某些行）上面加上一個排他鎖的話，則先在表上面加上一個意向排他鎖。

意向共享鎖定可以同時並存多個，但是意向排他鎖同時只能有一個存在。所以，可以說InnoDB的鎖定模式其實可以分為四種：共享鎖（S），排他鎖（X），意向共享鎖（IS）和意向排他鎖（IX）

#鎖模式的相容性：

五、行級鎖定實作方式

InnoDB行鎖是透過在索引上的索引項目加鎖來實現的。所以，只有透過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖。其他注意事項：

• 在不透過索引條件查詢的時候，InnoDB使用的是表鎖，而不是行鎖。

• 由於MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以即使是存取不同行的記錄，如果使用了相同的索引鍵，也是會出現鎖衝突的。

• 當表有多個索引的時候，不同的交易可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對資料加鎖。

• 即便在條件中使用了索引字段，但具體是否使用索引來檢索資料是由MySQL透過判斷不同執行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，例如對一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖定衝突時，別忘了檢查SQL的執行計劃，以確認是否真正使用了索引。

隱含加鎖：

• InnoDB自動加上意向鎖定。

• 對於UPDATE、DELETE和INSERT語句，InnoDB會自動將涉及資料集加上排他鎖定（X)；

• 對於普通SELECT語句，InnoDB不會加任何鎖定；

顯示加鎖：

• 共享鎖定（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

• 排他鎖（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE

用SELECT … IN SHARE MODE取得共享鎖，主要用在需要資料依賴關係時來確認某行記錄是否存在，並確保沒有人對這個記錄進行UPDATE或DELETE操作。

但是如果目前交易也需要對該記錄進行更新操作，則很有可能造成死鎖，對於鎖定行記錄後需要進行更新操作的應用，應該使用SELECT… FOR UPDATE方式獲得排他鎖。

InnoDB如何加表鎖定：

在用LOCK TABLES對InnoDB表加鎖時要注意，要將AUTOCOMMIT設為0，否則MySQL不會給表加鎖；事務結束前，不要用UNLOCK TABLES釋放表鎖，因為UNLOCK TABLES會隱含地提交事務；COMMIT或ROLLBACK並不能釋放用LOCK TABLES加的表級鎖，必須用UNLOCK TABLES釋放表鎖。

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

既然都用表鎖了，怎麼不選擇MyISAM引擎呢！

六、间隙锁（Next-Key锁）

1. 间隙锁定义：

Innodb的锁定规则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。 Innodb的这种锁定实现方式被称为“ NEXT-KEY locking” （间隙锁），因为Query执行过程中通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值并不存在。

例：假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

2. 间隙锁的缺点：

• 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

• 当Query无法利用索引的时候， Innodb会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；

• 当Quuery使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所指向的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；

• 当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定

3 . 间隙锁的作用：

• 防止幻读，以满足相关隔离级别的要求。

• 为了数据恢复和复制的需要。

4. 注意

• 在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

• InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。

七、查看行级锁争用情况

执行SQL:mysql> show status like 'InnoDB_row_lock%';

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+| Variable_name                 | Value |
+-------------------------------+-------+| InnoDB_row_lock_current_waits | 0     |
| InnoDB_row_lock_time          | 0     |
| InnoDB_row_lock_time_avg      | 0     |
| InnoDB_row_lock_time_max      | 0     |
| InnoDB_row_lock_waits         | 0     |+-------------------------------+-------+

如果发现锁争用比较严重，还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。如：

设置监视器：mysql> create table InnoDB_monitor(a INT) engine=InnoDB;

查看：mysql> show engine InnoDB status;

停止查看：mysql> drop table InnoDB_monitor;

具体参考：InnoDB Monitor

八、死锁

什么是死锁：你等我释放锁，我等你释放锁就会形成死锁。

如何发现死锁： 在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在

解决办法：

• 回滚较小的那个事务

• 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。

判断事务大小：事务各自插入、更新或者删除的数据量

注意：

• 当产生死锁的场景中涉及到不止InnoDB存储引擎的时候，InnoDB是没办法检测到该死锁的，这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。

九、优化行级锁定

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

（1）要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：

• 盡可能讓所有的資料檢索都透過索引來完成，從而避免InnoDB因為無法透過索引鍵加鎖而升級為表級鎖定；

• 合理設計索引，讓InnoDB在索引鍵上面加鎖的時候盡可能準確，盡可能的縮小鎖定範圍，避免造成不必要的鎖定而影響其他Query的執行；

• 盡可能減少基於範圍的資料檢索過濾條件，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定的記錄；

• 盡量控制交易的大小，減少鎖定的資源量和鎖定時間長度；

• 在業務環境允許的情況下，盡量使用較低層級的交易隔離，以減少MySQL因為實作事務隔離等級所帶來的附加成本。

（2）由於InnoDB的行級鎖定和事務性，所以肯定會產生死鎖，以下是一些比較常用的減少死鎖產生機率的小建議：

• 在類似業務模組中，盡可能按照相同的訪問順序來訪問，防止產生死鎖；

• 在同一個事務中，盡可能做到一次鎖定所需的所有資源，減少死鎖產生機率；

• 對於非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，透過表級鎖定來減少死鎖產生的機率。

相關推薦：《mysql教學》

以上是Mysql的行級鎖定是什麼意思？的詳細內容。更多資訊請關注PHP中文網其他相關文章！