myqsl 12种锁，提供12个真实业务与流程图，轻松掌握运用场

2024-07-09  本文已影响0人  肖哥弹架构

MySQL的锁设计是为了在多用户环境中管理对数据库数据的并发访问，确保数据的完整性和一致性，提供了大概12种锁，每一种锁对应的业务场景不一样，肖哥带你掌握他的使用技巧。

1、读锁/共享锁（S锁）

流程图

image.png 
SELECT * FROM accounts WHERE user_id = 'user1' LOCK IN SHARE MODE;

2、写锁/排它锁/独占锁（X锁）

流程图

image.png 
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 'user1';
COMMIT;

3、意向锁（Intention Locks）

流程图

image.png

4、自增锁（Auto-increment Locks）

流程图

image.png 
INSERT INTO users (username) VALUES ('new_user');

5、 悲观锁（Pessimistic Locks）

流程图

image.png 
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

6、乐观锁（Optimistic Locks）

流程图

image.png
  1. 初始读取记录 - 应用程序读取记录以及版本号。
SELECT id, data_column, version_column FROM table_name WHERE id = record_id;

这里，data_column 是你想要读取的数据列，version_column 是用于乐观锁的版本号列。

  1. 更新记录 - 应用程序在本地进行更改，并尝试更新数据库中的记录，同时检查版本号是否一致。
UPDATE table_name
SET data_column = new_data_value,
    version_column = version_column + 1
WHERE id = record_id
  AND version_column = original_version_value;

在这个例子中，new_data_value 是应用程序更新后的值，original_version_value 是最初读取的版本号。如果version_column 在尝试更新时没有改变，这条语句会成功更新记录并增加版本号。如果版本号已经改变（意味着另一个事务已经更新了记录），这个更新就不会执行。

  1. 检查更新是否成功 - 应用程序检查更新操作是否影响了预期数量的行。
-- 这通常在应用程序逻辑中处理，例如：
IF (update_affected_rows > 0) THEN
    -- 更新成功，版本号增加
ELSE
    -- 更新失败，版本号未改变，可能需要重新读取记录或抛出异常
END IF;

7、间隙锁（Gap Lock）

流程图

image.png 
SELECT * FROM table_name WHERE column_name BETWEEN val1 AND val2 LOCK IN SHARE MODE;

这个查询会锁定val1和val2之间的所有记录，并且防止其他事务在这个范围内插入新记录。

8、页锁（Page Lock）

流程图

image.png

9、MDL锁（Metadata Lock）

流程图

image.png 
ALTER TABLE table_name ADD COLUMN new_column datatype;

这个操作会隐式地使用MDL锁。

10、RL锁（Record Lock）

流程图

image.png 
SELECT * FROM table_name WHERE id = record_id FOR UPDATE;

这个查询会锁定指定ID的记录，防止其他事务修改它。

11、GL锁（Gap Lock）

流程图

image.png 
SELECT * FROM table_name WHERE column_name > val LOCK IN SHARE MODE;

这个查询会锁定大于val的所有记录，但不包括val本身的记录。

12、NKL锁（Next-Key Lock）

流程图

image.png 
SELECT * FROM table_name WHERE column_name = val FOR UPDATE;

历史热点文章

上一篇 下一篇

猜你喜欢

热点阅读