避免没加索引会锁全表
可以将 MySQL 里的 sql_safe_updates
参数设置为 1,开启安全更新模式。
update 语句必须满足如下条件之一才能执行成功:
-
使用 where,并且 where 条件中必须有索引列;
-
使用 limit;
-
同时使用 where 和 limit,此时 where 条件中可以没有索引列;
delete 语句必须满足以下条件能执行成功:
-
同时使用 where 和 limit,此时 where 条件中可以没有索引列;
如果 where 条件带上了索引列,但是优化器最终扫描选择的是全表,而不是索引的话,我们可以使用 force index([index_name])
可以告诉优化器使用哪个索引,以此避免有几率锁全表带来的隐患。
Insert 语句是怎么加行级锁的?
Insert 语句在正常执行时是不会生成锁结构的,它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。
隐式锁:当事务需要加锁的时,如果这个锁不可能发生冲突,InnoDB会跳过加锁环节,这种机制称为隐式锁。隐式锁是 InnoDB 实现的一种延迟加锁机制,其特点是只有在可能发生冲突时才加锁,从而减少了锁的数量,提高了系统整体性能。
隐式锁就是在 Insert 过程中不加锁,只有在特殊情况下,才会将隐式锁转换为显示锁,这里我们列举两个场景。
如果记录之间加有间隙锁(不存在时?),为了避免幻读,此时是不能插入记录的;
【有间隙锁就会生成插入意向锁(LOCK_MODE: X,INSERT_INTENTION),锁的状态设置为等待状态(意味着事务 B 并没有成功获取到插入意向锁),现象就是insert语句阻塞】
如果 Insert 的记录和已有记录存在唯一键冲突,此时也不能插入记录(对于这条记录加上了 S 型的锁);
如果是主键索引(已存在了):添加S型的记录锁
如果是二级唯一索引的话:添加 S 型 next-key 锁,原本的记录的「隐式锁」会变「显示锁」且锁类型为 X 型的记录锁
【锁的状态是各个事务共享的,因为锁是加在索引上的】
避免死锁
-
问题产生:
幂等性校验不能订单号重复时,select …… for update;导致生成了间隙锁。在insert的时候就会导致生成插入意向锁。
死锁的四个必要条件:互斥、占有且等待、不可强占用、循环等待。
-
事后解决死锁:
设置死锁超时时间:当一个事务的等待时间超过该值后,就对这个事务进行回滚,于是锁就释放了,另一个事务就可以继续执行了。在 InnoDB 中,参数
innodb_lock_wait_timeout
是用来设置超时时间的,默认值时 50 秒。设置自动检测死锁:主动死锁检测在发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数
innodb_deadlock_detect
设置为 on,表示开启这个逻辑,默认就开启。
事前预防:
可以设置唯一性索引【刚好业务匹配】
通过唯一性索引来校验的话就不会insert阻塞,只是会报错(id重复)。【唯一索引还是会插入间隙锁,只不过多了唯一性】
因为通过唯一性索引判断幂等就不需要用for update来加next-key-lock了。