乐趣区

关于mysql:举例解释一下MySQL的表锁和行锁

jiezi

一、锁的概述

在计算机中，锁是协调多个过程或线程并发拜访某一资源的一种机制。

在数据库中，除传统的计算资源（如 CPU、RAM、I/ O 等）的争用以外，数据也是一种供许多用户共享的资源。

如何保证数据并发拜访的一致性、有效性是所有数据库必须解决的一个问题，锁抵触也是影响数据库并发拜访性能的一个重要因素。

从这个角度来说，锁对数据库而言显得尤其重要，也更加简单。

二、锁的分类

2.1 按数据操作的类型分类：

  • 读锁（共享锁）：针对同一份数据，多个读操作能够同时进行而不会相互影响。
  • 写锁（排它锁）：以后操作没有实现之前，它会阻断其余的写操作或读操作。

2.2 按数据操作的粒度分：

  • 表锁：操作时，会锁定整个表。开销小，加锁快；不会呈现死锁；锁定力度大，产生锁抵触概率高，并发度最低。
  • 行锁：操作时，会锁定以后操作的表的某些行。开销大，加锁慢；会呈现死锁；锁定粒度小，产生锁抵触的概率低，并发度高。
  • 页锁：操作时，会锁定以后操作的表的某些页。开销和加锁速度介于表锁和行锁之间；会呈现死锁；锁定粒度介于表锁和行锁之间，并发度个别。

很难抽象地说哪种锁更好，只能就具体利用的特点来说哪种锁更适合！

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

三、MySQL 的锁

绝对其余数据库而言，MySQL 的锁机制比较简单，其最显著的特点是不同的存储引擎反对不同的锁机制。InnoDB 存储引擎反对行锁和表锁，MEMORY 和 MyISAM 等存储引擎只反对表锁。

3.1 MyISAM 的表锁

MyISAM 存储引擎在执行查问语句（SELECT），会主动给波及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等），会主动给波及的表加写锁，这个过程并不需要用户干涉。

为了不便演示，咱们利用零碎提供的显式加锁指令：

  • 读锁：lock table table_name read;
  • 写锁：lock table table_name write;
  • 解锁：unlock tables;

建设一个 MyISAM 存储引擎的表，并插入一些数据：

create table tb_myisam(
id INT AUTO_INCREMENT,
name VARCHAR(30),
age INT,
PRIMARY KEY(id)) engine=myisam;
 
insert into tb_myisam(name,age) value('zhang3',20);
insert into tb_myisam(name,age) value('li4',21);
insert into tb_myisam(name,age) value('wang5',22);

3.1.1 在两个会话终端来演示 MyISAM 的读锁：

3.1.2 演示 MyISAM 的写锁：

3.2 InnoDB 的表锁和行锁

InnoDB 存储引擎在执行更新操作（UPDATE、DELETE、INSERT 等），会主动给波及的表或行加写锁。InnoDB 存储引擎对于一般 SELECT 语句，不会加任何锁。如果读的数据正在执行 UPDATE 或 DELETE 操作，这时读操作不会期待写锁的开释，而是间接读取该数据的快照，具体读取那份快照数据，和零碎的隔离级别无关。对于 SELECT 语句，零碎提供了显式加锁指令：

  • 读锁：select * from table_name where … lock in share mode;
  • 写锁：select * from table_name where … for update;

建设一个 MyISAM 存储引擎的表，并插入一些数据：

create table tb_innodb (
id INT AUTO_INCREMENT,
name VARCHAR(30),
age INT,
PRIMARY KEY(id)) engine=innodb;
 
insert into tb_innodb(name,age) value('zhang3',20);
insert into tb_innodb(name,age) value('li4',21);
insert into tb_innodb(name,age) value('wang5',22);

为了不便演示，咱们须要关闭系统的主动提交 3.2.1 演示 InnoDB 表的读锁：

3.2.2 演示 InnoDB 表的写锁：

3.2.3 演示 InnoDB 行的读锁：

3.2.4 演示 InnoDB 行的写锁：

KunlunDB 我的项目已开源

【GitHub：】
https://github.com/zettadb

【Gitee：】
https://gitee.com/zettadb

END

退出移动版