Mysql X锁，S锁、间隙锁

1，Mysql X锁和S锁。

1）概念：利用数据库本身提供的锁机制(行级锁)来实现，锁定该行数据。
trx_rows_locked：事务锁住的行数（不是准确数字）。Approximate number or rows locked by this transaction. The value might include delete-marked rows that are physically present but not visible to the transaction.

image.png
2）InnoDB的锁trx_tables_locked是行锁。『trx_tables_locked: 1』 are row locks，the tables can usually still be read from and written to by multiple transactions, despite some rows being locked。
InnoDB的行锁是针对索引加的锁。只有在通过where检索条件使用索引时，才使用行级锁，否则使用表锁！
3）for update排他锁没有线程对该结果集中的任何行数据使用排他锁或共享锁，否则申请会阻塞。select * from account where id > 8 and id <> 10 for update;(会对查询结果集中每行数据都添加排他锁)
image.png
image.png 经测试，id=8的数据在另一事务中可以正常更新，repeatable read模式下
由于间隙锁的存在：当插入id = 13的数据时，会block等待锁。lock_data：上界虚记录supremum pseudo-record
4）mysql表锁InnoDB行锁是通过给索引上的索引项加锁来实现的，通过索引条件检索使用行锁，否则使用表锁。
当使用update、insert、delete、select… for update时，如果没有指定索引，则InnoDB使用表锁。
image.png
image.png
5）select ... lock in share mode。
为查询语句涉及到的数据加上共享锁，阻塞其他事务修改真实数据。
6）共享锁和排他锁。
共享锁(读锁S锁)：一个事务lock in share mode，其他事务只能读数据不能更新数据
排他锁(写锁X锁)：一个事务for update加上排他锁，其他事务不能对相关数据加其他锁
update、insert、delete默认会加排他锁。
eg：Hibernate中的query.setLockMode("user",LockMode.UPGRADE);，会给生成的sql增加for update语句，加上X锁。

2，行锁和间隙锁实例

1）for update 3行数据，封闭区间，会有4个行锁。

image.png
image.png
image.png
image.png
2）行锁+间隙锁
生成14个行锁，包含id = 15，以及（15，.....)的间隙锁。
image.png
3）基于索引使用范围条件检索数据时，会给符合检索条件的记录的索引项加锁，对于范围内当不存在的记录(称为间隙)加间隙锁Next-Key锁。。

3，行锁和表锁

1）Innodb的行锁是针对索引加的锁，当检索条件未使用索引时，升级为表锁。
当大量对一张表使用行锁时(需要扫描表中大多数数据)，行锁将升级为表锁。此时explain sql语句，其实是未使用索引的。
2）行锁：开销大、加锁慢、有可能会出现死锁；锁冲突第，并发能力强。
3）mysql innodb对insert、update、delete涉及到的数据，默认增加X(排他锁)
4）innodb select默认不加任何锁。手动指定lock in share mode 共享锁、for update 排他锁

4，mysql两阶段锁。

1）一个事务中，将加锁和解锁分为两个结算。
需要把最热点的记录，放到最后，可以提高吞吐量。

image.png

2，间隙锁、行锁、next-key锁

1）行锁: record lock，记录锁。
间隙锁(辅助索引时)：已(主键列，辅助索引列)为间隙点，两个间隙点之间的数据区域加锁。
next-key锁：包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身，InnoDB默认加锁方式是next-key 锁。
2）InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
任何辅助索引上的锁，或者非索引列上的锁，最终都要回溯到主键上，在主键上也要加一把锁。

image.png
3）间隙锁的目的
防止幻读、防止间隙内有新数据插入、防止已存在的数据更新为间隙内的数据。

3，demo

1）基于主键的锁

image.png
image.png
2）基于二级索引的锁
此时使用next-key锁。即(1,2)~(3,5)间隙锁1，(4,5)~(5,7)间隙锁2，以及number=5的两个记录锁
image.png
insert into tb_number values(2, 5);会放入间隙(1,2)~(3,5)中，存在间隙锁，阻塞。
insert into tb_number values(20, 5);会放入间隙(4,5)~(5,7)中(二级索引存的是主键的id，二级索引会加锁，number = 5，会找到索引位置，插入记录(20,5)，存在间隙锁，阻塞。
insert into tb_number values(2, 1); 成功。记录(2,1)根据二级索引，应该放入间隙(1,2)之前，所以不在间隙锁内。
3）select * from tb_number where number = 13 for update;
不存在number = 13，所以，最左边取number=9作为左区间，间隙锁的范围是，(6, 9)~无穷大
insert into tb_number values(7, 11);阻塞
insert into tb_number values(8, 17);阻塞
insert into tb_number values(9, 1); 成功