Deadlock in KMC_DBRM_TMPNODES

[Former Member]

Hello,

We have built a WebDynpro application using KMC module, and integrated in the EP.

Our systems is based on Oracle, SPS16 and patch #3 for KMC. The server has 1 Java dispatcher and 2 server processes.

Our application stores data in a dedicated CM Repository, and searches resources matching Predefined Properties using the "com.sapportals.wcm.repository.manager.cm.CmPropertySearchManager"Property Search Manager.

When I perform a load test with 20 concurrent users, I get the following exception :

#1.5#00E08131AFB2005D0000001500000CDC00041E7F1A669B41#1159431159410#com.sap.sql.jdbc.direct.DirectPreparedStatement#sap.com/tc_wddispwda#com.sap.sql.jdbc.direct.DirectPreparedStatement#rise_eu1#53####121216d04ec911db933300e08131afb2#ID\#(J2EE4659200)ID0963799650DB11903176495300471993End.WID1159341490666##0#0#Error#1#/System/Database/sql/jdbc/direct#Java#com.sap.sql_0003##SQL error occurred on connection KM4:nwb:SAPNWBDB: code=60, state="61000", message="ORA-00060: deadlock detected while waiting for resource

";

SQL statement is "DELETE FROM "KMC_DBRM_TMPNODES"".#5#60#61000#ORA-00060: deadlock detected while waiting for resource

#KM4:nwb:SAPNWBDB#DELETE FROM "KMC_DBRM_TMPNODES"#

#1.5#00E08131AFB2005D0000001600000CDC00041E7F1A66A875#1159431159410#com.sap.sql.jdbc.direct.DirectPreparedStatement#sap.com/tc_wddispwda#com.sap.sql.jdbc.direct.DirectPreparedStatement#rise_eu1#53####121216d04ec911db933300e08131afb2#ID\#(J2EE4659200)ID0963799650DB11903176495300471993End.WID1159341490666##0#0#Error##Plain###java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)

at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:589)

at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1957)

at oracle.jdbc.ttc7.TTC7Protocol.executeFetch(TTC7Protocol.java:975)

at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2187)

at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2063)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2922)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:644)

at com.sap.sql.jdbc.basic.BasicPreparedStatement.executeUpdate(BasicPreparedStatement.java:103)

at com.sap.sql.jdbc.oracle.Oracle9iPreparedStatement.executeUpdate(Oracle9iPreparedStatement.java:465)

at com.sap.sql.jdbc.direct.DirectPreparedStatement.executeUpdate(DirectPreparedStatement.java:311)

at com.sap.sql.jdbc.common.CommonPreparedStatement.executeUpdate(CommonPreparedStatement.java:314)

at com.sap.engine.services.dbpool.wrappers.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:240)

at com.sapportals.wcm.repository.manager.cm.persistence.commonjdbc.PersistenceCommonJDBC.executeQuery(PersistenceCommonJDBC.java:849)

at com.sapportals.wcm.repository.manager.cm.CmPropertySearchManager.execute(CmPropertySearchManager.java:86)

...

Is that a known bug of KMC ?

Have you already met this problem ?

Best Regards,

Nicolas

[Former Member]

Hi Nicolas,

I would suggest it's not necessary that this caused with the application

you deployed. So, could not conclude that a prop. search is causing the

load and not the other API calls / modification that your code is

performing. But it is possible that the search will cause this overhead

if the search scope (number of nodes to be affected by a query) is

large. The search component will prepare a temporary table with all

affected node identifiers. If e.g. your search root folder contains 1

million documents, this amount of IDs will temporarily inserted into

this table.

If this theory is correct then it seems that you have a database

administration issue here. If it is not possible to provide more hard

drive space to support such large repositories you could try to

deactivate the archive logs on the temporary table. I will find out the

table name and attach it to the

message (Oracle "alter table ..." statement can be used).

Best Regards

Anjali

[Former Member]

Hi Nicolas,

I tried to reproduce your description with automated testcases for loadtesting the property search in SPS20. But up to now the tests all succeeded. So I think I need some more input from you.

<b>*</b> In your test scenario: Do the ProertySearches fail or do they recover from the described exception and deliver the correct serach result anyway?

<b>*</b> What persistence mode are you using in your CM repository?

<b>*</b> Are there any services active on that repository? Can you reproduce the failure if you turn the services off?

<b>*</b> Are there any other processes working on your CM repository while you are testing?

<b>*</b> How large is the hierarchy scope that is covered by each of your search requests? Do they overlap?

<b>*</b> Can you please state what the query expression looks like that you use for testing?

Best regards,

Michael