一、数据库锁定义

定义

• 锁是计算机协调多个进程或线程并发访问某一资源的机制
• 事务的也是通过锁来实现的
• 加锁有很好有坏，容易造成阻塞

分类

• 表锁
• 行锁
• 间隙锁
• MDL表锁
• …等

（一）读锁和写锁的区别

• 先理解读锁与写锁的区别，更好理解表锁与行锁的不同之处

读锁

• 加读锁,不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其他进行的写操作
• MyISAM在对表读操作的时候会加自动给涉及的所有表加读锁

写锁

• 写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进程的读写操作
• MyISAM在对表写操作的时候会加自动给涉及的所有表加写锁
• InnoDB在在对表写操作的时候会加自动给涉及的行加写锁

自己添加锁

• lock table 表名字 read; – 读锁，write就是写锁
• lock table 表名字 write,表名字2 read; – 可以一次性给多张表添加读或写
• unlock tables; – 解锁

（二）锁在InnoDB与MyISAM的运用

InnoDB

• 对表普通写操作的时候，会加自动给涉及的加行锁，效果是写锁
• 对表进行事务操作的时候，系统会自动给表加上锁，保证读写的正确性，对表进行增删改查的操作的时候会自动增加MDL表锁，效果是写锁
• varchar类型的字段赋值没有添加引号，行锁升级为表锁

MyISAM

• MyISAM在对表读操作的时候会加自动给涉及的所有表加读锁
• MyISAM在对表写操作的时候会加自动给涉及的所有表加写锁

（三）如何锁定一行

• 让查询语句也加上行写锁，当前客户端修改中未提交的时候，其他客户端可以查询但无法修改

  select * from test_innodb_lock where a = 8 for update;
  update test_innodb_lock set b='5006' where a=5;
  

二、锁的分类

（一）表锁

定义

• 偏向MyISAM存储引擎,开销小,加锁快;无死锁,锁定粒度大,发送锁冲突的概率最高,并发度低，适合读，写优先

分类

• 表锁的读锁
  • 读操作，加读锁
• 表锁的写锁
  • 写操作，加写锁

案例

• myisam会自动加锁，这里利用lock上锁是因为系统自动上锁自动解锁时间太短，不好看效果myisam
• 这里采用的是手动上锁加锁

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

（1）给表1加上读锁后lock table mylock read;

 -- 手动增加读锁
 lock table mylock read;
 select * from mylock；
 update mylock set name='aa' where id=1;
 -- 释放表锁
 unlock tables;

• 当前客户端可以读表mylock但不可以读其他表，另一客户端可以读表mylock和其他表
  update mylock set name=‘aa’ where id=1;
• 都不可修改表mylock，当前客户端修改mylock报错，另一窗口线程修改语句表1堵塞，只有当前客户端解锁或timeout超时才能解堵塞
  

（2）给表1加上写锁后lock table mylock write;

-- 手动增加写锁
lock table mylock write;

• 当前客户端可以读表1但不可以读其他表，另一客户端不可以读表1会堵塞，但可以读其他表
• 当前客户端可以update、插入数据insert，另一窗口线程不可以会堵塞
  

（二）行锁

定义

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

要点

• InnoDB与MyISAM的最大不同点,支持事务，采用了行级锁
• 行锁加锁后，在当前客户端正对这行修改的时候，其他客户端查询可以读，但是不能修改这一行，会阻塞，其他行还是可以正常操作的
• 行锁升级成表锁：在当前客户端正对这行修改的时候，varchar类型的字段没有添加引号

案例

• 同样语句执行时间太短，看不到对其他客户端的约束，这里我们取消自动提交数据，让语句未提交的状态

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');
 update test_innodb_lock set b='5004' where a=5;
create index idx_test_innodb_a on test_innodb_lock(a);
create index idx_test_innodb_b on test_innodb_lock(b);

- 关闭自动提交
set autocommit=0;

（1）对行数据修改的时候

update test_innodb_lock set b='1003' where a=4; 
select * from test_innodb_lock;
commit;

• 在当前客户端修改，在其他客户端看不到修改，同时如果在其他客户也同时修改这一句会阻塞，但是还是可以查看这张表

（2）分析行锁定

• 如何分析行锁定:通过检查innodb_row_lock状态变量来分析系统上的行锁争夺情况
  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 系统启动后到现在总共等待的次数
    

（三）间隙锁

定义

• 当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,innodb会给符合条件的已有数据记录的索引项加锁, 对于键值在条件范围内但并不存在的记录,叫做"间隙"
• 对于键值在条件范围内但并不存在的记录,就是id顺序不是递增的，然后你对表修改的时候添加的范围条件id囊括了某个并不存在的id值，那么再修改的时候，这行不存在的id会被上锁，其他客户端没有办法插入创建这个不存在的id的数据，

要点

• innodb也会对这个"间隙"加锁,这种锁机制就是所谓的间隙锁

间隙锁的危害

• 因为SQL执行过程中通过范围查找的话,他会锁定整个范围内所有的索引值,即使这个键值并不存在
• 间隙锁有一个比较致命的弱点,就是当锁定以为范围键值之后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

案例

select * from test_innodb_lock where id>1 and id<4;
-- 在另一个客户端
insert into test_innodb_lock (a,b) values(2,'22');

• 锁定整个范围内所有的索引值,即使这个键值并不存在，所以另一个客户端无法插入2数据，阻塞

（四）MDL表锁

• 对表进行事务操作的时候，系统会自动给表加上锁，保证读写的正确性，对表进行增删改查的操作的时候会自动增加MDL表锁，效果是写锁
• 事务的四种隔离性用到了MDL表锁来实现的
• 参考：https://blog.csdn.net/weixin_43761516/article/details/117751597