[Building Sakai] Deleting resources in Melete is causing Deadlocks

[Dunstall, Christopher]

Hi Mallika,

I was wondering if you could help me with a Melete issue we're having?  We've seen this deadlock occur a few times now, since upgrading to Melete 2.6.

Do you know if this was a problem fixed in Melete's most recent release (2.7.2) or whether you know what might be causing this?

We are using Sakai 2.4.x, on Oracle 10.2.0.2.

Regards,

Chris Dunstall | Service Support - Applications
Technology Integration/OLE Virtual Team
Division of Information Technology | Charles Sturt University | Bathurst, NSW

Ph: 02 63384818 | Fax: 02 63384181

From: Dhillon, Harvarinder
Sent: Thursday, 19 August 2010 10:59 AM
To: Dunstall, Christopher; Roma, David
Cc: Roberts, John; Goopy, Dale; Christian, Heath; Gandhi, Chirag
Subject: I:158907 Deadlock in PRODOLE2

Hi Guys,

There has been a deadlock in PRODOLE2 created by the SAKAI application.  Can you please look into why the following two sessions are blocking each other out in performing DML?


*** 2010-08-19 09:56:54.078
*** SERVICE NAME:(SYS$USERS) 2010-08-19 09:56:54.076
*** SESSION ID:(440.17054) 2010-08-19 09:56:54.076
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
update melete_section_resource set RESOURCE_ID=null where SECTION_ID in (11207465 , 11207466 , 11207467 , 11207468 , 11207469 , 11207470 , 11207471 , 11207472 , 11207473 , 11207474 , 11207475 , 11207476 , 11207477 , 11207478 , 11207479 ,
 11207480 , 11207481 , 11207482 , 11207483 , 11207484 , 11207485 , 11207486 , 11207487 , 12353917 , 12353918 , 12353919 , 12353920 , 12353921 , 12353922 , 12353923 , 12353924 , 12353925 , 12353926 , 12353927 , 12353928 , 12353929 , 12353
930)
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 waits  process session holds waits
TM-0000d4fd-00000000        46     440    SX             33     824    SX   SSX
TX-00010010-0005cc7c        33     824     X             46     440           X
session 440: DID 0001-002E-00020CC6     session 824: DID 0001-0021-000058EA
session 824: DID 0001-0021-000058EA     session 440: DID 0001-002E-00020CC6
Rows waited on:
Session 824: no row
Session 440: obj - rowid = 0000D4FD - AAANT9AAbAAAFyGAAv
  (dictionary objn - 54525, file - 27, block - 23686, slot - 47)
Information on the OTHER waiting sessions:
Session 824:
  pid=33 serial=46241 audsid=13591928 user: 65/SAKAI
  O/S info: user: , term: , ospid: 1234, machine: appprod05
            program:
  Current SQL Statement:
  delete from melete_resource where RESOURCE_ID=:1
End of information on OTHER waiting sessions.


Best Regards,

H (Harvey) Dhillon
Service Support (DBA).
Division of Information Technology,
Charles Sturt University.
Wagga Wagga, NSW. 2678.
(02) 69332124.

YOU MUST READ THIS NOTICE

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20100819/69ed9908/attachment.html