Yan Cheng CHEOK <yccheok@yahoo.com> writes:
> Currently, I have a stored procedure(get_existing_or_create_lot), which will be called by 2 or more processes
simultaneously.
> Every process will have a unique lot name. What the store procedure does it
> 1) Insert lot name into "lot" table. A unique lot id will be returned after insertion into "lot" table.
> 2) Check if unit_{id} table does exist. For example, if the returned lot id is 14, PostgreSQL will check whether
"unit_14"table does exist. If no, "CREATE TABLE unit_14..." will be executed.
> Unfortunately, I get the run time error ;
> 2010-04-26 13:28:28 MYTERROR: deadlock detected
The reason for the error is probably that establishing the FK reference
to table "lot" requires an exclusive lock on "lot", so each occurrence
of this creation will serialize on that, in addition to anything else
it might be locking.
My opinion is that you're shooting yourself in the foot with a poorly
chosen database layout. Forget all the subtables and just have one
big unit table. It'll be far simpler and probably perform better too.
regards, tom lane