Great! Thanks for the clarification, Bart! Good job on tracing down this annoying deadlock problem! Cheers Jun
-----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Bart van der Schans Sent: Thursday, January 22, 2009 12:22 PM To: Hippo CMS development public mailinglist Subject: Re: [HippoCMS-dev] Deletion causes oracle deadlock? [email protected] wrote: > Thanks Bartosz! That's definitely great news. > A couple things to clarify with you: > 1. when you say "this is already be fixed", do you mean the deadlock > occurs in deleting document locking or deleting version* tables? The bug seems to come from the code that checks for existing locks in combination with some other (write) operations. > 2. when say recreate the indices, you are talking about the entire > work/slide_index directory, correct? You probably don't need to delete your old index. If it turns out it is needed it will be clearly stated in the release notes. And yes, that would be the work/slide_index directory (although the actual location depends on your configuration). Regards, Bart > > Thanks a lot! > Jun > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of Bartosz > Oudekerk > Sent: Thursday, January 22, 2009 12:05 PM > To: Hippo CMS development public mailinglist > Subject: Re: [HippoCMS-dev] Deletion causes oracle deadlock? > > > Hi Jun, > > this should already be fixed in the -current trunk. While it will > probably be a few weeks before 1.2.16 final is released, you might want > to try it (help us test it) on your test/dev environment. > > You will need to recreate you indices[0] when upgrading to the trunk > though. > > [0] Remove them before starting the new repository, and it will recreate > them. > > Regards, > Bartosz > > [email protected] wrote: >> Hi guys, >> >> We have oracle-backed hippo repository. Occasionally we see an oracle >> error ora06000 deadlock detected. When this happens, the hippo cms >> stopped responding when we try to open a document, but the >> hippo-repository (hippo:60000) is responding with no problem. The user >> was doing normal operations, eg, search, edit, delete, go back to >> search, open another document, delete. The only thing the user noticed >> is that sometimes she didn't wait till the spinner finish spinning >> before she performs her next operation. >> >> >> >> So we traced the error in oracle log. Almost all errors are like this: >> >> DEADLOCK DETECTED ( ORA-00060 ) >> >> [Transaction Deadlock] >> >> 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. >> >> ======== >> >> >> >> delete from LOCKS where LOCKS.LOCK_ID in (select u.URI_ID from URI u >> where u.URI_STRING=:1) >> >> delete from URI where URI_STRING=:1 >> >> name=delete from URI where URI_STRING=:1 >> >> name=delete from LOCKS where LOCKS.LOCK_ID in (select u.URI_ID >> from URI u where u.URI_STRING=:1) >> >> delete from URI where URI_STRING=:1 >> >> delete from LOCKS where LOCKS.LOCK_ID in (select u.URI_ID from URI u >> where u.URI_STRING=:1) >> >> >> >> We enabled document locking last month, and that's when this error has >> showed up frequently. So I assumed that there were flaws in the > document >> locking mechanism. However, I found one interesting deadlock error >> caused by this query BEFORE we enabled document locking. It is also an >> oracle deadlock trigged by delete: >> >> delete from VERSION_HISTORY vh where vh.URI_ID in (select u.URI_ID > from >> URI u where u.URI_STRING = :1) >> >> delete from URI where URI_STRING = :1 >> >> delete from VERSION_PREDS vp where vp.VERSION_ID in (select >> vh.VERSION_ID from VERSION_HISTORY vh, URI u where vh.URI_ID = > u.URI_ID >> and u.URI_STRING = :1) >> >> name=delete from VERSION_PREDS vp where vp.VERSION_ID in (select >> vh.VERSION_ID from VERSION_HISTORY vh, URI u where vh.URI_ID = > u.URI_ID >> and u.URI_STRING = :1) >> >> name=delete from PROPERTIES p where p.VERSION_ID in (select >> vh.VERSION_ID from VERSION_HISTORY vh, URI u where vh.REVISION_NO = :1 >> and vh.URI_ID = u.URI_ID AND u.URI_STRING = :2) >> >> name=delete from VERSION_LABELS vl where vl.VERSION_ID in > (select >> vh.VERSION_ID from VERSION_HISTORY vh, URI u where vh.REVISION_NO = :1 >> and vh.URI_ID = u.URI_ID AND u.URI_STRING = :2) >> >> name=delete from VERSION_CONTENT vc where vc.VERSION_ID in > (select >> vh.VERSION_ID from VERSION_HISTORY vh, URI u where vh.REVISION_NO = :1 >> and vh.URI_ID=u.URI_ID AND u.URI_STRING=:2) sqltxt(0x8a0b7a70)=delete >> from VERSION_PREDS vp where vp.VERSION_ID in (select vh.VERSION_ID > from >> VERSION_HISTORY vh, URI u where vh.URI_ID = u.URI_ID and u.URI_STRING > = >> :1) sqltxt(0x85ca1010)=delete from VERSION_CONTENT vc where >> vc.VERSION_ID in (select vh.VERSION_ID from VERSION_HISTORY vh, URI u >> where vh.REVISION_NO = :1 and vh.URI_ID=u.URI_ID AND u.URI_STRING=:2) >> sqltxt(0x8a0a9030)=delete from VERSION_LABELS vl where vl.VERSION_ID > in >> (select vh.VERSION_ID from VERSION_HISTORY vh, URI u where >> vh.REVISION_NO = :1 and vh.URI_ID = u.URI_ID AND u.URI_STRING = :2) >> sqltxt(0x8a0fc348)=delete from PROPERTIES p where p.VERSION_ID in >> (select vh.VERSION_ID from VERSION_HISTORY vh, URI u where >> vh.REVISION_NO = :1 and vh.URI_ID = u.URI_ID AND u.URI_STRING = :2) >> >> >> >> It looks like a fairly normal operation and should be tested well. So > I >> don't understand why we are getting this. Could it possibly be > anything >> we didn't do right in our oracle db, or the repository configuration? >> Anyone has any clues? >> >> >> >> Thanks >> >> Jun >> >> ******************************************** >> Hippocms-dev: Hippo CMS development public mailinglist >> >> Searchable archives can be found at: >> MarkMail: http://hippocms-dev.markmail.org >> Nabble: http://www.nabble.com/Hippo-CMS-f26633.html >> > > > -- > Bartosz Oudekerk > .---------------------------------.-----------------------------------. > | Hippo B.V. | Hippo USA Inc. | > | Oosteinde 11 | 101 H Street, suite Q Petaluma CA | > | 1017 WT Amsterdam | 94952-5100 San Francisco | > | The Netherlands | United States | > | Tel +31 (0)20 5224466 | +1 (707) 773-4646 | > +---------------------------------+-----------------------------------+ > | [email protected] | http://www.onehippo.com | > `---------------------------------^-----------------------------------' > ******************************************** > Hippocms-dev: Hippo CMS development public mailinglist > > Searchable archives can be found at: > MarkMail: http://hippocms-dev.markmail.org > Nabble: http://www.nabble.com/Hippo-CMS-f26633.html > > > ******************************************** > Hippocms-dev: Hippo CMS development public mailinglist > > Searchable archives can be found at: > MarkMail: http://hippocms-dev.markmail.org > Nabble: http://www.nabble.com/Hippo-CMS-f26633.html > -- Hippo B.V. - Amsterdam Oosteinde 11, 1017 WT, Amsterdam, +31(0)20-5224466 Hippo USA Inc. - San Francisco 101 H Street, Suite Q, Petaluma CA, 94952-3329, +1 (707) 773-4646 ----------------------------------------------------------------- http://www.onehippo.com - [email protected] ----------------------------------------------------------------- ******************************************** Hippocms-dev: Hippo CMS development public mailinglist Searchable archives can be found at: MarkMail: http://hippocms-dev.markmail.org Nabble: http://www.nabble.com/Hippo-CMS-f26633.html ******************************************** Hippocms-dev: Hippo CMS development public mailinglist Searchable archives can be found at: MarkMail: http://hippocms-dev.markmail.org Nabble: http://www.nabble.com/Hippo-CMS-f26633.html
