1、概述
鎖的定義
1.鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問(wèn)某一資源的機(jī)制。
2.在數(shù)據(jù)庫(kù)中，除傳統(tǒng)的計(jì)算資源（如CPU、RAM、I/O等）的爭(zhēng)用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。
3.如何保證數(shù)據(jù)并發(fā)訪問(wèn)的一致性、有效性是所有數(shù)據(jù)庫(kù)必須解決的一個(gè)問(wèn)題，鎖沖突也是影響數(shù)據(jù)庫(kù)并發(fā)訪問(wèn)性能的一個(gè)重要因素。
4.從這個(gè)角度來(lái)說(shuō)，鎖對(duì)數(shù)據(jù)庫(kù)而言顯得尤其重要，也更加復(fù)雜。
鎖的分類
1.從數(shù)據(jù)操作的類型（讀、寫）分
- 讀鎖（共享鎖）：針對(duì)同一份數(shù)據(jù)，多個(gè)讀操作可以同時(shí)進(jìn)行而不會(huì)互相影響
- 寫鎖（排它鎖）：當(dāng)前寫操作沒有完成前，它會(huì)阻斷其他寫鎖和讀鎖。
2.從對(duì)數(shù)據(jù)操作的顆粒度
- 表鎖
- 行鎖
2、表鎖
表鎖的特點(diǎn)
偏向MyISAM存儲(chǔ)引擎，開銷小，加鎖快，無(wú)死鎖，鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)最低
2.1表鎖案例分析
創(chuàng)建表
- 建表 SQL：引擎選擇 myisam
create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) engine myisam;
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
select * from mylock;
- mylock 表中的測(cè)試數(shù)據(jù)
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
手動(dòng)加鎖和釋放鎖
- 查看當(dāng)前數(shù)據(jù)庫(kù)中表的上鎖情況：show open tables;，0 表示未上鎖
mysql> show open tables;
+--------------------+----------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+----------------------------------------------------+--------+-------------+
| performance_schema | events_waits_history | 0 | 0 |
| performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
| performance_schema | setup_timers | 0 | 0 |
| performance_schema | events_waits_history_long | 0 | 0 |
| performance_schema | events_statements_summary_by_digest | 0 | 0 |
| performance_schema | mutex_instances | 0 | 0 |
| performance_schema | events_waits_summary_by_instance | 0 | 0 |
| performance_schema | events_stages_history | 0 | 0 |
| mysql | db | 0 | 0 |
| performance_schema | events_waits_summary_by_host_by_event_name | 0 | 0 |
| mysql | user | 0 | 0 |
| mysql | columns_priv | 0 | 0 |
| performance_schema | events_statements_history_long | 0 | 0 |
| performance_schema | performance_timers | 0 | 0 |
| performance_schema | file_instances | 0 | 0 |
| performance_schema | events_stages_summary_by_user_by_event_name | 0 | 0 |
| performance_schema | events_stages_history_long | 0 | 0 |
| performance_schema | setup_actors | 0 | 0 |
| performance_schema | cond_instances | 0 | 0 |
| mysql | proxies_priv | 0 | 0 |
| performance_schema | socket_summary_by_instance | 0 | 0 |
| performance_schema | events_statements_current | 0 | 0 |
| mysql | event | 0 | 0 |
| performance_schema | session_connect_attrs | 0 | 0 |
| mysql | plugin | 0 | 0 |
| performance_schema | threads | 0 | 0 |
| mysql | time_zone_transition_type | 0 | 0 |
| mysql | time_zone_name | 0 | 0 |
| performance_schema | file_summary_by_event_name | 0 | 0 |
| performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
| performance_schema | socket_summary_by_event_name | 0 | 0 |
| performance_schema | users | 0 | 0 |
| mysql | servers | 0 | 0 |
| performance_schema | events_waits_summary_by_account_by_event_name | 0 | 0 |
| db01 | tbl_emp | 0 | 0 |
| performance_schema | events_statements_summary_by_host_by_event_name | 0 | 0 |
| db01 | tblA | 0 | 0 |
| performance_schema | table_io_waits_summary_by_index_usage | 0 | 0 |
| performance_schema | events_waits_current | 0 | 0 |
| db01 | user | 0 | 0 |
| mysql | procs_priv | 0 | 0 |
| performance_schema | events_statements_summary_by_thread_by_event_name | 0 | 0 |
| db01 | emp | 0 | 0 |
| db01 | tbl_user | 0 | 0 |
| db01 | test03 | 0 | 0 |
| mysql | slow_log | 0 | 0 |
| performance_schema | file_summary_by_instance | 0 | 0 |
| db01 | article | 0 | 0 |
| performance_schema | objects_summary_global_by_type | 0 | 0 |
| db01 | phone | 0 | 0 |
| performance_schema | events_waits_summary_by_thread_by_event_name | 0 | 0 |
| performance_schema | setup_consumers | 0 | 0 |
| performance_schema | socket_instances | 0 | 0 |
| performance_schema | rwlock_instances | 0 | 0 |
| db01 | tbl_dept | 0 | 0 |
| performance_schema | events_statements_summary_by_user_by_event_name | 0 | 0 |
| db01 | staffs | 0 | 0 |
| db01 | class | 0 | 0 |
| mysql | general_log | 0 | 0 |
| performance_schema | events_stages_summary_global_by_event_name | 0 | 0 |
| performance_schema | events_stages_summary_by_account_by_event_name | 0 | 0 |
| performance_schema | events_statements_summary_by_account_by_event_name | 0 | 0 |
| performance_schema | table_lock_waits_summary_by_table | 0 | 0 |
| performance_schema | hosts | 0 | 0 |
| performance_schema | setup_objects | 0 | 0 |
| performance_schema | events_stages_current | 0 | 0 |
| mysql | time_zone | 0 | 0 |
| mysql | tables_priv | 0 | 0 |
| performance_schema | table_io_waits_summary_by_table | 0 | 0 |
| mysql | time_zone_leap_second | 0 | 0 |
| db01 | book | 0 | 0 |
| performance_schema | session_account_connect_attrs | 0 | 0 |
| db01 | mylock | 0 | 0 |
| mysql | func | 0 | 0 |
| performance_schema | events_statements_summary_global_by_event_name | 0 | 0 |
| performance_schema | events_statements_history | 0 | 0 |
| performance_schema | accounts | 0 | 0 |
| mysql | time_zone_transition | 0 | 0 |
| db01 | dept | 0 | 0 |
| performance_schema | events_stages_summary_by_host_by_event_name | 0 | 0 |
| performance_schema | events_stages_summary_by_thread_by_event_name | 0 | 0 |
| mysql | proc | 0 | 0 |
| performance_schema | setup_instruments | 0 | 0 |
| performance_schema | host_cache | 0 | 0 |
+--------------------+----------------------------------------------------+--------+-------------+
84 rows in set (0.00 sec)
- 添加鎖
lock table 表名1 read(write), 表名2 read(write), ...;
- 釋放表鎖
unlock tables;
2.1.1、讀鎖示例
- 在 session 1 會(huì)話中，給 mylock 表加個(gè)讀鎖
mysql> lock table mylock read;
Query OK, 0 rows affected (0.00 sec)
- 在 session1 會(huì)話中能不能讀取 mylock 表：可以讀
################# session1 中的操作 #################
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
- 在 session1 會(huì)話中能不能讀取 book 表：并不行。。。
################# session1 中的操作 #################
mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
- 在 session2 會(huì)話中能不能讀取 mylock 表：可以讀
################# session2 中的操作 #################
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
- 在 session1 會(huì)話中能不能修改 mylock 表：并不行。。。
################# session1 中的操作 #################
mysql> update mylock set name='a2' where id=1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
- 在 session2 會(huì)話中能不能修改 mylock 表：阻塞，一旦 mylock 表鎖釋放，則會(huì)執(zhí)行修改操作
################# session2 中的操作 #################
mysql> update mylock set name='a2' where id=1;
# 在這里阻塞著呢~~~
結(jié)論
1.當(dāng)前 session 和其他 session 均可以讀取加了讀鎖的表
2.當(dāng)前 session 不能讀取其他表，并且不能修改加了讀鎖的表
3.其他 session 想要修改加了讀鎖的表，必須等待其讀鎖釋放
2.1.2、寫鎖示例
在 session 1 會(huì)話中，給 mylock 表加個(gè)寫鎖
mysql> lock table mylock write;
Query OK, 0 rows affected (0.00 sec)
- 在 session1 會(huì)話中能不能讀取 mylock 表：闊以
################# session1 中的操作 #################
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a2 |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
- 在 session1 會(huì)話中能不能讀取 book 表：不闊以
################# session1 中的操作 #################
mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
- 在 session1 會(huì)話中能不能修改 mylock 表：當(dāng)然可以啦，加寫鎖就是為了修改呀
################# session1 中的操作 #################
mysql> update mylock set name='a2' where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
- 在 session2 會(huì)話中能不能讀取 mylock 表：
################# session2 中的操作 #################
mysql> select * from mylock;
# 在這里阻塞著呢~~~
結(jié)論
1.當(dāng)前 session 不能讀取其他表
2.其他 session 想要讀取加了寫鎖的表，必須等待其讀鎖釋放
3.MyISAM在執(zhí)行查詢語(yǔ)句（SELECT）前，會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行增刪改操作前，會(huì)自動(dòng)給涉及的表加寫鎖。
4.MySQL的表級(jí)鎖有兩種模式：
- 表共享讀鎖（Table Read Lock）
-
表獨(dú)占寫鎖（Table Write Lock）
結(jié)論：
結(jié)合上表，所以對(duì)MyISAM表進(jìn)行操作，會(huì)有以下情況：
1.對(duì)MyISAM表的讀操作（加讀鎖），不會(huì)阻塞其他進(jìn)程對(duì)同一表的讀請(qǐng)求，但會(huì)阻塞對(duì)同一表的寫請(qǐng)求。只有當(dāng)讀鎖釋放后，才會(huì)執(zhí)行其它進(jìn)程的寫操作。
2.對(duì)MyISAM表的寫操作（加寫鎖），會(huì)阻塞其他進(jìn)程對(duì)同一表的讀和寫操作，只有當(dāng)寫鎖釋放后，才會(huì)執(zhí)行其它進(jìn)程的讀寫操作
3.簡(jiǎn)而言之，就是讀鎖會(huì)阻塞寫，但是不會(huì)堵塞讀。而寫鎖則會(huì)把讀和寫都堵塞。
2.2、表鎖分析
- 查看哪些表被鎖了，0 表示未鎖，1 表示被鎖
show open tables;
【如何分析表鎖定】可以通過(guò)檢查table_locks_waited和table_locks_immediate狀態(tài)變量來(lái)分析系統(tǒng)上的表鎖定，通過(guò) show status like 'table%'; 命令查看
1.Table_locks_immediate：產(chǎn)生表級(jí)鎖定的次數(shù)，表示可以立即獲取鎖的查詢次數(shù)，每立即獲取鎖值加1；
2.Table_locks_waited：出現(xiàn)表級(jí)鎖定爭(zhēng)用而發(fā)生等待的次數(shù)（不能立即獲取鎖的次數(shù)，每等待一次鎖值加1），此值高則說(shuō)明存在著較嚴(yán)重的表級(jí)鎖爭(zhēng)用情況；
mysql> show status like 'table%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Table_locks_immediate | 500440 |
| Table_locks_waited | 1 |
| Table_open_cache_hits | 500070 |
| Table_open_cache_misses | 5 |
| Table_open_cache_overflows | 0 |
+----------------------------+--------+
5 rows in set (0.00 sec)
- 此外，Myisam的讀寫鎖調(diào)度是寫優(yōu)先，這也是myisam不適合做寫為主表的引擎。因?yàn)閷戞i后，其他線程不能做任何操作，大量的更新會(huì)使查詢很難得到鎖，從而造成永遠(yuǎn)阻塞
3、行鎖
行鎖的特點(diǎn)
1.偏向InnoDB存儲(chǔ)引擎，開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。
2.InnoDB與MyISAM的最大不同有兩點(diǎn)：一是支持事務(wù)（TRANSACTION）；二是采用了行級(jí)鎖。
3.1、事務(wù)復(fù)習(xí)
行鎖支持事務(wù)，復(fù)習(xí)下老知識(shí)
事務(wù)（Transation）及其ACID屬性
事務(wù)是由一組SQL語(yǔ)句組成的邏輯處理單元，事務(wù)具有以下4個(gè)屬性，通常簡(jiǎn)稱為事務(wù)的ACID屬性。
原子性（Atomicity）：事務(wù)是一個(gè)原子操作單元，其對(duì)數(shù)據(jù)的修改，要么全都執(zhí)行，要么全都不執(zhí)行。
一致性（Consistent）：在事務(wù)開始和完成時(shí)，數(shù)據(jù)都必須保持一致狀態(tài)。這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)的修改，以保持?jǐn)?shù)據(jù)的完整性；事務(wù)結(jié)束時(shí)，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)（如B樹索引或雙向鏈表）也都必須是正確的。
隔離性（Isolation）：數(shù)據(jù)庫(kù)系統(tǒng)提供一定的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的“獨(dú)立”環(huán)境執(zhí)行。這意味著事務(wù)處理過(guò)程中的中間狀態(tài)對(duì)外部是不可見的，反之亦然。
持久性（Durability）：事務(wù)院成之后，它對(duì)于數(shù)據(jù)的修改是永久性的，即使出現(xiàn)系統(tǒng)故障也能夠保持。
并發(fā)事務(wù)處理帶來(lái)的問(wèn)題
1.更新丟失（Lost Update）：
- 當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行，然后基于最初選定的值更新該行時(shí)，由于每個(gè)事務(wù)都不知道其他事務(wù)的存在，就會(huì)發(fā)生丟失更新問(wèn)題一一最后的更新覆蓋了由其他事務(wù)所做的更新。
- 例如，兩個(gè)程序員修改同一java文件。每程序員獨(dú)立地更改其副本，然后保存更改后的副本，這樣就覆蓋了原始文檔。最后保存其更改副本的編輯人員覆蓋前一個(gè)程序員所做的更改。
- 如果在一個(gè)程序員完成并提交事務(wù)之前，另一個(gè)程序員不能訪問(wèn)同一文件，則可避免此問(wèn)題。
2.臟讀（Dirty Reads）：
- 一個(gè)事務(wù)正在對(duì)一條記錄做修改，在這個(gè)事務(wù)完成并提交前，這條記錄的數(shù)據(jù)就處于不一致狀態(tài)；這時(shí)，另一個(gè)事務(wù)也來(lái)讀取同一條記錄，如果不加控制，第二個(gè)事務(wù)讀取了這些“臟”數(shù)據(jù)，并據(jù)此做進(jìn)一步的處理，就會(huì)產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系。這種現(xiàn)象被形象地叫做”臟讀”。
- 一句話：事務(wù)A讀取到了事務(wù)B已修改但尚未提交的的數(shù)據(jù)，還在這個(gè)數(shù)據(jù)基礎(chǔ)上做了操作。此時(shí)，如果B事務(wù)回滾，A讀取的數(shù)據(jù)無(wú)效，不符合一致性要求。
3.不可重復(fù)讀（Non-Repeatable Reads）：
- 一個(gè)事務(wù)在讀取某些數(shù)據(jù)后的某個(gè)時(shí)間，再次讀取以前讀過(guò)的數(shù)據(jù)，卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生了改變、或某些記錄已經(jīng)被刪除了！這種現(xiàn)象就叫做“不可重復(fù)讀”。
- 一句話：事務(wù)A讀取到了事務(wù)B已經(jīng)提交的修改數(shù)據(jù)，不符合隔離性
4.幻讀（Phantom Reads）:
- 一個(gè)事務(wù)按相同的查詢條件重新讀取以前檢索過(guò)的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)，這種現(xiàn)象就稱為“幻讀一句話：事務(wù)A讀取到了事務(wù)B體提交的新增數(shù)據(jù)，不符合隔離性。
- 多說(shuō)一句：幻讀和臟讀有點(diǎn)類似，臟讀是事務(wù)B里面修改了數(shù)據(jù)，幻讀是事務(wù)B里面新增了數(shù)據(jù)。
事物的隔離級(jí)別
1.臟讀”、“不可重復(fù)讀”和“幻讀”，其實(shí)都是數(shù)據(jù)庫(kù)讀一致性問(wèn)題，必須由數(shù)據(jù)庫(kù)提供一定的事務(wù)隔離機(jī)制來(lái)解決。
2.數(shù)據(jù)庫(kù)的事務(wù)隔離越嚴(yán)格，并發(fā)副作用越小，但付出的代價(jià)也就越大，因?yàn)槭聞?wù)隔離實(shí)質(zhì)上就是使事務(wù)在一定程度上“串行化”進(jìn)行，這顯然與“并發(fā)”是矛盾的。
3.同時(shí)，不同的應(yīng)用對(duì)讀一致性和事務(wù)隔離程度的要求也是不同的，比如許多應(yīng)用對(duì)“不可重復(fù)讀”和“幻讀”并不敏感，可能更關(guān)心數(shù)據(jù)并發(fā)訪問(wèn)的能力。
4.查看當(dāng)前數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別：show variables like 'tx_isolation'; mysql 默認(rèn)是可重復(fù)讀
3.2、行鎖案例分析
創(chuàng)建表
- 建表 SQL
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;
INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
- test_innodb_lock 表中的測(cè)試數(shù)據(jù)
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
- test_innodb_lock 表中的索引
mysql> SHOW INDEX FROM test_innodb_lock;
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_innodb_lock | 1 | test_innodb_a_ind | 1 | a | A | 9 | NULL | NULL | YES | BTREE | | |
| test_innodb_lock | 1 | test_innodb_lock_b_ind | 1 | b | A | 9 | NULL | NULL | YES | BTREE | | |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
操作同一行數(shù)據(jù)
- session1 開啟事務(wù)，修改 test_innodb_lock 中的數(shù)據(jù)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='4001' where a=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- session2 開啟事務(wù)，修改 test_innodb_lock 中同一行數(shù)據(jù)，將導(dǎo)致 session2 發(fā)生阻塞，一旦 session1 提交事務(wù)，session2 將執(zhí)行更新操作
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='4002' where a=4;
# 在這兒阻塞著呢~~~
# 時(shí)間太長(zhǎng)，會(huì)報(bào)超時(shí)錯(cuò)誤哦
mysql> update test_innodb_lock set b='4001' where a=4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
操作不同行數(shù)據(jù)
- session1 開啟事務(wù)，修改 test_innodb_lock 中的數(shù)據(jù)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='4001' where a=4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
- session2 開啟事務(wù)，修改 test_innodb_lock 中不同行的數(shù)據(jù)
- 由于采用行鎖，session2 和 session1 互不干涉，所以 session2 中的修改操作沒有阻塞
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='9001' where a=9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
無(wú)索引導(dǎo)致行鎖升級(jí)為表鎖
- session1 開啟事務(wù)，修改 test_innodb_lock 中的數(shù)據(jù)，varchar 不用 ’ ’ ，導(dǎo)致系統(tǒng)自動(dòng)轉(zhuǎn)換類型，導(dǎo)致索引失效，從而進(jìn)行了全表掃描
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set a=44 where b=4000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- session2 開啟事務(wù)，修改 test_innodb_lock 中不同行的數(shù)據(jù)
- 由于發(fā)生了自動(dòng)類型轉(zhuǎn)換，索引失效，導(dǎo)致行鎖變?yōu)楸礞i
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='9001' where a=9;
# 在這兒阻塞著呢~~~
3.3、間隙鎖
什么是間隙鎖
1.當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請(qǐng)求共享或排他鎖時(shí)，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖；對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP）”
2.InnoDB也會(huì)對(duì)這個(gè)“間隙”加鎖，這種鎖機(jī)制是所謂的間隙鎖（Next-Key鎖）
間隙鎖的危害
1.因?yàn)镼uery執(zhí)行過(guò)程中通過(guò)過(guò)范圍查找的話，他會(huì)鎖定整個(gè)范圍內(nèi)所有的索引鍵值，即使這個(gè)鍵值并不存在。
2.間隙鎖有一個(gè)比較致命的弱點(diǎn)，就是當(dāng)鎖定一個(gè)范圍鍵值之后，即使某些不存在的鍵值也會(huì)被無(wú)辜的鎖定，而造成在鎖定的時(shí)候無(wú)法插入鎖定鍵值范圍內(nèi)的任何數(shù)據(jù)。在某些場(chǎng)景下這可能會(huì)對(duì)性能造成很大的危害
間隙鎖示例
- test_innodb_lock 表中的數(shù)據(jù)
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
- session1 開啟事務(wù)，執(zhí)行修改 a > 1 and a < 6 的數(shù)據(jù)，這會(huì)導(dǎo)致 mysql 將 a = 2 的數(shù)據(jù)行鎖?。m然表中并沒有這行數(shù)據(jù)）
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='Heygo' where a>1 and a<6;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
- session2 開啟事務(wù)，修改 test_innodb_lock 中不同行的數(shù)據(jù)，也會(huì)導(dǎo)致阻塞，直至 session1 提交事務(wù)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='9001' where a=9;
# 在這兒阻塞著呢~~~
3.4、手動(dòng)行鎖
如何鎖定一行
- select xxx ... for update 鎖定某一行后，其它的操作會(huì)被阻塞，直到鎖定行的會(huì)話提交
- session1 開啟事務(wù)，手動(dòng)執(zhí)行 for update 鎖定指定行，待執(zhí)行完指定操作時(shí)再將數(shù)據(jù)提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_innodb_lock where a=8 for update;
+------+------+
| a | b |
+------+------+
| 8 | 8000 |
+------+------+
1 row in set (0.00 sec)
- session2 開啟事務(wù)，修改 session1 中被鎖定的行，會(huì)導(dǎo)致阻塞，直至 session1 提交事務(wù)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='XXX' where a=8;
# 在這兒阻塞著呢~
3.5、行鎖分析
案例結(jié)論
1.Innodb存儲(chǔ)引擎由于實(shí)現(xiàn)了行級(jí)鎖定，雖然在鎖定機(jī)制的實(shí)現(xiàn)方面所帶來(lái)的性能損耗可能比表級(jí)鎖定會(huì)要更高一些，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)優(yōu)于MyISAM的表級(jí)鎖定的。
2.當(dāng)系統(tǒng)并發(fā)量較高的時(shí)候，Innodb的整體性能和MyISAM相比就會(huì)有比較明顯的優(yōu)勢(shì)了。
3.但是，Innodb的行級(jí)鎖定同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r(shí)候（索引失效，導(dǎo)致行鎖變表鎖），可能會(huì)讓Innodb的整體性能表現(xiàn)不僅不能比MyISAM高，甚至可能會(huì)更差。
行鎖分析
如何分析行鎖定
- 通過(guò)檢查InnoDB_row_lock狀態(tài)變量來(lái)分析系統(tǒng)上的行鎖的爭(zhēng)奪情況
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 | 212969 |
| Innodb_row_lock_time_avg | 42593 |
| Innodb_row_lock_time_max | 51034 |
| Innodb_row_lock_waits | 5 |
+-------------------------------+--------+
5 rows in set (0.00 sec)
對(duì)各個(gè)狀態(tài)量的說(shuō)明如下：
1.Innodb_row_lock_current_waits：當(dāng)前正在等待鎖定的數(shù)量；
2.Innodb_row_lock_time：從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)間長(zhǎng)度；
3.Innodb_row_lock_time_avg：每次等待所花平均時(shí)間；
4.Innodb_row_lock_time_max：從系統(tǒng)啟動(dòng)到現(xiàn)在等待最常的一次所花的時(shí)間；
5.Innodb_row_lock_waits：系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的次數(shù)；
對(duì)于這5個(gè)狀態(tài)變量，比較重要的主要是
1.Innodb_row_lock_time_avg（等待平均時(shí)長(zhǎng)）
2.Innodb_row_lock_waits（等待總次數(shù)）
3.Innodb_row_lock_time（等待總時(shí)長(zhǎng)）
尤其是當(dāng)?shù)却螖?shù)很高，而且每次等待時(shí)長(zhǎng)也不小的時(shí)候，我們就需要分析系統(tǒng)中為什么會(huì)有如此多的等待，然后根據(jù)分析結(jié)果著手指定優(yōu)化計(jì)劃。
3.6、行鎖優(yōu)化
優(yōu)化建議
1.盡可能讓所有數(shù)據(jù)檢索都通過(guò)索引來(lái)完成，避免無(wú)索引行鎖升級(jí)為表鎖
2.合理設(shè)計(jì)索引，盡量縮小鎖的范圍
3.盡可能較少檢索條件，避免間隙鎖
4.盡量控制事務(wù)大小，減少鎖定資源量和時(shí)間長(zhǎng)度
5.盡可能低級(jí)別事務(wù)隔離
4、頁(yè)鎖
1.開銷和加鎖時(shí)間界于表鎖和行鎖之間：會(huì)出現(xiàn)死鎖；
2.鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。
3.了解即可
