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? 2. when say recreate the indices, you are talking about the entire work/slide_index directory, correct?
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
