MySql的锁有哪些?

MySql的锁可以分为三部分

• 全局锁(一旦使用数据库就处于只读的状态)
  • 主要用来进行 全库逻辑备份
  • 加上全局锁的缺点是因为是只读状态，可能会导致业务只能读数据，从而导致业务停滞。
• 表级锁
  • 表锁
    • 一旦实现，本线程就只能读本线程所在的表，不能访问其他的线程，其他线程的只能读该线程所在的表。

    1 2 3 4 5 6

    //表级别的共享锁，也就是读锁； //允许当前会话读取被锁定的表，但阻止其他会话对这些表进行写操作。 lock tables t_student read; //表级别的独占锁，也就是写锁； //允许当前会话对表进行读写操作，但阻止其他会话对这些表进行任何操作（读或写）。 lock tables t_stuent write;

  • 元数据锁(MDL)
    • 分为MDL读锁和MDL写锁(都是数据库自动加的MDL)对一张表进行CRUD操作时，加的是MDL读锁，对一张表做结构变更操作的时候，加的是MDL写锁。
    • MDL在事务执行期间，MDL是一直持有的。
    • MDL写锁获取优先级高于MDL读锁。
  • 意向锁
    • 意向锁简单来说就是提前说明记录是否存在共享锁和独占锁。
    • 意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享锁和独占表锁发生冲突。
    • 意向锁的目的就是为了判断表里是否有记录被加锁。
  • AUTO-INC锁
    • AUTO-INC锁的作用就是自动增加主键的值。
    • 锁不是在一个事务提交后才释放，而是在执行完插入语句后就会立即释放。
    • 在插入数据时，会加一个表级别的AUTO-INC锁。
    • 从MySql5.1.22版本开始，InnoDB存储引擎提供了一种轻量级的锁来实现自增。
    • 对于解决InnoDB的数据库一致性问题，可以采用innodb_autoinc_lock_mode = 2，并且binlog_format = row，既能提高并发性，又不会出现数据一致性问题
• 行级锁(基于InnoDB)
  • Record Lock(记录锁)
    • S型记录锁(S型与S型兼容，S型与X型不兼容，X型与X型也不兼容)
    • X型记录锁(S型与S型兼容，S型与X型不兼容，X型与X型也不兼容)
  • Gap Lock(间隙锁)
    • 间隙锁只存在可重复读隔离级别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入缓影记录而提出的。
  • Next-Key Lock(间隙锁加记录锁-临键锁) Next-key lock是包含间隙锁+记录锁的，如果一个事务获取了X型的next-key lock,那么另外一个事务在获取相同范围的X型的间隙锁时，是会被阻塞的。
  • 插入意向锁
    • 插入意向锁不是意向锁，它是一种特殊的间隙锁，属于行级别锁。
    • 一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁，如果有的话，插入操作就会发生阻塞，直到有间隙锁的那个事务提交为止，在此期间会生成一个插入意向锁。

MySql是咋加锁的？

啥SQL语句会加行级锁？

首先对于支持加行级锁的只有InnoDB，而对于InnoDB存储引擎加锁的语句为

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

//对读取的记录加共享锁(S型锁)
select ... lock in share mode;

//对读取的记录加独占锁(X型锁)
select ... for update;

//对操作的记录加独占锁(X型锁)
update table .... where id = 1;

//对操作的记录加独占锁(X型锁)
delete from table where id = 1;

update和delete操作都会加行级锁，且锁的类型都是独占锁(X型锁)

MySql是咋加行级锁？

MySQL行级锁的加锁规则

• 唯一索引等值查询
  • 当查询的记录是存在的，在索引树上定位到这一条记录后，将该记录的索引中的next-key lock会退化成记录锁。
  • 当查询的记录是不存在的，在索引树上找到第一条大于该记录的记录后，将该记录的索引中的next-key lock 会退化成间隙锁。
• 唯一索引范围查询
  • 针对大于等于的范围查询，因为存在等值的查询，那么等值查询的记录存在的话，那么该记录的索引中的next-key锁会退化成记录锁
  • 针对小于或者小于等于的范围查询，要看条件值的记录是否存在于表中:
    • 当条件值得记录不在表中，那么不管是小于还是小于等于条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的next-key锁会退化成间隙锁，其他扫描的记录，都是在这些记录的索引上加next-key锁。
    • 当条件值的记录在表中，如果是小于条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁；如果小于等于条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。

因为存在两个索引，一个是主键索引，一个是非唯一索引(二级索引)，所以在加锁的时候，同时会对这两个索引都加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁。

• 非唯一索引等值查询
  • 当查询的记录存在时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描的二级索引记录加的是next-key锁，而对于第一个不符合条件的二级索引记录，该二级索引的next-key锁会退化成间隙锁，同时，在符合查询条件的记录的主键索引上加记录锁。
  • 当查询的记录不存在时，扫描到第一条不符合条件的二级索引记录，该二级索引的next-key锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。
• 非唯一索引范围查询
  • 该查询进行时，索引的next-key lock 不会有退化为间隙锁和记录锁的情况。
• 没有加索引的查询
  • 如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加next-key锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。
  • 在线上在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了

update没加索引会锁全表？

为啥会发生这种事故？

• 可能是在update语句的where条件没有使用索引，就会全表扫描，于是就会对所有记录加上next-key锁，相当于加的是表锁。(相当于把整个表锁住了)
• 上面的情况为啥是可能，因为决定全表扫描的是MySql中的优化器决定的。

如何避免这种事故？

主观做法

• 一定要为需要经常更新的字段加上索引，且执行update语句必须加上where条件。 客观做法
• 我们可以将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。
• MySQL 里的 sql_safe_updates的原理和作用

1. 防止意外的大规模数据修改
   • 原理：在sql_safe_updates设置为非0值（通常为1）时，MySQL对于UPDATE和DELETE操作有更严格的限制。这是为了避免因错误的WHERE条件或者缺少合适的限制条件而导致大量数据被意外修改或删除。
   • 示例：假设你有一个包含100万条客户记录的customers表。如果sql_safe_updates为0，执行UPDATE customers SET status = 'inactive'这样没有WHERE条件的语句，会将表中所有客户的状态都更新为inactive。但如果sql_safe_updates为1，这个操作就会被阻止，因为它既没有使用键（例如customer_id）来限制更新范围，也没有LIMIT子句。
2. 提高数据安全性和稳定性
   • 对于开发环境：在开发过程中，开发人员可能会频繁地编写和测试UPDATE和DELETE语句。开启sql_safe_updates可以作为一种安全机制，减少因代码错误而对数据造成不可逆损坏的风险。例如，新手开发人员可能在测试UPDATE语句时，忘记添加WHERE条件，有了这个设置可以避免这种错误操作。
   • 对于生产环境：虽然在生产环境中通常会有更严格的代码审查和测试流程，但意外情况仍可能发生。开启sql_safe_updates可以作为额外的一层保护，防止恶意或误操作对重要数据的破坏。比如，防止有人在数据库管理工具中不小心执行了无限制的DELETE语句。
3. 强制使用合适的更新和删除策略
   • 促进正确的SQL编写习惯：要求UPDATE语句在WHERE子句中使用键或者有LIMIT子句，以及DELETE语句同时具备这两个条件，有助于开发人员养成更谨慎、更精确的SQL编写习惯。例如，开发人员会更加注意在UPDATE语句中使用索引列作为WHERE条件，这样不仅能避免大规模的数据修改，还能提高查询性能。
   • 与索引使用的关联：通过强制使用键（索引），间接鼓励开发人员合理利用索引来优化UPDATE和DELETE操作。这与数据库的性能优化是紧密相关的，因为正确使用索引可以减少锁的范围，提高数据库的并发处理能力。

MySQL记录锁+间隙锁可以防止删除操作而导致的吗?

• 可以的，对于MySql的可重复读隔离级别下，针对当前读的语句不会出现幻读的现象，因为MVCC机制，对于在快照读的情况下，next-key锁可以避免幻读的现象，因为锁不让进行插入，删除，更新等操作(该锁是X型独占锁)

MySQL死锁了，咋办？

• 啥是死锁？
  • 根据操作系统的官方定义来讲的话，就是多个进程互相拥有对方的所需的资源，但是都不放弃自己的资源，从而形成循环等待链，形成僵局。
  • 从数据库的角度来看的话就是一个业务或多个业务在进行幂等性校验时，进行快照读，然后互相占有锁，这样就造成了死锁。
• 如何避免死锁？
  • 死锁的四个必要条件:互斥，占有且等待，不可强占用，循环等待。对于这四个条件只要破坏其中任意一个条件就行。
• 设置事务等待锁的超时时间，这个是破坏了占有且等待的条件。
• 开启主动死锁检测，破坏了循环等待的条件。