【SQL】锁机制

• 锁的不同角度分类
• • 从数据操作的类型划分：读锁，写锁
• 从数据操作的粒度划分：表级锁，页级锁，行锁
• • 表锁
  • • 意向锁（intention lock）
    • 自增锁（AUTO-INC锁）
    • 元数据锁（MDL锁）
  • 行锁
  • • 记录锁（Record Locks）
    • 间隙锁（Gap Locks）
    • 临键锁（Next-Key Locks）
    • 插入意向锁（Insert Intention Locks）
  • 页锁
• 从对待锁的态度划分:乐观锁、悲观锁
• • 悲观锁
  • 乐观锁
  • 两种锁的适用场景
• 按加锁的方式划分：显式锁、隐式锁
• • 隐式锁
• 其它锁之：全局锁

# 查看锁信息
select * from performance_schema.data_lock_waits;

锁的不同角度分类

从数据操作的类型划分：读锁，写锁

读锁：也称为共享锁、英文用S 表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。
写锁：也称为排他锁、英文用X 表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

读时可以加共享锁也可以加排他锁；
写时只能加排他锁；

开启共享锁S：

begin;
select * from account lock in share mode;
# ...
commit;

开启排他锁X：

begin;
select * from account for update;
# ...
commit;

S锁与S锁相互兼容
S锁与X锁不兼容
X锁与X锁不兼容

从数据操作的粒度划分：表级锁，页级锁，行锁

表锁

可以很好的避免死锁。
innodb支持行级锁，因此通常不会选择表级锁。但在一些场景中，如在其他会话中改变表结构，对表执行ddl操作，也会发生阻塞，这时用到server层提供的元数据锁结构（英文名： Metadata Locks ，简称MDL）。

LOCK TABLES t READ ;	# 加表级别的S锁。
LOCK TABLES t WRITE ;	# 加表级别的X锁。

意向锁（intention lock）

在数据表的场景中，如果我们给某一行数据加上了排它锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排它锁了，相当于做了标记。
在行上加行级锁X时，会自动在表级别加上意向锁IX
意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）
意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

– 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;
– 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;

自增锁（AUTO-INC锁）

在使用MySQL过程中，我们可以为表的某个列（如id）添加AUTO_INCREMENT 属性。
插入数据的三种方式：
Simple inserts（简单插入）insert into 'teacher' (name) values ('zhangsan'), ('lisi');
Bulk inserts （批量插入） 如INSERT ... SELECT ， REPLACE... SELECT 和LOAD DATA 语
Mixed-mode inserts（混合模式插入） 如INSERT INTO teacher (id,name)VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 只是指定了部分id的值

元数据锁（MDL锁）

当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

行锁

针对innodb，行锁只在存储引擎层实现，锁定力度小，发生所冲突概率低，可以实现的并发度高，但锁的开销较大，加锁比较忙，容易出现死锁情况。

innodb与myisam最大的不同：事务和行级锁。

记录锁（Record Locks）

仅把一条记录锁上，比如我们把id值为8的那条记录加一个记录锁，仅仅是锁住了id值为8的记录，对周围的数据没有影响。

间隙锁（Gap Locks）

可以解决幻读问题，比如，id 1 3 8 10 12 20 ，在3 8 之间，给8加上间隙锁，意味着不允许别的事务在id值为8的记录前边的间隙插入新记录。比如，有另外一个事务再想插入一条id值为4的新记录，它定位到该条新记录的下一条记录的id值为8，而这条记录上又有一个gap锁，所以就会阻塞插入操作，直到拥有这个gap锁的事务提交了之后，id列的值在区间(3, 8)中的新记录才可以被插入。
共享gap锁和独占gap锁起的作用是相同的。

# session1:
begin;
select * from student where id = 5 lock in share mode;	# 这里id不一定 =5，只要在区间内的都可以
# session2:
begin;
select * from student where id = 5 for update;

这里session2并不会被堵住，因为表里没有id = 5这个记录，因此session1加的是间隙锁，而session2也是在这个间隙加的间隙锁，它们有共同的目标，保护这个间隙，不允许插入值。它们之间是不冲突的。

id = 5 在区间（3，8）之内，在session1或session2未commit时，如果在区间(3,8)内insert一条记录：

begin;
insert into student(id,name,class) values (6,'tom','三班');

此时间隙锁起了作用，语句不会往下执行，直到session1和session2提交。

再比如，给id = 20 后面加上一个间隙锁：

select * from student where id = 25 lock in share mode;

此时，20往后都被锁住，区间是（20，+∞），20之前的不受影响。

间隙锁有可能导致死锁。
比如，两个会话同时在（3，8）区间内开启间隙锁，接下来在会话1中插入一条id=5的记录（阻塞），在会话2中插入一条id=6的记录，我们设想它会阻塞，但是实际执行时会报错：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction，这就是发生了死锁，两个会话都在等对方释放资源，形成了一个死扣。但是我们发现，发生了死锁却没有一直僵持，可以看到会话1此时执行成功了，这就涉及到MySQL的死锁机制问题。
当出现死锁以后，有两种策略：

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout 来设置。
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务（将持有最少行级排他锁的事务进行回滚），让其他事务得以继续执行。将参数innodb_deadlock_detect 设置为on ，表示开启这个逻辑。

临键锁（Next-Key Locks）

记录锁+间隙锁，在锁住某条记录同时，又阻止其他事务在该记录前边的间隙插入新记录。上边的例子也能感受到临键锁兼顾记录锁和间隙锁的特征。

begin;
select * from student where id <=15 and id >8 for update; # id=15加了X锁

插入意向锁（Insert Intention Locks）

我们说一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了gap锁（ next-key锁也包含gap锁），如果有的话，插入操作需要等待，直到拥有gap锁的那个事务提交。但是InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。
插入意向锁不是意向锁，意向锁是表级锁，插入意向锁是针对表中几行数据产生的行为，是gap锁。
有以下场景：
id 1 3 5 7 8 15 20

# session 1
begin;
select * from student where id = 12 for update;		# id=12加了间隙锁X锁
# session 2 
begin;
insert into student(id,name,class) values(11,'tim','一班');	# 因间隙锁的存在，语句被阻塞，同时给阻塞的结构上一个插入意向锁
# session 3
begin;
insert into student(id,name,class) values(12,'marry','一班');	# 因间隙锁的存在，语句被阻塞，同时给阻塞的结构上一个插入意向锁
# 此时，session2 与session3的锁是兼容的# 这时释放session1的X锁，session2和session3都能执行

页锁

页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

从对待锁的态度划分:乐观锁、悲观锁

乐观锁和悲观锁并不是锁，而是锁的设计思想

悲观锁

悲观锁是一种思想，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。
案例：
商品秒杀过程中，库存量减少，为避免出现超卖的情况，商品表中设置字段quantity表示当前商品的库存量，现id=1001，quantity=100，不使用锁，两个会话同时进行时，会出现数据不同步的情况。

select quantity from items where id = 1001;	# 查出商品库存
insert into orders (item_id) values (1001);	# 库存量>0，则根据商品信息生产订单
update items set quantity = quantity-num where id = 1001;	# 修改商品库存，num表示购买数量

此时在查询库存时加上X锁，之后的操作便不会被干扰。

select quantity from items where id = 1001 for update;	# 查出商品库存
insert into orders (item_id) values (1001);	# 库存量>0，则根据商品信息生产订单
update items set quantity = quantity-num where id = 1001;	# 修改商品库存，num表示购买数量

select … for update 是MySQL中的悲观锁。select … for update语句执行过程中所有扫描的行都会被锁上，因此在查询时必须确保查询的id上面有索引，而不是全表扫描。如果没有索引，会把扫描到的数据全部加锁，这时会影响别的事务操作。悲观锁的使用场景不是很多。

乐观锁

乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。乐观锁适用于读操作多的应用类型，这样可以提高吞吐量。

1. 乐观锁的版本号机制
   在表中设计一个版本字段 version ，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行UPDATE … SET version=version+1 WHERE version=version 。此时如果已经有事务对这条数据进行了更改，修改就不会成功。
2. 乐观锁的时间戳机制
   时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。你能看到乐观锁就是程序员自己控制数据并发操作的权限，基本是通过给数据行增加一个戳（版本号或者时间戳），从而证明当前拿到的数据是否最新。

两种锁的适用场景

1. 乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现， 不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
2. 悲观锁适合写操作多的场景，因为写的操作具有排它性。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止读 - 写和写 - 写的冲突。

按加锁的方式划分：显式锁、隐式锁

隐式锁

防止别的事务在当前事务没有结束的情况下访问，导致并发问题。隐式锁是一种延迟加锁的机制，从而来减少加锁的数量。
insert时，通过隐式锁结构来保护这条新插入的记录在本事务提交前不被别的事务访问。（回顾：插入意向锁是在间隙锁的基础上想要insert时的一种锁结构）

其它锁之：全局锁

全局锁就是对整个数据库实例加锁。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用场景是：做全库逻辑备份。

Flush tables with read lock;