Derick,
you are right, there are two different problems in the CR showing up with heavy concurrency:
a) duplicate revision_id issue:
two concurrent writers cause a conflict with the cr_dummy update, causing a redo of the rule body with the duplicate insert attempt. Your proposed solution won't work for deeper derived types, where the computed value of content_revision__new() is used as first attribute for multiple type records (not only for one as in your example). Do a "\d xowiki_objecti" for an example when you have xowiki install. Using a generated stored procedure is better here, since the return value of content_revision__new() can be stored in a local variable of the procedure.
b) the deadlock problem:
this is the even harder problem, caused by foreign key references to acs_objects (i think). The problem happens not only for acs_objects() but also (for me much more frequent) in the materialized security context table acs_object_context_index(), which i have check in more detail. acs_object_context_index has three attributes, two of which are foreign keys to acs_objects. these foreign keys are the problem.
postgres performs a lock (share lock) on the tuples to which the foreign keys point, apparently to prevent other transactions from modifying the foreign key before this transaction commits. it is practically impossible to cause the references to be always in the same order, so a deadlock can occur. See for a nice simple cases in http://archives.postgresql.org/pgsql-general/2004-09/msg00442.php
the same can happen with the foreign key constraint of context_id in acs_objects as well.
The problem is especially bad for long transactions containing many operations (assessment might be a good example for this). the time of the locking can be shortened by making the constraints deferred (e.g. set these deferrable and use "SET CONSTRAINTS ALL DEFERRED" in the transactions of the CR. The problem won't vanish but occur less often. i would recommend this as a short term improvement.
For testing purposes, i changed the cr-repository such that revisions are no acs-objects any more (removed the acs_object__new withing content_revision__new). now, all deadlocks are gone. this is not a compatible change, since one looses the ability to give permissions on the revision granularity (one can still give permissions on the item_id) and attributes like modifiying-user would have to be stored in the revision tuple, and some queries have to be rewritten. i would believe, that for the sake of robustness and speed, this would be acceptable for many applications. Another idea would be to drop the FK constraint and add some more delete trigger plus maybe a sweeper for orphans. Maybe, some other other ideas will show up.
while this might be a working solution for the CR, i am pretty sure, the same problems with deadlocks caused by FKs can show up for many other acs-objects as well. The large acs_objects table with its many triggers has some issues. But addressing this issue will require many TIPs.
