Re: Table modifications with dependent views - best practices?

> So I have a scenario in which account creation at the application
> layer generates a set of tables and indexes. The tables created have
> foreign keys to tables in use (and frequently read from and written
> to) by the rest of the application. Occasionally I was getting
> deadlocks, and this definitely explains why, if creating foreign keys
> requires an AccessExclusiveLock on the table to which the key refers.
>
> Ideally, this DDL work should occur in a transaction to avoid partial
> creation of the relevant objects, but it seems like it will always run
> the risk of generating deadlocks in a production environment. Blocking
> is less of an issue because the transaction shouldn't ever take
> terribly long, but deadlocks always strike me as a red flag,
> especially in a production application environment.
>
> Is there a best practice or suitable workaround for this sort of
> scenario?
>

Deadlock only occurs when resources are acquired out of order by two or
more concurrent processes. So you can avoid deadlock by acquiring
locks in the same order. This of course is non-trivial, if the
different processes are doing different tasks.
One way is to acquire an exclusive lock at the very beginning of your
transaction, say on the users table. Perhaps do this at the very
begging, i.e. before the trigger fires. Of course whether this works
depends on what you are deadlocking on. You probably need to analyse
the lock cycles to determine which locks you need to acquire early.

Another possibility would be to change the trigger to write a message
to a queue table, and have another process service the queue and add
the users. This way you are splitting the transaction in half, and
thus releasing the locks obtained before the trigger fired.

Of course you don't get this problem in oracle. I find its optimistic
locks wonderful.

David