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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
