mysql数据库锁机制

你的名字 2022-12-16 09:15 152阅读 0赞

## 为什么要加锁 ##

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

**锁是用于管理对公共资源的并发控制**

## 首先对mysql锁进行划分： ##

按照锁的粒度划分：行锁、表锁、页锁

按照锁的使用方式划分：共享锁、排它锁（悲观锁的一种实现）

还有两种思想上的锁：悲观锁、乐观锁

## 行锁 ##

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。**行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。有可能会出现死锁的情况。** 行级锁按照使用方式分为共享锁和排他锁

**共享锁用法（S锁 读锁）**：

若事务T对数据对象A加上S锁，则事务T**可以读A但不能修改A**，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

select ... lock in share mode;

**共享锁就是允许多个线程同时获取一个锁，一个锁可以同时被多个线程拥有。**

**排它锁用法（X 锁 写锁）**：

若事务T对数据对象A加上X锁，事务T**可以读A也可以修改A**，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

select ... for update

**排它锁，也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。**

## 表锁 ##

**表级锁加锁力度最大，发生锁冲突的概率很大，但锁的开销少，不会出现死锁的情况**，被大部分的mysql引擎支持，MyISAM和InnoDB都支持表级锁，但是InnoDB默认的是行级锁。

**共享锁用法**：

LOCK TABLE table_name [ AS alias_name ] READ

**排它锁用法**：

LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE

**解锁用法**：

unlock tables;

## 页锁 ##

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

## 悲观锁 ##

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段

悲观锁：如果一个事务执行的操作对某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中

悲观锁实际上是采取了“先取锁在访问”的策略，为数据的处理安全提供了保证，但是在效率方面，由于额外的加锁机制产生了额外的开销，并且增加了死锁的机会。并且降低了并发性

## 乐观锁 ##

乐观锁：多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚

相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本，当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

**乐观锁的优点和不足**：

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题

### InnoDB锁的特性 ###

*  在不通过索引条件查询的时候，InnoDB使用的确实是表锁
 *  由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行 的记录,但是如果是使用相同的索引键,是会出现锁冲突的
 *  当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁
 *  即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划（explain查看）,以确认是否真正使用了索引

**通过非索引项检索数据，加表锁！**

price属性并没有加索引，因此这时候添加的锁为表级锁！
    窗口1：
    mysql> select * from product where price=88 for update;
    +----+------+-------+-----+
    | id | name | price | num |
    +----+------+-------+-----+
    |  2 | 蒙牛 |    88 |   1 |
    +----+------+-------+-----+
    
    窗口2：
    mysql> update product set price=price-100 where id=6;
    这里会等待，直到窗口1 commit后显示下面结果！
    Query OK, 1 row affected
    Rows matched: 1  Changed: 1  Warnings: 0

**2.使用相同索引值但是不同行引发的冲突**

这里的num属性 加上了普通索引，price属性并没有索引
    窗口1：
    mysql> set autocommit=0;
    Query OK, 0 rows affected
    
    mysql> select * from product where num=1 and price=68 for update;
    +----+------+-------+-----+
    | id | name | price | num |
    +----+------+-------+-----+
    |  1 | 伊利 |    68 |   1 |
    +----+------+-------+-----+
    
    窗口2：
    mysql> update product set price=price+100 where num=1 and price=88;
    这里会发生等待，直到窗口1 commit 显示下面结果
    Query OK, 1 row affected
    Rows matched: 1  Changed: 1  Warnings: 0
    mysql> select * from product;
    +----+----------+-------+-----+
    | id | name     | price | num |
    +----+----------+-------+-----+
    |  1 | 伊利     |    68 |   1 |
    |  2 | 蒙牛     |   188 |   1 |
    +----+----------+-------+-----+

**3.当使用索引检索数据时不同事务可以操作不同行数据**

锁一行数据，DML操作其他行并没有影响
    窗口1：
    mysql> select * from user where id=1 for update;
    +----+-------+
    | id | price |
    +----+-------+
    |  1 |   400 |
    +----+-------+
    
    窗口2：
    mysql> update user set price=price+100 where id=2;
    无需等待窗口1 commit
    Database changed
    Rows matched: 1  Changed: 1  Warnings: 0

## 为什么会发生死锁  ##

MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。（不过现在一般都是InnoDB引擎，关于MyISAM不做考虑）

在InnoDB中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70][]

这种情况很好理解，首先session1获得 id=1的锁 session2获得id=5的锁，然后session想要获取id=5的锁 等待，session2想要获取id=1的锁 ，也等待！

**为什么锁住唯一索引后，主键索引也会被锁住？**

这个是由InnoDB索引的存储和检索方式决定的。辅助索引中存储的是二级索引和主键的ID，所以锁住辅助索引后，会根据主键ID找到对应的主键索引，也锁定之（图中红色箭头）。

而通过主键索引检索数据加锁，则只会锁住主键索引（图中绿色箭头）。

![27fe53019a91d3a43fa1848ce6bb7069.png][]

**2、为什么表没有索引，表里所有的记录都会被锁住？**

当表上没有创建索引的时候，InnoDB会为每一行创建一个隐藏的主键作为聚集索引。这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。

当不通过索引检索数据的时候，MySQL会使用全表扫描，此时所有行的索引都会被锁定，行锁升级为表锁

## 如何避免死锁： ##

*  如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
 *  在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
 *  对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70]: /images/20221123/eca754082b5f4aaeb1094664cae3ceeb.png
[27fe53019a91d3a43fa1848ce6bb7069.png]: /images/20221123/1bbc2e0b0ec948ff82299dccdcd50124.png