資料庫基礎之鎖（3. 避免幻讀對事務并發性的影響）

事務并發性，粗略的了解就是機關時間内能夠執行的事務數量，常見的機關是 TPS（ transactions per second）。

那在資料量和業務操作量一定的情況下，常見的提高事務并發性主要考慮的有哪幾點呢？

1. 提高伺服器的處理能力，讓事務的處理時間變短。

這樣不僅加快了這個事務的執行時間，也降低了其他等待該事務執行的事務執行時間。

2. 盡量将事務涉及到的sql操作語句控制在合理範圍，換句話說就是不要讓一個事務包含的操作太多或者太少。

在業務繁忙情況下，如果單個事務操作的表或者行資料太多，其他的事務可能都在等待該事務commit或者rollback，這樣會導緻整體上的TPS降低。但是，如果每個sql語句都是一個事務也是不太現實的。一來，有些業務本身需要多個sql語句來構成一個事務（比如彙款這種多個表的操作）；二來，每個 sql都需要commit，如果在mysql裡 innodb_flush_log_at_trx_commit=1的情況下，會導緻redo log的重新整理過于頻繁，也不利于整體事務數量的提高（IO限制也是需要考慮的重要因素）。

3. 在操作的時候，盡量控制鎖的粒度，能用小的鎖粒度就盡量用鎖的粒度，用完鎖資源後要記得立即釋放，避免後面的事務等待。

但是有些情況下，由于業務需要，或者為了保證資料的一緻性的時候，必須增加鎖的粒度，這個時候就是下面所說的幾種情況。

select for update了解

select col from t where where clause for update 的目的是在執行這個select查詢語句的時候，會将對應的索引通路條目進行上排他鎖（X 鎖），也就是說這個語句對應的鎖就相當于update帶來的效果。

那這種文法為什麼會存在呢？肯定是有需要這種方式的存在啦！！請看下面的案例描述：

案例1

【前提條件】mysql隔離級别：RR

事務1：

# 建表
CREATE TABLE `lockt` (
  `id` int(11) NOT NULL,
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col1_ind` (`col1`),
  KEY `col2_ind` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


# 插入資料 
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from lockt;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    3 |
|  5 |    5 |    5 |
|  6 |    6 |    9 |
|  7 |    7 |   14 |
|  8 |    8 |   20 |
+----+------+------+
6 rows in set (0.00 sec)

然後另外一個事務2進行了下面的操作：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from lockt;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    3 |
|  5 |    5 |    5 |
|  6 |    6 |    9 |
|  7 |    7 |   14 |
|  8 |    8 |   20 |
+----+------+------+
6 rows in set (0.00 sec)

mysql> update lockt set  col2= 144  where col2=14;  
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

【結果】可以看到事務2 将[col2=14]的列改為了[col2=144]。

可是事務1繼續執行的時候根本沒有覺察到lockt發生了變化，請看事務1繼續後面的操作：

mysql> select * from lockt;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    3 |
|  5 |    5 |    5 |
|  6 |    6 |    9 |
|  7 |    7 |   14 |
|  8 |    8 |   20 |
+----+------+------+
6 rows in set (0.01 sec)

mysql> update lockt set  col2=col2*2  where col2=14;    
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from lockt;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    3 |
|  5 |    5 |    5 |
|  6 |    6 |    9 |
|  7 |    7 |  144 |
|  8 |    8 |   20 |
+----+------+------+
6 rows in set (0.00 sec)

【結果】事務1明明檢視到的存在[col2=12]的行資料，可是update後，竟然不僅沒有改為他想要的[col2=28]的值，反而變成了[col2=144] ！！！！

這在有些業務情況下是不允許的，因為有些業務希望我通過 select * from lockt; 查詢到的資料是此時資料庫裡面真正存儲的最新資料，并且不允許其他的事務來修改隻允許我來修改。（這個要求很霸氣，但是我喜歡。。）

這種情況就是很牛逼的情況了。具體的細節請參考下面的案例2：

案例2

mysql條件和案例1一樣。

事務1操作：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from lockt where col2=20 for update;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  8 |    8 |   20 |
+----+------+------+
1 row in set (0.00 sec)

事務2 操作：

mysql> select * from lockt;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    3 |
|  5 |    5 |    5 |
|  6 |    6 |    9 |
|  7 |    7 |  144 |
|  8 |    8 |   20 |
+----+------+------+
6 rows in set (0.00 sec)

mysql> update lockt set  col2=222  where col2=20;

【注意】事務2在執行 update lockt set col2=222 where col2=20; 的時候，會發現sql語句被block住了，為什麼會發現這種情況呢？

因為事務1的 select * from lockt where col2=20 for update; 語句會将[col2=20]這個索引的入口給鎖住了（其實有些時候是範圍的索引條目也被鎖住了，暫時不讨論），那麼事務2雖然看到了所有的資料，但是想去修改[col2=20]的行資料的時候，事務1隻能說 “不可能也不允許”。

後面隻有事務1 commit或者rollback以後，事務2 的才能夠修改[col2=20]的這個行資料。

總結

這就是select for update的使用場景，為了避免自己看到的資料并不是資料庫存儲的最新資料并且看到的資料隻能由自己修改，需要用for update來限制。

select lock in share mode 了解

如果看了前面的select *** for update ，就可以很好的了解 select lock in share mode ，in share mode子句的作用就是将查找到的資料加上一個share鎖，這個就是表示其他的事務隻能對這些資料進行簡單的select操作，并不能夠進行DML操作。

那它和for update在引用場景上究竟有什麼實質上的差別呢？

lock in share mode沒有for update那麼霸道，是以它有時候也會遇到問題，請看案例3。

案例3（in share mode vs. for update）

mysql環境和案例1類似。

mysql> select * from lockt;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    3 |
|  5 |    5 |    5 |
|  6 |    6 |    9 |
|  7 |    7 |  144 |
|  8 |    8 |   20 |
+----+------+------+
6 rows in set (0.00 sec)

mysql> select * from lockt where col2=20 lock in share mode;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  8 |    8 |   20 |
+----+------+------+
1 row in set (0.00 sec)

事務2 接着開始操作：

mysql> select * from lockt;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    3 |
|  5 |    5 |    5 |
|  6 |    6 |    9 |
|  7 |    7 |  144 |
|  8 |    8 |   20 |
+----+------+------+
6 rows in set (0.00 sec)

mysql> select * from lockt where col2=20 lock in share mode;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  8 |    8 |   20 |
+----+------+------+
1 row in set (0.01 sec)

後面的比較蛋疼的一幕出現了，當事務1想更新[col2=20]的時候，他發現block住了。

mysql> update lockt set col2=22 where col2=20;

【解釋】因為事務1和事務2都對該行上了一個share鎖，事務1以為就隻有自己一個人上了S鎖，是以當事務1想修改的時候發現沒法修改，這種情況下，事務1需要使用for update子句來進行限制了，而不是使用for share來使用。

意向鎖作用

innodb的意向鎖有什麼作用？ mysql官網上對于意向鎖的解釋中有這麼一句話: “The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.” 意思是說加意向鎖的目的是為了表明某個事務正在鎖定一行或者将要鎖定一行。 那麼，意向鎖的作用就是“表明”加鎖的意圖，可是為什麼要表明這個意圖呢？ 如果僅僅鎖定一行僅僅需要加一個鎖，那麼就直接加鎖就好了，這裡要表明加鎖意圖的原因是因為要鎖定一行不僅僅是要加一個鎖，而是要做一系列操作嗎？ 作者：發條地精 我最近也在看這個，我說一下我的了解 ①在mysql中有表鎖，LOCK TABLE my_tabl_name READ; 用讀鎖鎖表，會阻塞其他事務修改表資料。LOCK TABLE my_table_name WRITE ; 用寫鎖鎖表，會阻塞其他事務讀和寫。 ②Innodb引擎又支援行鎖，行鎖分為共享鎖，一個事務對一行的共享隻讀鎖。排它鎖，一個事務對一行的排他讀寫鎖。 ③這兩中類型的鎖共存的問題考慮這個例子： 事務A鎖住了表中的一行，讓這一行隻能讀，不能寫。之後，事務B申請整個表的寫鎖。如果事務B申請成功，那麼理論上它就能修改表中的任意一行，這與A持有的行鎖是沖突的。 資料庫需要避免這種沖突，就是說要讓B的申請被阻塞，直到A釋放了行鎖。 資料庫要怎麼判斷這個沖突呢？ step1：判斷表是否已被其他事務用表鎖鎖表 step2：判斷表中的每一行是否已被行鎖鎖住。 注意step2，這樣的判斷方法效率實在不高，因為需要周遊整個表。 于是就有了意向鎖。在意向鎖存在的情況下，事務A必須先申請表的意向共享鎖，成功後再申請一行的行鎖。在意向鎖存在的情況下， 上面的判斷可以改成： step1：不變 step2：發現表上有意向共享鎖，說明表中有些行被共享行鎖鎖住了，是以，事務B申請表的寫鎖會被阻塞。 注意：申請意向鎖的動作是資料庫完成的，就是說，事務A申請一行的行鎖的時候，資料庫會自動先開始申請表的意向鎖，不需要我們程式員使用代碼來申請。 總結：為了實作多粒度鎖機制（白話：為了表鎖和行鎖都能用）

可能用到的情景和對性能的影響

使用情景

1. select *** for update的使用場景

為了讓自己查到的資料確定是最新資料，并且查到後的資料隻允許自己來修改的時候，需要用到for update子句。

2. select *** lock in share mode的使用場景

為了確定自己查到的資料沒有被其他的事務正在修改，也就是說確定查到的資料是最新的資料，并且不允許其他人來修改資料。但是自己不一定能夠修改資料（比如a,b都拿了鎖，a更改了資料，因為b還拿着鎖，a送出不了，直到逾時），因為有可能其他的事務也對這些資料使用了 in share mode的方式上了S鎖。

性能影響

select for update語句，相當于一個update語句。在業務繁忙的情況下，如果事務沒有及時的commit或者rollback可能會造成其他事務長時間的等待，進而影響資料庫的并發使用效率。

select lock in share mode語句是一個給查找的資料上一個共享鎖（S 鎖）的功能，它允許其他的事務也對該資料上S鎖，但是不能夠允許對該資料進行修改。如果不及時的commit或者rollback也可能會造成大量的事務等待。

for update和lock in share mode的差別：前一個上的是排他鎖（X 鎖），一旦一個事務擷取了這個鎖，其他的事務是沒法在這些資料上執行for update ；後一個是共享鎖，多個事務可以同時的對相同資料執行lock in share mode。

Min是清明的茗

資料庫基礎之鎖（3. 避免幻讀對事務并發性的影響）

案例1

案例2

總結

案例3（in share mode vs. for update）

意向鎖作用

使用情景

性能影響

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

詳解STM32單片機的堆棧

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述