精算业务死锁的具体分析

真实表名、字段名、函数名、机器名、实例名已经替换

截取trace 文件的片段：

trace 文件是 42 号进程——也就是后面的  sid 777 生成的。

Instance name: PROD

Redo thread mounted by this instance: 1

Oracle process number:  42

Unix process pid: 4620, image: oracle@hostname  (TNS V1-V3)

是两个 session 的交叉等待。

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session  holds  waits  process session holds  waits

TX-0061001a-029a615c        42     777      X              82     163            X

TX-00620000-02e23e15        82     163      X              42     777            X

等待的行锁情况

Rows waited on:

  Session 777: obj - rowid = 000B670F - AAC6hMAIdAACr0fAAL

  (dictionary objn -  747279 , file - 541, block - 703775, slot - 11)

  Session 163: obj - rowid = 000B670F - AAC6hMAIJAAMOzeAAA

  (dictionary objn -  747279 , file - 521, block - 3206366, slot - 0)

从 objn 747279 得知，是 TAB1 表。死锁的原因是两个  session 各自锁住一行，未提交的情况下请求对方已获得的行锁。

10:22:06  SQL> select object_name, object_type from dba_objects where object_id = 747279;

OBJECT_NAME                    OBJECT_TYPE

------------------------------ -------------------

TAB1                            TABLE

sid 163 执行的 SQL  是 SELECT  FUNC2 (' :B3 ,  86', :B2 , :B1 ) FROM DUAL

----- Information for the OTHER waiting sessions -----

Session 163:

  sid: 163 ser: 5387 audsid: 124855449 user: 117/usr

  ...

  ...

  current SQL:

   SELECT  FUNC2 (:B3 , '86', :B2 , :B1 ) FROM DUAL

----- End of information for the OTHER waiting sessions -----

sid 777 执行的 SQL

Information for THIS session:

----- Current SQL Statement for this session (sql_id=4vd948jucpdj7) -----

DELETE FROM  TAB1  WHERE COL1 = :B6 AND COL2 = :B5 AND  COL3  = :B4 AND  COL4  = :B3 AND  COL5  = :B2 AND  COL6  = :B1

sid 777 的call stack：

可见  163 和 777 两个 session  是执行同一个函数。

----- PL/SQL Stack -----

----- PL/SQL Call Stack -----

  object      line  object

  handle    number  name

c0000006b13f2808      1963  function usr .FUNC2

c0000006a8debfe8       119  function usr .FUNC1

下面是代码里FUNC1 中调用 FUNC2 的地方。需要注意的是参数  col1 和截止日期enddate  。

Select FUNC2 (  Cursor_Col1 ,

                                 '86',

                                 CalDate,

                                 enddate )

出现死锁的  delete 语句的条件，正好是TAB1 的主键  6 个字段

INDEX_OWNE TABLE_NAME          INDEX_NAME                     COLUMN_NAME   COLUMN_POSITION

---------- ------------------- ------------------------------ ------------- ---------------

usr         TAB1                 PK_TAB1 _NEW                    COL1                        1

usr         TAB1                   PK_TAB1 _NEW                       COL2                         2

usr         TAB1                   PK_TAB1 _NEW                       COL3                         3

usr         TAB1                   PK_TAB1 _NEW                       COL4                         4

usr         TAB1                   PK_TAB1 _NEW                       COL5                         5

usr         TAB1                   PK_TAB1 _NEW                       COL6                         6

这些字段的来源，可以看出  TAB1 的主键虽然有很多字段，但是本质上是由  col1 对应的 tab2 记录，以及“提数截止日期”  这两个因素决定的  。

代码里的具体过程：

先通过  col1 取出 tab2 的数据

Cursor v_cur_col1(tCol1 xxx.Col1%Type) Is

      Select * From tab2  Where col1  = tcol1

      And .... ;

...

...

v_Row_data .Col1           := v_Row_tab2 .Col1;

v_Row_data . Col2         := v_Row_tab2 .Col2;

...

v_Row_data . Col3  := v_Row_tab2 .Col3;

...

col4是通过 col1 和 col2 获取tab3. custno

Select xx, xx, xx, xx, xx,custno,xx

            Into v_xx,

                 v_xx ,

                 v_xx ,

                 v_xx ,

                 v_xx ,

                 v_col4 ,

                 v_xx

            From tab3

           Where col1  = v_Row_data .col1

             And insid = v_Row_tab2 . col2 ;

...

v_Row_data . col4  := v_col4;

...

col5  是调用函数时传入的参数，具体是  FUNC1 的“提数截止日期”，即enddate 。

v_Row_data .Col5 := enddate

...

col6 写死为 1

v_Row_data . col6 := 1;

也就是说只要两个  session 在调用 FUNC2 时，传入了相同的 col1 和 enddate ，就有可能出现死锁。

下面是  col1 的具体来源。

通过  FUNC1 的参数 threadnumber 线程号，从  selcol1 里取 col1 。这样在并发执行的时候可以让每个线程处理不同的col1。

Cursor v_Cursor_SelCol1 Is

      Select col1

        From SelCol1

       Where Default3 =  Theardnumber ;

因此出现死锁的原因可能是：

1  不同 Theardnumber  对应相同的  col1

2  两个 session  传入的 Theardnumber 参数相同。

根据下面查询得知，整张  selcol1 表没有重复的 col1  。否定第一种可能。

11:11:41 SQL> select col1 , count(*) from usr .selcol1  having(count(*) > 1) group by col1 ;

no rows selected

从 awr 得知调用  FUNC1 的 SQL  是 SELECT  usr . FUNC1 ('0','0',to_date('2015-03-07','YYYY-MM-DD'),  Theardnumber ) FROM dual 的形式

force_matching_signature 为 17618561598766997330

下面是执行次数超过一次的

11:29:33 SQL> select sql_id, max(EXECUTIONS_TOTAL) from dba_hist_sqlstat where force_matching_signature = 17618561598766997330 and EXECUTIONS_TOTAL > 1 group by sql_id;

SQL_ID        MAX(EXECUTIONS_TOTAL)

------------- ---------------------

0dwvwdw0ds5h5                     2

0zkf60kczb79a                     3

24b81bj11mw50                     3

4xnsbjz2axhsp                     4

54n4p2s1rc1vd                     4

7kkv6zy3ndx68                     3

9q15dhs75tb4f                     3

g7thjdfqsb07w                     3

12:45:52 SQL> select distinct sql_id, dbms_lob.substr(sql_text, 100) from dba_hist_sqltext where sql_id in

12:45:52   2  (

12:45:52   3  '0dwvwdw0ds5h5',

12:45:53   4  '0zkf60kczb79a',

12:45:53   5  '24b81bj11mw50',

12:45:53   6  '4xnsbjz2axhsp',

12:45:53   7  '54n4p2s1rc1vd',

12:45:53   8  '7kkv6zy3ndx68',

12:45:53   9  '9q15dhs75tb4f',

12:45:53  10  'g7thjdfqsb07w'

12:45:53  11  )

12:45:53  12  order by 2

12:45:53  13  ;

SQL_ID

-------------

DBMS_LOB.SUBSTR(SQL_TEXT,100)

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

g7thjdfqsb07w

SELECT usr .FUNC1 ('0','0',to_date('2015-03-07','YYYY-MM-DD'), '110') FROM dual

0zkf60kczb79a

SELECT usr .FUNC1 ('0','0',to_date('2015-03-07','YYYY-MM-DD'), '118') FROM dual

24b81bj11mw50

SELECT usr .FUNC1 ('0','0',to_date('2015-03-07','YYYY-MM-DD'), '18') FROM dual

9q15dhs75tb4f

SELECT usr .FUNC1 ('0','0',to_date('2015-03-07','YYYY-MM-DD'), '32') FROM dual

0dwvwdw0ds5h5

SELECT usr .FUNC1 ('0','0',to_date('2015-03-07','YYYY-MM-DD'), '36') FROM dual

4xnsbjz2axhsp

SELECT usr .FUNC1 ('0','0',to_date('2015-03-07','YYYY-MM-DD'), '74') FROM dual

7kkv6zy3ndx68

SELECT usr .FUNC1 ('0','0',to_date('2015-03-07','YYYY-MM-DD'), '75') FROM dual

54n4p2s1rc1vd

SELECT usr .FUNC1 ('0','0',to_date('2015-03-07','YYYY-MM-DD'), '78') FROM dual

8 rows selected.

上面的统计数据是  3 月 12 号以后的。

如果这些执行次数超过一次的 sql的执行时间有相互重叠时间段，尤其是历史执行计划中 selcol1既有走全表，又有走索引的，返回数据的顺序不一致，就很有可能出现死锁。

11:21:03 SQL> select snap_id, end_interval_time from dba_hist_snapshot where snap_id = (

12:57:21   2  select min(snap_id) from dba_hist_sqlstat where sql_id in

12:57:22   3  (

12:57:22   4  '0dwvwdw0ds5h5',

12:57:22   5  '0zkf60kczb79a',

12:57:22   6  '24b81bj11mw50',

12:57:22   7  '4xnsbjz2axhsp',

12:57:22   8  '54n4p2s1rc1vd',

12:57:22   9  '7kkv6zy3ndx68',

12:57:22  10  '9q15dhs75tb4f',

12:57:22  11  'g7thjdfqsb07w'

12:57:22  12  ))

12:57:23  13  ;

   SNAP_ID END_INTERVAL_TIME

---------- ---------------------------------------------------------------------------

     51955 2015-03-12 04:00:13

下面是同一条  SQL 由不同 session 执行的情况，标红的是时间上有重合的。  其中就包括  trace 中的 777 和  163 。

13:15:25 SQL> select session_id, sql_id, min(sample_time), max(sample_time)

13:15:26   2  from dba_hist_active_sess_history

13:15:26   3  where dbid = 2127610945

13:15:26   4  and instance_number = 1

13:15:26   5  and snap_id > 51953

13:15:26   6  and sql_id in

13:15:26   7  (

13:15:26   8  '0dwvwdw0ds5h5',

13:15:26   9  '0zkf60kczb79a',

13:15:26  10  '24b81bj11mw50',

13:15:27  11  '4xnsbjz2axhsp',

13:15:27  12  '54n4p2s1rc1vd',

13:15:27  13  '7kkv6zy3ndx68',

13:15:27  14  '9q15dhs75tb4f',

13:15:27  15  'g7thjdfqsb07w'

13:15:27  16  )

13:15:27  17  group by session_id, sql_id

13:15:27  18  order by 2, 3

13:15:27  19  ;

SESSION_ID SQL_ID        MIN(SAMPLE_TIME)          MAX(SAMPLE_TIME)

---------- ------------- ------------------------- --------------------------

       466 0dwvwdw0ds5h5 2015-03-19 19:22:17       2015-03-21 18:15:24

       701 0dwvwdw0ds5h5 2015-03-23 09:30:24       2015-03-24 12:38:32

       388 0zkf60kczb79a 2015-03-12 20:11:50       2015-03-14 03:01:01

       619 0zkf60kczb79a 2015-03-19 16:54:39       2015-03-22 12:20:59

       548 0zkf60kczb79a 2015-03-19 17:06:16       2015-03-22 06:52:14

      1156 0zkf60kczb79a 2015-03-20 01:21:01       2015-03-22 15:54:56

        80 24b81bj11mw50 2015-03-12 22:15:14       2015-03-14 16:08:00

     1084 24b81bj11mw50 2015-03-19 22:24:15       2015-03-23 05:13:51

       850 24b81bj11mw50 2015-03-20 03:43:46       2015-03-23 00:20:17

       854 24b81bj11mw50 2015-03-20 08:55:01       2015-03-23 03:21:45

       851 4xnsbjz2axhsp 2015-03-12 06:57:08       2015-03-14 02:00:55

        469 4xnsbjz2axhsp 2015-03-19 18:06:32       2015-03-22 14:31:57

       852 4xnsbjz2axhsp 2015-03-20 13:27:54       2015-03-22 13:18:44

       933 4xnsbjz2axhsp 2015-03-20 20:59:03       2015-03-22 15:53:26

      1081 54n4p2s1rc1vd 2015-03-13 11:05:42       2015-03-15 10:42:54

        700 54n4p2s1rc1vd 2015-03-19 18:53:40       2015-03-23 04:07:12

      1007 54n4p2s1rc1vd 2015-03-20 06:16:37       2015-03-23 01:43:57

       313 54n4p2s1rc1vd 2015-03-20 11:07:41       2015-03-23 00:18:06

       851 7kkv6zy3ndx68 2015-03-14 07:25:49       2015-03-16 08:22:17

         8 7kkv6zy3ndx68 2015-03-19 23:22:50       2015-03-22 08:45:51

       392 7kkv6zy3ndx68 2015-03-20 00:47:01       2015-03-22 11:30:39

       625 7kkv6zy3ndx68 2015-03-20 09:56:38       2015-03-22 13:14:31

       774 9q15dhs75tb4f 2015-03-17 22:03:30       2015-03-19 21:39:19

       393 9q15dhs75tb4f 2015-03-20 17:56:14       2015-03-23 03:58:47

       313 9q15dhs75tb4f 2015-03-23 11:14:36       2015-03-23 18:23:51

       159 g7thjdfqsb07w 2015-03-12 22:56:08       2015-03-14 22:43:15

      777 g7thjdfqsb07w 2015-03-19 20:30:28       2015-03-22 21:19:50

       163 g7thjdfqsb07w 2015-03-19 21:27:11       2015-03-22 21:42:33

      1160 g7thjdfqsb07w 2015-03-20 08:36:51       2015-03-22 20:58:17

29 rows selected.

  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26239116/viewspace-1476280/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26239116/viewspace-1476280/