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
