select ... for share 可在多個事務(wù)中讀丘跌，但是不可被其他事務(wù)寫
select ... for update

https://www.hollischuang.com/archives/1716

https://www.cnblogs.com/ktgu/p/3529143.html

死鎖：鎖被一個客戶端長期占據(jù)袭景，一直處于鎖定狀態(tài)；對方持有對方的鎖闭树，進(jìn)入僵持狀態(tài)耸棒；
活鎖：是指線程1可以使用資源，但它很禮貌报辱，讓其他線程先使用資源与殃，線程2也可以使用資源，但它很紳士，也讓其他線程先使用資源幅疼。這樣你讓我米奸，我讓你，最后兩個線程都無法使用資源衣屏。
饑餓：是指如果線程T1占用了資源R躏升，線程T2又請求封鎖R，于是T2等待狼忱。T3也請求資源R膨疏，當(dāng)T1釋放了R上的封鎖后，系統(tǒng)首先批準(zhǔn)了T3的請求钻弄，T2仍然等待佃却。然后T4又請求封鎖R，當(dāng)T3釋放了R上的封鎖之后窘俺，系統(tǒng)又批準(zhǔn)了T4的請求......饲帅，T2可能永遠(yuǎn)等待。

Innodb行級鎖都是基于索引的瘤泪？

http://www.hollischuang.com/archives/934

上面我們提到灶泵，使用select…for update會把數(shù)據(jù)給鎖住，不過我們需要注意一些鎖的級別对途，MySQL InnoDB默認(rèn)行級鎖赦邻。行級鎖都是基于索引的，如果一條SQL語句用不到索引是不會使用行級鎖的实檀，會使用表級鎖把整張表鎖住惶洲，這點(diǎn)需要注意。

Innodb鎖

InnoDB鎖和索引的關(guān)系膳犹？
執(zhí)行計(jì)劃對select ... for update 行級鎖的影響恬吕？
InnoDB事務(wù)和鎖的持有？
select ... for update 有什么問題须床？

文檔中的 index records 是啥铐料？

14.6.2.2 The Physical Structure of an InnoDB Index

Index records are stored in the leaf pages of their B-tree data structure.

1. InnoDB鎖和索引的關(guān)系？

14.7.3 Locks Set by Different SQL Statements in InnoDB

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows.
如果沒有匹配的索引則掃描全表豺旬，并且把所有行都鎖定余赢。
因此必須要創(chuàng)建必須的索引，否則全表掃描哈垢，所有行都被鎖定。

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHEREcondition, but only knows which index ranges were scanned.
locking read扛拨， UPDATE, DELETE 設(shè)置 record locks在所有被掃描的索引記錄上耘分。
InnoDB不會記得確切的where條件匣椰，僅記得被掃描的索引范圍驮俗。
如果不用索引，所有被掃描的行都被鎖上。
用了索引飒泻，所有被掃描的索引記錄也都是鎖上。
只要被掃描桑驱，都會被鎖上将塑。
因?yàn)橛兴饕钥梢灾苯佣ㄎ唬?而不用全表掃描卜朗，只需要掃描相關(guān)的索引記錄就行了（可能會掃描多行拔第，鎖定多行）。如果是唯一性索引场钉，則只需要掃描一行就行了蚊俺。
因?yàn)闆]有索引，必須要全表掃描逛万，所以表中所有行都被鎖定泳猬。

索引文件和記錄文件，兩份文件宇植，分開存儲

index-record locks 鎖定的是索引文件中行
InnoDB所有表中如果不設(shè)置索引得封，或主鍵索引，則clustered index作為默認(rèn)索引指郁。

1. InnoDB鎖和索引的關(guān)系忙上？舉例子

mysql> show create table t ;
+-------+------------------------------------------------------------------------------------+
| Table | Create Table                                                                       |
+-------+------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

insert

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

mysql> 
mysql> 
mysql> select * from t;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    5 |
|    5 |
|    5 |
|    5 |
+------+
9 rows in set (0.00 sec)

mysql> insert into t(i) values(7);
Query OK, 1 row affected (0.00 sec)

mysql> SHOW ENGINE INNODB STATUS\G

------------
TRANSACTIONS
------------
Trx id counter 152491
Purge done for trx's n:o < 152490 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479467061704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479467059896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479467058992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 152490, ACTIVE 5 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 123145358168064, query id 3204 localhost root starting
SHOW ENGINE INNODB STATUS

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

update

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

mysql> 
mysql> 
mysql> select * from t;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    5 |
|    5 |
|    5 |
|    5 |
+------+
9 rows in set (0.00 sec)

mysql> update t set i=10 where i=8;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0



mysql> SHOW ENGINE INNODB STATUS\G

------------
TRANSACTIONS
------------
Trx id counter 152491
Purge done for trx's n:o < 152490 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479467061704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479467059896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479467058992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 152490, ACTIVE 97 sec
3 lock struct(s), heap size 1136, 12 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 123145358168064, query id 3206 localhost root starting
SHOW ENGINE INNODB STATUS

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

delete



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

mysql> select * from t;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    5 |
|    5 |
|    5 |
|    5 |
+------+
9 rows in set (0.00 sec)


mysql> delete from t where i=9;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW ENGINE INNODB STATUS\G

------------
TRANSACTIONS
------------
Trx id counter 152491
Purge done for trx's n:o < 152490 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479467061704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479467059896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479467058992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 152490, ACTIVE 158 sec
3 lock struct(s), heap size 1136, 12 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 123145358168064, query id 3208 localhost root starting
SHOW ENGINE INNODB STATUS

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

總結(jié)：驗(yàn)證了 repeatable-read isolation level下的設(shè)置的鎖

locking read:
select ... for update
select ... lock in share mode
UPDATE
INSERT

InnoDB不會記得確切的where條件，僅記得被掃描的索引范圍坡氯。
如果不用索引晨横，所有被掃描的行都被鎖上。
用了索引箫柳，所有被掃描的索引記錄也都是鎖上手形。
只要被掃描，都會被鎖上悯恍。
因?yàn)橛兴饕饪罚钥梢灾苯佣ㄎ唬?而不用全表掃描，只需要掃描相關(guān)的索引記錄就行了（可能會掃描多行涮毫，鎖定多行）瞬欧。如果是唯一性索引，則只需要掃描一行就行了罢防。
因?yàn)闆]有索引艘虎，必須要全表掃描，所以表中所有行都被鎖定咒吐。

index-record locks 僅鎖定索引記錄（索引文件中的）
Gap locks僅鎖定間隙（索引文件中）
next-key locks：
index-record locks和Gap locks的結(jié)合野建，同時鎖定索引記錄和索引記錄間隙属划。
如果沒有索引，則全表掃描候生，鎖住表中所有記錄同眯。

鎖開銷

https://keithlan.github.io/2017/06/05/innodb_locks_show_engine/
鎖10條記錄和鎖1條記錄的開銷是成正比的嗎？

由于鎖的內(nèi)存對象針對的是頁而不是記錄唯鸭，所以開銷并不是非常大
鎖10條記錄和鎖1條記錄的內(nèi)存開銷都是一樣的须蜗，都是heap size=1136個字節(jié)

2019-07-28 InnoDB鎖和索引？索引和執(zhí)行計(jì)劃？

2019-07-28 InnoDB鎖和索引送浊？索引和執(zhí)行計(jì)劃？

Innodb行級鎖都是基于索引的瘤泪？

Innodb鎖

文檔中的 index records 是啥铐料？

1. InnoDB鎖和索引的關(guān)系？

索引文件和記錄文件，兩份文件宇植，分開存儲

1. InnoDB鎖和索引的關(guān)系忙上？舉例子

insert

update

delete

總結(jié)：驗(yàn)證了 repeatable-read isolation level下的設(shè)置的鎖

鎖開銷

2019-07-28 InnoDB鎖和索引送浊？索引和執(zhí)行計(jì)劃？

Innodb行級鎖都是基于索引的瘤泪？

Innodb鎖

文檔中的 index records 是啥铐料？

1. InnoDB鎖和索引的關(guān)系？

索引文件和記錄文件，兩份文件宇植， 分開存儲

1. InnoDB鎖和索引的關(guān)系忙上？舉例子

insert

update

delete

總結(jié)： 驗(yàn)證了 repeatable-read isolation level下的設(shè)置的鎖

鎖開銷

索引文件和記錄文件，兩份文件宇植，分開存儲

總結(jié)：驗(yàn)證了 repeatable-read isolation level下的設(shè)置的鎖