mysql/mariadb数据库乐观锁、悲观锁（包含共享锁、排他锁）分析

偏执的太偏执、 2022-04-16 05:12 365阅读 0赞

不少人在开发的时候，应该很少会注意到这些锁的问题，也很少会给程序加锁(除了库存这些对数量准确性要求极高的情况下)，即使我们不会这些锁知识，我们的程序在一般情况下还是可以跑得好好的。因为这些锁数据库隐式帮我们加了。

对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预。只会在某些特定的场景下才需要手动加锁。下面来详细介绍各种数据库锁。

**注：本文结论及截图是在mariadb10.3.8(存储引擎为innodb)上验证的，同时其结论在mysql5.6.4（存储引擎为innodb）也基本一致，其他引擎的表现会有较大的区别。**

## 一、乐观锁 ##

乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。乐观锁不是数据库自带的，需要我们自己去实现。

通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。举例：

下单操作包括3步骤：

1.查询出商品信息

select (status,status,version) from t\_goods where id=\#\{id\}

2.根据商品信息生成订单

3.修改商品status为2

update t\_goods

set status=2,version=version+1

where id=\#\{id\} and version=\#\{version\};

除了自己手动实现乐观锁之外，现在网上许多框架已经封装好了乐观锁的实现，如hibernate，需要时，可能自行搜索"hiberate 乐观锁"试试看。

## 二、悲观锁 ##

悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

开始一个事务，执行sql，结束一个事务的sql语句（以非autocommit模式为例）如下：

#设置MySQL为非autocommit模式
    set autocommit=0;
    
    # 设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：
    # 1. 开始事务
    begin;/begin work;/start transaction; (三者选一就可以)
    
    # 2. 查询表信息
    select status from TABLE where id=1 for update;
    
    # 3. 插入一条数据
    insert into TABLE (id,value) values (2,2);
    
    # 4. 修改数据为
    update TABLE set value=2 where id=1;
    
    # 5. 提交事务
    commit;/commit work;

共享锁与排他锁是悲观锁的两个实现。

1）共享锁：

共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁。共享锁又称**读锁 read lock**，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获得共享锁的事务只能读数据，不能修改数据。共享锁的实现即在执行语句后面加上lock in share mode就代表对某些资源加上共享锁了。具体例子如下所示：

在一个窗口中执行以下sql：

![20181117134205404.png][]

在另一个窗口中执行以下sql：

![20181117135014984.png][]

此时，该数据表更新操作因为之前加了共享锁的原因超时，失败。

如果在更新操作超时前，在第一个窗口中执行了commit操作，则该条记录可以成功更新。

在查询语句后面增加\*\* `LOCK IN SHARE MODE`\*\*，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。加上共享锁后，对于`update,insert,delete`语句会自动加排它锁。

2）排他锁

排他锁 exclusive lock（也叫writer lock）又称**写锁**。若事务 1 对数据对象A加上X锁，事务 1 可以读A也可以修改A，其他事务不能再对A加任何锁，直到事物 1 释放A上的锁。这保证了其他事务在事物 1 释放A上的锁之前不能再读取和修改A。排它锁会阻塞所有的排它锁和共享锁。读取为什么要加读锁呢：防止数据在被读取的时候被别的线程加上写锁，使用方式：在需要执行的语句后面加上`for update`就可以了。

## 三、锁的其他分类方式--粒度 ##

1、行锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

2、表锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

不同的存储引擎支持的锁粒度是不一样的：**InnoDB行锁和表锁都支持**！**MyISAM只支持表锁**！

InnoDB只有通过**索引条件**检索数据**才使用行级锁**，否则，InnoDB将使用**表锁，也就是说，InnoDB的行锁是基于索引的！**

## 四、谈谈死锁 ##

死锁（Deadlock）  所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

解除正在死锁的状态有两种方法：

**第一种**：

1.查询是否锁表

show OPEN TABLES where In_use > 0;

2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

show processlist

3.杀死进程id（就是上面命令的id列）

kill id

**第二种**：

1：查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

**杀死进程**

kill 进程ID

如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。 产生死锁的四个必要条件：

（1） 互斥条件：一个资源每次只能被一个进程使用。 （2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。 （3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。 （4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚而由应用程序重新提交。

**下列方法有助于最大限度地降低死锁：**

（1）按同一顺序访问对象。 （2）避免事务中的用户交互。 （3）保持事务简短并在一个批处理中。 （4）使用低隔离级别。 （5）使用绑定连接。

参考文章：

[https://juejin.im/post/5b5ea32351882519f6477c5c][https_juejin.im_post_5b5ea32351882519f6477c5c]

[https://juejin.im/post/5b55b842f265da0f9e589e79][https_juejin.im_post_5b55b842f265da0f9e589e79]

[20181117134205404.png]: /images/20220416/74333245883740f69e28f6a23ef0667f.png
[20181117135014984.png]: /images/20220416/b40423e6dbc94deabdef9d1d769ff53e.png
[https_juejin.im_post_5b5ea32351882519f6477c5c]: https://juejin.im/post/5b5ea32351882519f6477c5c
[https_juejin.im_post_5b55b842f265da0f9e589e79]: https://juejin.im/post/5b55b842f265da0f9e589e79