MySQL——锁机制

1. 概述

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

2. 锁的分类

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

2.1.1 读锁（共享锁）

针对同一份数据，多个读操作可以同时进行而不会互相影响

2.1.2 写锁（排他锁）

当前写操作没有完成前，它会阻断其他写锁和读锁

2.2 从对数据操作粒度分

2.2.1 表锁（偏读）

1）特点：偏向MyISAM存储引擎，开销小，加锁块；无死锁；锁粒度大，发生冲突的概率最高，并发度最低

2）案例分析

2.1 建表

CREATE TABLE mylock(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
)ENGINE MYISAM CHARSET utf8;

INSERT INTO mylock(NAME) VALUES('a'),('b'),('c'),('d'),('e')

 

2.2 手动增加表锁

2.2.1 查看表上加过的锁：

2.2.2 加锁

语法格式：

lock table 表名称 read(write) ,表名称2 read(write)，其他;

例如：给mylock表添加读锁，给book表添加表

LOCK TABLE mylock READ,book WRITE;

查询锁状态：

2.2.3 释放锁

UNLOCK TABLES;

2.3 加读锁

为mylock表加read锁（读阻塞写例子）

session1	session2
-- 给mylock表添加读锁 LOCK TABLE mylock READ; -- 当前session可以查询该表记录 SELECT * FROM mylock;	其他session也可以查看：
当前session更新表数据：	其他session更新： 一直执行中
在当前session上读其他没加锁的表：	其他session读book表：
当前session解锁：	其他session的更新语句马上执行完毕，一共耗时2min

结论：

1）session1加了读锁，其他session1和session2都可以读

2）session1不能查询其他没加锁的表，session2可以

3）session1不能更新加了读锁的表，否则会报错，session2可以更新加了读锁的表，但是会一直阻塞等待session1解锁

2.4 加写锁

为mylock表加write锁（MyISAM存储引擎的写阻塞读例子）

session1	session2
	session2对标定表的查询被阻塞，需要等待锁的释放：
当前session查询其他未锁定的表：	session2可以查询其他未锁定的表：
session1解锁，session立马执行完毕，如图：

总结：

结论：读锁会阻塞写，但是不会阻塞读，而写锁则会把读和写都阻塞。

2.3 表锁分析

1）查看哪些表被加锁了：

show open tables;

2）如何分析表锁定：

可以通过检查table_locks_waited 和 table_locks_immediate状态变量来分析系统上的表锁定：

SQL：show status like 'table%'

这里的两个状态变量记录mysql内部表级锁定的情况，两个变量说明如下：

2.1 ）table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1

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

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

 

2.2.2 行锁（偏写）

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

2）与MyISAM比较：

2.1 支持事务

2.2 采用了行级锁

3）关闭自动提交

SET autocommit=0;

4）无索引行锁升级为表锁

5）如何锁定某一行

通过for update，例如；

SELECT * FROM test_innodb_lock WHERE a = 8 FOR UPDATE;

注意：利用for update锁定某一行之后，其他操作会被阻塞，直到锁定行的会话提交commit

总结：

        InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定。当系统并发量比较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。
        但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

6）行锁分析

6.1 如何分析行锁

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%'

对各个状态量的说明：

1）Innodb_row_lock_current_waits：当前正在等待锁定的数量

2）Innodb_row_lock_time：从系统启动到现在锁定总时间长度

3）innodb_row_lock_time_avg：每次等待所花的平均时间

4）innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间

5）innodb_row_lock_waits：系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：
innodb_row_lock_time_avg（等待平均时长）
innodb_row_lock_waits（等待总次数）
Innodb_row_lock_time（等待总时长）
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划

7）优化建议

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

2.2.3 间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是间隙锁（Next-key锁）

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

例如：A表中不存在id=2的记录，执行SQL:select * from A where id > 1 and id < 6

这时候，id=2的数据也会被锁住，如果有另一个线程执行语句insert into A（id） values(2)，这时候，插不进去，会阻塞直到A查询释放锁。

2.2.4 页锁

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