MySQL锁

2021-10-08 11:12:43 浏览数 (1)

锁.md

文章首发于GitHub开源项目: Java成长之路 欢迎大家star!

锁的定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。 在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL锁的分类

  • 从对数据操作的类型（读写）分
代码语言：txt复制
- 读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响。
- 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
  • 从对数据操作的粒度分
  • 表锁
  • 行锁

表锁(读优先)

特点

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

代码语言：txt复制
        `对 MyISAM表进行操作,读表会阻塞其他会话的写操作，写表会阻塞其他会话的读写操作。`

语法

代码语言：txt复制
#上锁
lock TABLES 表名 read/write;
#查看表上加过的锁  
show open tables;
#释放表锁
unlock tables;

案例演示

  • 建表SQL
代码语言：txt复制

create table mylock

(

代码语言：txt复制
  id   int not null primary key auto_increment,
代码语言：txt复制
  name varchar(20)

) engine myisam;

insert into mylock(name)

values ('a');

insert into mylock(name)

values ('b');

insert into mylock(name)

values ('c');

insert into mylock(name)

values ('d');

insert into mylock(name)

values ('e');

代码语言：txt复制
  • session1给mylock表加读锁
代码语言：txt复制

LOCK table mylock read;

代码语言：txt复制

session1写mylock表，不可以

image-20211004221735397image-20211004221735397

session1读写别的表,不可以

image-20211004221826894image-20211004221826894

session2读mylock表，不可以

session2写mylock会阻塞等待，等session1释放读锁，方可写。

  • session1给mylock表加写锁
代码语言：txt复制

LOCK table mylock write;

代码语言：txt复制

session1读mylock 可以

image-20211004222058095image-20211004222058095

session1写mylock 可以

image-20211004222128879image-20211004222128879

session1读写别的表，不可以

session2读写mylock会阻塞等待，直到session1释放写锁

行锁(写优先)

特点

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。 InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁

案例分析

建表SQL

代码语言：txt复制
/**
  table test_innodb_lock by shaoxiongdu 2021/10/04
*/
create table test_innodb_lock
(
    a int(11),
    b varchar(16)
) engine = innodb;
insert into test_innodb_lock
values (1, 'b2');
insert into test_innodb_lock
values (3, '3');
insert into test_innodb_lock
values (4, '4000');
insert into test_innodb_lock
values (5, '5000');
insert into test_innodb_lock
values (6, '6000');
insert into test_innodb_lock
values (7, '7000');
insert into test_innodb_lock
values (8, '8000');
insert into test_innodb_lock
values (9, '9000');
insert into test_innodb_lock
values (1, 'b1');

首先打开两个会话,关闭其自动提交。

代码语言：txt复制
set autocommit = 0;

然后session1对表中某条数据进行写操作，则会自动锁住该行

代码语言：txt复制
update mylock
set b = '4001'
where a = 4;

session2对该行其进行写操作时，会阻塞等待，直到session1提交事务。

结论

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

优化建议

  • 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
  • 尽可能较少检索条件，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度
  • 锁住某行后，尽量不要去调别的行或表，赶紧处理被锁住的行然后释放掉锁。
  • 涉及相同表的事务，对于调用表的顺序尽量保持一致。
  • 在业务环境允许的情况下,尽可能低级别事务隔离

页锁

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

文章首发于GitHub开源项目: Java成长之路 欢迎大家star!

云数据库SQLServer 云数据库MySQL

0 人点赞