Alfresco 4.1 with Oracle database: tens of sessions executing UPDATE ALF_NODE are waiting on "enq: TX - row lock contention". What's wrong?

[riccardohp]

This is the UPDATE being executed:

SQL Details: 1jx8qmjafqffj: PROD.gruppoitas.local_PROD2 (Database Instance) - Oracle Enterprise Manager

update alf_node set
version = :1



, transaction_id = :2

, audit_creator = :3
, audit_created = :4
, audit_modifier = :5
, audit_modified = :6
, audit_accessed = :7

where
id = :8

and version = (:9 - 1)

[afaust]

Alfresco 4.1 was an Enterprise-only release and you are using it with Oracle, which also is Enterprise-specific. Have you contacted Support with your question? As far as I know, 4.1 should already be in limited support state as it is quite an old release by now.

A lock contention on such simple statements can happen if you have concurrent operations accessing the same rows. You won't see it from just looking at one statement - you'll have to look at the entire set of operations being executed at the time. Ideally, you would use a JDBC logging tool (like p6spy) to capture the various SQL statements, and use the log file to look at what operations the various threads have been trying to execute around the time of such occurences. Then by correlating IDs used in the operations, you can potentially identify conflicting / concurrent operations and determine causes for (excessive) row lock contentions, potentially leading to a refactoring of custom code / rules etc.

[riccardohp]

Hi,

thank you for your response.

We will try and use such jdbc tools to have an idea on what's happening, generally speaking we are finding at least unusual such attempt to update the very same row when the application is based on optimistic locking (so I would expect more of a "0 rows updated" in case of concurrency for all sessions coming "too late"). The core issue seems here to be that the first session modifying the row doesn't actually commit/rollback so causing the enqueue problem.

Riccardo