Hi Silke, I could be that the indices are missing in your database schema. This is known to cause deadlocks in oracle in combination with foreign keys. You can add the indices by running the following sql script: http://svn.hippocms.org/repos/hippo/hippo-repository/trunk/server/src/config/slide/Oracle-add-indices-to-foreign-keys.sql
Regards, Bart On Mon, Aug 31, 2009 at 7:47 PM, silke<[email protected]> wrote: > > Hi Bart et al, > > >From the thread below, it looks like this fix was done. However, we are > running into the same issue every day. We have the Slide Repo version > 1.2.15 and Hippo CMS on Tomcat 6.0.18/Oracle 10g. We have applied the patch > that was given for this fix and removed the indices/restarted repo - still > no change. > > Could you please let us know if we need more than the following for fixing > this problem: > > a. slide-kernel-2.1h25.jar > > b. slide-stores-2.1h25.jar > > c. slide-webdavlib-2.1h25.jar > > d. slide-webdavservlet-2.1h25.jar > > > > In addition, the following jar file is removed > > a. webdavlib-2.0.jar > > > Do we need to completely upgrade to repo 1.2.16 version to get this fixed. > Please advise. > > Also, where is the Slide Source that we can peek into to solve this problem. > > Appreciate your help in this, > > Silke > > RE: [HippoCMS-dev] Deletion causes oracle deadlock? > > jun.ni > Thu, 22 Jan 2009 12:27:20 -0800 > > 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 >>> > > -- > View this message in context: > http://www.nabble.com/Deletion-leading-to-Oracle-Deadlock-issue-tp25192517p25192517.html > Sent from the Hippo CMS development public mailinglist mailing list archive > at Nabble.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 > > -- 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
