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.
