面试题：MySQL中常见导致阻塞的场景有哪些

锁争用

行锁争用
- 场景：当多个事务同时尝试对同一行数据进行修改操作（如 UPDATE 或 DELETE）时，如果没有合适的隔离级别设置或锁机制协调，就会发生行锁争用。例如，事务A先对某一行数据加了排他锁（写锁）进行更新操作，此时事务B也尝试对同一行数据进行更新，由于排他锁的独占性，事务B就会被阻塞，直到事务A释放锁。
- 示例：

-- 事务A
START TRANSACTION;
UPDATE users SET age = age + 1 WHERE user_id = 1;
-- 事务B
START TRANSACTION;
UPDATE users SET age = age - 1 WHERE user_id = 1;

在上述示例中，如果事务A先执行，事务B就会因为等待事务A释放行锁而被阻塞。 2. 表锁争用

场景：使用 LOCK TABLES 语句手动锁定表，或者某些操作（如 ALTER TABLE）会自动对表加锁。当一个事务锁定了整个表，其他事务想要对该表进行任何读写操作都会被阻塞。例如，一个事务执行 LOCK TABLES my_table WRITE; 后，其他事务的 SELECT、INSERT、UPDATE、DELETE 等操作都无法执行，直到该事务执行 UNLOCK TABLES。
示例：

-- 事务A
START TRANSACTION;
LOCK TABLES my_table WRITE;
INSERT INTO my_table (column1) VALUES ('value1');
-- 事务B
START TRANSACTION;
SELECT * FROM my_table;

事务B的查询会被阻塞，因为事务A持有表的写锁。

长时间运行的事务

未及时提交或回滚
- 场景：如果一个事务长时间执行复杂的操作，比如大量数据的处理或者包含长时间运行的存储过程，且没有及时提交或回滚，那么它持有的锁就不会释放。其他需要获取相同锁的事务就会被阻塞。例如，一个事务需要对一张大表进行逐条数据处理，且没有设置合理的批处理，导致事务长时间运行。
- 示例：

-- 事务A
START TRANSACTION;
-- 这里进行大量复杂的单条数据处理操作
UPDATE big_table SET column1 = 'new_value' WHERE id = 1;
UPDATE big_table SET column1 = 'new_value' WHERE id = 2;
-- 持续进行类似操作，长时间不提交
-- 事务B
START TRANSACTION;
UPDATE big_table SET column2 = 'new_value2' WHERE id = 1;

事务B会因为事务A长时间持有锁而被阻塞。

不当的索引使用

缺失索引
- 场景：如果查询语句中涉及的列没有合适的索引，MySQL可能会进行全表扫描。在全表扫描过程中，可能会对表加锁，导致其他事务无法及时获取锁进行操作。例如，一个频繁执行的 SELECT 语句，条件列没有索引，每次执行都需要扫描全表。当这个查询在事务中执行时，就可能阻塞其他事务对表的操作。
- 示例：

-- 表结构
CREATE TABLE products (id INT, name VARCHAR(255), price DECIMAL(10, 2));
-- 无索引的查询
SELECT * FROM products WHERE name = 'product_name';

如果这个查询在事务中执行，可能会因为全表扫描导致表锁，阻塞其他事务。 2. 索引失效

场景：即使存在索引，但如果查询条件使用不当，索引可能会失效。例如，在索引列上使用函数、类型转换等操作，会使索引失效，从而导致全表扫描和潜在的锁争用。
示例：

-- 表结构
CREATE TABLE orders (order_id INT, order_date DATE, amount DECIMAL(10, 2));
-- 索引列上使用函数导致索引失效
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

这种情况下，索引 order_date 失效，查询可能导致全表扫描，进而引发阻塞。

高并发写入

写入冲突
- 场景：在高并发环境下，大量的写入操作（如 INSERT、UPDATE、DELETE）同时进行，容易导致锁争用。因为这些操作通常需要获取排他锁，多个事务同时请求排他锁就会造成阻塞。例如，在一个电商系统的订单处理模块，高并发时多个订单同时进行状态更新操作。
- 示例：

-- 事务A
START TRANSACTION;
UPDATE orders SET status = 'processed' WHERE order_id = 1;
-- 事务B
START TRANSACTION;
UPDATE orders SET status = 'processed' WHERE order_id = 2;
-- 事务C
START TRANSACTION;
UPDATE orders SET status = 'processed' WHERE order_id = 3;

在高并发情况下，这些事务可能会因为锁争用而相互阻塞。

死锁

相互等待锁
- 场景：两个或多个事务互相持有对方需要的锁，形成循环等待的情况。例如，事务A持有资源R1的锁，并且请求资源R2的锁；同时事务B持有资源R2的锁，并且请求资源R1的锁，这样就形成了死锁。
- 示例：

-- 事务A
START TRANSACTION;
UPDATE table1 SET column1 = 'value1' WHERE id = 1;
UPDATE table2 SET column2 = 'value2' WHERE id = 1;
-- 事务B
START TRANSACTION;
UPDATE table2 SET column2 = 'new_value2' WHERE id = 1;
UPDATE table1 SET column1 = 'new_value1' WHERE id = 1;

如果事务A和事务B按上述顺序执行，就可能发生死锁，导致两个事务都被阻塞。MySQL一般会自动检测并解决死锁，通常是回滚其中一个事务来打破死锁。

星途面试题库

面试题：MySQL中常见导致阻塞的场景有哪些

知识考点

面试题答案

锁争用

长时间运行的事务

不当的索引使用

高并发写入

死锁