mysql 锁

第一章 概述

 

锁的分类:从对数据操作的粒度分表锁、行锁。从对数据的操作类型(读\写)分 读锁(共享锁)、写锁(排它锁)

 

读锁(共享锁):针对同一份数据，多个读操作可以同时进行而不会互相影响。

写锁(排它锁):当前写操作没完成前，它会阻断其他写锁和读锁。

 

第二章 表锁(偏读)

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

 

关闭自动提交

set autocommit=0;

 

一、表级锁争用情况

 

show status like 'table%'

 

table_locks_immediate:产生表级锁的次数，表示可以立即获取锁的查询次数，每立即获取锁值加一

table_locks_waited:出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，没等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况；

 

 

 

二、查看是否有表被锁。

 show open tables

 

 

 

三、读锁

1．命令

 mylock为表名

 lock table mylock read,book write;

 

2．某张表添加读后出现的情况

 

①　当前session可以查询该表记录

②　当前session不能查询其他没有绑定的表

③　其他session可以查询该表的记录

④　其他session可以查询或者更新未锁定的表。

⑤　其他session插入或者更新锁定表会一直等待直到获得锁。

 

四、写锁

 book 为表名

 lock table book write;

 

 

 

 

五、解锁

1．手动解锁

UNLOCK TABLES;

 

 

 

第三章 行锁（偏写）

 

一、特点

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

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

 

二、分析行级锁争用情况

 

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:系统启动后到现在总共等待的次数；

 

 

 

 

 

三、事务

 

1．事务(Transaction)以及ACID属性

 

2．并发事务处理带来的问题

 

①　更新丢失

②　脏读

③　不可重复读

④　幻读

 

3．事务隔离级别

 

四、如何锁定一行

 

select * from test_innodb_lock where a=8 for update;

 

 

五、无索引行锁升级为表锁

无索引行锁升级为表锁

 

InnoDB只有在通过索引条件检索数据时使用行级锁，否则使用表锁！

总结：InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

 

 索引失效的情况：https://www.cnblogs.com/areyouready/p/7802885.html

 

 

六、间隙锁危害

 

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

InnoDB也会对这个"间隙" 加锁，这种锁机制就是所谓的间隙锁(next-key锁)。

 

危害：因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

 

 

 

 

 

 

 

七、优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

合理设计索引，尽量缩小锁的范围

尽可能减少检索条件，避免间隙锁

尽量控制事务大小，减少锁定资源量和时间长度

尽可能低级别事务隔离

 

第四章 页锁

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