8.1 improved locking for foreign key references but had an unexpected
consequence to our application - no parallel loads in our application.
The application does an EXCLUSIVE lock on 'addresses'. It then gets all of
the keys from 'addresses' it needs, and adds new ones encountered in this
load. It then completes the transaction, releases the exclusive lock, and
inserts the other table's records using the values read from/inserted into
'addresses'.
There are foreign key constraints between the various tables and 'addresses'
to insure referential integrity.
Previously (pgsql 7.4.5), multiple loads would run simultaneously - and
occasionally got 'deadlock detected' with the foreign key locks even though
they were referenced in sorted order. When loading tables other than
'addresses', foreign key locks did not prevent other jobs from grabbing the
exclusive lock on 'addresses'.
With 8.1.4, the foreign key locks prevent other instances from grabbing the
lock, so they wait until the first job is complete - only one job loads at a
time.
About EXCLUSIVE locks, the manual says: "...only reads from the table can
proceed in parallel with a transaction holding this lock mode."
What is now the appropriate lock? It needs to:
1. Prevent others from updating the table
2. Block other jobs that are requesting the same lock (if job 2 does a
SELECT and finds nothing, it will try to create the record that job 1 may
already have created in its transaction).
3. Not conflict with foreign key reference locks
SHARE does not appear to be appropriate - it would fail #2. Maybe "SHARE
UPDATE EXCLUSIVE"?
Wes
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
