MySql什么时候表锁or行锁？

在数据库的世界里，性能优化是一个永恒的话题。MySQL作为广泛使用的数据库之一，其锁机制是保证数据一致性的关键。了解何时使用行锁和表锁，能够帮助我们更有效地进行数据库操作，避免死锁和性能瓶颈。

众所周知，我们都知道 Innodb 有全局锁、表级锁、行级锁三种，但你知道什么时候会用表锁，什么时候会用行锁吗？

1. 对于表级锁而言，当执行 DDL 语句去修改表结构时，会使用表级锁
2. 对于行级锁而言，一般情况下都会默认使用行级锁，貌似是需要有索引匹配到才行

锁的基本概念

在深入讨论行锁与表锁之前，我们先来回顾一下锁的基本概念。锁是数据库管理系统用来控制对数据的并发访问的一种机制。在MySQL中，锁分为共享锁（读锁）和排他锁（写锁）
在MySQL中，锁是用于管理并发访问数据库资源的一种机制，以确保数据的完整性和一致性。锁分为两种基本类型：共享锁（Shared Locks，通常称为读锁）和排他锁（Exclusive Locks，通常称为写锁）

共享锁（读锁）

共享锁是一种允许多个用户同时读取同一数据资源的锁类型。当一个事务对数据行或表持有共享锁时，其他事务也可以获取该数据行或表的共享锁，但无法获取排他锁。共享锁通常用于以下场景：

1. 读取操作：当用户需要从数据库中读取数据时，数据库会为这些数据行或整个表添加共享锁。
2. 非阻塞读取：共享锁允许多个事务同时读取数据，不会阻止其他读取操作，从而提高了并发性能。
3. 隔离级别：在某些事务隔离级别下（如READ COMMITTED），MySQL会使用共享锁来实现非阻塞读取。

排他锁（写锁）

排他锁是一种不允许其他用户同时读取或写入同一数据资源的锁类型。当一个事务对数据行或表持有排他锁时，其他事务既不能获取该数据行或表的共享锁，也不能获取排他锁。排他锁通常用于以下场景：

1. 写入操作：当用户需要向数据库中插入、更新或删除数据时，数据库会为这些数据行或整个表添加排他锁。
2. 数据保护：排他锁确保在数据被修改时，没有其他事务可以访问这些数据，从而防止数据不一致。
3. 隔离级别：在更高的事务隔离级别下（如REPEATABLE READ和SERIALIZABLE），MySQL会使用排他锁来防止脏读、不可重复读和幻读。

锁的兼容性

• 共享锁与共享锁：兼容。多个事务可以同时持有同一数据资源的共享锁。
• 共享锁与排他锁：不兼容。持有共享锁的事务不能获取排他锁，反之亦然。
• 排他锁与排他锁：不兼容。同一数据资源上不能同时存在多个排他锁。

锁的升级和降级

在某些情况下，MySQL可能会自动将共享锁升级为排他锁，或者将排他锁降级为共享锁，以满足事务的需求。例如，如果一个事务持有共享锁并尝试修改数据，MySQL会尝试将共享锁升级为排他锁。
了解共享锁和排他锁的工作原理对于优化数据库性能和设计高效的数据库应用至关重要。正确的锁策略可以减少锁争用，提高并发性能，同时确保数据的一致性和完整性。

全局锁、表锁、行锁

全局锁

全局锁就是对整个数据库实例加锁。 MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。你可以理解为，全局锁基本上把数据所所有的变更语句都锁住了。

全局锁的典型场景应用场景是全库逻辑备份，也就是把整个库每个表都 select 出来存起来。上面说到全局锁会锁住所有变更语句，但这只是对于 MyISAM 存储引擎而言的。对于 Innodb 而言，其可以利用 MVCC 实现数据的一致性视图，从而不需要锁整个库就可以实现全库的数据备份。

表锁

表锁，顾名思义就是对某个表加锁。

表锁是一种粗粒度的锁，它锁定的是整个表。当一个表被锁定时，其他用户无法对该表进行读写操作，直到锁被释放。表锁通常在以下情况下使用：

1. 全表扫描：当执行全表扫描操作时，使用表锁可以减少锁的开销。
2. 批量操作：在进行大量数据的插入、更新或删除操作时，表锁可以减少锁的争用。
3. 低并发环境：在并发较低的环境中，表锁可以简化锁管理，提高操作效率。
   一般情况是对应的存储引擎没有行级锁（例如：MyIASM），或者是对应的 SQL 语句没有匹配到索引。

对于第一种情况而言，因为对应存储引擎不支持行锁，所以只能是使用更粗粒度的锁来实现，这也比较好理解。

对于第二种情况而言，如果存储引擎支持行锁，但对应的 SQL 就没有使用索引，那么此时也是会全表扫描，那此时也是会使用表锁。例如下面的语句没有指定查询列，或者指定了查询列但是并没有用到索引，那么也是会直接锁定整个表。
// 没有指定查询列
selectfrom user;
// 指定查询列，但是没有用到索引
selectfrom user where name = ‘zhangsan’;
上面说的索引，可以说是判断是否会用行级锁的关键

行锁

行锁是MySQL中一种细粒度的锁，它锁定的是数据库表中的一行或多行数据。行锁的优点是它能够提供较高的并发性，因为只锁定需要操作的数据行，而不会影响其他行。行锁通常在以下情况下使用：

1. 事务隔离级别较高：在REPEATABLE READ或SERIALIZABLE隔离级别下，MySQL默认使用行锁。
2. 高并发读写操作：当有大量的读写操作同时进行时，行锁可以减少锁的争用，提高系统性能。
3. 使用索引进行查询：如果查询语句能够利用索引，MySQL会自动使用行锁。

何时使用行锁

1. 高并发读写：在高并发读写的场景下，使用行锁可以减少锁的争用，提高系统的并发处理能力。
2. 细粒度控制：当需要对单个或少数几条记录进行操作时，行锁可以提供更细粒度的控制。
3. 避免锁升级：在某些情况下，如果事务只涉及少量数据行，使用行锁可以避免锁从行锁升级为表锁。

何时使用表锁

1. 全表操作：当执行全表的插入、更新或删除操作时，使用表锁可以减少锁的开销。
2. 批量数据处理：在处理大量数据时，表锁可以简化操作，提高效率。
3. 低并发环境：在并发较低的环境中，使用表锁可以减少锁管理的复杂性。

额外思考：

如果查询或更新用到了索引，但是查询或更新的数据特别多，占全表的 80% 甚至更多，这时候是会用表锁，还是行锁呢？
在MySQL中，即使查询或更新操作使用了索引，如果涉及到的数据量非常大，比如占全表的80%或更多，MySQL的行为可能会取决于几个因素：

1. 事务隔离级别：不同的隔离级别会影响锁的行为。例如，在REPEATABLE READ（MySQL的默认隔离级别）或SERIALIZABLE隔离级别下，MySQL倾向于使用行锁，但在高并发和大量锁定的情况下，可能会出现锁升级，即从行锁变为表锁。
2. 锁的争用和死锁：如果大量的行被锁定，MySQL的锁管理器可能会决定升级为表锁以减少锁的争用和避免死锁。
3. InnoDB的锁策略：InnoDB存储引擎有自己的锁策略，它试图平衡行锁和表锁的使用以优化性能。InnoDB会根据事务的大小和并发情况动态调整锁的行为。
4. 锁的粒度：InnoDB通常优先使用行锁，但如果锁定的行数超过了一定比例（通常认为是表中行数的20%左右），InnoDB可能会将锁升级为表锁。
5. 具体的SQL操作：某些特定的SQL操作，如UPDATE或DELETE涉及大量数据，即使使用索引，也可能触发表锁。
6. 系统配置和版本：MySQL的配置和版本也会影响锁的行为。例如，某些配置参数可以调整锁升级的阈值。
   在实际操作中，如果确实涉及到大量数据的查询或更新，并且对性能有严格要求，建议采取以下措施：

• 优化索引：确保索引设计得当，以减少锁的范围和提高查询效率。
• 分批处理：将大量数据的操作分批进行，每次处理一小部分数据，以减少锁的影响。
• 调整隔离级别：根据业务需求，适当调整事务隔离级别。
• 监控和调优：使用MySQL的监控工具来观察锁的行为，并根据实际情况进行调优。
  总之，MySQL会根据当前的事务情况和系统配置来决定使用行锁还是表锁。如果操作涉及的数据量非常大，即使使用了索引，也可能会使用表锁，特别是当系统认为这样做可以提高性能或减少死锁风险时。