深入理解Mysql——锁、事务与并发控制

缺乏、安全感 2022-05-21 08:23 228阅读 0赞

下面大部分博文转自https://blog.csdn.net/lemon89/article/details/51477497

SQL 语句主要可以划分为以下 3 个类别。

DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。

DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）

DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

DDL 语句：

DDL 是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL 语句更多的被数据库管理员（DBA）所使用，一般的开发人员很少使用。

# mysql并发控制——共享锁、排他锁 #

## 共享锁 ##

共享锁也称为读锁，读锁允许多个连接可以同一时刻并发的读取同一资源,互不干扰；

## 排他锁 ##

排他锁也称为写锁，一个写锁会阻塞其他的写锁或读锁，保证同一时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。

## 锁策略 ##

锁的开销是较为昂贵的，锁策略其实就是保证了线程安全的同时获取最大的性能之间的平衡策略。

*  **mysql锁策略：talbe lock(表锁)**

表锁是mysql最基本的锁策略，也是开销最小的锁，它会锁定整个表；

具体情况是：若一个用户正在执行写操作，会获取排他的“写锁”，这可能会锁定整个表，阻塞其他用户的读、写操作；

若一个用户正在执行读操作，会先获取共享锁“读锁”，这个锁运行其他读锁并发的对这个表进行读取，互不干扰。只要没有写锁的进入，读锁可以是并发读取统一资源的。

通常发生在DDL语句\\DML不走索引的语句中，比如这个DML update table set columnA=”A” where columnB=“B”.

如果columnB字段不存在索引（或者不是组合索引前缀），会锁住所有记录也就是锁表。如果语句的执行能够执行一个columnB字段的索引，那么会锁住满足where的行(行锁)。

通过SELECT \* from information\_schema.INNODB\_TRX这一句可以看每个事务的锁的情况

我在事务A中查询一条数据

select \* from tab1 where column1 = xxx for update 　　column1是没有索引的

这时trx\_rows\_locked锁的是全表的数量,当我给column1加上索引时,就只锁了一行

*  **mysql锁策略：row lock(行锁)**

行锁可以最大限度的支持并发处理，当然也带来了最大开销，顾名思义，行锁的粒度实在每一条行数据。

## REPEATABLE READ(可重复读) ##

一个事务中多次执行统一读SQL,返回结果一样。   
这个隔离级别解决了脏读的问题，幻读问题(现在网络上还有很多文章说，Repetable read级别会有幻读问题，其实已经没有了)。这里指的是innodb的rr级别，innodb中使用next-key锁对”当前读”进行加锁，锁住行以及可能产生幻读的插入位置，阻止新的数据插入产生幻行。

# 多版本并发控制-MVCC #

MVCC(multiple-version-concurrency-control）是个**行级锁**的变种，它在**普通读情况下避免了加锁操作，因此开销更低**。   
虽然实现不同，但通常都是实现**非阻塞读**，对于**写操作只锁定必要的行**。

*  **一致性读 （就是读取快照）**   
    select \* from table ….;
 *  **当前读(就是读取实际的持久化的数据)**   
    特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。   
    select \* from table where ? lock in share mode;   
    select \* from table where ? for update;   
    insert;   
    update ;   
    delete;

**注意：select …… from where…… （没有额外加锁后缀）使用MVCC，保证了读快照(mysql称为consistent read)，所谓一致性读或者读快照就是读取当前事务开始之前的数据快照，在这个事务开始之后的更新不会被读到。详细情况下文select的详述。**

**对于加锁读SELECT with FOR UPDATE(排他锁) or LOCK IN SHARE MODE(共享锁)、update、delete语句，要考虑是否是唯一索引的等值查询。**

MVCC 可以保证,假如说你这条数据被很多地方用到,然后我这个地方加了锁,不会影响其它没加锁的地方,比如说我加了for update,如果没有MVCC，则所有查询都会被阻塞，有了MVCC，就只有同样加for update会被阻塞

## 写锁-recordLock,gapLock,next key lock ##

**对于使用到唯一索引 等值查询：比如，where columnA=”…” ，如果columnA上的索引被使用到，**

**那么会在满足where的记录上加行锁(for update是排他锁,lock in shared 是共享锁，其他写操作加排他锁)。这里是行级锁，record lock。**

我拿id作了验证,只锁住了相关行

**对于范围查询（使用非唯一的索引）：**

**比如(做范围查询)：where columnA between 10 and 30 ,会导致其他会话中10以后的数据都无法插入(next key lock),从而解决了幻读问题。**

**![70][]**

**事务１相关行是25行**

**我们用select \* from information\_schema.INNODB\_TRX查询一下锁住的行**