Row lock contention / deadlock on roller_weblogentrytagagg table

[Van Rogers-Ho <va...@gmail.com>]

Hi,

Has anyone experience this issue on high transaction blogs? I'm running into
a deadlock situation during load testing. I'm using 50 virtual users spread
out 3 different blog instances (website). After a few hours, I run into a
deadlock situation and then my jdbc connections starts to ramp up until it
runs out and my midtier becomes overloaded.


Oracle ADDM shows:

*Application Analysis
Significant row contention was detected in the TABLE
"ROLLER.ROLLER_WEBLOGENTRYTAGAGG" with object ID 66503. Trace the cause of
row contention in the application logic using the given blocked SQL.
Database ObjectROLLER.ROLLER_WEBLOGENTRYTAGAGG

The SQL statement with SQL_ID "dnz0jw4db85ss" was blocked on row locks.
SQL Textupdate roller_weblogentrytagagg set total=total+:1,
lastused=current_timestamp w...
SQL IDdnz0jw4db85ss
update roller_weblogentrytagagg set total=total+:1,
lastused=current_timestamp where name=:2 and websiteid=:3


The SQL statement with SQL_ID "5v9yr2v6ud26v" was blocked on row locks.
SQL Textupdate roller_weblogentrytagagg set total=total+:1,
lastused=current_timestamp w...
SQL ID5v9yr2v6ud26v
update roller_weblogentrytagagg set total=total+:1,
lastused=current_timestamp where name=:2 and (websiteid is null)*


Roller logs show:

Apr 11, 2011 2:28:23 PM org.hibernate.util.JDBCExceptionReporter
logExceptions
WARNING: SQL Error: 60, SQLState: 61000
Apr 11, 2011 2:28:23 PM org.hibernate.util.JDBCExceptionReporter
logExceptions
SEVERE: ORA-00060: deadlock detected while waiting for resource

...
Then transactiosn start to backup:

Apr 11, 2011 2:28:23 PM
org.apache.roller.weblogger.ui.struts2.editor.EntryRemove remove
SEVERE: Error removing entry 71ed214c-cda7-4991-89c6-7317da092d19
org.hibernate.exception.GenericJDBCException: could not execute update query

 Finally, connections ran out:

Caused by: java.sql.SQLException: Internal error: Cannot obtain XAConnection
weblogic.common.resourcepool.ResourceLimitException: No resources currently
available in pool BLOGS_server1 to allocate to applications, please increase
the size of the pool and retry..



I'm running 4.0.1 on WLS 11g and Oracle DB.

Could it be that I have to many new entries (with tags) being created
against one particular website/blog with x minutes? Any body have
performance/load testing experience with Roller?
Any work around to the code /table to prevent this during peak transactions?

Thanks,
Van