MySQL锁使用示例

原创 已于 2025-05-29 08:17:17 修改 · 762 阅读 · 17 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql

于 2025-05-27 14:14:34 首次发布

文章目录

环境：

  • MySQL版本：8.0.27
  • 隔离级别：RR

基础数据

CREATE TABLE t5
(
    id int(11) NOT NULL,
    c  int(11) DEFAULT NULL,
    d  int(11) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY c (c)
) ENGINE = InnoDB;

insert into t5
values (0, 0, 0),
       (5, 5, 5),
       (10, 10, 10),
       (15, 15, 15),
       (20, 20, 20),
       (25, 25, 25);

【1】MySQL 表级锁的四种类型使用示例

表共享读锁 (S锁)

场景：数据库备份时允许其他会话读取但不能修改

-- 会话1
LOCK TABLES t5 READ ;  -- 加共享读锁
-- 此时可以执行SELECT但不能执行INSERT/UPDATE/DELETE

-- 会话2 (其他连接)
SELECT * FROM t5;    -- 允许执行
UPDATE t5 SET d = d + 1 WHERE id = 5;  -- 被阻塞，直到锁释放

注意：SHARED_WRITE 和 SHARED_READ_ONLY 是元数据锁(MDL)。类似 Innodb 锁中的 IX 和 IS 锁。

死锁排查方案：

select * from sys.schema_table_lock_waits;

select *from performance_schema.events_statements_current;
select * from performance_schema.threads where THREAD_ID = 49;
select * from performance_schema.metadata_locks;

表排他写锁 (X锁)

场景：执行数据迁移时需要完全独占表

-- 会话1
LOCK TABLES t5 WRITE;  -- 加排他写锁
-- 可以执行任何操作
-- 解锁
UNLOCK TABLES;

-- 会话2 (其他连接)
SELECT * FROM t5;    -- 被阻塞
UPDATE t5 SET d = d + 1 WHERE id = 5;  -- 被阻塞，直到锁释放

死锁排查：同上述方法一致

意向共享锁 (IS锁)

场景：事务准备读取某些行前的标记

-- 会话1
BEGIN;
SELECT * FROM t5 WHERE id = 5 LOCK IN SHARE MODE;
-- 自动先获取表的IS锁，再获取行的S锁

-- 会话2
SELECT * FROM t5 where id = 5;    -- 允许执行
UPDATE t5 SET d = d + 1 WHERE id = 5;  -- 被阻塞，直到锁释放

-- 此时在另一个会话可以观察到：
SHOW ENGINE INNODB STATUS\G
...
---TRANSACTION 1234, ACTIVE
... TABLE LOCK table `test`.`orders` trx id 1234 lock mode IS
... RECORD LOCKS space id 56 page no 3 n bits 72 index PRIMARY trx id 1234 lock mode S

死锁排查：

-- 持有锁类型
select * from sys.innodb_lock_waits;
select * from performance_schema.data_locks where ENGINE_TRANSACTION_ID in (31756, 31761);
select * from performance_schema.threads where THREAD_ID in (49, 50);
select * from performance_schema.events_statements_history where THREAD_ID = 49 order by EVENT_ID desc ;

意向排他锁 (IX锁)

场景：事务准备修改某些行前的标记

-- 会话1
BEGIN;
SELECT * FROM t5 WHERE id = 5 FOR UPDATE;
-- 自动先获取表的IX锁，再获取行的X锁

-- 会话2
SELECT * FROM t5 where id = 5;    -- 被阻塞
UPDATE t5 SET d = d + 1 WHERE id = 5;  -- 被阻塞，直到锁释放

-- 此时在另一个会话可以观察到：
SHOW ENGINE INNODB STATUS\G
...
---TRANSACTION 1235, ACTIVE
... TABLE LOCK table `test`.`orders` trx id 1235 lock mode IX
... RECORD LOCKS space id 56 page no 3 n bits 72 index PRIMARY trx id 1235 lock mode X

死锁排查：同上述方法一致

【2】MySQL 行级锁的类型使用示例

记录锁(Record Lock)

排他记录锁(X锁)：

-- 会话1
BEGIN;
SELECT * FROM t5 WHERE id = 5 FOR UPDATE;
-- 自动先获取表的IX锁，再获取行的X锁

-- 会话2
SELECT * FROM t5 where id = 5;    --被阻塞
UPDATE t5 SET d = d + 1 WHERE id = 5;  -- 被阻塞，直到锁释放

共享记录锁(S锁)：

-- 会话1
BEGIN;
SELECT * FROM t5 WHERE id = 10 LOCK IN SHARE MODE;
-- 自动先获取表的IS锁，再获取行的S锁

-- 会话2
SELECT * FROM t5 where id = 10;    -- 允许执行
UPDATE t5 SET d = d + 1 WHERE id = 10;  -- 被阻塞，直到锁释放

-- 锁分析
select * from performance_schema.data_locks;

会话 2 锁分析

SQL2：

ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140563598155776:1085:1405634751532803181063146demot5nullnullnull140563475153280TABLEIXGRANTEDnull
INNODB140563598155776:20:4:4:1405634751503683181063146demot5nullnullPRIMARY140563475150368RECORDX,REC_NOT_GAPWAITING10
INNODB140563598154968:1085:14056347514718442203857486562461202demot5nullnullnull140563475147184TABLEISGRANTEDnull
INNODB140563598154968:20:4:4:14056347514419242203857486562461202demot5nullnullPRIMARY140563475144192RECORDS,REC_NOT_GAPGRANTED10

间隙锁(Gap Lock)

防止幻读，锁定记录之间的间隙：

-- 会话1
BEGIN;
SELECT * FROM t5 WHERE id >3 AND id < 7 FOR UPDATE;

-- 会话2 
insert into t5 values (2, 2, 2); -- 阻塞
UPDATE t5 SET d = d + 1 WHERE id = 5; -- 阻塞

-- 锁分析
select * from performance_schema.data_locks;

会话 2 锁分析

SQL1：

THREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
6386demot5nullnullnull140563475153280TABLEIXGRANTEDnull
6386demot5nullnullPRIMARY140563475150368RECORDX,GAP,INSERT_INTENTIONWAITING5
61173demot5nullnullnull140563475147184TABLEIXGRANTEDnull
61173demot5nullnullPRIMARY140563475144192RECORDXGRANTED5
61173demot5nullnullPRIMARY140563475144536RECORDX,GAPGRANTED10

SQL2：

ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140563598155776:1085:1405634751532803180763122demot5nullnullnull140563475153280TABLEIXGRANTEDnull
INNODB140563598155776:20:4:3:1405634751503683180763122demot5nullnullPRIMARY140563475150368RECORDX,REC_NOT_GAPWAITING5
INNODB140563598154968:1085:1405634751471843179961173demot5nullnullnull140563475147184TABLEIXGRANTEDnull
INNODB140563598154968:20:4:3:1405634751441923179961173demot5nullnullPRIMARY140563475144192RECORDXGRANTED5
INNODB140563598154968:20:4:4:1405634751445363179961173demot5nullnullPRIMARY140563475144536RECORDX,GAPGRANTED10

临键锁(Next-Key Lock)

InnoDB默认锁机制，锁定记录+前间隙：

BEGIN;
SELECT * FROM t5 WHERE id <= 20 FOR UPDATE;
-- 锁定范围: (-∞,0],(0,5],(5,10],(10,20]

UPDATE t5 SET d = d + 1 WHERE id = 0; -- 阻塞
UPDATE t5 SET d = d + 1 WHERE id = 20; -- 阻塞
UPDATE t5 SET d = d + 1 WHERE id = 25; -- 正常执行
insert into t5 values (2, 2, 2); -- 阻塞

会话 2 锁分析

SQL1：

ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140563598155776:1085:1405634751532803182663247demot5nullnullnull140563475153280TABLEIXGRANTEDnull
INNODB140563598155776:20:4:2:1405634751503683182663247demot5nullnullPRIMARY140563475150368RECORDX,REC_NOT_GAPWAITING0
INNODB140563598154968:1085:1405634751471843182561260demot5nullnullnull140563475147184TABLEIXGRANTEDnull
INNODB140563598154968:20:4:2:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED0
INNODB140563598154968:20:4:3:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED5
INNODB140563598154968:20:4:4:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED10
INNODB140563598154968:20:4:5:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED15
INNODB140563598154968:20:4:6:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED20

SQL2：

ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140563598155776:1085:1405634751532803182763258demot5nullnullnull140563475153280TABLEIXGRANTEDnull
INNODB140563598155776:20:4:6:1405634751503683182763258demot5nullnullPRIMARY140563475150368RECORDX,REC_NOT_GAPWAITING20
INNODB140563598154968:1085:1405634751471843182561260demot5nullnullnull140563475147184TABLEIXGRANTEDnull
INNODB140563598154968:20:4:2:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED0
INNODB140563598154968:20:4:3:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED5
INNODB140563598154968:20:4:4:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED10
INNODB140563598154968:20:4:5:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED15
INNODB140563598154968:20:4:6:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED20

SQL4：

ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140563598155776:1085:1405634751532803183263306demot5nullnullnull140563475153280TABLEIXGRANTEDnull
INNODB140563598155776:20:4:3:1405634751503683183263306demot5nullnullPRIMARY140563475150368RECORDX,GAP,INSERT_INTENTIONWAITING5
INNODB140563598154968:1085:1405634751471843182561260demot5nullnullnull140563475147184TABLEIXGRANTEDnull
INNODB140563598154968:20:4:2:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED0
INNODB140563598154968:20:4:3:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED5
INNODB140563598154968:20:4:4:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED10
INNODB140563598154968:20:4:5:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED15
INNODB140563598154968:20:4:6:1405634751441923182561260demot5nullnullPRIMARY140563475144192RECORDXGRANTED20

【3】加锁原则

  • 加锁基本单位都是 next-key lock，查找过程中访问到的对象才会加锁。
  • 索引等值查询，给唯一索引加锁时，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
  • 索引等值查询，给唯一索引加锁时，next-key lock 退化为行锁。

注意**BUG: **在唯一索引（主键）的范围查询中，InnoDB会对满足条件的记录加记录锁，并对范围之后的间隙加间隙锁以防止插入。

【4】实战

主键索引等值查询

begin ;
SELECT * FROM t5 WHERE id=5 for update ;

锁分析

ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140563598154968:1085:140563475147184319508579demot5nullnullnull140563475147184TABLEIXGRANTEDnull
INNODB140563598154968:20:4:3:140563475144192319508579demot5nullnullPRIMARY140563475144192RECORDX,REC_NOT_GAPGRANTED5

主键索引间隙锁

-- session 1
begin ;
select * from t5 where id = 6 for update ; -- (5,10)

-- session 2
UPDATE t5 SET d = d + 1 WHERE id = 10; -- 正常执行
insert into t5 value (4, 4, 4); -- 正常执行
 insert into t5 value (7, 7, 7); -- 阻塞

会话 2 锁分析

ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140563598155776:1085:140563475153280319338650demot5nullnullnull140563475153280TABLEIXGRANTEDnull
INNODB140563598155776:20:4:4:140563475150368319338650demot5nullnullPRIMARY140563475150368RECORDX,GAP,INSERT_INTENTIONWAITING10
INNODB140563598154968:1085:140563475147184319248521demot5nullnullnull140563475147184TABLEIXGRANTEDnull
INNODB140563598154968:20:4:4:140563475144192319248521demot5nullnullPRIMARY140563475144192RECORDX,GAPGRANTED10

主键索引范围锁

范围一：

-- session 1:
begin ;
select * from t5 where id >= 5 and id <10 for update ; -- (0,5],(5,10] -> 5,(5,10);

-- session 2:
insert into t5 value (2, 2,2); -- 正常执行
insert into t5 value (7, 7, 7); -- 阻塞 
UPDATE t5 SET d = d + 1 WHERE id = 10; -- 正常执行

会话 2 锁分析

ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140563598155776:1085:140563475153280319398698demot5nullnullnull140563475153280TABLEIXGRANTEDnull
INNODB140563598155776:20:4:4:140563475150368319398698demot5nullnullPRIMARY140563475150368RECORDX,GAP,INSERT_INTENTIONWAITING10
INNODB140563598154968:1085:140563475147184319348550demot5nullnullnull140563475147184TABLEIXGRANTEDnull
INNODB140563598154968:20:4:3:140563475144192319348550demot5nullnullPRIMARY140563475144192RECORDX,REC_NOT_GAPGRANTED5
INNODB140563598154968:20:4:4:140563475144536319348550demot5nullnullPRIMARY140563475144536RECORDX,GAPGRANTED10

范围二：

-- session1
begin ;
select * from t5 where id > 5 and id <=10 for update ; -- (5,10]

-- session2
insert into t5 value (2, 2,2); -- 正常执行
insert into t5 value (7, 7, 7); -- 阻塞 
insert into t5 value (11, 11, 11); -- 正常执行
UPDATE t5 SET d = d + 1 WHERE id = 10; -- 阻塞
UPDATE t5 SET d = d + 1 WHERE id = 5; -- 正常执行

会话 2 锁分析

ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140563598155776:1085:1405634751532803195486122demot5nullnullnull140563475153280TABLEIXGRANTEDnull
INNODB140563598155776:20:4:4:1405634751503683195486122demot5nullnullPRIMARY140563475150368RECORDX,REC_NOT_GAPWAITING10
INNODB140563598154968:1085:1405634751471843195185108demot5nullnullnull140563475147184TABLEIXGRANTEDnull
INNODB140563598154968:20:4:4:1405634751441923195185108demot5nullnullPRIMARY140563475144192RECORDXGRANTED10

范围 3：

begin;
select * from t5 where id <= 10 for update ;

锁分析

ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140697851113472:1085:1406977599901443310958292demot5nullnullnull140697759990144TABLEIXGRANTEDnull
INNODB140697851113472:20:4:2:1406977599872323310958292demot5nullnullPRIMARY140697759987232RECORDXGRANTED0
INNODB140697851113472:20:4:3:1406977599872323310958292demot5nullnullPRIMARY140697759987232RECORDXGRANTED5
INNODB140697851113472:20:4:4:1406977599872323310958292demot5nullnullPRIMARY140697759987232RECORDXGRANTED10

非唯一索引等值查询

-- session1：
begin ;
select * from t5  where c=5 for update ;

-- session2：
insert into t5 value (2, 2,2); -- 阻塞
insert into t5 value (7, 7, 7); -- 阻塞
UPDATE t5 SET d = d + 1 WHERE c = 5; -- 阻塞
UPDATE t5 SET d = d + 1 WHERE c = 10; -- 正常执行
UPDATE t5 SET d = d + 1 WHERE id = 5;  -- 阻塞
UPDATE t5 SET d = d + 1 WHERE id = 10; -- 正常执行

会话 2 锁分析

ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140697851114280:1085:140697759996128330405013demot5nullnullnull140697759996128TABLEIXGRANTEDnull
INNODB140697851114280:20:5:3:140697759993216330405013demot5nullnullc140697759993216RECORDX,GAP,INSERT_INTENTIONWAITING5, 5
INNODB140697851113472:1085:140697759990144330394978demot5nullnullnull140697759990144TABLEIXGRANTEDnull
INNODB140697851113472:20:5:3:140697759987232330394978demot5nullnullc140697759987232RECORDXGRANTED5, 5
INNODB140697851113472:20:4:3:140697759987576330394978demot5nullnullPRIMARY140697759987576RECORDX,REC_NOT_GAPGRANTED5
INNODB140697851113472:20:5:4:140697759987920330394978demot5nullnullc140697759987920RECORDX,GAPGRANTED10, 10

非唯一索引间隙锁

-- session1：
begin ;
select * from t5  where c=6 for update ;

-- session2：
UPDATE t5 SET d = d + 1 WHERE c = 5; -- 正常执行
UPDATE t5 SET d = d + 1 WHERE id = 5; -- 正常执行
UPDATE t5 SET d = d + 1 WHERE id = 10; -- 正常执行
UPDATE t5 SET d = d + 1 WHERE c = 10; -- 正常执行
insert into t5 value (7, 7, 7); -- 阻塞

会话 2 锁分析

ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140697851114280:1085:1406977599961283306750181demot5nullnullnull140697759996128TABLEIXGRANTEDnull
INNODB140697851114280:20:5:4:1406977599932163306750181demot5nullnullc140697759993216RECORDX,GAP,INSERT_INTENTIONWAITING10, 10
INNODB140697851113472:1085:1406977599901443305249107demot5nullnullnull140697759990144TABLEIXGRANTEDnull
INNODB140697851113472:20:5:4:1406977599872323305249107demot5nullnullc140697759987232RECORDX,GAPGRANTED10, 10

非唯一索引范围锁

范围一：

-- session1：
begin ;
select * from t5 where c >= 5 and c <10 for update ;

-- session2：
UPDATE t5 SET d = d + 1 WHERE id = 10; -- 正常执行
UPDATE t5 SET d = d + 1 WHERE c = 10; -- 阻塞
insert into t5 value (2, 2,2); -- 阻塞

会话 2 锁分析

ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140697851112664:1085:140697759984048330845784demot5nullnullnull140697759984048TABLEIXGRANTEDnull
INNODB140697851112664:20:5:3:140697759981056330845784demot5nullnullc140697759981056RECORDX,GAP,INSERT_INTENTIONWAITING5, 5
INNODB140697851113472:1085:1406977599901443308358108demot5nullnullnull140697759990144TABLEIXGRANTEDnull
INNODB140697851113472:20:5:3:1406977599872323308358108demot5nullnullc140697759987232RECORDXGRANTED5, 5
INNODB140697851113472:20:5:4:1406977599872323308358108demot5nullnullc140697759987232RECORDXGRANTED10, 10
INNODB140697851113472:20:4:3:1406977599875763308358108demot5nullnullPRIMARY140697759987576RECORDX,REC_NOT_GAPGRANTED5

范围 2：

begin ;
select * from t5 where c > 5 and c <=10 for update ;

-- sesssion 2：
insert into t5 value (2, 2,2); -- 正常执行
insert into t5 value (7, 7,7); -- 阻塞
insert into t5 value (11, 11,11); -- 阻塞
UPDATE t5 SET d = d + 1 WHERE c = 5; -- 正常执行
UPDATE t5 SET d = d + 1 WHERE c = 10; -- 阻塞
UPDATE t5 SET d = d + 1 WHERE c = 15; -- 阻塞
UPDATE t5 SET d = d + 1 WHERE id = 15; -- 正常执行
ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140697851112664:1085:1406977599840483310257192demot5nullnullnull140697759984048TABLEIXGRANTEDnull
INNODB140697851112664:20:5:5:1406977599810563310257192demot5nullnullc140697759981056RECORDXWAITING15, 15
INNODB140697851113472:1085:1406977599901443308758137demot5nullnullnull140697759990144TABLEIXGRANTEDnull
INNODB140697851113472:20:5:4:1406977599872323308758137demot5nullnullc140697759987232RECORDXGRANTED10, 10
INNODB140697851113472:20:5:5:1406977599872323308758137demot5nullnullc140697759987232RECORDXGRANTED15, 15
INNODB140697851113472:20:4:4:1406977599875763308758137demot5nullnullPRIMARY140697759987576RECORDX,REC_NOT_GAPGRANTED10

范围 3：

begin ;
select * from t5 where c <= 10 for update ;

锁分析

ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATA
INNODB140697851113472:1085:1406977599901443310858263demot5nullnullnull140697759990144TABLEIXGRANTEDnull
INNODB140697851113472:20:5:2:1406977599872323310858263demot5nullnullc140697759987232RECORDXGRANTED0, 0
INNODB140697851113472:20:5:3:1406977599872323310858263demot5nullnullc140697759987232RECORDXGRANTED5, 5
INNODB140697851113472:20:5:4:1406977599872323310858263demot5nullnullc140697759987232RECORDXGRANTED10, 10
INNODB140697851113472:20:5:5:1406977599872323310858263demot5nullnullc140697759987232RECORDXGRANTED15, 15
INNODB140697851113472:20:4:2:1406977599875763310858263demot5nullnullPRIMARY140697759987576RECORDX,REC_NOT_GAPGRANTED0
INNODB140697851113472:20:4:3:1406977599875763310858263demot5nullnullPRIMARY140697759987576RECORDX,REC_NOT_GAPGRANTED5
INNODB140697851113472:20:4:4:1406977599875763310858263demot5nullnullPRIMARY140697759987576RECORDX,REC_NOT_GAPGRANTED10

【5】performance_schema.data_locks 表

表级锁 (LOCK_TYPE = ‘TABLE’)

锁类型LOCK_MODE 显示值说明
表共享读锁S允许其他会话读但禁止写
表排他写锁X禁止其他会话任何操作
意向共享锁IS表示事务打算在表的某些行上设置共享锁
意向排他锁IX表示事务打算在表的某些行上设置排他锁

行级锁 (LOCK_TYPE = ‘RECORD’)

基本行锁类型

锁类型LOCK_MODE 显示值锁定范围兼容性
记录锁 (X锁)X,REC_NOT_GAP仅锁定单条记录与其他所有锁互斥
记录锁 (S锁)S,REC_NOT_GAP仅锁定单条记录允许多个S锁，与X锁互斥
间隙锁X,GAP
或 S,GAP		锁定记录之间的间隙允许多个事务持有相同间隙锁
临键锁X
或 S		锁定记录+前间隙 (左开右闭)X锁与其他所有锁互斥
插入意向锁X,INSERT_INTENTION插入操作特有的间隙锁允许多个不冲突的插入意向锁

特殊行锁类型

锁类型LOCK_MODE 显示值说明
谓词锁X,PURGED用于空间索引的谓词锁 (MySQL 8.0.17+)
超级锁X,SUPER系统内部使用的特殊锁
MySQL的数据锁表（data_locks 表）
jkzyx123的博客
01-25 3704
MySQL的数据锁表（data_locks 表）信息详解
MySQL中锁使用及原理
chengxuyuan_2012的专栏
04-02 2294
当我们向一个由AUTO_INCREMENT关键字的主键插入值的时候，每条语句都要对这个表锁进行竞争，这样的并发潜力其实是很低下的，所以InnoDB通过innodb_autoinc_lock_mode的不同取值来提供不同的锁定机制，来显著提高SQL语句的可伸缩性和性能。这也意味着在高并发的情况下，可能会出现自增值的重复或者不连续的情况。表级锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大。
mysql怎么用锁_MySQL的锁应该怎么用(一)
weixin_32746931的博客
01-19 518
先说重点：锁是为了固化资源状态的，加锁之后一定会有状态判断(或加锁语句的条件里含有这个)，只加锁不判断状态，那这个锁就没啥用。如果你加了锁，但是后边并没有回滚机制，那你就要考虑下你代码的逻辑是不是有问题了。代码要有自检的功能，第一不能相信用户，第二不能相信调用你函数的其它程序员。尽量保证你的代码在被重复执行时不会出现问题(要不你就保证你这段代码绝对不会遇到并发)。写完任何一个函数或者脚本，都要想一...
MySQL-锁表和解锁
weixin_45203607的博客
02-19 1万+
介绍 锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。 加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。 锁机制 共享锁与排他锁 共享锁（读锁）：其他事务可以读，但不能写。 排他锁（写锁） ：其他事务不能读取，也不能写。 粒度锁 MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的
MySQL | 行锁——记录锁、间隙锁 、临键锁、插入意向锁
最新发布
m0_60500421的博客
08-10 2842
MySQL | 行锁——记录锁、间隙锁 、临键锁、插入意向锁
Mysql基础(十九)：锁
mingyuli的博客
11-13 5163
1、Mysql锁机制 1.1、行锁 mysql锁机制分为表级锁和行级锁。mysql行级锁包括共享锁与排他锁： 共享锁又称为读锁，简称S锁 共享锁：多个事务都可以加共享锁读同⼀⾏数据，但是别的事务不能写这⾏数据。 一个事务获取了共享锁，其他事务也只能加共享锁或不加锁查询。 其他事务加排他锁就查不到，因为排他锁与共享锁不能存在同一数据上。 排他锁又称为写锁，简称X锁 排他锁：⼀个事务可以读/写这⾏数据，别的事务只能读不能写。 一个事务获取了一个数...
Mysql悲观锁和乐观锁的使用示例
12-16
以下是一个悲观锁的使用示例： ```sql -- 创建商品表 CREATE TABLE `goods` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `status` tinyint(4) DEFAULT NULL, `version` int(11...
使用MySQL实现一个分布式锁
09-08
以下是如何使用MySQL实现分布式锁的一个简单示例： 1. **尝试获取锁**：通过`tryLock`方法尝试获取锁。在`GlobalLockComponent`类中，调用`tryLockUtil`的`tryLock`方法，传入数据库操作接口`globalLockDAO`和需要...
mysql 锁表锁行语句分享(MySQL事务处理)
09-11
在标题和描述中提到的“mysql 锁表锁行语句分享”是关于如何在MySQL中使用事务来锁定特定行数据，确保在操作期间不会被其他并发事务访问。 在给出的代码示例中，首先通过`set autocommit=0`关闭了自动提交模式，这...
MySQL锁详解
热门推荐
hxt的博客
06-05 9万+
根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类 一、全局锁 全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock。当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句 全局锁的......
MySQL中锁的使用（全局、表级、行级锁）
S mile0804的博客
05-18 2248
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、 RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。 MySQL 中的锁，按照锁的粒度分，分为以下三类： 全局锁：锁定数据库中的所有表。 表级锁：每次操作锁住整张表。 行级锁：每次操作锁住对应的行数据。 接下来，我们
mysql锁的使用总结
dressrosa
07-16 3538
来总结一下mysql数据库锁的使用. 目前,我所知道的数据库锁就是悲观锁和乐观锁.一般用到数据库锁的场景都是对数据的准确性比较高的,比如钱等.由于水平有限,这里不研究原理,只看看怎样去使用数据库锁. 首先,对于悲观锁,也就是我们默认每次的更新都是非常不安全的操作,我们得给她加锁.相当于java里面的synchronized.在mysql里面这样使用: select xxx for u...
一般mysql使用锁吗_Mysql锁一般使用
weixin_39534121的博客
01-31 366
Mysql数据库锁受引擎影响，不同的引擎锁的方式不一样。常用的引擎：MyISAM引擎是仅支持表锁，InnoDB引擎支持表锁或索引锁(行锁)。MyISAM引擎已经不再推荐使用，主是要因为MyISAM引擎不支持事务虽然查询性能略微高点，但InnoDB经过几个版本的升级后各方面已经有很大的提升，其中在MySQL5.6版本后InnoDB开始支持全文索引，到5.7后可以使用全文索引分词插件(不过全文索引在关...
通过一个例子详解 MySQL record lock(记录锁) 的加锁方式
落落的专栏 专注SQL调优 性能调优
10-11 2394
本文基于MySQL8.0.19，下面通过一个例子讲解 MySQL record lock(记录锁) 的加锁方式 SESSION1: mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 8.0.19 | +-----------+ 1 row in set (0.01 sec) mysql> CREATE TABLE t(id INT UNSIGNED PRIMARY KEY ,NAME VARCH
mysql中的锁怎么使用_mysql中的"锁"事儿总结
weixin_30220707的博客
01-30 488
定义关于mysql中的锁，是用于管理不同事务对共享资源的并发访问的问题，分为行锁和表锁。表锁与行锁的区别：锁定粒度：表锁 > 行锁加锁效率：表锁 > 行锁冲突概率：表锁 > 行锁并发性能：表锁 < 行锁InnoDB存储引擎支持行锁和表锁(另类的行锁)mysql innodb锁类型共享锁(行锁)：Shared Locks又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同...
MySQL中锁详解（行锁、表锁、页锁、悲观锁、乐观锁等）
杨春建的博客
10-18 5万+
原文地址：http://blog.youkuaiyun.com/mysteryhaohao/article/details/51669741 锁，在现实生活中是为我们想要隐藏于外界所使用的一种工具。在计算机中，是协调多个进程或线程并发访问某一资源的一种机制。在数据库当中，除了传统的计算资源（CPU、RAM、I/O等等）的争用之外，数据也是一种供许多用户共享访问的资源。如何保证数据并发访问的一致性、有
MySQL锁的用法之行级锁
tigernorth的专栏
09-05 1万+
行级锁是MySQL中粒度最小的一种锁，他能大大减少数据库操作的冲突。但是粒度越小，实现的成本也越高。MYISAM引擎只支持表级锁，而INNODB引擎能够支持行级锁，下面的内容也是针对INNODB行级锁展开的。     INNODB的行级锁有共享锁（S LOCK）和排他锁（X LOCK）两种。共享锁允许事物读一行记录，不允许任何线程对该行记录进行修改。排他锁允许当前事物删除或更新一行记录，其他线程
mysql中的锁（表锁，行锁）
huangwei18351的博客
09-10 7101
MySQL锁 开销、加锁速度、死锁、粒度、并发性能 --表锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突概率高，并发度低 --行锁：开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突概率低，并发度高 --页锁：介于行锁和表锁之间，会出现死锁，并发度一般 表锁更适用于以查询为主，只有少量按索引条件更新数据的应用；行锁更适用于有大量按索引条件并发更新少量不同数据，同时又有并发查...
并发编程中MySQL语句双锁示例与死锁解析
作者以淘宝丁奇的讲师身份，分享了对MySQL锁的理解和实践经验，旨在为对MySQL锁机制感兴趣的学员提供深入的学习资料。 首先，文章以"一个语句两个锁"为例，解释并发情况下执行两条涉及不同表的SQL语句可能会引发的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值