详解MySQL中的锁

君莫笑(๑˙ー˙๑)

已于 2023-03-03 15:33:40 修改

阅读量889

点赞数 1

分类专栏： MySQL进阶文章标签： mysql 数据库 java

于 2019-09-19 15:43:57 首次发布

本文链接：https://blog.csdn.net/weixin_44367006/article/details/101011838

版权

MySQL进阶专栏收录该内容

8 篇文章 2 订阅

订阅专栏

MySQL中的锁

概述
MyISAM中的锁
InnoDB中的锁
锁的等待问题

前置文章：
MySQL存储引擎

概述

为什么需要锁

到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？

解决方式：

我们先让库存表中物品数量减1
然后插入订单
付款后插入付款表信息
然后更新商品数量

在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

锁的概念

锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
锁对数据库而言显得尤其重要，也更加复杂。

MySQL中的锁

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

在这个部分只讲表级锁、行级锁，gap锁放到事务中讲

表锁与行锁的使用场景

表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如OLAP系统。
OLAP，On-Line Analytical Processing，联机分析处理。它使分析人员能够迅速、一致、交互地从各个方面观察信息，以达到深入理解数据的目的。

行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如OLTP系统。
OLTP，On-Line Transaction Processing，联机事务处理，也称为面向交易的处理。其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果，是对用户操作快速响应的方式之一。

很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适。

MyISAM中的锁

MySQL的表级锁有两种模式：

表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）

读锁兼容读锁，写锁不兼容任何锁。

共享读锁

语法：lock table 表名 read

示例（使用Navicat调试）：

CREATE TABLE testmyisam (
  id int(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM;

session1:
lock table testmyisam READ;
session2:
select * from testmyisam;
成功，其它session可以读当前表
session1:
insert into testmyisam value(2);
update testmyisam set id=2 where id=1;
报错，当前session不能写当前表
session2:
insert into testmyisam value(2);
等待，其它session写当前表会等待
session1:
insert into account value(4,'aa',123);
报错，当前session不能写其它表
select * from account ;
报错，当前session不能读其它表
session2:
insert into account value(4,'aa',123);
成功，其它session可以写其它表
session1/session2:
select s.* from testmyisam s;
报错，不能给当前表加别名
如果要加:
lock table 表名 as 别名 read;

独占写锁

语法：lock table 表名 write

示例：

session1:
lock table testmyisam WRITE;
insert testmyisam value(3);
delete from testmyisam where id = 3;
select * from testmyisam;
成功，当前session可以增删改查
session1:
insert into account value(4,'aa',123);
报错，当前session不能处理其它表
session2:
select * from testmyisam;
等待，其它session读当前表会等待
session1/session2：
select s.* from testmyisam s;
报错，不能给当前表加别名

解锁

unlock tables;

总结

读锁，对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
读锁，对MyISAM表的读操作，不会阻塞当前session对表读，当对表进行修改会报错
读锁，一个session使用LOCK TABLE命令给表加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误
写锁，对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作
写锁，对 MyISAM表的写操作，当前session可以对本表做CRUD,但对其他表进行操作会报错

InnoDB中的锁

在mysql 的 InnoDB引擎支持行锁

共享锁，又称读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。

排它锁，又称写锁。当一个事务对某几行上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

语法

共享锁：select * from 表 where 条件 lock in share mode；
排它锁：select * from 表 where 条件 for update；

示例：

CREATE TABLE testdemo (
id  INT(255) NOT NULL ,
c1  VARCHAR(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
c2  INT(50) NULL DEFAULT NULL ,
PRIMARY KEY (id),
INDEX idx_c2 (c2) USING BTREE 
)ENGINE=INNODB;
INSERT INTO testdemo VALUES(1,'1',1),(2,'2',2);

在这里插入图片描述

session1:
BEGIN;
select * from testdemo where id =1 for update;
session2:
update testdemo set c1 = '1' where id = 2;
成功，锁的是 id=1 的那一行
update testdemo set c1 = '1' where id = 1;
等待，锁的是 id=1 的那一行
session1:
BEGIN;
update testdemo set c1 = '1' where id = 1;
session2:
update testdemo set c1 = '1' where id = 1;
等待，写操作自动上锁
session1:
BEGIN;
update testdemo set c1 = '1' where c1 = '1';
session2:
update testdemo set c1 = '2' where c1 = '2';
等待，c1不是索引，锁的是全表
session1:
select * from testdemo where id =1 for update
session2:
select * from testdemo where id =1 lock in share mode
session1:
UNLOCK TABLES;
报错，使用 UNLOCK TABLES 不能解行锁
session1:
lock table testdemo WRITE;
使用commit，ROLLBACK 并不会解锁
使用UNLOCK TABLES 或者 begin 会解锁

解锁

行锁：commit / begin / rollback
表锁：unlock tables / begin

总结

两个事务不能锁同一个索引。
insert ，delete ， update在事务中都会自动默认加上排它锁。
行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

锁的等待问题

在工作中经常一个数据被锁住，导致另外的操作完全进行不下去。

你肯定碰到过这问题，有些程序员在debug程序的时候，经常会锁住一部分数据库的数据，而这个时候你也要调试这部分功能，却发现代码总是运行超时，你是否碰到过这问题了，其实这问题的根源我相信你也知道了。

举例来说，有两个会话。

程序员甲，正直调试代码

BEGIN;
SELECT * FROM testdemo WHERE id = 1 FOR UPDATE;

你正直完成的功能也要经过那部分的代码，你得上个读锁

BEGIN;
SELECT * FROM testdemo WHERE id = 1 lock in share mode;

这个时候很不幸，你并不知道发生了什么问题，在你调试得过程中永远就是一个超时的异常，而这种问题不管在开发中还是在实际项目运行中都可能会碰到，那么怎么排查这个问题呢？
在这里插入图片描述
这其实也是有小技巧的

在超时之前，再打开一个会话，执行：
select * from information_schema.INNODB_LOCKS;
在这里插入图片描述
真好，我通过这个sql语句起码发现在同一张表里面得同一个数据有了2个锁其中一个是X（写锁），另外一个是S（读锁），我可以跳过这一条数据，使用其他数据做调试

可能我就是绕不过，一定就是要用这条数据呢？吼一嗓子吧（哪个缺德的在debug这个表，请不要锁着不动）。好吧，这是个玩笑，其实还有更好的方式，来看：

select * from sys.innodb_lock_waits;
在这里插入图片描述
我现在执行的这个sql语句有了，另外看下最下面，kill命令，你在工作中完全可以通过kill吧阻塞了的sql语句给干掉，你就可以继续运行了，不过这命令也要注意使用过，如果某同事正在做比较重要的调试，你kill，被他发现可能会被暴打一顿。