MySQL 共享排他锁,MySQL【05】锁机制：共享锁、排他锁、意向锁

本文将以实验为主，演示共享锁、排他锁的特性，演示行锁升级为表锁。

为进行实验，先建立account表，含有一个主键id

CREATE TABLE account (

id INT (11) NOT NULL AUTO_INCREMENT,

name VARCHAR(255) DEFAULT NULL,

balance INT DEFAULT 0,

PRIMARY KEY (id)

) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO account (name, balance) VALUES ('li', 10);

INSERT INTO account (name, balance) VALUES ('ha', 233);

account表

1、共享锁、也叫读锁、S锁

读锁是共享的，或者说是互相不阻塞的，多个客户在同一时刻可以同时读取同一资源，而互不干扰。

若 事务T 对数据 对象A 加上 S锁 ，其他事务可以对 A 加 S锁 ，但不能加 X锁 ，直到 T 释放 A 上的 S锁。

S锁保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

加读锁：普通的select语句不会加任何锁，加读锁需要显示的加上【LOCK IN SHARE MODE】。

下面通过例子展示读锁的特点。

步骤

会话 A命令

balance值

会话 B命令

balance值

1

BEGIN;

2

select balance from account where id = 1 LOCK IN SHARE MODE;

10

3

select balance from account where id = 1 LOCK IN SHARE MODE;

10

4

update account set balance = 666 where id = 1;

将会被阻塞

5

COMMIT;

update执行

可以发现会话A 对id=1行加读锁之后，会话B任然可以对id=1行加读锁，但是会话B继续对id=1行执行更新操作将会被阻塞。

如果在第5步不是提交事务，而是执行一个更新语句的话，update account set balance = 777 where id = 1，这时候将会发生死锁现象。

2、排他锁、也叫写锁、X锁

写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑。

事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

X锁保证了其他事务在T释放A上的锁之前不能再读取和修改A。

1、自动加锁： delete / update / insert 语句会自动加上排他锁；

2、手动加排它锁：select * from student where id=1 FOR UPDATE；

步骤

会话 A命令

balance值

会话 B命令

balance值

1

BEGIN;

2

update account set balance = 0 where id = 1;

3

select balance from account where id = 1;

10

4

select balance from accountwhere id = 2 LOCK IN SHARE MODE;

不阻塞233

4

select balance from account where id = 1 LOCK IN SHARE MODE;

阻塞

5

COMMIT;

结果出来0

可以发现， update 语句中 where 条件为主键时，是对该行上锁，其他会话想获取该行的读或写锁都将被阻塞，但是其他行不受影响。

3、为什么推荐 MySQL 的 update 语句中 where 条件要有主键索引、唯一索引？

接下来演示一下如果update 语句中 where 条件是没有索引的name字段会怎么样呢。

将会发现，由于name字段没有索引，所以对该行的行锁升级为了表锁，其他行想获取读锁或写锁都将被阻塞。

步骤

会话 A命令

balance值

会话 B命令

balance值

1

BEGIN;

2

update account set balance = 0 where name = 'li';

3

select balance from account where id = 1;

10

4

select balance from account where id = 2 LOCK IN SHARE MODE;

阻塞

5

Ctrl-c

6

select balance from account where id = 1 LOCK IN SHARE MODE;

阻塞

增加唯一索引：CREATE UNIQUE INDEX index_name ON account (NAME);

删除指定索引：DROP INDEX NAME ON account;

查询索引：SHOW INDEX FROM account;

步骤

会话 A命令

balance值

会话 B命令

balance值

1

BEGIN;

2

update account set balance = 0 where name = 'li';

3

select balance from account where id = 1;

10

4

select balance from account where id = 2 LOCK IN SHARE MODE;

10

6

select balance from accountwhere id = 1 LOCK IN SHARE MODE;

阻塞

会发现，对name 字段添加索引后，仅name为'li'的这一行上了锁。

原理简析：

推荐阅读：

MySQL InnoDB锁原理剖析

如何书写 update 避免表锁

InnoDB行锁是通过给索引上的索引项加锁来实现的。所以，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

4、意向锁

Inno存储引擎支持意向共享锁，意向锁为表级别的锁。设计的主要目的是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁。

1、意向共享锁 IS

2、意向排他锁 IX

意向锁可以优化锁之间的性能。

B站学习视频

相关问题:

为什么表没有索引，表里所有的记录都会被锁住

update age from user无索引是行锁还是表锁，为什么