MySQL 表鎖和行鎖機制
行鎖變表鎖,是福還是坑?如果你不清楚MySQL加鎖的原理,你會被它整的很慘!不知坑在何方?沒事,我來給你們标記幾個坑。遇到了可别亂踩。通過本章内容,帶你學習MySQL的行鎖,表鎖,兩種鎖的優缺點,行鎖變表鎖的原因,以及開發中需要注意的事項。還在等啥?經驗等你來拿!
MySQL的存儲引擎是從MyISAM到InnoDB,鎖從表鎖到行鎖。後者的出現從某種程度上是彌補前者的不足。比如:MyISAM不支援事務,InnoDB支援事務。表鎖雖然開銷小,鎖表快,但高并發下性能低。行鎖雖然開銷大,鎖表慢,但高并發下相比之下性能更高。事務和行鎖都是在確定資料準确的基礎上提高并發的處理能力。本章重點介紹InnoDB的行鎖。
案例分析
目前,MySQL常用的存儲引擎是InnoDB,相對于MyISAM而言。InnoDB更适合高并發場景,同時也支援事務處理。我們通過下面這個案例(坑),來了解行鎖和表鎖。
業務:因為訂單重複導入,需要用腳本将訂單狀态為"待客服确認"且平台是"xxx"的資料批量修改為"已關閉"。
說明:避免直接修改訂單表造成資料異常。這裡用innodb_lock 表示範InnoDB的行鎖。表中有三個字段:id,k(key值),v(value值)。表在github上:https://github.com/ITDragonBlog/daydayup/tree/master/MySQL/
步驟:
第一步:連接配接資料庫,這裡為了友善區分命名為Transaction-A,設定autocommit為零,表示需手動送出事務。
第二步:Transaction-A,執行update修改id為1的指令。
第三步:新增一個連接配接,命名為Transaction-B,能正常修改id為2的資料。再執行修改id為1的資料指令時,卻發現該指令一直處理阻塞等待中。
第四步:Transaction-A,執行commit指令。Transaction-B,修改id為1的指令自動執行,等待37.51秒。
總結:多個事務操作同一行資料時,後來的事務處于阻塞等待狀态。這樣可以避免了髒讀等資料一緻性的問題。後來的事務可以操作其他行資料,解決了表鎖高并發性能低的問題。
# Transaction-A
mysql> set autocommit = 0;
mysql> update innodb_lock set v='1001' where id=1;
mysql> commit;
# Transaction-B
mysql> update innodb_lock set v='2001' where id=2;
Query OK, 1 row affected (0.37 sec)
mysql> update innodb_lock set v='1002' where id=1;
Query OK, 1 row affected (37.51 sec)
有了上面的模拟操作,結果和理論又驚奇的一緻,似乎可以放心大膽的實戰。。。。。。但現實真的很殘酷。
現實:當執行批量修改資料腳本的時候,行鎖更新為表鎖。其他對訂單的操作都處于等待中,,,
原因:InnoDB隻有在通過索引條件檢索資料時使用行級鎖,否則使用表鎖!而模拟操作正是通過id去作為檢索條件,而id又是MySQL自動建立的唯一索引,是以才忽略了行鎖變表鎖的情況。
步驟:
第一步:還原問題,Transaction-A,通過k=1更新v。Transaction-B,通過k=2更新v,指令處于阻塞等待狀态。
第二步:處理問題,給需要作為查詢條件的字段添加索引。用完後可以删掉。
總結:InnoDB的行鎖是針對索引加的鎖,不是針對記錄加的鎖。并且該索引不能失效,否則都會從行鎖更新為表鎖。索引失效的原因在上一章節中已經介紹:http://www.cnblogs.com/itdragon/p/8146439.html
Transaction-A
mysql> update innodb_lock set v='1002' where k=1;
mysql> commit;
mysql> create index idx_k on innodb_lock(k);
Transaction-B
mysql> update innodb_lock set v='2002' where k=2;
Query OK, 1 row affected (19.82 sec)
從上面的案例看出,行鎖變表鎖似乎是一個坑,可MySQL沒有這麼無聊給你挖坑。這是因為MySQL有自己的執行計劃。
當你需要更新一張較大表的大部分甚至全表的資料時。而你又傻乎乎地用索引作為檢索條件。一不小心開啟了行鎖(沒毛病啊!保證資料的一緻性!)。可MySQL卻認為大量對一張表使用行鎖,會導緻事務執行效率低,進而可能造成其他事務長時間鎖等待和更多的鎖沖突問題,性能嚴重下降。是以MySQL會将行鎖更新為表鎖,即實際上并沒有使用索引。
我們仔細想想也能了解,既然整張表的大部分資料都要更新資料,一行一行地加鎖效率則更低。其實我們可以通過explain指令檢視MySQL的執行計劃,你會發現key為null。表明MySQL實際上并沒有使用索引,行鎖更新為表鎖也和上面的結論一緻。
本章重點介紹InnoDB的行鎖及其相關的事務知識。如果想了解MySQL的執行計劃,請看上一章節。
行鎖
行鎖的劣勢:開銷大;加鎖慢;會出現死鎖
行鎖的優勢:鎖的粒度小,發生鎖沖突的機率低;處理并發的能力強
加鎖的方式:自動加鎖。對于UPDATE、DELETE和INSERT語句,InnoDB會自動給涉及資料集加排他鎖;對于普通SELECT語句,InnoDB不會加任何鎖;當然我們也可以顯示的加鎖:
共享鎖:select * from tableName where ... + lock in share more
排他鎖:select * from tableName where ... + for update
InnoDB和MyISAM的最大不同點有兩個:一,InnoDB支援事務(transaction);二,預設采用行級鎖。加鎖可以保證事務的一緻性,可謂是有人(鎖)的地方,就有江湖(事務);我們先簡單了解一下事務知識。
MySQL 事務屬性
事務是由一組SQL語句組成的邏輯處理單元,事務具有ACID屬性。
原子性(Atomicity):事務是一個原子操作單元。在當時原子是不可分割的最小元素,其對資料的修改,要麼全部成功,要麼全部都不成功。
一緻性(Consistent):事務開始到結束的時間段内,資料都必須保持一緻狀态。
隔離性(Isolation):資料庫系統提供一定的隔離機制,保證事務在不受外部并發操作影響的"獨立"環境執行。
持久性(Durable):事務完成後,它對于資料的修改是永久性的,即使出現系統故障也能夠保持。
事務常見問題
更新丢失(Lost Update)
原因:當多個事務選擇同一行操作,并且都是基于最初標明的值,由于每個事務都不知道其他事務的存在,就會發生更新覆寫的問題。類比github送出沖突。
髒讀(Dirty Reads)
原因:事務A讀取了事務B已經修改但尚未送出的資料。若事務B復原資料,事務A的資料存在不一緻性的問題。
不可重複讀(Non-Repeatable Reads)
原因:事務A第一次讀取最初資料,第二次讀取事務B已經送出的修改或删除資料。導緻兩次讀取資料不一緻。不符合事務的隔離性。
幻讀(Phantom Reads)
原因:事務A根據相同條件第二次查詢到事務B送出的新增資料,兩次資料結果集不一緻。不符合事務的隔離性。
幻讀和髒讀有點類似
髒讀是事務B裡面修改了資料,
幻讀是事務B裡面新增了資料。
事務的隔離級别
資料庫的事務隔離越嚴格,并發副作用越小,但付出的代價也就越大。這是因為事務隔離實質上是将事務在一定程度上"串行"進行,這顯然與"并發"是沖突的。根據自己的業務邏輯,權衡能接受的最大副作用。進而平衡了"隔離" 和 "并發"的問題。MySQL預設隔離級别是可重複讀。
髒讀,不可重複讀,幻讀,其實都是資料庫讀一緻性問題,必須由資料庫提供一定的事務隔離機制來解決。
+------------------------------+---------------------+--------------+--------------+--------------+
| 隔離級别 | 讀資料一緻性 | 髒讀 | 不可重複 讀 | 幻讀 |
+------------------------------+---------------------+--------------+--------------+--------------+
| 未送出讀(Read uncommitted) | 最低級别 | 是 | 是 | 是 |
+------------------------------+---------------------+--------------+--------------+--------------+
| 已送出讀(Read committed) | 語句級 | 否 | 是 | 是 |
+------------------------------+---------------------+--------------+--------------+--------------+
| 可重複讀(Repeatable read) | 事務級 | 否 | 否 | 是 |
+------------------------------+---------------------+--------------+--------------+--------------+
| 可序列化(Serializable) | 最進階别,事務級 | 否 | 否 | 否 |
+------------------------------+---------------------+--------------+--------------+--------------+
檢視目前資料庫的事務隔離級别:show variables like 'tx_isolation';
mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
間隙鎖
當我們用範圍條件檢索資料,并請求共享或排他鎖時,InnoDB會給符合條件的已有資料記錄的索引項加鎖;對于鍵值在條件範圍内但并不存在的記錄,叫做"間隙(GAP)"。InnoDB也會對這個"間隙"加鎖,這種鎖機制就是所謂的間隙鎖(Next-Key鎖)。
Transaction-A
mysql> update innodb_lock set k=66 where id >=6;
Query OK, 1 row affected (0.63 sec)
mysql> commit;
Transaction-B
mysql> insert into innodb_lock (id,k,v) values(7,'7','7000');
Query OK, 1 row affected (18.99 sec)
危害(坑):若執行的條件是範圍過大,則InnoDB會将整個範圍内所有的索引鍵值全部鎖定,很容易對性能造成影響。
排他鎖
排他鎖,也稱寫鎖,獨占鎖,目前寫操作沒有完成前,它會阻斷其他寫鎖和讀鎖。
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5yN0YWOiNTMzgjZ1YzM3YDNmRjMilTN4cjM0MGM0cjZ58CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
# Transaction_A
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 for update;
+----+------+------+
| id | k | v |
+----+------+------+
| 4 | 4 | 4000 |
+----+------+------+
1 row in set (0.00 sec)
mysql> update innodb_lock set v='4001' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
# Transaction_B
mysql> select * from innodb_lock where id=4 for update;
+----+------+------+
| id | k | v |
+----+------+------+
| 4 | 4 | 4001 |
+----+------+------+
1 row in set (9.53 sec)
共享鎖
共享鎖,也稱讀鎖,多用于判斷資料是否存在,多個讀操作可以同時進行而不會互相影響。當如果事務對讀鎖進行修改操作,很可能會造成死鎖。如下圖所示。
# Transaction_A
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 lock in share mode;
+----+------+------+
| id | k | v |
+----+------+------+
| 4 | 4 | 4001 |
+----+------+------+
1 row in set (0.00 sec)
mysql> update innodb_lock set v='4002' where id=4;
Query OK, 1 row affected (31.29 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# Transaction_B
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 lock in share mode;
+----+------+------+
| id | k | v |
+----+------+------+
| 4 | 4 | 4001 |
+----+------+------+
1 row in set (0.00 sec)
mysql> update innodb_lock set v='4002' where id=4;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
分析行鎖定
通過檢查InnoDB_row_lock 狀态變量分析系統上的行鎖的争奪情況 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_row_lock_current_waits: 目前正在等待鎖定的數量
innodb_row_lock_time: 從系統啟動到現在鎖定總時間長度;非常重要的參數,
innodb_row_lock_time_avg: 每次等待所花平均時間;非常重要的參數,
innodb_row_lock_time_max: 從系統啟動到現在等待最常的一次所花的時間;
innodb_row_lock_waits: 系統啟動後到現在總共等待的次數;非常重要的參數。直接決定優化的方向和政策。
行鎖優化
1 盡可能讓所有資料檢索都通過索引來完成,避免無索引行或索引失效導緻行鎖更新為表鎖。
2 盡可能避免間隙鎖帶來的性能下降,減少或使用合理的檢索範圍。
3 盡可能減少事務的粒度,比如控制事務大小,而從減少鎖定資源量和時間長度,進而減少鎖的競争等,提供性能。
4 盡可能低級别事務隔離,隔離級别越高,并發的處理能力越低。
表鎖
表鎖的優勢:開銷小;加鎖快;無死鎖
表鎖的劣勢:鎖粒度大,發生鎖沖突的機率高,并發處理能力低
加鎖的方式:自動加鎖。查詢操作(SELECT),會自動給涉及的所有表加讀鎖,更新操作(UPDATE、DELETE、INSERT),會自動給涉及的表加寫鎖。也可以顯示加鎖:
共享讀鎖:lock table tableName read;
獨占寫鎖:lock table tableName write;
批量解鎖:unlock tables;
共享讀鎖
對MyISAM表的讀操作(加讀鎖),不會阻塞其他程序對同一表的讀操作,但會阻塞對同一表的寫操作。隻有當讀鎖釋放後,才能執行其他程序的寫操作。在鎖釋放前不能取其他表。
Transaction-A
mysql> lock table myisam_lock read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from myisam_lock;
9 rows in set (0.00 sec)
mysql> select * from innodb_lock;
ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES
mysql> update myisam_lock set v='1001' where k='1';
ERROR 1099 (HY000): Table 'myisam_lock' was locked with a READ lock and can't be updated
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Transaction-B
mysql> select * from myisam_lock;
9 rows in set (0.00 sec)
mysql> select * from innodb_lock;
8 rows in set (0.01 sec)
mysql> update myisam_lock set v='1001' where k='1';
Query OK, 1 row affected (18.67 sec)
獨占寫鎖
對MyISAM表的寫操作(加寫鎖),會阻塞其他程序對同一表的讀和寫操作,隻有當寫鎖釋放後,才會執行其他程序的讀寫操作。在鎖釋放前不能寫其他表。
Transaction-A
mysql> set autocommit=0;
Query OK, 0 rows affected (0.05 sec)
mysql> lock table myisam_lock write;
Query OK, 0 rows affected (0.03 sec)
mysql> update myisam_lock set v='2001' where k='2';
Query OK, 1 row affected (0.00 sec)
mysql> select * from myisam_lock;
9 rows in set (0.00 sec)
mysql> update innodb_lock set v='1001' where k='1';
ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Transaction-B
mysql> select * from myisam_lock;
9 rows in set (42.83 sec)
總結:表鎖,讀鎖會阻塞寫,不會阻塞讀。而寫鎖則會把讀寫都阻塞。
檢視加鎖情況
show open tables; 1表示加鎖,0表示未加鎖。
mysql> show open tables where in_use > 0;
+----------+-------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------------+--------+-------------+
| lock | myisam_lock | 1 | 0 |
+----------+-------------+--------+-------------+
分析表鎖定
可以通過檢查table_locks_waited 和 table_locks_immediate 狀态變量分析系統上的表鎖定:show status like 'table_locks%'
mysql> show status like 'table_locks%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 104 |
| Table_locks_waited | 0 |
+----------------------------+-------+
table_locks_immediate: 表示立即釋放表鎖數。
table_locks_waited: 表示需要等待的表鎖數。此值越高則說明存在着越嚴重的表級鎖争用情況。
此外,MyISAM的讀寫鎖排程是寫優先,這也是MyISAM不适合做寫為主表的存儲引擎。因為寫鎖後,其他線程不能做任何操作,大量的更新會使查詢很難得到鎖,進而造成永久阻塞。
什麼場景下用表鎖
InnoDB預設采用行鎖,在未使用索引字段查詢時更新為表鎖。MySQL這樣設計并不是給你挖坑。它有自己的設計目的。
即便你在條件中使用了索引字段,MySQL會根據自身的執行計劃,考慮是否使用索引(是以explain指令中會有possible_key 和 key)。如果MySQL認為全表掃描效率更高,它就不會使用索引,這種情況下InnoDB将使用表鎖,而不是行鎖。是以,在分析鎖沖突時,别忘了檢查SQL的執行計劃,以确認是否真正使用了索引。
第一種情況:全表更新。事務需要更新大部分或全部資料,且表又比較大。若使用行鎖,會導緻事務執行效率低,進而可能造成其他事務長時間鎖等待和更多的鎖沖突。
第二種情況:多表級聯。事務涉及多個表,比較複雜的關聯查詢,很可能引起死鎖,造成大量事務復原。這種情況若能一次性鎖定事務涉及的表,進而可以避免死鎖、減少資料庫因事務復原帶來的開銷。
頁鎖
開銷和加鎖時間介于表鎖和行鎖之間;會出現死鎖;鎖定粒度介于表鎖和行鎖之間,并發處理能力一般。隻需了解一下。
總結
1 InnoDB 支援表鎖和行鎖,使用索引作為檢索條件修改資料時采用行鎖,否則采用表鎖。
2 InnoDB 自動給修改操作加鎖,給查詢操作不自動加鎖
3 行鎖可能因為未使用索引而更新為表鎖,是以除了檢查索引是否建立的同時,也需要通過explain執行計劃查詢索引是否被實際使用。
4 行鎖相對于表鎖來說,優勢在于高并發場景下表現更突出,畢竟鎖的粒度小。
5 當表的大部分資料需要被修改,或者是多表複雜關聯查詢時,建議使用表鎖優于行鎖。
6 為了保證資料的一緻完整性,任何一個資料庫都存在鎖定機制。鎖定機制的優劣直接影響到一個資料庫的并發處理能力和性能。
到這裡,Mysql的表鎖和行鎖機制就介紹完了,若你不清楚InnoDB的行鎖會更新為表鎖,那以後會吃大虧的。若有打什麼不對的地方請指正。若覺得文章不錯,麻煩點個贊!來都來了,留下你的痕迹吧!