本文将以实验为主，演示共享锁、排他锁的特性，演示行锁升级为表锁。
为进行实验，先建立account表，含有一个主键id
CREATE TABLE account (
id INT (11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) DEFAULT NULL,
balance INT DEFAULT 0,
PRIMARY KEY (id)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO account (name, balance) VALUES ('li', 10);
INSERT INTO account (name, balance) VALUES ('ha', 233);
1、共享锁、也叫读锁、S锁
读锁是共享的，或者说是互相不阻塞的，多个客户在同一时刻可以同时读取同一资源，而互不干扰。
若 事务T 对数据 对象A 加上 S锁 ，其他事务可以对 A 加 S锁 ，但不能加 X锁 ，直到 T 释放 A 上的 S锁。
S锁保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
加读锁：普通的select语句不会加任何锁，加读锁需要显示的加上【LOCK IN SHARE MODE】。
下面通过例子展示读锁的特点。
| 步骤 | 会话 A命令 | balance值 | 会话 B命令 | balance值 |
|---|---|---|---|---|
| 1 | BEGIN; | |||
| 2 | select balance from account where id = 1 LOCK IN SHARE MODE; |
10 | ||
| 3 | select balance from account where id = 1 LOCK IN SHARE MODE; | 10 | ||
| 4 | update account set balance = 666 where id = 1; | 将会被阻塞 | ||
| 5 | COMMIT; | update执行 |
可以发现会话A 对id=1行加读锁之后，会话B任然可以对id=1行加读锁，但是会话B继续对id=1行执行更新操作将会被阻塞。
如果在第5步不是提交事务，而是执行一个更新语句的话，update account set balance = 777 where id = 1，这时候将会发生死锁现象。
2、排他锁、也叫写锁、X锁
写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑。
事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。
X锁保证了其他事务在T释放A上的锁之前不能再读取和修改A。
1、自动加锁： delete / update / insert 语句会自动加上排他锁；
2、手动加排它锁：select * from student where id=1 FOR UPDATE；
| 步骤 | 会话 A命令 | balance值 | 会话 B命令 | balance值 |
|---|---|---|---|---|
| 1 | BEGIN; | |||
| 2 | update account set balance = 0 where id = 1; |
|||
| 3 | select balance from account where id = 1; | 10 | ||
| 4 | select balance from accountwhere id = 2 LOCK IN SHARE MODE; |
不阻塞233 |
||
| 4 | select balance from account where id = 1 LOCK IN SHARE MODE; |
阻塞 |
||
| 5 | COMMIT; | 结果出来0 |
可以发现， update 语句中 where 条件为主键时，是对该行上锁，其他会话想获取该行的读或写锁都将被阻塞，但是其他行不受影响。
3、为什么推荐 MySQL 的 update 语句中 where 条件要有主键索引、唯一索引？
接下来演示一下如果update 语句中 where 条件是没有索引的name字段会怎么样呢。
将会发现，由于name字段没有索引，所以对该行的行锁升级为了表锁，其他行想获取读锁或写锁都将被阻塞。
| 步骤 | 会话 A命令 | balance值 | 会话 B命令 | balance值 |
|---|---|---|---|---|
| 1 | BEGIN; | |||
| 2 | update account set balance = 0 where name = 'li'; |
|||
| 3 | select balance from account where id = 1; | 10 | ||
| 4 | select balance from account where id = 2 LOCK IN SHARE MODE; |
阻塞 |
||
| 5 | Ctrl-c | |||
| 6 | select balance from account where id = 1 LOCK IN SHARE MODE; |
阻塞 |
现在对name 字段新增唯一索引 https://www.cnblogs.com/ShareJia/p/10008110.html
增加唯一索引：CREATE UNIQUE INDEX index_name ON account (NAME);
删除指定索引：DROP INDEX NAME ON account;
查询索引：SHOW INDEX FROM account;
| 步骤 | 会话 A命令 | balance值 | 会话 B命令 | balance值 |
|---|---|---|---|---|
| 1 | BEGIN; | |||
| 2 | update account set balance = 0 where name = 'li'; |
|||
| 3 | select balance from account where id = 1; | 10 | ||
| 4 | select balance from account where id = 2 LOCK IN SHARE MODE; |
10 |
||
| 6 | select balance from accountwhere id = 1 LOCK IN SHARE MODE; |
阻塞 |
会发现，对name 字段添加索引后，仅name为'li'的这一行上了锁。
原理简析：
推荐阅读：
MySQL InnoDB锁原理剖析
如何书写 update 避免表锁
InnoDB行锁是通过给索引上的索引项加锁来实现的。所以，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
4、意向锁
Inno存储引擎支持意向共享锁，意向锁为表级别的锁。设计的主要目的是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁。
1、意向共享锁 IS
2、意向排他锁 IX
意向锁可以优化锁之间的性能。
B站学习视频
相关问题:
为什么表没有索引，表里所有的记录都会被锁住
update age from user无索引是行锁还是表锁，为什么
