Mysql中各类锁的机制图文详细解析（全）

前言

尽量配合自已的数据库来操作
可以更加容易的理解

登录自身的mysql 以及建立表格需要会一些

这是下面所需要用到的数据库表，文中也有给出如何建立

1. 定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。

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

锁的分类

从对数据操作的类型

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

从对数据操作的粒度分

• 表锁
• 行锁

1. 表锁（读写锁）

• 表锁：
  特点：偏向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');

• 上锁的过程：lock table 表名字 read(write), 表名字2 read(write), 其他;
  

• 查看表加过的锁：show open tables;
  
  

• 释放所有表的锁：unlock tables;

---

案例1：
1. 如果A加了读锁

A可以查自已的表，但不能更新或者增加自已的表数据。

B可以查A的表，但不能更新或者增加A的表数据（会阻塞），直到A释放了读锁

直到A释放锁，B才可以修改不然就一直阻塞。

---

案例2：

2. 如果A加了写锁

A可以查自已的表，也可以更新或者添加自已的表数据

B不可以查A的表，也不能更新或者增加A的数据（会阻塞），直到A释放了写锁

通过如上的加读写锁：

• 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

• 对MyISAM表的写操作〈加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定
通过这个命令行show status like 'table_locks%';

• Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1 ;
• Table_locks_waited：出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在着较严重的表级锁争用情况；

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

2. 行锁

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

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

关于事务的相关内容可看我之前的文章
数据库中事务(Transaction)的详解

建立一张sql表

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);

怎么操作行锁，只需要将其自动提交关闭即可
通过命令行的 SET autocommit=0;该命令是开启手动提交事务

---

案例1：

• 两个进程都开启手动提交，而且修改同一行数据

如果A进程修改了事务，B事务打算修改的话，进程会被阻塞

只有当A提交了事务之后，B进程才会解除阻塞

---

案例2：

• 两个进程都开启手动提交，修改不同行数据

都是可以的，但是事务不一样，只有都提交的时候，才会更新

• 如果提交事务的过程中，类型出错
  类型转换导致索引失效，失效之后只能全表锁定，因为innodb是索引加锁。行锁变表锁

由于表锁了，即使A提交了事务，B想修改事务（即使不同行也回阻塞），也会被阻塞。只有等他到表锁他自动解除

补充行锁的总结：
nnodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MylISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

如何分析行锁定

通过检查lnnoDB_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：系统启动后到现在总共等待的次数；（等待总次数）

具体可以通过如下进行优化：

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

3. 页级锁

(1) 描述

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折中的页级锁，一次锁定相邻的一组记录。BDB 支持页级锁。

(2) 特点

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

4. 间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。加了间隙锁，但是想添加间隙的东西，发现不可修改

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

危害

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

补充：
如何锁定一行的数据（排他锁）

• begin
• sql数据 for update
• commit

只有commit 才不会阻塞

添加共享锁是：
加共享锁可以使用select ... lock in share mode语句

5. 总结

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

6. 补充

关于mysql的锁还有一些跟java很类似
可看我之前的文章进行一一比较对比
java中各类锁的机制详细解析（全）

类似的还有悲观锁乐观锁，和java中都比较相像