MySQL行级锁实现方式：FOR UPDATE与LOCK IN SHARE MODE详解

MySQL中的行级锁通过FOR UPDATE和LOCK IN SHARE MODE实现，1.FOR UPDATE用于排他锁，防止其他事务修改数据，适用于并发更新控制和原子操作，需注意性能、死锁、索引依赖和超时设置；2.LOCK IN SHARE MODE用于共享锁，允许多个事务读取但阻止修改，适用于一致性读和报表生成，需注意并发读、修改限制和死锁问题；3.避免死锁策略包括统一加锁顺序、减少锁持有时间、使用低隔离级别、避免交叉更新及NOWAIT/SKIP LOCKED选项；4.不同隔离级别影响FOR UPDATE锁定范围，REPEATABLE READ锁定所有查询行，READ COMMITTED仅锁定修改行；5.可通过Performance Schema监控行级锁状态；6.行级锁相比表级锁具有更高并发性但开销更大，应根据应用场景选择合适的锁粒度。

行级锁在MySQL中主要通过FOR UPDATE和LOCK IN SHARE MODE来实现，目的是为了在并发环境下保证数据的一致性和完整性。FOR UPDATE用于排他锁，确保只有一个事务可以修改某一行数据；LOCK IN SHARE MODE用于共享锁，允许多个事务读取同一行数据，但阻止任何事务修改。

如何使用FOR UPDATE和LOCK IN SHARE MODE？

FOR UPDATE的使用场景和注意事项

FOR UPDATE是一种悲观锁机制，它会在事务开始时锁定选定的行，直到事务结束才释放锁。

使用场景：

• 防止并发更新： 当多个用户同时尝试更新同一行数据时，FOR UPDATE可以确保只有一个用户成功，其他用户需要等待锁释放。
• 实现原子操作： 例如，在库存系统中，先查询库存，然后减少库存，这两个操作需要原子性保证，FOR UPDATE可以防止其他事务在查询和更新之间修改库存。

示例：

START TRANSACTION;
SELECT quantity FROM products WHERE product_id = 1 FOR UPDATE;
-- 检查库存是否足够
IF quantity > 0 THEN
    UPDATE products SET quantity = quantity - 1 WHERE product_id = 1;
    COMMIT;
ELSE
    ROLLBACK;
END IF;

注意事项：

• 性能影响： FOR UPDATE会阻塞其他事务对锁定的行的访问，可能降低并发性能。
• 死锁风险： 如果多个事务以不同的顺序锁定资源，可能会导致死锁。需要仔细设计事务逻辑，避免循环依赖。
• 索引依赖： FOR UPDATE必须依赖索引才能工作。如果没有索引，MySQL会锁定整个表，这会严重影响性能。
• 锁等待超时： 可以通过innodb_lock_wait_timeout参数设置锁等待超时时间，避免事务长时间阻塞。

LOCK IN SHARE MODE的使用场景和注意事项

LOCK IN SHARE MODE允许事务获取共享锁，多个事务可以同时读取同一行数据，但阻止其他事务修改或删除这些行。

使用场景：

• 数据一致性读取： 当需要确保在读取数据的过程中，数据不会被其他事务修改时，可以使用LOCK IN SHARE MODE。
• 生成报表： 在生成报表时，需要确保数据在报表生成期间保持不变。

示例：

START TRANSACTION;
SELECT * FROM products WHERE category = 'Electronics' LOCK IN SHARE MODE;
-- 生成报表
COMMIT;

注意事项：

• 并发读取： LOCK IN SHARE MODE允许多个事务并发读取数据，提高了并发性能。
• 防止修改： 其他事务不能修改或删除被锁定的行，但可以插入新的行。
• 锁释放： 共享锁在事务结束时释放。
• 死锁： 虽然LOCK IN SHARE MODE主要用于读取，但仍然可能与其他类型的锁发生死锁。

如何避免死锁？

死锁是使用行级锁时常见的问题，以下是一些避免死锁的策略：

• 统一加锁顺序： 确保所有事务以相同的顺序锁定资源。
• 减少锁持有时间： 尽快提交或回滚事务，减少锁的持有时间。
• 使用较低隔离级别： 适当降低事务隔离级别，例如使用读已提交（READ COMMITTED）隔离级别。
• 避免交叉更新： 尽量避免多个事务更新同一批数据。
• 使用NOWAIT或SKIP LOCKED： MySQL 8.0及以上版本支持NOWAIT和SKIP LOCKED选项，可以避免事务无限期等待锁。

FOR UPDATE在不同隔离级别下的行为差异

事务隔离级别会影响FOR UPDATE的行为。在可重复读（REPEATABLE READ）隔离级别下，FOR UPDATE会锁定所有被查询到的行，即使这些行没有被修改。而在读已提交（READ COMMITTED）隔离级别下，FOR UPDATE只会锁定实际被修改的行。

如何监控行级锁？

可以使用MySQL的性能模式（Performance Schema）来监控行级锁。通过查询performance_schema.data_locks和performance_schema.data_lock_waits表，可以了解当前系统中哪些事务正在持有锁，哪些事务正在等待锁。

例如，查询正在等待锁的事务：

SELECT
  waiting_trx.trx_id AS waiting_trx_id,
  waiting_trx.trx_mysql_thread_id AS waiting_thread,
  waiting_trx.trx_query AS waiting_query,
  blocking_trx.trx_id AS blocking_trx_id,
  blocking_trx.trx_mysql_thread_id AS blocking_thread,
  blocking_trx.trx_query AS blocking_query
FROM performance_schema.data_lock_waits dlw
INNER JOIN information_schema.innodb_trx waiting_trx
ON dlw.requesting_engine_transaction_id = waiting_trx.trx_id
INNER JOIN information_schema.innodb_trx blocking_trx
ON dlw.blocking_engine_transaction_id = blocking_trx.trx_id;

行级锁与表级锁的比较

行级锁和表级锁是两种不同的锁定粒度。行级锁只锁定需要修改的行，并发性能较高，但开销也较大。表级锁锁定整个表，开销较小，但并发性能较低。在选择锁定粒度时，需要根据具体的应用场景进行权衡。通常情况下，行级锁适用于并发较高的场景，而表级锁适用于并发较低的场景。