I made the change in a clean install and tested it
for concurrency problems and with 5 simultaneous scripts
creating new users I was about to create 50k users
(100k acs_objects) without any duplicated keys. There were
no deadlocks or any appreciable decline in the speed of
account creation versus doing just one thread creating accounts (i.e. one was taking ~100ms per acct and 5 were
taking ~500ms per account for about the same total accounts/sec being created).
I did find a concurrency problem with the acs_objects_context_id_up_tr trigger (which maintains the
acs_object_context_index table), which only manifested itself because of a second bug in the trigger. The plain old bug was simple...it said:
if new.object_id = old.object_id and
new.context_id = old.context_id and
new.security_inherit_p = old.security_inherit_p then
return new;
end if;
but if new.context_id and old.context_id were null it would
still evaluate the trigger code since new.context_id = old.context_id is false if both are null.
Changing it to this:
if new.object_id = old.object_id
and ((new.context_id = old.context_id)
or (new.context_id is null and old.context_id is null))
and new.security_inherit_p = old.security_inherit_p then
return new;
end if;
fixes that. Although the concurrency problem still exists,
it is far less likely that it would be encountered
with this fix applied.
