數(shù)據(jù)并發(fā)處理主要有兩種方式樂觀鎖和悲觀鎖祖凫。
樂觀鎖 ： 是假設(shè)并發(fā)事務(wù)處理時(shí)彼此不會(huì)相互影響莫绣，各種事務(wù)能在不產(chǎn)生鎖的情況下處理各自影響的那一部分?jǐn)?shù)據(jù)流妻；數(shù)據(jù)在更新時(shí)對(duì)數(shù)據(jù)進(jìn)行檢查，其他事務(wù)是否更新了該數(shù)據(jù)幕帆，如果更新過則回退，如果沒有更新則正常更新虑粥。所以樂觀鎖是一種數(shù)據(jù)并發(fā)處理方法如孝，沒有利用數(shù)據(jù)庫提供的鎖機(jī)制，需要業(yè)務(wù)實(shí)現(xiàn)控制數(shù)據(jù)并發(fā)處理娩贷。（樂觀鎖比較適合讀場(chǎng)景比較多第晰，寫操作比較少的場(chǎng)景）
樂觀鎖的實(shí)現(xiàn)一般有以下兩種方式：
1、 數(shù)據(jù)創(chuàng)建時(shí)添加一個(gè)version字段
# 創(chuàng)建表
create table student_info(id int primary key, name varchar(10), age int, version int);
# 邏輯操作
# 1彬祖、 讀取數(shù)據(jù)
select id, name, age, version from student_info where id=${id}
# 2茁瘦、更新數(shù)據(jù) 如果其他事務(wù)沒有修改數(shù)據(jù)，則修改成功储笑，否則修改失敗
update student_info set name=${name}, age=${age} , version=version+1 where id=${id} and version=${version}
2甜熔、 數(shù)據(jù)庫創(chuàng)建時(shí)添加update_time（精確到毫秒）字段,處理邏輯與上面邏輯大致相同
悲觀鎖 : 在數(shù)據(jù)并發(fā)處理時(shí)，利用數(shù)據(jù)庫提供的鎖機(jī)制對(duì)數(shù)據(jù)加鎖突倍，阻止其他事務(wù)對(duì)其進(jìn)行修改腔稀，只有當(dāng)該事務(wù)釋放該鎖之后其他事務(wù)才能繼續(xù)數(shù)據(jù)處理。
MySQL數(shù)據(jù)庫一般提供兩種鎖羽历，讀鎖（共享鎖）焊虏、寫鎖（排它鎖）：
讀鎖（共享鎖）： A事務(wù)獲取了表A'的共享鎖，則其他的事務(wù)依然可以讀取該數(shù)據(jù)（即獲取表A的共享鎖）秕磷，但是不能對(duì)該數(shù)據(jù)進(jìn)行修改（即獲取表A的排它鎖）
寫鎖（排它鎖）： A事務(wù)獲取了表A'的寫鎖之后诵闭，其他事務(wù)不能對(duì)表A'做任何鎖操作。
說明：讀鎖會(huì)阻塞其他事務(wù)的寫操作澎嚣，但不阻塞讀操作疏尿； 寫鎖會(huì)阻塞其他事務(wù)的讀寫操作
行鎖和表鎖
行鎖(Record Lock): 針對(duì)某行數(shù)據(jù)加鎖， 加鎖方式select * from test where id=1 for update/lock in share mode 釋放鎖commit易桃、rollback褥琐、kill阻塞進(jìn)程
Mysql中InnoDB引擎支持行鎖，MyISAM引擎不支持行鎖颈抚。
經(jīng)過測(cè)試無論查詢條件是否使用聚族索引踩衩，外面很多資料說不使用聚族索引就加表鎖的說法是錯(cuò)誤的。
表鎖(Table Lock): 針對(duì)某一個(gè)表加鎖,加鎖方式lock table test write/read;贩汉，釋放鎖unlock tables; unlock table test
** 在一個(gè)事務(wù)中不會(huì)同時(shí)持有兩個(gè)表鎖，在授予第二個(gè)表鎖的時(shí)候锚赤，隱式釋放已經(jīng)持有的表鎖匹舞。
行鎖的實(shí)現(xiàn)方式有三種Record Lock、Gap Lock线脚、 Next-Key Lock三種
行鎖（Record Lock）：直接對(duì)某一行數(shù)據(jù)加鎖就叫行鎖（例如： select * from test where id=1就是對(duì)ID=1的記錄添加了行鎖）
間隙鎖（Gap Lock）: 是為了防止事務(wù)級(jí)別在可重復(fù)讀（read-repeat）的情況下赐稽，出現(xiàn)幻讀問題叫榕。
Next-Key Lock: 是存儲(chǔ)引擎innoDB在事務(wù)級(jí)別可重復(fù)讀（read-repeat）的情況下的數(shù)據(jù)庫鎖，是行鎖和間隙鎖的組合姊舵，
下面演示一下這三種鎖是如何工作的晰绎，所有操作均基于假設(shè)存在如下數(shù)據(jù),并且隔離等級(jí)是repeatable-read.
CREATE TABLE `gap_table` (
`id` varchar(2) NOT NULL,
`count` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `gap_table_index` (`count`)
) ENGINE=InnoDB DEFAULT
insert into gap_table(id, count) values('a', 1);
insert into gap_table(id, count) values('d', 4);
insert into gap_table(id, count) values('g', 10);
主鍵查詢（聚族索引）鎖
1、 主鍵等值查詢括丁，數(shù)據(jù)存在時(shí)荞下，會(huì)對(duì)該主鍵的值加行鎖
# session A
begin
select * from gap_table where id='a' for update;，
select OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| lock_test | gap_table | NULL | TABLE | IX | GRANTED | NULL |
| lock_test | gap_table | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'a' |
+---------------+-------------+------------+-----------+---------------+-------------+-----------+
對(duì)表gap_table添加意向排它鎖（IX）史飞，對(duì)數(shù)據(jù)a添加行鎖（X,REC_NOT_GAP）
2尖昏、 主鍵等值查詢，數(shù)據(jù)不存在時(shí)构资，對(duì)該間隙加鎖
#session A
begin;
select * from gap_table where id='c' for update;
select OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| lock_test | gap_table | NULL | TABLE | IX | GRANTED | NULL |
| lock_test | gap_table | PRIMARY | RECORD | X,GAP | GRANTED | 'd' |
+---------------+-------------+------------+-----------+-----------+-------------+-----------+
對(duì)表gap_table添加意向排它鎖（IX）抽诉，對(duì)數(shù)據(jù)（a~d）添加間隙鎖（X,GAP）
3、 主鍵區(qū)間查詢,比較復(fù)雜吐绵，這里以8.18版本以后版本說明
begin;
select * from gap_table where id between 'e' and 'f' for update;
select OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| lock_test | gap_table | NULL | TABLE | IX | GRANTED | NULL |
| lock_test | gap_table | PRIMARY | RECORD | X,GAP | GRANTED | 'g' |
+---------------+-------------+------------+-----------+-----------+-------------+-----------+
對(duì)（d,g）間隙加鎖（X,GAP）
select * from gap_table where id between 'e' and 'g' for update;
select OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
| lock_test | gap_table | NULL | TABLE | IX | GRANTED | NULL |
| lock_test | gap_table | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
| lock_test | gap_table | PRIMARY | RECORD | X | GRANTED | 'g' |
+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
select * from gap_table where id > 'e' for update;
select OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
| lock_test | gap_table | NULL | TABLE | IX | GRANTED | NULL |
| lock_test | gap_table | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
| lock_test | gap_table | PRIMARY | RECORD | X | GRANTED | 'g' |
+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
對(duì)gap_table表的主鍵加了一個(gè)（supremum pseudo-record）鎖(個(gè)人理解是最大值以上的開區(qū)間鎖)迹淌，對(duì)數(shù)據(jù)(d, g]加行鎖nextkey-lock（X）;
但是經(jīng)過測(cè)試對(duì)(d, +∞）區(qū)間都加鎖
select * from gap_table where id < 'f' for update;
select OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+---------------+-------------+-----------------+-----------+---------------+-------------+------------------------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+---------------+-------------+-----------------+-----------+---------------+-------------+------------------------+
| lock_test | gap_table | NULL | TABLE | IX | GRANTED | NULL |
| lock_test | gap_table | gap_table_index | RECORD | X | GRANTED | supremum pseudo-record |
| lock_test | gap_table | gap_table_index | RECORD | X | GRANTED | 4, 'd' |
| lock_test | gap_table | gap_table_index | RECORD | X | GRANTED | 10, 'g' |
| lock_test | gap_table | gap_table_index | RECORD | X | GRANTED | 12, 'a' |
| lock_test | gap_table | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'a' |
| lock_test | gap_table | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'd' |
| lock_test | gap_table | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'g' |
+---------------+-------------+-----------------+-----------+---------------+-------------+------------------------+
首先對(duì)標(biāo)中的數(shù)據(jù)a d g都加了行鎖，其次在gap_table_index 所以中加NextKey-Lock(相當(dāng)于鎖住了整個(gè)gap_table_index 索引的所有區(qū)間)
非主鍵（輔助索引）鎖
添加如下數(shù)據(jù)
create table secondary_index_lock(id int primary key, no int unique, name varchar(2), age int);
insert into secondary_index_lock(id, no, name, age) values(1, 10, 'z1', 10), (2, 20, 'z2', 20), (3, 30, 'z3', 30), (4, 40, 'z4', 40),(9,90, 'z9',90);
1己单、 非主鍵唯一索引等值查詢巍沙，數(shù)據(jù)存在，for update 在主鍵加鎖荷鼠，for share僅僅在自己索引上加鎖
select * from secondary_index_lock where no=20 for update/ lock in share mode;
select ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------------------+---------------+----------------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+---------------+----------------------+------------+-----------+---------------+-------------+-----------+
| 2007 | lock_test | secondary_index_lock | NULL | TABLE | IX | GRANTED | NULL |
| 2007 | lock_test | secondary_index_lock | no | RECORD | X,REC_NOT_GAP | GRANTED | 20, 2 |
| 2007 | lock_test | secondary_index_lock | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 2 |
+-----------------------+---------------+----------------------+------------+-----------+---------------+-------------+-----------+
在主鍵索引和唯一索引上對(duì)該數(shù)據(jù)加行鎖(排他/共享)
2句携、 非主鍵索引等值查詢，數(shù)據(jù)不存在允乐，無論是否索引覆蓋矮嫉，相當(dāng)于一個(gè)范圍查詢，僅僅會(huì)在非主鍵索引上加間隙鎖
select * from secondary_index_lock where name = 'z5' for update;
select ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------------------+---------------+----------------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+---------------+----------------------+------------+-----------+-----------+-------------+-----------+
| 2027 | lock_test | secondary_index_lock | NULL | TABLE | IX | GRANTED | NULL |
| 2027 | lock_test | secondary_index_lock | name_index | RECORD | X,GAP | GRANTED | 'z9', 9 |
+-----------------------+---------------+----------------------+------------+-----------+-----------+-------------+-----------+
3牍疏、非主鍵唯一索引范圍查詢時(shí)蠢笋，不是覆蓋索引的時(shí)候，會(huì)對(duì)相應(yīng)范圍內(nèi)加間隙鎖鳞陨，并且如果存在數(shù)據(jù)昨寞，會(huì)對(duì)對(duì)應(yīng)的主鍵加鎖
select * from secondary_index_lock where no=25 for update;
select ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------------------+---------------+----------------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+---------------+----------------------+------------+-----------+-----------+-------------+-----------+
| 2008 | lock_test | secondary_index_lock | NULL | TABLE | IX | GRANTED | NULL |
| 2008 | lock_test | secondary_index_lock | no | RECORD | X,GAP | GRANTED | 30, 3 |
+-----------------------+---------------+----------------------+------------+-----------+-----------+-------------+-----------+
在no索引上加間隙鎖 （20， 30）
4厦滤、 非主鍵唯一索引范圍查詢時(shí)援岩，如果是覆蓋索引時(shí)，會(huì)對(duì)所有的后閉區(qū)間對(duì)應(yīng)的主鍵加行鎖
select * from secondary_index_lock where no > 25 for update;
select ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------------------+---------------+----------------------+------------+-----------+---------------+-------------+------------------------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+---------------+----------------------+------------+-----------+---------------+-------------+------------------------+
| 2029 | lock_test | secondary_index_lock | NULL | TABLE | IX | GRANTED | NULL |
| 2029 | lock_test | secondary_index_lock | no | RECORD | X | GRANTED | supremum pseudo-record |
| 2029 | lock_test | secondary_index_lock | no | RECORD | X | GRANTED | 30, 3 |
| 2029 | lock_test | secondary_index_lock | no | RECORD | X | GRANTED | 40, 4 |
| 2029 | lock_test | secondary_index_lock | no | RECORD | X | GRANTED | 100, 10 |
| 2029 | lock_test | secondary_index_lock | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 3 |
| 2029 | lock_test | secondary_index_lock | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 4 |
| 2029 | lock_test | secondary_index_lock | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
+-----------------------+---------------+----------------------+------------+-----------+---------------+-------------+------------------------+
