select for update語句造成ORA-00060 deadlock死鎖問題分析

neverinit發表於2017-09-13

測試環境出現問題

今天 ，收到專案組通知說發生了死鎖，讓我查一下死鎖出現的原因 。

首先 ，登入資料庫，檢視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 . . 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 . / 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 . . 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 ;


如果有輸出的結果，則說明有死鎖，且能看到死鎖的機器是哪一臺。欄位說明：

OWNER：死鎖語句所用的資料庫使用者，這裡是HR使用者。

OBJECT_NAME：產生死鎖的物件，這裡是TEST表。

OBJECT_TYPE：產生死鎖的物件型別，這裡是TABLE。

SID：SESSION標識，常用於連線 其它 列 。

SERIAL#： SID有可能會重複，當兩個session的SID重複時，SERIAL#用來區別session

LOCKWAIT： 可以透過這個欄位查詢出當前正在等待的鎖的相關資訊 ，如果有內容表示被死鎖或者有鎖等待事件。

STATUS： 用來判斷session狀態。 ACTIVE :正執行 。 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 ) ;


這裡查出來出現死鎖的語句和之前的測試結果一致。

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 ;


2）kill掉這個死鎖的程式：


  1. alter system kill session 'sid, serial#' ;


這裡執行語句為：alter system kill session '29,69';

原先造成死鎖的程式被killed。

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


殺完程式之後，造成死鎖的程式被殺死

查不到死鎖程式

方法二（重啟庫）


  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 .


檢視死鎖程式：

方法三（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


這裡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


此時，死鎖狀態解除：

問題總結

最後，透過與開發人員交流，得出的結論是出現該問題的原因不是程式設計的問題，而是在開發環境中，有人透過debug模式連到伺服器上進行程式碼除錯，有人透過客戶端的形式訪問伺服器上的應用，當兩者同時除錯某一功能時（透過select for update的語句進行一張表中的資料訪問），在特殊的情況下，出現了死鎖的情況。

當然，在開發環境中比較容易出現這類情況，如果需要避免，就需要使用該開發環境的開發人員與測試人員做好溝通了。對於容易出現鎖表的功能，可以要求測試人員在測試環境中測試，除特殊情況，儘量減少在開發環境中測試的次數，以免鎖表影響開發測試進度。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31394774/viewspace-2144941/，如需轉載，請註明出處，否則將追究法律責任。

相關文章