Mysql锁的快速扫盲

Mysql 各种锁

粒度锁

Mysql 的锁定主要分为全局锁、表锁和行锁

全局锁

1. 读锁（共享锁）：它阻止其他用户更新数据，但允许他们读取数据。保持一段时间内的数据一致性时很有用。
2. 写锁（排他锁）：它阻止其他用户读取和更新数据。在修改大量数据，并不希望其他用户来干扰的这段时间很有用。

使用场景：全库备份、全库导出、全库只读

FLUSH TABLES WITH READ LOCK (FTWRL)
UNLOCK TABLES

• 全局锁的开销非常大，因为会阻止其他所有的数据修改操作，并且在高并发情况下可能导致大量的线程等待锁定。因此应该避免在生产环境中使用全局锁，或者尽量减少全局所的持有时间

表级锁

1. 表共享读锁（Table Read Lock）
2. 表独占写锁（Table Write Lock）

在 Mysql 中，对 MyISAM 表的读操作，会自动加上读锁，写亦是如此

适用于读多写少，并发争用不是很激烈

使用场景：读密集型应用、写操作不频繁、数据量不大、全表更新或删除

开销较小但是锁的粒度大，并发能力低，不适合高并发

哪些命令会发生表级锁

1. ALTER TABLE：当对表结构进行修改时，如添加、删除列或者索引，或者更改表的存储引擎等操作，MySQL会对整张表施加表级锁。
2. LOCK TABLES：这是一个显式的加锁命令，可以通过指定READ或WRITE模式来对表进行加锁。例如，LOCK TABLES t1 READ会对表t1施加共享锁，而LOCK TABLES t1 WRITE则会施加排他锁。
3. REPAIR TABLE：当执行表修复操作时，MySQL会对表施加锁，以防止在修复过程中数据被修改。
4. OPTIMIZE TABLE：执行表优化操作时，MySQL也会对表施加锁，以确保在优化过程中表的数据不会被更改。
5. DELETE、INSERT、UPDATE：虽然这些操作通常与行级锁相关，但在某些情况下，如果存储引擎不支持行级锁，或者操作涉及到全表扫描，MySQL可能会对表施加表级锁。
6. SELECT … FOR UPDATE 和 SELECT … LOCK IN SHARE MODE：这两个查询提示可以用于显式地对查询结果施加行级锁或共享锁，但如果查询条件没有使用索引，或者存储引擎不支持行级锁，MySQL可能会回退到使用表级锁。

风险点

1. 性能下降
2. 并发能力差
3. 可能导致锁等待和超时
4. 写操作影响大
5. 死锁的可能性：多标操作中，如果没有按照一定的顺序获得锁，可能会导致死锁

为了避免这些问题，通常会选择 InnoDB 存储引擎，它主要使用行级锁，可以提供更好的并发性能，一定程度上较少了锁争用的问题，而且还支持事物，可以保证数据的一致性和完整性。

行锁

单行锁定，粒度小，更好的并发性能和更少的锁冲突。

需要更多的内存和 CPU 资源，因为每一行都需要管理。

1. 共享锁（S 锁）
2. 排他锁（X 锁）

实际使用中，InnoDB 还提供了一种间隙锁的特性。

值得注意的是，行级锁只在事务中有效。

使用场景

• 高并发读写
• 单行操作
• 短期锁
• 实现并发控制
• 复杂事务处理

哪些命令会发生表级锁

1. SELECT … FOR UPDATE: 这个命令用于锁定查询结果中的行，以便进行更新操作。它对查询到的行加上排他锁（X锁），阻止其他事务对这些行进行修改或加排他锁，直到当前事务完成（提交或回滚）。

   SELECT * FROM table_name WHERE condition FOR UPDATE;
   

2. SELECT … LOCK IN SHARE MODE: 这个命令用于锁定查询结果中的行，以便进行共享读取操作。它对查询到的行加上共享锁（S锁），阻止其他事务对这些行加排他锁，但允许其他事务对这些行加共享锁。

   SELECT * FROM table_name WHERE condition LOCK IN SHARE MODE;
   

3. UPDATE: 当执行更新操作时，InnoDB会对涉及的行加上排他锁（X锁），防止其他事务对这些行进行修改。

   UPDATE table_name SET column_name = value WHERE condition;
   

4. DELETE: 删除操作也会对涉及的行加上排他锁（X锁），确保删除的行不能被其他事务修改。

   DELETE FROM table_name WHERE condition;
   

5. INSERT: 在执行插入操作时，如果插入的行是基于索引的，InnoDB会对这些行加上排他锁（X锁）。

   INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
   

6. REPLACE: REPLACE命令类似于INSERT，但如果插入的行的索引键已存在，则先删除旧行再插入新行，这个过程中会对涉及的行加上排他锁（X锁）。

   复制
   REPLACE INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
   

风险点

1. 锁升级
2. 死锁
3. 锁等待
4. 资源消耗
5. 难以调试和排查
6. 事务隔离级别

乐观锁和悲观锁

乐观锁

在表中加一个关于版本的字段，可以是时间戳也可以是版本号，在做修改操作的时候，对比版本号是否一致

使用场景

• 低冲突环境
• 读多写少
• 短事务操作
• 分布式系统：网络延迟
• 互联网应用：多数是读取，修改同一数据几率较小

缺点

• 冲突检测：在高并发的环境中，乐观锁可能会导致大量的冲突。因为乐观锁只有提交事务时才检查是否有冲突，如果多个事物同一时间操作同一行，只有一个事务能提交成功，其他的事务都要大量的回滚并且重新尝试
• 处理开销：大量的回滚和重试，这会增加系统的开销，导致性能下降
• 版本管理：乐观锁通常通过版本号来检测冲突，这要求系统能正确地管理这些版本号
• 编程复杂性：更复杂的编程

悲观锁

为了保证数据的完整性和一致性，每次在读写数据时都会先加锁，避免其他事务惊醒并发的读写操作

使用场景：

• 写操作多的场景：预先加锁确保数据的完整性和一致性
• 并发冲突高的场景
• 有特务需要强一致的场景

值得注意的是，悲观锁也可能引入死锁的问题和锁定过程中事务长时间等待而影响性能。

SELECT ... FOR UODATE
SELETE ... LOCK IN SHARE MODE

缺点：

• 性能开销
• 并发度降低
• 死锁
• 锁超时