mysql学习笔记(6)之锁机制

最新推荐文章于 2024-09-24 09:50:23 发布

小鱼AS

最新推荐文章于 2024-09-24 09:50:23 发布

阅读量131

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_39775274/article/details/114013539

版权

mysql 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

锁机制

mysql锁基础
- innodb锁类型
排它锁与共享锁
死锁的产生与处理
乐观锁于悲观锁
- MVCC-多版本并发控制实现乐观锁
间隙锁与行锁升级为表锁

内容来源为六星教育，这里仅作为学习笔记

mysql锁基础

表级锁：它直接锁住的是一个表，开销小，加锁快，不会出现死锁的情况，锁定粒度大，发生锁冲突的概率更高，并发度最低。适用于 myisam innodb
页级锁：它是锁住的一个页面，在 InnoDB 中一个页面为16KB，它的开销介于表级锁和行级锁中间，也可能会出现死锁，锁定粒度也介于表级锁和行级锁中间，并发度也介于表级锁和行级锁中间。适用于 BDB
行级锁：它直接锁住的是一条记录，开销大，加锁慢，发生锁冲突的概率较低，并发度很高。适用于 innodb

innodb锁类型

InnoDB 实现了下面两种类型的锁：

(1)共享锁(S):允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
(2)排他锁(X):允许获得排他锁的事务更新数据，阻止其他事务获得相同数据集的共享读锁和排他写锁。
这里有个锁兼容和冲突的概念，如果在加一个锁的时候，另一个锁可以加上去，那么就是锁兼容。如果加上一个锁之后，拒绝其他的锁加上，那么就是锁冲突。
如果一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务，如果两者是冲突的，那么该事务就要等待锁释放。
对于 update、delete、insert 语句，InnoDB 会自动给设计到的数据集加排他锁
对于 select 语句，InnoDB 不会加任何锁。
我们可以用 select …in share mode 来获得共享锁，主要用在数据依存关系时来确认某行记录是否存在，并确认没有人对这个记录进行 update 或者 delete 操作。
我们可以使用 select… for update 来获得排他锁，它会拒绝其他事务在其上加其他锁。

排它锁与共享锁

语法：

排它锁：select * from user where id =1 for update;
for update表示对要检查的语句加排它锁
共享锁：select * from user where id =1 lock in share mode;
lock in share mode表示对要检查的语句加排它锁加共享锁

排它锁：排它锁与排它锁不能一起使用，排它锁与共享锁不能一起使用
共享锁：共享锁与共享锁可以一起使用
普通查询不会受到加锁的影响，他依然可以获取加锁后的数据
对于update ，insert ，alter等写操作，mysql在事务中会自动加上一把排它锁
select * from user where id =1;
begin / 默认会自带事务的开启以及提交
update user set username = ‘111’ where id =1;
commit

当前读取：一般只的是加锁的select
快照读取：一般只的是没加锁的普通查询

事务1 加锁 select * from product where id = 1 for update;
事务2 select * from product where id = 1 for update;

使用我们的业务强制进入串行化

死锁的产生与处理

产生原因：

是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。
死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。
那么对应的解决死锁问题的关键就是：让不同的session加锁有次序
死锁的现象
事务1事务2分别对id为1与id为2的数据进行排它锁加锁，随后进行交叉的数据修改。

事务运行顺序	事务1	事务2
1	begin	begin
2	select * from user where id = 1 for update;	select * from user where id = 2 for update;
3	update user set username = ‘shine York’ where id =2;	update user set username = ‘starsky’ where id =1;
4	commit	commit

运行结果：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
还有一种可能不会直接报错，而是事务1在等待事务2 ，事务2在等待事务1，

锁等待的时间 wait_timeout=120 --设置的是锁等待的时间
锁等待时间结束之后会有一个失败和一个成功
mysql的选择与事务的大小有关 select 舍小保大

一般死锁会出现在模糊查询的查询范围的查询里加锁的情况里
3. 可能的避免事务死锁

1）以固定的顺序访问表和行。比如对第2节两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；又比如对于3.1节的情形，将两个事务的sql顺序调整为一致，也能避免死锁。
2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

乐观锁于悲观锁

悲观锁：悲观，意味着每次去拿数据的时候都认为别人会修改悲观锁不管是加什么锁，只要是上了锁都属于悲观锁
乐观锁：乐观，意味着每次去拿数据的时候都认为别人不会修改比如说MVCC

MVCC-多版本并发控制实现乐观锁

本质
乐观并发控制和悲观并发控制都是通过延迟或者终止相应的事务来解决事务之间的竞争条件来保证事务的可串行化；虽然前面的两种并发控制机制确实能够从根本上解决并发事务的可串行化的问题，但是其实都是在解决写冲突的问题，两者区别在于对写冲突的乐观程度不同 (悲观锁也能解决读写冲突问题，但是性能就一般了)。而在实际使用过程中，数据库读请求是写请求的很多倍，我们如果能解决读写并发的问题的话，就能更大地提高数据库的读性能，而这就是多版本并发控制所能做到的事情。

与悲观并发控制和乐观并发控制不同的是，MVCC 是为了解决读写锁造成的多个、长时间的读操作饿死写操作问题，也就是解决读写冲突的问题。MVCC 可以与前两者中的任意一种机制结合使用，以提高数据库的读性能。

数据库的悲观锁基于提升并发性能的考虑，一般都同时实现了多版本并发控制。不仅是 MySQL，包括 Oracle、PostgreSQL 等其他数据库系统也都实现了 MVCC，但各自的实现机制不尽相同，因为 MVCC 没有一个统一的实现标准。

总的来说，MVCC 的出现就是数据库不满用悲观锁去解决读 - 写冲突问题，因性能不高而提出的解决方案。

实现方式
MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。每个事务读到的数据项都是一个历史快照，被称为快照读，不同于当前读的是快照读读到的数据可能不是最新的，但是快照隔离能使得在整个事务看到的数据都是它启动时的数据状态。而写操作不覆盖已有数据项，而是创建一个新的版本，直至所在事务提交时才变为可见。
实例：
一张简化的商品表：

字段	描述
id	主键id
product_name	商品名称
product_price	商品价格
product_stock	商品库存
version	数据版本（新增默认为1）

<?php
try {
  $pdo=new pdo("mysql:host=localhost;dbname=mysql_php", "root", "root", array(PDO::ATTR_AUTOCOMMIT=>0));//最后是关闭自动提交
  $pdo->setAttribute(PDO::ATTR_ERRMODE,  PDO::ERRMODE_EXCEPTION);//开启异常处理
  try {
    $pdo->beginTransaction();//开启事务处理
    $product_id = 1;//商品id
    $sql = "select * from product where id = {$product_id}";
    $restful=$pdo->query($sql)->fetch(PDO::FETCH_ASSOC);
    if (!$restful) {
      echo "商品不存在：".$e->getMessage();
      exit;
    }else{
      $version = $restful['version'];//商品的版本号
      $sql = "update product set product_stock=product_stock-1,version = version+1 where id = {$product_id} and version = {$version}";
      //当下个请求发生时会出现数据库修改失败，因为版本号不对
      $restful=$pdo->exec($sql);
      if ($restful) {
        $pdo->commit();//事务提交
        echo "修改库存成功";
        exit;
      }else{
        echo "修改库存失败";
        exit;
      }
    }

  } catch (\Exception $e) {
    echo $sql.$e->getMessage();
    exit;
  }

} catch (\Exception $e) {
  echo "数据库连接失败：".$e->getMessage();
  exit;
}
?>

当前读取和快照读取

当前读取

像 select lock in share mode (共享锁), select for update ; update, insert ,delete (排他锁) 这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

快照读取

像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是未提交读和串行化级别，因为未提交读总是读取最新的数据行，而不是符合当前事务版本的数据行。而串行化则会对所有读取的行都加锁

优缺点

MVCC 使大多数读操作都可以不用加锁，这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

间隙锁与行锁升级为表锁

间隙锁
原因

当我们使用的范围查询而不是等式查询，并使用共享锁或排他锁的时候

危害

如果在查询中通过范围去查询，锁锁住的范围会是所有索引的键值。即使这个数据不存在

测试
数据表此时的数据如下：
```
mysql> select * from user;
+----+------------+------+
| id | username   | age  |
+----+------------+------+
|  1 | shine York |   20 |
|  2 | starsky    |   20 |
|  4 | will       |   10 |
|  5 | harry      |   10 |
|  7 | cara       |   30 |
|  8 | jace       |   40 |
+----+------------+------+
6 rows in set (0.00 sec)
```
注意表中的数据，包含1,2,4,5,7,8…当然缺少3,6，没有连续下来.

事务1 事务2
begin begin
select * from user id between 1 and 5 for update insert into user(id,username,age)values(3,‘php’,30)
commit commit
运行结果：
可以看到，事务2在执行新增id为2的数据时出现了所等待现象。说明id为2的数据被事务1进行的范围查询加锁锁住，其他事务需要等到事务1进行提交或者回滚之后才能继续操作事务1锁住的数据。
行锁升级为表锁
众所周知，MySQL 的 InnoDB 存储引擎支持事务，支持行级锁（innodb的行锁是通过给索引项加锁实现的）。得益于这些特性，数据库支持高并发。如果 InnoDB 更新数据使用的不是行锁，而是表锁呢？是的，InnoDB 其实很容易就升级为表锁，届时并发性将大打折扣了。

常用的索引有三类：主键、唯一索引、普通索引。主键不由分说，自带最高效的索引属性；唯一索引指的是该属性值重复率为0，一般可作为业务主键，例如学号；普通索引与前者不同的是，属性值的重复率大于0，不能作为唯一指定条件，例如学生姓名。

下面我们来看下使用索引与不使用索引的情况下加锁会不会升级会表锁。

在不使用索引的情况下进行加锁

事务1 事务2
begin begin
select * from user where age = 20 for update select * from user where age = 10 for update
commit commit
运行结果：
在不给age字段加索引的情况下进行排它锁的加锁操作，可以看到尽管加锁的数据是不同的，但是事务2在加锁时出现了所等待现象。说明此时事务1从行锁升级为表锁，导致事务2在给age=10的数据加锁时出现了所等待现象。

在使用普通索引的情况进行加锁
alter table user add index idx_age(age); --给age字段加个索引

事务1 事务2
begin begin
select * from user where age = 20 for update select * from user where age = 10 for update
commit commit
运行结果：
在加了索引之后，再一次进行以上操作，可以看到，user表不在进行表锁，那是因为行锁是建立在索引字段的基础上，如果行锁定的列不是索引列则会升级为表锁

范围性查询测试

事务1 事务2
begin begin
select * from user where age between 10 and 20 for update select * from user where age = 30 for update
commit commit
运行结果：
当要进行加锁的数据不确定时，也一样会是表锁。

总结：

行锁是建立在索引的基础上。
在不加索引的字段（除主键以及唯一索引之外）上进行数据的加锁，会升级为表锁
在加了索引之后加锁会根据普通索引的基础上去进行加锁，而一旦索引的数据重复率过高导致索引失效，就会升级会表锁。
1. 会尽可能是索引字段进行加锁
2. 尽可能使用等式而不是范围性的查询。
不只是排它锁，共享锁也是一样的道理