ORA-00060 死锁，大家帮忙看看文件

显示全部楼层 · 2005-10-30 17:05:33

ORACLE9I AIX 5.3

千问 · 2005-10-30 17:05:33

session 52 被session15锁住了。session 15的update还没结束，占着资源。
用这个sql可以查：
SELECT
bs.username as Blocking_User,
ws.username asWaiting_User,
bs.sid asBlocking_Session_ID,
ws.sidas Waiting_Session_ID,
bs.program as Blocking_App,
ws.program as Waiting_App,
bs.machine asBlocking_Machine,
ws.machine as Waiting_Machine,
bs.osuser as Blocking_OS_User,
ws.osuser as Waiting_OS_User
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.sid = bs.sid(+)
AND wk.sid = ws.sid(+)
AND (bs.username IS NOT NULL) );

千问 · 2005-10-30 17:05:33

但现在ORA已经自动解锁了,什么都查不到了.哪位可以帮忙看看TRC文件中有没有可以发现死锁原因的地方

千问 · 2005-10-30 17:05:33

你的trc文件中的两条SQL是不是在一个事务里面。
而同时又有多个用户同时运行这个事务。
查一下应用吧。

千问 · 2005-10-30 17:05:33

首先你要找出这个deadlock发生在什object上用下面的命令告诉我结果。
SELECT object_name, dba_objects.object_type
FROM dba_objects
WHERE object_id IN (SELECT to_number('00009D53', 'xxxxxxxx') FROM dual);

SELECT object_name, dba_objects.object_type
FROM dba_objects
WHERE object_id IN (SELECT to_number('00009D69', 'xxxxxxxx') FROM dual);

千问 · 2005-10-30 17:05:33

[php]
*** 2007-01-09 10:39:20.857
*** SESSION ID

52.37446) 2007-01-09 10:39:20.664
Warning: I/O request got either EINTR or EAGAIN
Warning: I/O request got either EINTR or EAGAIN
Warning: I/O request got either EINTR or EAGAIN
Warning: I/O request got either EINTR or EAGAIN
Warning: I/O request got either EINTR or EAGAIN
Warning: I/O request got either EINTR or EAGAIN
*** 2007-01-09 10:40:56.007
DEADLOCK DETECTED
Current SQL statement for this session:
update LCGrpPol set Prem=(select SUM(Prem) from LCPol where GrpContNo='99029288' and riskcode ='212401'), Amnt=(select SUM(Amnt) from LCPol where GrpContNo='99029288' and riskcode ='212401'), SumPrem=(select SUM(SumPrem) from LCPol where GrpContNo='99029288' and riskcode ='212401'), Mult=(select SUM(Mult) from LCPol where GrpContNo='99029288' and riskcode ='212401'), Peoples2=(select SUM(InsuredPeoples) from LCPol where GrpContNo='99029288' and riskcode ='212401' and PolTypeFlag'2') where grppolno='9022000000042588'
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:

---------Blocker(s)-----------------Waiter(s)---------
Resource Name
process session holds waitsprocess session holds waits
TX-0004001c-000042112752 X
2015 X
TX-0005000d-000041ac2015 X
2752 X
session 52: DID 0001-001B-00000002
session 15: DID 0001-0014-00000002
session 15: DID 0001-0014-00000002
session 52: DID 0001-001B-00000002
Rows waited on:
Session 15: obj - rowid = 00009D53 - AAAJ1TAAMAAAEPWAAA
(dictionary objn - 40275, file - 12, block - 17366, slot - 0)
Session 52: obj - rowid = 00009D69 - AAAJ1pAANAAADDWAAA
(dictionary objn - 40297, file - 13, block - 12502, slot - 0)
Information on the OTHER waiting sessions:
Session 15:
pid=20 serial=9756 audsid=10627 user: 66/LISCNPC
O/S info: user: root, term: unknown, ospid: , machine: P520_AS

program: JDBC Thin Client
Current SQL Statement:
UPDATE LCGrpCont SETGrpContNo = :1 , ProposalGrpContNo = :2 , PrtNo = :3 , SaleChnl = :4 , ManageCom = :5 , AgentCom = :6 , AgentType = :7 , AgentCode = :8 , AgentGroup = :9 , AgentCode1 = :10 , Password = :11 , Password2 = :12 , AppntNo = :13 , AddressNo = :14 , Peoples2 = :15 , GrpName = :16 , BusinessType = :17 , GrpNature = :18 , RgtMoney = :19 , Asset = :20 , NetProfitRate = :21 , MainBussiness = :22 , Corporation = :23 , ComAera = :24 , Fax = :25 , Phone = :26 , GetFlag = :27 , Satrap = :28 , EMail = :29 , FoundDate = :30 , GrpGroupNo = :31 , BankCode = :32 , BankAccNo = :33 , AccName = :34 , DisputedFlag = :35 , OutPayFlag = :36 , GetPolMode = :37 , Lang = :38 , Currency = :39 , LostTimes = :40 , PrintCount = :41 , RegetDate = :42 , LastEdorDate = :43 , LastGetDate = :44 , LastLoanDate = :45 , SpecFlag = :46 , GrpSpec = :47 , PayMode = :48 , SignCom = :49 , SignDate = :50 , SignTime = :51 , CValiDate = :52 , PayIntv = :53 , ManageFeeRate = :54 , ExpPeoples = :55 , ExpPremium = :56 , ExpAmnt = :57 , Peoples = :58 , Mult = :59 , Prem = :60 , Amnt = :61 , SumPrem = :62 , SumPay = :63 , Dif = :64 , Remark = :65 , StandbyFlag1 = :66 , StandbyFlag2 = :67 , StandbyFlag3 = :68 , InputOperator = :69 , InputDate = :70 , InputTime = :71 , ApproveFlag = :72 , ApproveCode = :73 , ApproveDate = :74 , ApproveTime = :75 , UWOperator = :76 , UWFlag = :77 , UWDate = :78 , UWTime = :79 , AppFlag = :80 , PolApplyDate = :81 , CustomGetPolDate = :82 , GetPolDate = :83 , GetPolTime = :84 , State = :85 , Operator = :86 , MakeDate = :87 , MakeTime = :88 , ModifyDate = :89 , ModifyTime = :90 , EnterKind = :91 , AmntGrade = :92 , Peoples3 = :93 , OnWorkPeoples = :94 , OffWorkPeoples = :95 , OtherPeoples = :96 , RelaPeoples = :97 , RelaMatePeoples = :98 , RelaYoungPeoples = :99 , RelaOtherPeoples = :100 , FirstTrialOperator = :101 , FirstTrialDate = :102 , FirstTrialTime = :103 , ReceiveOperator = :104 , ReceiveDate = :105 , ReceiveTime = :106 , TempFeeNo = :107 , HandlerName = :108 , HandlerDate = :109 , HandlerPrint = :110 , AgentDate = :111 , BusinessBigType = :112 , MarketType = :113 , ProposalType = :114 , SaleChnlDetail = :115 , ContPrintLoFlag = :116 , PremApportFlag = :117 , ContPremFeeNo = :118 , CustomerReceiptNo = :119 , CInValiDate = :120 , RoleAgentCode = :121 , SpecialFlag = :122 , ProtocolRelaFlag = :123 , ProposalState = :124 , ContFlag = :125 , PayFlag = :126 , PlanPay = :127 , NotePad = :128 , ManageMode = :129 , ClientSource = :130 , GEBClient = :131 , ProfitReturn = :132 , RepeatBill = :133 , OldPtrNo = :134 , OldTime = :135 , InfoMode = :136 , InsureUnit = :137 , BankInfo = :138 , BalanceMode = :139 , BalanceCyc = :140 , SpecialTag = :141 , BankPrv = :142 , BankCity = :143 , ProDistributeType = :144 , TKBillTag = :145 WHEREGrpContNo = :146
End of information on OTHER waiting sessions.
[/php]

千问 · 2005-10-30 17:05:33

以上信息足够了，你还要找啥？

千问 · 2005-10-30 17:05:33

...楼上能告诉我，如果不用我的那个SQL查，能从这个trace中看出，到底锁发生在那个object上么？到底是索引还是表？？

千问 · 2005-10-30 17:05:33

我也想知道顶上去