MySQL高级4-MySQL锁机制-CSDN博客

本文链接：https://blog.csdn.net/u014215131/article/details/112547114

1. 概述

锁的分类
从对数据操作的类型（读/写）分

1.读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
2.写锁（排他锁）：当前写操作没有完成前，他会阻断其他写锁和读锁

从对数据操作的粒度分
1.表锁
2.行锁

2. 三锁

2.1 表锁（偏读）

偏向MyISMy存储引擎，开销小，加锁快；无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低
MyISAM的读写锁调度是写优先，这也是他不适合做写为主表的引擎。写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而永远阻塞

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;
#手动添加表锁
lock table 表名1 read[write],表名2 read[write],其他;
#查看表上加过的锁
show open tables;
#释放表锁
unlock tables;
#分析表锁定
show status like 'table%';
/*
Table_locks_immediate  产生表级锁定的次数
Table_locks_waited      出现表级锁定争用而发生等待的次数
*/

#加读锁,session1可以读自己，不能改自己，不能读没加锁的别表
lock table mylock read;
select * from mylock;
update mylock set name='a2' where id =1;
#sesson2可以读自己，改自己处于阻塞，可以读别表
select * from mylock

#加写锁，session1可以读自己，可以改自己，不能读别表
lock table mylock write;
select * from mylock;
update mylock set name='a2' where id =1;
#session2读写自己堵塞，可以读别表

MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的加锁
读锁会阻塞写，但是不会阻塞读；写锁则会读和写都阻塞
|锁类型| 可否兼容 | 读锁|写锁
|–|--|–|--|
| 读锁 | 是 |是|否
|写锁|是|否|否

2.2 行锁（偏写）

偏向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;

SET autocommit=0;
#session1数据改变，session2未读到
update test_innodb_lock set b='4001' where a=4;
select * from test_innodb_lock;
#更新但不提交，没有commit，session2只能阻塞
#无索引行锁升级为表锁

#间隙锁，当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给复合条件的已有数据记录的 索引项加锁，对于键值在条件范围内但并不存在的记录，叫做间隙
#锁定整个范围内的索引键值，即使这个键值并不存在，对性能危害很大

#session1
update test_innodb_lock set b='0629' where a>1 and a<6;
#session2
INSERT INTO test_innodb_lock VALUES(2, '2000');

面试题：常考如何锁定一行
begin; select * from test_lock where a=8 for update; commit;

总结

Innodb存储引擎由于实现了行级锁，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁更高，但在整体并发能力远远优于MyISAM的
但Innodb行级锁也有脆弱的一面，当使用不当时，可能让Innodb的整体性能表现不佳

#行锁分析
show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits | 当前正在等待锁定的数量
Innodb_row_lock_time          | 等待总次数
Innodb_row_lock_time_avg      | 等待平均时长
Innodb_row_lock_time_max      | 等待总时长
Innodb_row_lock_waits         | 等待次数

优化建立