阿里云-云小站（无限量代金券发放中）
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

select for update语句造成ORA-00060 deadlock死锁问题分析

199次阅读
没有评论

共计 11567 个字符，预计需要花费 29 分钟才能阅读完成。

测试环境出现问题

今天，收到项目组通知说发生了死锁，让我查一下死锁出现的原因。

首先 ，登录数据库，查看trace 日志所在路径

  1. SYS@cams>show parameter dump;
  2. NAME TYPE VALUE
  3. ———————————— ———– ——————————
  4. background_core_dump string partial
  5. background_dump_dest string /u01/app/Oracle/diag/rdbms/cams/cams/trace
  6. core_dump_dest string /u01/app/oracle/diag/rdbms/cams/cams/cdump
  7. max_dump_file_size string unlimited
  8. shadow_core_dump string PARTIAL
  9. user_dump_dest string /u01/app/oracle/diag/rdbms/cams/cams/trace

查看 alert 日志，搜索00060

  1. [oracle@db trace]$ cd /u01/app/oracle/diag/rdbms/cams/cams/trace/
  2. [oracle@db trace]$ vi alert_cams.log

找到 报错 trace 日志如下：

  1. ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_ora_12850.trc.
  2. Tue Sep 12 13:28:31 2017
  3. ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_ora_12854.trc.
  4. Tue Sep 12 13:28:36 2017
  5. ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_ora_12822.trc.
  6. Tue Sep 12 13:31:12 2017

打开 其中一个 trace 文件，部分内容如下：

  1. Trace file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_ora_12850.trc
  2. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
  3. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  4. ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
  5. System name: Linux
  6. Node name: db
  7. Release: 2.6.32-431.el6.x86_64
  8. Version: #1 SMP Sun Nov 10 22:19:54 EST 2013
  9. Machine: x86_64
  10. VM name: VMWare Version: 6
  11. Instance name: cams
  12. Redo thread mounted by this instance: 1
  13. Oracle process number: 293
  14. Unix process pid: 12850, image: oracle@db
  15. *** 2017-09-12 13:28:28.401
  16. *** SESSION ID:(1139.47765) 2017-09-12 13:28:28.401
  17. *** CLIENT ID:() 2017-09-12 13:28:28.401
  18. *** SERVICE NAME:(SYS$USERS) 2017-09-12 13:28:28.401
  19. *** MODULE NAME:(JDBC Thin Client) 2017-09-12 13:28:28.401
  20. *** ACTION NAME:() 2017-09-12 13:28:28.401
  21.  
  22.  
  23. *** 2017-09-12 13:28:28.401
  24. DEADLOCK DETECTED ( ORA-00060 )
  25.  
  26. [Transaction Deadlock]
  27.  这里指出死锁不是 Oracle 自身的错误，是用户的设计造成的问题
  28. The following deadlock is not an ORACLE error. It is a
  29. deadlock due to user error in the design of an application
  30. or from issuing incorrect ad-hoc SQL. The following
  31. information may aid in determining the deadlock:
  32.  
  33. 这个死锁图表明两个 session 各持有一份资源，等待对方持有的资源
  34. Deadlock graph:
  35.                        ———Blocker(s)–––––––– ———Waiter(s)–––––––––
  36. Resource Name process session holds waits process session holds waits
  37. TX-00160018-00000adc 293 1139 X 384 65 X
  38. TX-0004000f-000064d6 384 65 X 293 1139 X
  39.  
  40. session 1139: DID 0001-0125-000074A5 session 65: DID 0001-0180-000037EC
  41. session 65: DID 0001-0180-000037EC session 1139: DID 0001-0125-000074A5
  42.  
  43. Rows waited on:
  44.   Session 1139: obj – rowid = 00021C7B – AAAhx7AAHAAARMrAAB
  45.   (dictionary objn – 138363, file – 7, block – 70443, slot – 1)
  46.   Session 65: obj – rowid = 00021C7B – AAAhx7AAHAAARMtAAD
  47.   (dictionary objn – 138363, file – 7, block – 70445, slot – 3)
  48.  
  49. —– Information for the OTHER waiting sessions —–
  50. Session 65:
  51.   sid: 65 ser: 27869 audsid: 3026187 user: 111/CAMS_CORE
  52.     flags: (0x45) USR/– flags_idl: (0x1) BSY/–/–/–/–/–
  53.     flags2: (0x40009) –/–/INC
  54.   pid: 384 O/S info: user: oracle, term: UNKNOWN, ospid: 12854
  55.     image: oracle@db
  56.   client details:
  57.     O/S info: user: cams, term: unknown, ospid: 1234
  58.     machine: yy program: JDBC Thin Client
  59. application name: JDBC Thin Client, hash value=2546894660
  60.   current SQL:
  61. 导致死锁的 SQL 语句 1
  62.   select
  63.  
  64. SEQ_NO, ACO_AC,AC_DTE,CCY,BAL,FRZ_AMT,
  65. CRE_TLR, CRE_DTE,
  66. CRE_BR, CRE_BK, UPD_TLR, UPD_DTE, UPD_BR, UPD_BK,TSL_AMT,VPS_BAL
  67.  
  68. FROM CD_ACBALANCE
  69.  WHERE ACO_AC = :1
  70. FOR UPDATE
  71.  
  72. —– End of information for the OTHER waiting sessions —–
  73.  
  74. Information for THIS session:
  75.  
  76. —– Current SQL Statement for this session (sql_id=cfy88pmyts0fn) —–
  77. 导致死锁的 SQL 语句 2
  78. select
  79.  
  80. SEQ_NO, ACO_AC,AC_DTE,CCY,BAL,FRZ_AMT,
  81. CRE_TLR, CRE_DTE,
  82. CRE_BR, CRE_BK, UPD_TLR, UPD_DTE, UPD_BR, UPD_BK,TSL_AMT,VPS_BAL
  83.  
  84. FROM CD_ACBALANCE
  85.  WHERE ACO_AC = :1
  86. FOR UPDATE
  87. ===================================================

这里可以明确的是 select for update 语句造成了死锁。但是考虑到环境为测试环境，没开启归档，不能通过 logminer 挖掘故障期间的 SQL 事物，而且开发人员已经通过 kill session 的方式处理了死锁，所以需要在别的环境中重现该问题，并进行分析。

重现死锁问题

首先连上数据库，启用 HR 用户

  1. [oracle@prod ~]$ sqlplus / as sysdba
  2. SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 13 03:14:17 2017
  3. Copyright (c) 1982, 2013, Oracle. All rights reserved.
  4. Connected to an idle instance.
  5. SYS@PROD>startup;
  6. ORACLE instance started.
  7. Total System Global Area 1202556928 bytes
  8. Fixed Size 2252704 bytes
  9. Variable Size 771752032 bytes
  10. Database Buffers 419430400 bytes
  11. Redo Buffers 9121792 bytes
  12. Database mounted.
  13. Database opened.
  14. SYS@PROD>conn hr/hr
  15. ERROR:
  16. ORA-28000: the account is locked
  17. Warning: You are no longer connected to ORACLE.
  18. @>conn / as sysdba
  19. Connected.
  20. SYS@PROD>alter user hr identified by hr account unlock;
  21. User altered.
  22. SYS@PROD>conn hr/hr
  23. Connected.

选中 COUNTRIES 表作为测试对象

  1. HR@PROD>desc countries;
  2.  Name Null? Type
  3.  —————————————– ——– —————————-
  4.  COUNTRY_ID NOT NULL CHAR(2)
  5.  COUNTRY_NAME VARCHAR2(40)
  6.  REGION_ID NUMBER
  7. HR@PROD>select count(*) from countries;
  8.   COUNT(*)
  9. ———-
  10. 25
  11. HR@PROD>select count(distinct(COUNTRY_ID)) from countries;
  12. COUNT(DISTINCT(COUNTRY_ID))
  13. —————————
  14.  25

经过验证，COUNTRIES表中的 COUNTRY_ID 字段是唯一的。

用 COUNTRIES 表新建一个 test 表，在 test 表中做测试

  1. HR@PROD>create table test as select * from countries;
  2. Table created.
  3. HR@PROD>desc test
  4.  Name Null? Type
  5.  —————————————– ——– —————————-
  6.  COUNTRY_ID NOT NULL CHAR(2)
  7.  COUNTRY_NAME VARCHAR2(40)
  8.  REGION_ID NUMBER
  9. HR@PROD>select country_id,country_name,region_id from countries;
  10. CO COUNTRY_NAME REGION_ID
  11. — —————————————- ———-
  12. AR Argentina 2
  13. AU Australia 3
  14. BE Belgium 1
  15. BR Brazil 2
  16. CA Canada 2
  17. CH Switzerland 1
  18. CN China 3
  19. DE Germany 1
  20. DK Denmark 1
  21. EG Egypt 4
  22. FR France 1
  23. CO COUNTRY_NAME REGION_ID
  24. — —————————————- ———-
  25. IL Israel 4
  26. IN India 3
  27. IT Italy 1
  28. JP Japan 3
  29. KW Kuwait 4
  30. ML Malaysia 3
  31. MX Mexico 2
  32. NG Nigeria 4
  33. NL Netherlands 1
  34. SG Singapore 3
  35. UK United Kingdom 1
  36. CO COUNTRY_NAME REGION_ID
  37. — —————————————- ———-
  38. US United States of America 2
  39. ZM Zambia 4
  40. ZW Zimbabwe 4
  41. 25 rows selected.

情形1：

两个 select for update 语句想要同时锁定一条语句。

Session1：

  1. HR@PROD>select country_id,country_name,region_id from test where country_id=‘AU’ for update;
  2. CO COUNTRY_NAME REGION_ID
  3. — —————————————- ———-
  4. AU Australia 3

Sessions2：

  1. HR@PROD>select country_id,country_name,region_id from test where country_id=‘AU’ for update;

这时，session2会一直处于锁等待状态，而不会出现死锁。当把第一个事物 commit 或者 rollback 之后，第二个事物会继续执行。

Session1：

  1. HR@PROD>rollback;
  2. Rollback complete.

Session2：

  1. HR@PROD>select country_id,country_name,region_id from test where country_id=‘AU’ for update;
  2. CO COUNTRY_NAME REGION_ID
  3. — —————————————- ———-
  4. AU Australia

显然，两个 select for update 语句想要同时锁定一条语句，并不会出现死锁，而会出现锁等待的现象。

情形2：

第一个 select for update 事物锁定 A row 后，想要再锁定 B row；第二个select for update 事物锁定 B row 后，想要锁定A row。

Session1：

  1. HR@PROD>select country_id,country_name,region_id from test where country_id=‘AU’ for update;
  2. CO COUNTRY_NAME REGION_ID
  3. — —————————————- ———-
  4. AU Australia 3

Session 2：

  1. HR@PROD>select country_id,country_name,region_id from test where country_id=‘UK’ for update;
  2. CO COUNTRY_NAME REGION_ID
  3. — —————————————- ———-
  4. UK United Kingdom 1

如果 Session1 想要继续持有 Session2 正在持有的row：

Session1：

  1. HR@PROD>select country_id,country_name,region_id from test where country_id=‘UK’ for update;

这时，会出现锁等待的现场，和上一场景类似。如果 Session2 也想持有 Session1 正在持有的row：

Session2：

  1. HR@PROD>select country_id,country_name,region_id from test where country_id=‘AU’ for update;

这时，Session2还处于等待状态，但是 Session1 出现死锁：

Session1：

  1. HR@PROD>select country_id,country_name,region_id from test where country_id=‘UK’ for update;
  2. select country_id,country_name,region_id from test where country_id=‘UK’ for update
  3. *
  4. ERROR at line 1:
  5. ORA-00060: deadlock detected while waiting for resource

这时，死锁的现象已经重现，而且，场景 2 从头到尾只使用了一个 select for update 语句，只是换了参数而已。

定位死锁语句

通过检查数据库表，能够检查出是哪一条语句被死锁，产生死锁的机器是哪一台。

1）用 dba 用户执行以下语句

  1. select c.owner, c.object_name, c.object_type, b.sid,
  2. b.serial#, b.lockwait, b.status, b.oSUSEr, b.machine, b.process, b.program
  3. from v$locked_object a ,
  4. v$session b,
  5. dba_objects c
  6. where b.sid = a.session_id
  7. and a.object_id = c.object_id;

select for update 语句造成 ORA-00060 deadlock 死锁问题分析

如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：

OWNER：死锁语句所用的数据库用户，这里是 HR 用户。

OBJECT_NAME：产生死锁的对象，这里是 TEST 表。

OBJECT_TYPE：产生死锁的对象类型，这里是TABLE。

SID：SESSION标识，常用于连接 其它 列。

SERIAL#：SID有可能会重复，当两个 session 的SID重复时，SERIAL#用来区别session

LOCKWAIT：可以通过这个字段查询出当前正在等待的锁的相关信息，如果有内容表示被死锁或者有锁等待事件。

STATUS：用来判断 session 状态。ACTIVE:正执行 SQL 语句 。INACTIVE: 等待操作。KILLED:被标注为杀死。

OSUSER：客户端操作系统用户名。

MACHINE：客户端操作系统的机器名。

PROCESS：客户端进程的ID。

PROGRAM：客户端执行的应用程序。

2）用 dba 用户执行以下语句，可以查看到被死锁的语句。

  1. select sql_text from v$sql where (address,hash_value) in
  2. (select sql_address,sql_hash_value from v$session where sid in
  3. (select session_id from v$locked_object));

或者

  1. select sql_text from v$sql where (address,hash_value) in
  2. (select sql_address,sql_hash_value from v$session where lockwait is not null);

select for update 语句造成 ORA-00060 deadlock 死锁问题分析

这里查出来出现死锁的语句和之前的测试结果一致。

3）分析 trace 日志文件

死锁发生时，可以再 trace 日志文件中找到如下语句：

  1. ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_4852.trc.

打开 trace 文件，可以找到死锁的语句：

  1. —– Information for the OTHER waiting sessions —–
  2. Session 31:
  3.   sid: 31 ser: 25 audsid: 110280 user: 84/HR
  4.     flags: (0x45) USR/– flags_idl: (0x1) BSY/–/–/–/–/–
  5.     flags2: (0x40009) –/–/INC
  6.   pid: 23 O/S info: user: oracle, term: UNKNOWN, ospid: 4853
  7.     image: oracle@prod (TNS V1-V3)
  8.   client details:
  9.     O/S info: user: oracle, term: pts/1, ospid: 3149
  10.     machine: prod program: sqlplus@prod (TNS V1-V3)
  11.     application name: SQL*Plus, hash value=3669949024
  12.   current SQL:
  13.   select country_id,country_name,region_id from test where country_id=‘AU’ for update
  14.  
  15. —– End of information for the OTHER waiting sessions —–
  16.  
  17. Information for THIS session:
  18.  
  19. —– Current SQL Statement for this session (sql_id=6n5kjs2twrwwq) —–
  20. select country_id,country_name,region_id from test where country_id=‘UK’ for update
  21. ===================================================

解决死锁问题

方法一（杀进程）

1）查找死锁的进程：

  1. select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null;

select for update 语句造成 ORA-00060 deadlock 死锁问题分析

2）kill掉这个死锁的进程：

  1. alter system kill session ‘sid, serial#’;

这里执行语句为：alter system kill session ‘29,69’;

select for update 语句造成 ORA-00060 deadlock 死锁问题分析

原先造成死锁的进程被killed。

select for update 语句造成 ORA-00060 deadlock 死锁问题分析

3）如果还不能解决，使用杀系统进程的方式处理：

这里为了测试，再次重现了死锁，并使用杀进程方式进行处理。

  1. select p.spid from v$session s,v$process p where s.sid=XXX and s.paddr=p.addr;

其中，XXX使用第一步查出来的 SID 参数替代

这里查出来的进程号为：4257

  1. [oracle@prod ~]$ ps -ef | grep 4257
  2. oracle 4257 3149 0 07:08 ? 00:00:00 oraclePROD (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
  3. oracle 4321 4000 0 07:21 pts/4 00:00:00 grep 4257
  4. [oracle@prod ~]$ kill -9 4257
  5. [oracle@prod ~]$ ps -ef | grep 4257
  6. oracle 4327 4000 0 07:21 pts/4 00:00:00 grep 4257

杀完进程之后，造成死锁的进程被杀死

select for update 语句造成 ORA-00060 deadlock 死锁问题分析

查不到死锁进程

select for update 语句造成 ORA-00060 deadlock 死锁问题分析

方法二（重启库）

  1. SYS@PROD>shutdown immediate;
  2. Database closed.
  3. Database dismounted.
  4. ORACLE instance shut down.
  5. SYS@PROD>startup;
  6. ORACLE instance started.
  7. Total System Global Area 1202556928 bytes
  8. Fixed Size 2252704 bytes
  9. Variable Size 771752032 bytes
  10. Database Buffers 419430400 bytes
  11. Redo Buffers 9121792 bytes
  12. Database mounted.
  13. Database opened.

查看死锁进程：

select for update 语句造成 ORA-00060 deadlock 死锁问题分析

方法三（commit || rollback）

Session1：

  1. HR@PROD>select country_id,country_name,region_id from test where country_id=‘AU’ for update;
  2. CO COUNTRY_NAME REGION_ID
  3. — —————————————- ———-
  4. AU Australia 3

Session2：

  1. HR@PROD>select country_id,country_name,region_id from test where country_id=‘UK’ for update;
  2. CO COUNTRY_NAME REGION_ID
  3. — —————————————- ———-
  4. UK United Kingdom 1

Session1：

  1. HR@PROD>select country_id,country_name,region_id from test where country_id=‘UK’ for update;
  2. Waiting……

Session2：

  1. HR@PROD>select country_id,country_name,region_id from test where country_id=‘AU’ for update;
  2. Waiting……

Session1：

  1. HR@PROD>select country_id,country_name,region_id from test where country_id=‘UK’ for update;
  2. select country_id,country_name,region_id from test where country_id=‘UK’ for update
  3.                                               *
  4. ERROR at line 1:
  5. ORA-00060: deadlock detected while waiting for resource

select for update 语句造成 ORA-00060 deadlock 死锁问题分析

这里 Session1 出现死锁，只要执行 commit 或者 rollback 就可以解除死锁，只不过事务中第一个 SQL 执行成功，第二个 SQL 执行失败。

Session1：

  1. HR@PROD>commit;
  2. Commit complete.

Session2：

  1. HR@PROD>select country_id,country_name,region_id from test where country_id=‘AU’ for update;
  2. CO COUNTRY_NAME REGION_ID
  3. — —————————————- ———-
  4. AU Australia 3

此时，死锁状态解除：

select for update 语句造成 ORA-00060 deadlock 死锁问题分析

问题总结

最后，通过与开发人员交流，得出的结论是出现该问题的原因不是程序设计的问题，而是在开发环境中，有人通过 debug 模式连到服务器上进行代码调试，有人通过客户端的形式访问服务器上的应用，当两者同时调试某一功能时（通过 select for update 的语句进行一张表中的数据访问），在特殊的情况下，出现了死锁的情况。

当然，在开发环境中比较容易出现这类情况，如果需要避免，就需要使用该开发环境的开发人员与测试人员做好沟通了。对于容易出现锁表的功能，可以要求测试人员在测试环境中测试，除特殊情况，尽量减少在开发环境中测试的次数，以免锁表影响开发测试进度。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-10/147436.htm

正文完
星哥玩云-微信公众号
post-qrcode
MongoDB MySQL NoSQL ORA-00060 Oracle Oracle教程 Oracle数据库 PostgreSQL select for update select for update语句造成ORA-00060 deadlock死锁问题分析 数据库
发表至： 数据库技术
2022-01-22
 0
星锅
版权声明：本站原创文章，由 星锅 于2022-01-22发表，共计11567字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
【腾讯云】推广者专属福利，新客户无门槛领取总价值高达2860元代金券，每种代金券限量500张，先到先得。
MySQL占用IO过高解决方案
Oracle使用游标查询所有数据表备注
Oracle分析函数Over() 详述
CentOS7.4 源码编译安装MySQL8.0
深入理解MySQL触发器
CentOS 7.4部署MariaDB Galera Cluster集群架构
升级Oracle 11g重复执行catupgrd.sql脚本引起大量ORA-00001错误
CentOS 6.7 X64下MySQL5.1.73和5.5.32主从复制服务配置实战
阿里云-最新活动爆款每日限量供应
评论（没有评论）
验证码
星哥说事
星哥说事
分享互联网知识
阅读量
3564424
评论数
4
文章搜索
热门文章
Centos 7平滑无缝升级PHP7.1.0到PHP 7.1.5

Centos 7平滑无缝升级PHP7.1.0到PHP 7.1.5

实验环境：CentOS Linux release 7.3.1611 (Core) 内核版本：Linux ve...
选择PHP与Python，可以考虑这三个问题

选择PHP与Python，可以考虑这三个问题

导读 尽管 RESTful API 和跨平台库已经使网页开发容易起来，但依然没有解决阻碍并隔分开发者、管理者和...
介绍ansible的Ad-hoc与commands模块

介绍ansible的Ad-hoc与commands模块

Ad-Hoc 是指 ansible 下临时执行的一条命令，并且不需要保存的命令，对于复杂的命令后面会说 pla...
linux下使用tree命令以树形结构显示文件目录结构

linux下使用tree命令以树形结构显示文件目录结构

导读 今天小编来给分享 Linux 系统下一个非常有用的命令的使用：tree 命令可以以树形结构显示文件目录结...
Linux安装使用pidstat命令以对进程数据进行监控

Linux安装使用pidstat命令以对进程数据进行监控

导读 Linux 系统监控的命令其实有很多，pidstat 命令就是其中的一种，使用 pidstat 命令可以...
CDN
阿里云CDN-提高用户访问的响应速度和成功率
随机文章
选择PHP与Python，可以考虑这三个问题

选择PHP与Python，可以考虑这三个问题

导读 尽管 RESTful API 和跨平台库已经使网页开发容易起来，但依然没有解决阻碍并隔分开发者、管理者和...
详解：Nginx反代实现Kibana登录认证功能

详解：Nginx反代实现Kibana登录认证功能

Kibana 5.5 版后，已不支持认证功能，也就是说，直接打开页面就能管理，想想都不安全，不过官方提供了 X...
Zabbix 3.4 Source code compilation installation

Zabbix 3.4 Source code compilation installation

导读 官介：Zabbix 是一个企业级的分布式开源监控解决方案。它可以监控各种网络应用以及服务器健康性和完整性...
CentOS7 configuration uses the SFTP server

CentOS7 configuration uses the SFTP server

导读 SFTP，即 SSH 文件传输协议（SSH File Transfer Protocol），或者说是安全...
怎样在减少数据中心成本的同时不牺牲性能？

怎样在减少数据中心成本的同时不牺牲性能？

导读 虽然组织对数据中心提出了更高的要求，但 IT 管理人员确实有办法在严格的预算内展开工作。如今，组织认为即...
一言一句话
-「」
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中