欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL学习笔记5---MySQL 锁机制

程序员文章站 2024-03-08 17:03:28
...

8、MySQL 锁机制

8.1、概述

锁的分类

从数据操作的类型(读、写)分

  • 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响
  • 写锁(排它锁):当前写操作没有完成前,它会阻断其他写锁和读锁。

从对数据操作的颗粒度

  • 表锁
  • 行锁

8.2、表锁

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

8.2.1、读锁

create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) 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');

select * from mylock;

MySQL学习笔记5---MySQL 锁机制
MySQL学习笔记5---MySQL 锁机制

8.2.2、写锁

MySQL学习笔记5---MySQL 锁机制
MySQL学习笔记5---MySQL 锁机制

SHOW STATUS like 'table%';

MySQL学习笔记5---MySQL 锁机制

  • MyISAM 的读写锁调度是写优先,所以不适合作为以写为主的引擎。因为写锁后,其他线程不能做任何操作,大量的更新会使查询很难得到锁,从而造成永久阻塞。

8.3、行锁

特点:偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁

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');
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
SELECT * from test_innodb_lock;

MySQL学习笔记5---MySQL 锁机制

  • 索引失效导致行锁变表锁

MySQL学习笔记5---MySQL 锁机制

8.4、间隙锁

什么是间隙锁?

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙”;

InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。

面试题:如何锁定一行?

  • select * from test where a = 8 for update   #其他session对这行的操作将被阻塞
    

8.5、优化建议

  • 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
  • 合理设计索引,尽量缩小锁的范围
  • 尽可能较少检索条件,避免间隙锁
  • 尽量控制事务大小,减少锁定资源量和时间长度
  • 尽可能低级别事务隔离

据检索都通过索引来完成,避免无索引行锁升级为表锁

  • 合理设计索引,尽量缩小锁的范围
  • 尽可能较少检索条件,避免间隙锁
  • 尽量控制事务大小,减少锁定资源量和时间长度
  • 尽可能低级别事务隔离