本文共 8624 字,大约阅读时间需要 28 分钟。
trace 文件是 42 号进程——也就是后面的 sid 777 生成的。 Redo thread mounted by this instance: 1 Oracle process number: 42 Unix process pid: 4620, image: oracle@hostname
(TNS V1-V3) ---------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 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; ------------------------------ ------------------- sid 163 执行的 SQL 是 SELECT FUNC2 (' :B3 , 86', :B2 , :B1 ) FROM DUAL ----- Information for the OTHER waiting sessions ----- sid: 163 ser: 5387 audsid: 124855449 user: 117/usr
SELECT FUNC2 (:B3 , '86', :B2 , :B1 ) FROM DUAL ----- End of information for the OTHER waiting sessions ----- 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 可见 163 和 777 两个 session 是执行同一个函数。 ----- PL/SQL Call Stack ----- c0000006b13f2808 1963 function usr
.FUNC2
c0000006a8debfe8 119 function usr
.FUNC1
下面是代码里FUNC1 中调用 FUNC2 的地方。需要注意的是参数 col1 和截止日期enddate
。 Select FUNC2
( Cursor_Col1 , 出现死锁的 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 记录,以及“提数截止日期” 这两个因素决定的 。 Cursor v_cur_col1(tCol1 xxx.Col1%Type) Is Select * From tab2
Where col1
= tcol1
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 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
也就是说只要两个 session 在调用 FUNC2 时,传入了相同的 col1 和 enddate ,就有可能出现死锁。 通过 FUNC1 的参数 threadnumber 线程号,从 selcol1 里取 col1 。这样在并发执行的时候可以让每个线程处理不同的col1。 Cursor v_Cursor_SelCol1 Is 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
; 从 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) ------------- --------------------- 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 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' DBMS_LOB.SUBSTR(SQL_TEXT,100) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SELECT usr
.FUNC1
('0','0',to_date('2015-03-07','YYYY-MM-DD'), '110') FROM dual SELECT usr
.FUNC1
('0','0',to_date('2015-03-07','YYYY-MM-DD'), '118') FROM dual SELECT usr
.FUNC1
('0','0',to_date('2015-03-07','YYYY-MM-DD'), '18') FROM dual SELECT usr
.FUNC1
('0','0',to_date('2015-03-07','YYYY-MM-DD'), '32') FROM dual SELECT usr
.FUNC1
('0','0',to_date('2015-03-07','YYYY-MM-DD'), '36') FROM dual SELECT usr
.FUNC1
('0','0',to_date('2015-03-07','YYYY-MM-DD'), '74') FROM dual SELECT usr
.FUNC1
('0','0',to_date('2015-03-07','YYYY-MM-DD'), '75') FROM dual SELECT usr
.FUNC1
('0','0',to_date('2015-03-07','YYYY-MM-DD'), '78') FROM dual 如果这些执行次数超过一次的 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 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' 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 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 17 group by session_id, sql_id 13:15:27 18 order by 2, 3 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 来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26239116/viewspace-1476280/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26239116/viewspace-1476280/