面试官:请用SQL模拟一个死锁

文章首发于公众号:BiggerBoy

有读者说面试被问到怎么用SQL模拟数据库死锁?
这位读者表示对Java中的死锁还是略知一二的,但是突然用SQL写死锁的案例之前还真没遇到过,这个问题没答上来。所以今天就带大家一起来看下怎么用SQL让数据库中产生死锁。

什么是死锁

说到死锁,我们来回顾一下什么是死锁。

[En]

Speaking of deadlocks, let’s review what deadlocks are.

死锁是指在执行过程中,由于两个或多个进程之间竞争资源或通信而导致的阻塞现象,如果没有外力,就无法向前推进。此时,就会说系统处于死锁状态或者系统出现了死锁,而这些总是在等待对方的进程被称为死锁进程。

[En]

Deadlock refers to a blocking phenomenon caused by competition for resources or communication between two or more processes in the process of execution, which will not be able to move forward without external force. At this point, it is said that the system is in a deadlock state or the system has a deadlock, and these processes that are always waiting for each other are called deadlock processes.

数据库死锁是指两个资源互相等待,如果需要”修改”一条数据,首先数据库管理系统会在上面加锁,以保证在同一时间只有一个事务能进行修改操作。锁定(Locking)发生在当一个事务获得对某一资源的”锁”时,这时,其他的事务就不能更改这个资源了,这种机制的存在是为了保证数据一致性。

数据库死锁示例

好,我们复习之后再回到今天的话题。

[En]

All right, let’s get back to today’s topic after review.

有如下两个事务:
事务1先执行SQL1,更新id=1的,然后执行SQL2,更新id=2的。
事务2恰恰相反,它先更新id=2的,再更新id=1的。

SQL代码如下:

-- 事务1
begin;
-- SQL1更新id为1的
update user set age = 1 where id = 1;
-- SQL2更新id为2的
update user set age = 2 where id = 2;
commit;
-- 事务2
begin;
-- SQL1更新id为2的
update user set age = 3 where id = 2;
-- SQL2更新id为1的
update user set age = 4 where id = 1;
commit;

我们怎么手动操作模拟一下呢?

先执行事务1的SQL1

面试官:请用SQL模拟一个死锁

再执行事务2的SQL1

面试官:请用SQL模拟一个死锁

此时不会有什么问题。
接着,我们执行事务1的SQL2。此时这条SQL没有执行成功,一直在等待,如下如所示,”查询时间”一直在增加

面试官:请用SQL模拟一个死锁

然后执行事务2的SQL2,事务2报错,”Deadlock found when trying to get lock; try restarting transaction”,即数据库发现死锁了。

面试官:请用SQL模拟一个死锁

此时执行事务1的commit操作,再查看数据,id为1和2的age字段分别被修改为了1和2,即事务1执行成功。事务2即使再执行commit数据也不会发生变化,因为事务2报错终止操作被回滚了。

面试官:请用SQL模拟一个死锁

怎么造成死锁的呢?

让我们画一幅图来了解死锁是如何引起的。(事务处理1和事务处理2的向下箭头表示时间线)

[En]

Let’s draw a picture to understand how deadlocks are caused. (the downward arrows for transaction 1 and transaction 2 indicate the timeline)

面试官:请用SQL模拟一个死锁

当事务1和事务2都开始执行,如果都执行到第一个SQL时,是不会产生死锁的,因为操作的是不同的行,此时事务1对id=1的这条记录加了独占锁,事务2对id=2的这条记录加了独占锁,由于事务都没提交,所以这两个独占锁都没有释放。
然后两个事务都继续往下执行,我们手动控制了事务1先执行它的SQL2,即更新id=2的这条记录,由于id=2的这条记录被事务2锁着,所以这条SQL语句会被阻塞,一直等待,也就是上述图中显示的”查询时间”。
接着事务2执行它的SQL2,即更新id=1的这条记录,又因为事务1锁着id=1的这条记录,所以,此时形成了相互等待对方持有的锁的局面,即发生了死锁。但,数据库不会任由这两个事务一直等待下去,所以事务2执行SQL2时提示死锁,”Deadlock found when trying to get lock; try restarting transaction”,事务1不受影响,commit之后事务1执行成功。
此时可以通过看数据库状态,找到死锁相关的信息
SHOW ENGINE INNODB STATUS;

面试官:请用SQL模拟一个死锁

将status字段内容复制出来,由于内容太多,这里只贴出和死锁相关的,如下:

2022-04-23 15:47:53 0x10d08
*** (1) TRANSACTION:
TRANSACTION 202027, ACTIVE 20 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 68972, query id 398 localhost ::1 root updating
-- SQL2更新id为2的
update user set age = 2 where id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 495 page no 3 n bits 72 index PRIMARY of table walking_mybatis.user trx id 202027 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000003152c; asc      ,;;
 2: len 7; hex 4000000132303f; asc @   20?;;
 3: len 16; hex 77616c6b696e67383634353532303835; asc walking864552085;;
 4: len 1; hex 30; asc 0;;
 5: len 4; hex 80000003; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 202028, ACTIVE 12 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 68872, query id 402 localhost ::1 root updating
-- SQL2更新id为1的
update user set age = 4 where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 495 page no 3 n bits 72 index PRIMARY of table walking_mybatis.user trx id 202028 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000003152c; asc      ,;;
 2: len 7; hex 4000000132303f; asc @   20?;;
 3: len 16; hex 77616c6b696e67383634353532303835; asc walking864552085;;
 4: len 1; hex 30; asc 0;;
 5: len 4; hex 80000003; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 495 page no 3 n bits 72 index PRIMARY of table walking_mybatis.user trx id 202028 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000003152b; asc      +;;
 2: len 7; hex 3f000001c31070; asc ?     p;;
 3: len 16; hex 77616c6b696e67313533323639323335; asc walking153269235;;
 4: len 1; hex 30; asc 0;;
 5: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

从上面的日志中我们可以找到发生死锁的SQL和线程ID等相关信息。
通过以上的分析大家知道怎么模拟数据库中的死锁了吧。其实和Java多线程的死锁道理都是相通的,无非就是满足四个必要条件,即:
1、互斥条件:一个资源每次只能被一个进程使用;
2、请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放;
3、不剥夺条件:进程已获得的资源,在未使用完之前,不能强行剥夺;
4、循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

Java应用中数据库死锁的表现

通过Java操作数据库,模拟在实际应用中的数据库死锁。
首先是第一个业务方法,其实和上面用SQL模拟死锁的思路是一样的,这里的业务也很简单,先更新id为1的,再更新id为2的

@Transactional(rollbackFor = Exception.class)
public void updateById() {
    User record1 = new User();
    record1.setId(1);
    record1.setAge(1);
    userMapper.updateByPrimaryKey(record1);
    System.out.println("事务1 执行第一条SQL完毕");

    User record2 = new User();
    record2.setId(2);
    record2.setAge(2);
    userMapper.updateByPrimaryKey(record1);
    System.out.println("事务1 执行第二条SQL完毕");
}

然后第二个业务方法,同样,模拟上面的SQL死锁,先更新id为2的,然后为了使这个先后顺序更加明显,效果更突出,我们让第二个业务方法休眠30毫秒,再更新id为1的

@Transactional(rollbackFor = Exception.class)
public void updateById1() {
    User record1 = new User();
    record1.setId(2);
    record1.setAge(3);
    userMapper.updateByPrimaryKeySelective(record1);
    System.out.println("事务2 执行第一条SQL完毕");
    //休眠,保证先后执行顺序
    try {
        Thread.sleep(30);
    } catch (InterruptedException e) {
        e.printStackTrace();
    }
    User record2 = new User();
    record2.setId(1);
    record2.setAge(4);
    userMapper.updateByPrimaryKeySelective(record2);
    System.out.println("事务2 执行第二条SQL完毕");
}

然后进行单元测试,打开两个线程,模拟多个用户请求,触发不同的业务操作数据库。

[En]

Then we do the unit test, open two threads, simulate multiple user requests, and trigger different business operation databases.

@Test
public void testDeadLock() {
    new Thread(() -> {
      userService.updateById();
      System.out.println("事务1 执行完毕");
    }).start();

    new Thread(() -> {
      userService.updateById1();
      System.out.println("事务2 执行完毕");
    }).start();
    Thread.sleep(2000);//休眠,等待两个线程,确保都能执行
}

运行上述代码,执行结果如下。通过日志,我们发现事务1执行顺利,事务2抛出异常。

[En]

Run the above code, and the execution result is as follows. Through the log, we find that transaction 1 executes smoothly and transaction 2 throws an exception.

Exception in thread "Thread-5" org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction\

面试官:请用SQL模拟一个死锁

详细错误堆栈如下:

事务1 执行第一条SQL完毕
事务2 执行第一条SQL完毕
事务1 执行第二条SQL完毕
事务1 执行完毕
Exception in thread "Thread-5" org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in file [E:\idea_project\springboot-mybatis-demo\target\classes\mapper\UserMapper.xml]
### The error may involve com.wenbei.mapper.UserMapper.updateByPrimaryKeySelective-Inline
### The error occurred while setting parameters
### SQL: update user      SET age = ?      where id = ?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
  at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:267)
  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
  at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
  at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
  at com.sun.proxy.$Proxy81.update(Unknown Source)
  at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:287)
  at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:67)
  at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
  at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
  at com.sun.proxy.$Proxy82.updateByPrimaryKeySelective(Unknown Source)
  at com.wenbei.service.UserService.updateById1(UserService.java:50)
  at com.wenbei.service.UserService$$FastClassBySpringCGLIB$$de54ea56.invoke()
  at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
  at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
  at com.wenbei.service.UserService$$EnhancerBySpringCGLIB$$4badf6b6.updateById1()
  at com.wenbei.AppTests.lambda$testDeadLock$1(AppTests.java:54)
  at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
  at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
  at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
  at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
  at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409)
  at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
  at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
  at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
  at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
  at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
  at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
  at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
  at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
  at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
  at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:498)
  at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
  ... 20 more

你觉得一切都结束了吗?在回答完这个问题后,面试官有一系列的问题:

[En]

You think it’s over? After answering this question, the interviewer has a series of questions:

  • 什么是死锁?如何避免?
  • 数据库锁与隔离级别有什么关系?
    [En]

    what is the relationship between database locks and isolation levels?*

  • 数据库锁的类型有哪些?
  • MySQL中InnoDB引擎的行锁模式及其是如何实现的?
  • 数据库的乐观和悲观锁是什么,如何实施?
    [En]

    what are the optimistic and pessimistic locks of the database, and how can they be implemented?

关于上述问题,我们将在下一期中谈到。

[En]

With regard to the above questions, we will talk about them in the next issue.

如果对你有帮助,可以关注公众号BiggerBoy支持一下,第一时间获取文章干货。感谢!

Original: https://www.cnblogs.com/ibigboy/p/16202718.html
Author: 问北
Title: 面试官:请用SQL模拟一个死锁

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505446/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • 删除chrome的域名安全策略

    使用过程中总有特殊情况必须使用http请求、又或者必须使用https请求 目前有两种思路,一种是浏览器默认开了http转https请求,像chrome和Firefox,另一种就是服…

    数据库 2023年6月6日
    0143
  • Zabbix-(1)安装

    环境: VMware Workstation Pro 16.0 版本 系统 Centos7 …

    数据库 2023年6月14日
    0127
  • 8086指令码汇总表(表格)

    8086指令码汇总表 8086指令有汇编语言指令和指令码两种形式,汇编语言指令形式经过汇编程序处理后生成指令码形式。 通过指令码形式可以帮助理解汇编语言指令格式的含义和用法。 80…

    数据库 2023年6月14日
    0210
  • likeshop搭建商城系统,一步到位

    什么是商城系统?商城系统又称在线商城系统,是一个功能完善的在线购物系统,主要为在线销售和在线购物服务。 一般的商城系统运营模式有B2C单商户商城系统,B2B2C多商户商城系统以及S…

    数据库 2023年6月14日
    0193
  • Java 线程创建与常用方法

    进程与线程 进程 程序由指令和数据组成,但这些指令要运行,数据要读写,就必须将指令加载至 CPU,数据加载至内存。在指令运行过程中还需要用到磁盘、网络等设备。进程就是用来加载指令、…

    数据库 2023年6月16日
    0121
  • SQL的函数

    MySQL常用的日期函数函数 功能 curdate() 返回当前日期 curtime() 返回当前时间 now() 返回当前日期和时间 year() 获取指定date的年份 mon…

    数据库 2023年6月16日
    0163
  • .NET在单台Windows2008下百万TCP连接测试

    测试客户端: 客户端程序建立TCP连接,发送一条几个字节的数据。 虚拟机8 台,PC 机8 台,服务器1 台。 设置MaxUserPort=60000 ,有一台机没有设置约在1.5…

    数据库 2023年6月14日
    0160
  • Java并发

    Java并发 JAVA技术交流群:737698533 CAS compare and swap 比较并交换,cas又叫做无锁,自旋锁,乐观锁,轻量级锁 例如下面的代码,如果想在多线…

    数据库 2023年6月16日
    0132
  • 查看PostgreSQL监听端口

    如何查看PostgreSQL的监听端口呢?下面总结一下查看PostgreSQL监听端口的方法。 方法1:netstat命令查看 或者sudo netstat -plunt |gre…

    数据库 2023年6月11日
    0108
  • MySQL高可用安装

    MySQL HA部署 环境准备 创建本地yum源 确认关闭 SELinux 防火墙设置 MySQL安装 使用 root 用户操作创建相关的用户组和用户 上传/解压介质 设置自启动 …

    数据库 2023年5月24日
    0158
  • mysql主从

    mysql主从 mysql主从 1.主从原理 1.1 主从介绍 1.2 主从作用 1.3 主从形式 1.4 主从复制原理 2.主从复制配置 2.1 mysql安装 2.2 mysq…

    数据库 2023年5月24日
    0151
  • SQL语句大全–SQL

    前言 本片博客使用mysql数据库进行数据操作,使用Navicat for mysql 这个IDE进行可视化操作。每个SQL语句都是亲身实验验证的,并且经过自己的思考的。能够保证s…

    数据库 2023年5月24日
    0105
  • 手写spring的ioc的流程截图(笔记-1)

    spring ioc是什么? IoC 容器是 Spring 的核心,也可以称为 Spring 容器。Spring 通过 IoC 容器来管理对象的实例化和初始化,以及对象从创建到销毁…

    数据库 2023年6月6日
    0120
  • MySQL45讲之随机查询和临时表

    本文介绍 MySQL 随机查询的工作流程、优化随机查询的方式、和临时表。 工作流程 根据下表结构建立 words 表,并通过过程插入 10000 条模拟数据。 CREATE TAB…

    数据库 2023年5月24日
    0127
  • 详解 Serverless 架构的 6 大应用场景

    Serverless 架构将成为未来云计算领域重要的技术架构,将会被更多的业务所采纳。进一步深究,Serverless 架构在什么场景下有优秀的表现,在什么场景下可能表现得并不是很…

    数据库 2023年6月14日
    0131
  • day01-数据库的安装和使用

    Java数据库的安装和使用 1.数据库的作用 一个问题:淘宝网、京东、微信抖音,都有各自的功能,那么我们退出系统的时候,为什么信息还在? 解决之道-文件,数据库 为了解决上诉问题,…

    数据库 2023年6月11日
    0160
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球