[Building Sakai] Locks on SAKAI_EVENT table

[Drew Zhu]

The problem is your bitmap index:

create bitmap index IDX_SAKAI_EVENT_0001 on SAKAI_EVENT(EVENT);

Bitmap index is not for high transaction volume. Bitmap will cause lock 
and dead locks in online transactional system. It is meant to data 
warehouse type application for improving selections.

Drop that index and create it as a regular b-tree index and your problem 
should go away.

Thanks,
Drew Zhu
Oracle DBA
University of Michigan

On 11/17/2010 1:30 PM, Benito J. Gonzalez wrote:
> Hi all,
>
> We are seeing a number of locks on our SAKAI_EVENT table.  Has anyone
> else dealt with issues surrounding this table?  Some details follow:
>
>      * Five public-facing tomcats and three task-specific tomcats against
>        our production DB
>      * Oracle 10g release 10.2.0.4.0 64bit
>      * The following indices have been added to SAKAI_EVENT, then removed
> in case they caused performance issues
>            o create bitmap index IDX_SAKAI_EVENT_0001 on SAKAI_EVENT(EVENT);
>            o create index IDX_SAKAI_EVENT_0002 on SAKAI_EVENT(REF);
>      * We had 60 million records last week.  Trimming the records to a
>        few hundred thousand did not reduce deadlocks
>      * Presence turned off recently (last day or so)
>      * Deadlocks are from insert contention on SAKAI_EVENT
>      * We suspected that the bitmap index on the table was contributing
>        to the issue.  Removing it resulted major load from the following
> SELECT:
>            o High load from "SELECT EVENT_DATE, REF, SESSION_ID,
>              EVENT_CODE FROM SAKAI_EVENT WHERE EVENT = :1 ORDER BY
>              EVENT_DATE"
>            o No deadlocks / locks in last 24 hours
>      * DB connection pool is DBCP 1.2.2 (out of date?)
>      * No DBCP settings explicitly set
>
> Any help appreciated,
>