MySQL的行锁和表锁机制

一、引言

​ 在数据库管理系统中，锁是用来控制对数据的访问的机制。MySQL 既支持行级锁（Row-level Locking），也支持表级锁（Table-level Locking）。

​ MySQL引擎中，MyISAM不支持行锁，而InnoDB支持行锁和表锁。

二、行锁（Row-level Locking）

​ 行锁是指对数据表中的一行记录进行锁定，其他事务需要等待该锁释放才能访问被锁定的行。行锁可以提高并发性，不同的事务可以同时锁定不同的行，从而避免了对整个表的锁定。

• 优点

  • 高并发性：不同事务可以同时锁定不同行，从而提高了数据库的并发性能。
  • 精确控制： 只锁定需要操作的行，避免了无谓的锁竞争，降低了锁冲突的概率。

• 缺点

  • 内存消耗： 行锁需要维护每一行的锁信息，会占用一定的内存空间。
  • 性能开销： 锁管理的细粒度导致了额外的性能开销，例如死锁检测等。
  • 锁竞争： 当大量事务同时访问不同行时，仍然可能出现锁竞争问题。

• 注意：

  1. 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
  2. 两个事务不能锁同一个索引。
  3. insert，delete，update在事务中都会自动默认加上排它锁。

三、表锁（Table-level Locking）

​ 表锁是指对整个数据表进行锁定，当一个事务锁定了表后，其他事务需要等待该锁释放才能访问整个表。表锁是一种较粗粒度的锁。(表共享读锁、表独占写锁)

• 优点

  • 简单： 实现相对简单，不需要维护大量的锁信息。
  • 节省内存： 只需要维护表级的锁信息，节省了内存开销。

• 缺点

  • 低并发性： 表锁限制了并发性，当一个事务锁定了表后，其他事务无法并行访问。
  • 锁竞争： 表级锁导致多个事务之间的锁竞争增加，可能出现更多的死锁问题。
  • 性能瓶颈： 在高并发情况下，表级锁可能成为性能瓶颈，限制了系统的吞吐量。

• 读锁会阻塞写，写锁会阻塞读和写

  • 对MyISAM表的读操作，不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
  • 对MyISAM表的写操作，会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

四、如何选择

在选择行锁还是表锁时，需要根据具体的业务场景来决定：

• 行锁： 适用于高并发、细粒度的操作，能够提供更好的并发性能，但可能会引入更多的锁管理开销。
• 表锁： 适用于简单的操作，能够减少锁管理开销，但并发性能较差。

综合考虑，通常情况下优先选择行锁，只有在特定情况下（例如大量的读操作或简单的数据操作）才考虑使用表锁。

五、如何加锁

​ MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

• 共享锁（读锁）：lock in share mode

  select 字段 from 表名 where 条件 lock in share mode;
  

• 排它锁（写锁）：for update

  select 字段 from 表名 where 条件 for update;
  

参考资料：https://blog.csdn.net/lanzhupi/article/details/109740544；

​ https://blog.csdn.net/xts5701046/article/details/81395958