資料庫高並發請求，如何保證資料完整性？詳解MySQL/InnoDB的加鎖

本文是對MySQL/InnoDB中,樂觀鎖、悲觀鎖、共享鎖、排它鎖、行鎖、表鎖、死鎖概念的理解，這些在面試中也經常遇到，如數據庫高並發請求，如何保證資料完整性？今天我查閱資料進行了MySQL/InnoDB中加鎖知識點的匯總，這樣也會大家就不會感覺很繁瑣和雜亂了，覺得有用的話就繼續分享吧。 apache php mysql

註：MySQL是一個支援外掛程式儲存引擎的資料庫系統。本文以下的所有介紹，都是基於InnoDB儲存引擎，其他引擎的表現，會有較大的差異。

儲存引擎檢視

MySQL為開發者提供了查詢儲存引擎的功能，我這裡使用的是MySQL5.6.4，可以使用：

SHOW ENGINES

begin！

樂觀鎖定

以資料版本（Version）記錄機制實現，這是樂觀鎖定最常用的實作方式。何謂數據版本？即為資料增加一個版本標識，一般是透過為資料庫表增加一個數字類型的 “version” 字段來實現。當讀取資料時，將version欄位的值一同讀出，資料每更新一次，對此version值加1。當我們提交更新的時候，判斷資料庫表對應記錄的當前版本資訊與第一次取出的version值進行比對，如果資料庫表目前版本號與第一次取出的version值相等，則予以更新，否則認為是過期數據。

範例

1、資料庫表設計

三個字段，分別是id,value、version

select id,value,version from TABLE where id=#{id}

2、每次更新表中的value欄位時，為了防止發生衝突，需要這樣操作

update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version};

悲觀鎖定

與樂觀鎖相對應的就是悲觀鎖了。悲觀鎖定就是在操作資料時，認為此操作會出現資料衝突，所以在進行每次操作時都要透過取得鎖定才能進行對相同資料的操作，這點跟java中的synchronized很相似，所以悲觀鎖定需要耗費較多的時間。另外與樂觀鎖相對應的，悲觀鎖是由資料庫自己實現了的，要用的時候，我們直接呼叫資料庫的相關語句就可以了。

說到這裡，由悲觀鎖涉及到的另外兩個鎖概念就出來了，它們就是共享鎖與排它鎖。 共享鎖和排它鎖是悲觀鎖的不同的實現，它兩個都屬於悲觀鎖的範疇。

使用，排它鎖舉例

要使用悲觀鎖，我們必須關閉mysql資料庫的自動提交屬性，因為MySQL預設使用autocommit模式，也就是說，當你執行一個更新操作後，MySQL會立刻將結果提交。

我們可以使用指令設定MySQL為非autocommit模式：

set autocommit=0;

# 设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

# 1. 开始事务

begin;/begin work;/start transaction; (三者选一就可以)

# 2. 查询表信息

select status from TABLE where id=1 for update;

# 3. 插入一条数据

insert into TABLE (id,value) values (2,2);

# 4. 修改数据为

update TABLE set value=2 where id=1;

# 5. 提交事务

commit;/commit work;

共享鎖定

共享鎖定又稱讀鎖定read lock，是讀取操作創建的鎖。其他使用者可以並發讀取數據，但任何事務都不能對數據進行修改（獲取數據上的排他鎖），直到已釋放所有共享鎖。

如果交易T對資料A加上共享鎖定後，則其他交易只能對A再加共享鎖，不能加排他鎖。取得共享鎖定的事務只能讀數據，不能修改數據

開啟第一個查詢視窗

begin;/begin work;/start transaction;  (三者选一就可以)

SELECT * from TABLE where id = 1  lock in share mode;

然後在另一個查詢視窗中，對id為1的數據進行更新

update  TABLE set name="www.souyunku.com" where id =1;

此時，操作介面進入了卡頓狀態，過了超時間，提示錯誤訊息

如果在逾時前，執行commit，此更新語句就會成功。

[SQL]update  test_one set name="www.souyunku.com" where id =1;
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

加上共享鎖定後，也提示錯誤訊息

update  test_one set name="www.souyunku.com" where id =1 lock in share mode;

[SQL]update  test_one set name="www.souyunku.com" where id =1 lock in share mode;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1

在查詢語句後面增加 LOCK IN SHARE MODE，Mysql會對查詢結果中的每行都加共享鎖，當沒有其他執行緒對查詢結果集中的任何一行使用排他鎖時，可以成功申請共享鎖，否則會被阻塞。其他執行緒也可以讀取使用了共享鎖的表，而這些執行緒讀取的是同一個版本的資料。

加上共享鎖定後，對於update,insert,delete語句會自動加排它鎖定。

排它鎖

排他鎖 exclusive lock（也叫writer lock）又稱呼寫鎖定。

排它鎖是悲觀鎖的一種實現，在上面悲觀鎖也介紹過。

若交易 1 對資料物件A加上X鎖，交易 1 可以讀A也可以修改A，其他事務不能再對A加任何鎖，直到事物 1 釋放A上的鎖。這保證了其他事務在事物 1 釋放A上的鎖之前不能再讀取和修改A。排它鎖會阻塞所有的排它鎖和共享鎖

讀取為什麼要加讀鎖呢：防止資料在被讀取的時候被別的線程加上寫鎖，

使用方式：在需要執行的語句後面加上for update就可以了

#行鎖定

#行鎖又分共享鎖定和排他鎖,由字面意思理解，就是給某一行加上鎖，也就是一筆記錄加上鎖。

注意：行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

共享锁：

名词解释：共享锁又叫做读锁，所有的事务只能对其进行读操作不能写操作，加上共享锁后在事务结束之前其他事务只能再加共享锁，除此之外其他任何类型的锁都不能再加了。

SELECT * from TABLE where id = "1"  lock in share mode;  结果集的数据都会加共享锁

排他锁：

名词解释：若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。

select status from TABLE where id=1 for update;

可以参考之前演示的共享锁,排它锁语句

由于对于表中,id字段为主键，就也相当于索引。执行加锁时，会将id这个索引为1的记录加上锁，那么这个锁就是行锁。

表锁

如何加表锁

innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.

Innodb中的行锁与表锁

前面提到过，在Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？
只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

死锁

死锁（Deadlock） 
所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

解除正在死锁的状态有两种方法：

第一种：

1.查询是否锁表

show OPEN TABLES where In_use > 0;

2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

show processlist

3.杀死进程id（就是上面命令的id列）

kill id

第二种：

1：查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

杀死进程

kill 线程ID

如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。
产生死锁的四个必要条件：

（1） 互斥条件：一个资源每次只能被一个进程使用。
（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
（3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
（4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚而由应用程序重新提交。

下列方法有助于最大限度地降低死锁：

（1）按同一顺序访问对象。
（2）避免事务中的用户交互。
（3）保持事务简短并在一个批处理中。
（4）使用低隔离级别。
（5）使用绑定连接。

end！

相关文章：

数据库并发事务控制 二：mysql数据库锁机制

Mysql数据库锁定机制详细介绍

相关视频：

数据库设计那些事

以上是資料庫高並發請求，如何保證資料完整性？詳解MySQL/InnoDB的加鎖的詳細內容。更多資訊請關注PHP中文網其他相關文章！