row lock contention

[effjott]

Hi all,

we just have Alfresco installed on Oracle 10.2.0.2

on several point we got "TX row lock contention"

One causing sql is
update alf_node set version=:1, protocol=:2, identifier=:3, uuid=:4, type_qname=:5, acl_id=:6 where id=:7 and version=:8

another is
delete from alf_child_assoc where id=:1 and version=:2

Does someone out there have simular problems ?

"row lock contention" occurs is 2 or more sessions want to update the same record, but the first session didn't send a commit.

br
EffJott

[loglady]

Hello,

did you manage to resolve your issue? We have similar problems with table ALF_CONTENT_DATA.
In AWR report from Oracle DB we observe:

Event   Waits   %Time -outs   Total Wait Time (s)   Avg wait (ms)   Waits /txn   % DB time
enq: TM - contention   103   0   746   7242   0.00   29.28
enq: TX - row lock contention   1,058   4   677   640   0.01   26.56

In DB trace files we have deadlocks:

—– Information for the OTHER waiting sessions —–
Session 2304:
  sid: 2304 ser: 55635 audsid: 337050 user: 43/ALFRESCO
    flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 200 O/S info: user: oracle11, term: UNKNOWN, ospid: 13041830
    image: oracle@gprdufedb
  client details:
    O/S info: user: was, term: unknown, ospid: 1234
    machine: aprdufe10.centrala.kbsa program: JDBC Thin Client
    application name: JDBC Thin Client, hash value=2546894660
  current SQL:
  delete
        from
            alf_content_data
        where
            id = :1

—– End of information for the OTHER waiting sessions —–


We are using Alfresco Community Edition version 4.0.d
Databse Oracle 11g 11.2.0.3

Any help will be appreciated.

LogLady