MySQL如何实现行级锁 FOR UPDATE与LOCK IN SHARE MODE-mysql教程-PHP中文网

MySQL如何实现行级锁 FOR UPDATE与LOCK IN SHARE MODE

裘德小鎮的故事

发布： 2025-06-28 22:31:01

原创

602人浏览过

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

行级锁在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;

登录后复制