锁

共享锁

S锁，也叫读锁，在查询时加的一种锁，读锁与读锁之间不会阻塞。

select ... lock in share mode

排他锁

X锁，也叫写锁，写锁之间以及写锁与读锁之间无法共存，会彼此阻塞。

select ... for update

表锁

  • 表加共享锁时，表内行仍可加共享锁
  • 表加写锁时，表内行不可加任何锁

在加表锁时要确定表内的行当前没有加排斥的锁，为了防止遍历，在加行锁时会在表上加一个意向锁，即行读锁会加表意向读锁，行写锁会加表意向写锁，这样加表锁时只需要看有无意向锁即可。

表级AUTO_INC锁

  • 对于自增列，插入时会自动添加表级AUTO_INC锁，获取对应自增值，插入后释放该锁。
  • 对于自增列，插入时会自动添加轻量级锁，获取到对应自增值后释放该锁，不用等插入执行完毕再释放

行级锁

通过给索引上锁，由于记录更新时需要同步更新索引，而由于索引被锁，因此可以产生阻塞的作用。因此行级锁的使用必须是使用索引才行。

记录锁

对单独的一条记录加锁；当能够根据唯一索引确定到单独的一条记录时，只会进行加记录锁。

  1. insert into (...) values (...) where id = 1;
  2. insert into (...) values (...) where id = 2;

对于以上并发执行时，不会产生阻塞，因为两条语句均通过主键id确定的唯一一条记录，因此只会加记录锁，不会进行阻塞操作。

间隙锁

锁定一个范围，但不含记录本身。

insert into (...) values (...) where id > 2;
insert into (...) values (...) where id = 3;
insert into (...) values (...) where id = 1;

对于以上两条语句，第一条语句会将所有id>2这个范围进行加锁，因此第二条语句会被阻塞。

第三条语句不在范围锁的范围内，因此不会被阻塞。

Next-Key锁

记录锁+间隙锁。

create table test(a int , b int , primary key(a) , key(b))
insert into test select 1,1
insert into test select 3,1
insert into test select 5,3
insert into test select 7,6
insert into test select 10,8

对于select * from test where b = 3 for update而言，聚集索引a和辅助索引b均需要进行加锁锁定。

由于b=3对应的记录的主键a=5，因此主键索引加的是记录锁，即a=5的记录锁。

辅助索引b的上下索引值分别为1和6，因此其范围锁为（1，3）（3，6）两个范围锁。

因此对以下的语句有

insert into test select 4,2 
insert into test select 5,6
insert into test select 6,5
insert into test select 8,6
  1. b = 2∈(1,3)因此会被阻塞
  2. a=5被阻塞
  3. b=5∈(3,6)因此会被阻塞
  4. 不会被阻塞

幻读

select ... where a > 2 for update

上面的语句将手动加范围X锁，要插入时需要获取X锁，因此会被阻塞，因此可以阻止幻读。

在实际的使用时，默认使用可重复读的隔离级别，读取的数据均为事物开始时的数据，因此不会产生幻读。

锁升级

其他数据库的锁加载记录上，当记录很多时，可以存在大量的锁对象，开销很大，因此此时会锁升级为表级锁，降低锁对象带来的开销。

innodb的行级锁并不加载记录上，而是记录在内存页上，通过位图记录，因此多个锁和单个锁带来的开销是相同的，因此不存在锁升级的情况。

正常读不加锁，使用mvcc

主键扫描显式加锁

  • 读已提交：扫描时对扫描到的记录加行锁，如果不符合，释放锁，如果符合，不释放行锁

  • 可重复读：扫描时对扫描到的记录加next key lock，无论符不符合都不释放锁

    二级索引扫描显式加锁

  • 读已提交：扫描二级索引时，对扫描到的二级索引记录加行锁，如果不符合，释放锁，如果符合，回表根据主键查聚簇索引，并对该聚簇记录加锁，如果符合不释放，不符合则释放。

  • 可重复读：扫描二级索引时，对扫描到的二级索引记录加next key lock，无论符不符合都不释放锁，如果符合，回表根据主键查聚簇索引，并对该聚簇记录加next key lock，无论符不符合都不释放锁。

更新加锁与查询类似，但如果更新了索引列，二级索引更新时要加记录写锁
插入加锁一般没有显式加锁，插入前会先定位到位置，如果存在gap锁会阻塞

show eninge innodb status查看当前系统各个事务加锁情况

MySQL · 引擎特性 · InnoDB 事务锁系统简介
MySQL · 引擎特性 · InnoDB 事务锁系统简介.pdf