[Building Sakai] Deleting resources in Melete is causing Deadlocks

[Mallika M Thoppay]

Hi Christopher,

I don't have a good answer for you apart from being able to tell you 
that we had a similar deadlock error reported by someone in March this 
year. We don't use Oracle, so are at a bit of a loss as to be able to 
simulate these errors. I would like to try to help you from the 
information you have given us. As far as I understand from the log, the 
current query that is being executed is the update section_resource... 
statement. The query that is causing the deadlock is the delete from 
melete_resource statement.. Is this correct? It is sort of strange that 
this happens, since according to the code the update statement executes 
*before* the delete statement. Perhaps the delete statement is 
deadlocking from a previous attempt to delete the modules? This = 
condition in the delete statement is replaced in the recent release with 
the in clause, btw. Not sure if that will make a difference. What are 
the conditions under which a delete statement on a table can deadlock? 
Would it happen when one query tries to delete from it and another query 
tries to update it?

Thanks,
Mallika


Dunstall, Christopher wrote:
>
> 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*
>


-- 
Mallika M Thoppay
Learning Systems Developer
Etudes Inc
http://etudes.org