-- 示例：對uid=8的行加排他鎖
SELECT * FROM student WHERE uid = 8 FOR UPDATE;
-- 或
UPDATE student SET age = 60 WHERE uid = 8;

-- 示例：鎖定uid在(5, 10)之間的所有間隙
SELECT * FROM student WHERE uid BETWEEN 6 AND 9 FOR UPDATE;
-- 或
SELECT * FROM student WHERE uid > 5 AND uid < 10 FOR UPDATE;

uid: 1, 2, 3, 4, 5, 7, 8, 10, 11

-- 事務(wù)一
BEGIN;
SELECT * FROM student WHERE age BETWEEN 20 AND 30 FOR UPDATE;
-- 鎖定所有age在20-30之間的間隙
-- 事務(wù)二嘗試插入age=25的新記錄（會被阻塞）
INSERT INTO student (name, age, sex) VALUES ('new_student', 25, 'M');

MySQL 實(shí)際上經(jīng)常使用 **臨鍵鎖 = 記錄鎖 + 間隙鎖**
-- 鎖定uid=8的行及其前后的間隙
SELECT * FROM student WHERE uid = 8 FOR UPDATE;
-- 實(shí)際鎖定：(7, 8] 和 (8, 10)

-- 如果name有唯一索引，不會加間隙鎖
SELECT * FROM student WHERE name = 'bbbb' FOR UPDATE;
-- 只對'bbbb'這一行加排他鎖
-- 如果name沒有索引或不是唯一索引，可能會鎖全表間隙
SELECT * FROM student WHERE name LIKE 'b%' FOR UPDATE;

-- 查看當(dāng)前的鎖（MySQL 8.0+）
SELECT 
    engine_transaction_id as trx_id,
    lock_type,
    lock_mode,
    lock_data,
    index_name
FROM performance_schema.data_locks 
WHERE object_name = 'student';

-- 事務(wù)A
BEGIN;
SELECT * FROM student WHERE uid = 15 FOR UPDATE; -- 間隙鎖(10, +∞)
-- 事務(wù)B  
BEGIN;
SELECT * FROM student WHERE uid = 16 FOR UPDATE; -- 間隙鎖(10, +∞)
-- 事務(wù)A
INSERT INTO student (uid, name) VALUES (15, 'test'); -- 等待B
-- 事務(wù)B
INSERT INTO student (uid, name) VALUES (16, 'test'); -- 等待A → 死鎖！

-- 好的設(shè)計：使用唯一索引避免不必要的間隙鎖
CREATE UNIQUE INDEX idx_uid ON student(uid);
-- 查詢時使用唯一索引
SELECT * FROM student WHERE uid = 8 FOR UPDATE; -- 只鎖一行

-- 如果不需要防止幻讀，使用讀已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 此時不會加間隙鎖

-- 盡快提交事務(wù)，減少鎖持有時間
BEGIN;
-- 立即執(zhí)行需要加鎖的操作
SELECT ... FOR UPDATE;
UPDATE ...;
-- 盡快提交
COMMIT;