mysql中的行级锁、表级锁、页级锁

快来打我* 2022-12-23 11:16 306阅读 0赞

在DBMS中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

## 行级锁 ##

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为**`共享锁`** 和 **`排他锁`**。

### 特点 ###

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

共享锁（S锁）：共享 (S) 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。

1.  如果事务T对数据A加上共享锁，则其他事务只能对A再加共享锁，不能加排它锁。只有事务T可以对A进行读取和修改，其他事务只能读取数据而不能修改数据。

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他锁（X锁）：用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。

1.  如果事务T对数据A加上排它锁，只有事务T可以对A进行读取和修改，其他任何事务都不能对A进行读取和修改。

排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

## 表级锁 ##

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为**`表共享读锁`（共享锁）**与**`表独占写锁`（排他锁）**。

### 特点 ###

开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

## 页级锁 ##

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

![06a81e4dd042c0c1afb0fbbdab963af5.png][]

### 特点 ###

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

## MySQL常用存储引擎的锁机制 ##

> MyISAM和MEMORY采用表级锁(table-level locking)
> 
> BDB采用页面锁(page-level locking)或表级锁，默认为页面锁
> 
> InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

## Innodb中的行锁与表锁 ##

前面提到过，在Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

## 行级锁与死锁 ##

MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。

在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。 在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。

当两个事务同时执行，一个锁住了逐渐索引在等待其他相关索引，一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。

发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。

**有多种方法可以避免死锁，这里只介绍常见的三种**

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

参考：

[https://blog.csdn.net/weixin\_37598682/article/details/90080008][https_blog.csdn.net_weixin_37598682_article_details_90080008]

[06a81e4dd042c0c1afb0fbbdab963af5.png]: /images/20221120/7bad3791fa33429c81f794870382950e.png
[https_blog.csdn.net_weixin_37598682_article_details_90080008]: https://blog.csdn.net/weixin_37598682/article/details/90080008