Morning Martin,
Underpaid overworked and overstressed DBA is inserting
records to a small table which constitutes a foreign key for Master
So, this small table is the PARENT table in the relationship.
The Master table which references the small table will toss
A foreign key constraint if the small table child record
1)isnt there
2)is inserted or updated but not yet committed.
This 'master' table is actually the CHILD in the relationship then.
Given that, (1) and (2) ONLY apply if the FK constraint is not deferred.
If the constraint is deferrable and currently deferred, the insert will
not fail until COMMIT time.
The DBA noticing that he hasn't has food in last 12 hours
runs to the local lunchroom to salvage scraps before scurvy sets in
Yes, we must keep our sustenance levels high!
Client uses Browser to insert brand new master table record
which references the just inserted record (which is not committed)
Which should have failed immediately if the FK constraint is immediate.
I suspect that it may actually be DEFERRED so the insert was allowed to
happen even without seeing the uncommitted row in the 'small' PARENT
table.
The client is displayed resource busy and starts jangling
the phone of the DBA who is on his hands and knees looking for crumbs
Not sure about this. Do you mean that an error "resource busy and
acquired with nowait" occurred or the client saw an hour-glass or other
"working ..." cursor?
This is interesting, because if the FK constraint is deferred, no delay
should have been seen. At client commit time, an error would have
occurred as the client cannot see the DBAs inserts that are not yet
committed. I won't mention the fact that the DBA left a transaction
uncommitted while he was fighting the local pigeons for crumbs! 
If the FK is immediate, then the client should have seen an error as
soon as s/he inserted the child row with no parent in the 'small' table.
Is it possible that the client's insert is actually not hanging on the
uncommitted 'small' inserts done (and uncommitted) by the DBA and is
actually hanging up on some other problem such as a "potential" PK
duplicate (or other Unique constraint) as follows:
- User 1 inserts a row with PK=1234 into table. Does not commit yet.
- User 2, cannot see User 1's inserted row, and does exactly the same
insert - PK=1234.
At this point, user two will hang on, if I remember correctly, a mode 6
enqueue (Normal enqueue hangs are mode 3). This remains hanging until
User 1 commits, in which case User 2 gets a constraint violation, or
rolls back in which case, User 2 gats the insert to work.
Causes of this problem are many and varied:
- Not using a sequence to create the PK column.
- Using a table to get the next value. Anyone reading the current value
at the same time, gets the same value.
- Using "select max(pk) + 1" to get the next PK value.
- For CHAR based PKs, somehow managing to generate the same PK value.
- etc.
The DBA returns to his desk to find the client has contacted
"world authority" to complain that he/she/it is seeing
resource busy while inserting his record via JDBC (assume
thin type4) acquired connection
Users, always in a rush!
What can a conscientious, hardworking (and starving) DBA do
to prevent these (fairly common) resource busy errors?
Martin Gainty
The following:
- Check the deferability of the FK constraint;
- Are the FK columns indexed in the CHILD (master) table?
- Check how the master table PK columns (or other Uniquely constrained
columns) have their data generated;
- Run a trace against the client session showing waits (and binds too,
might be useful) and analyse the trace file.
- Don't leave uncommitted transactions lying around!
- V$SESSION_WAIT.
- BLOCKERS & WAITERS scripts. (Or in Toad!)
- Toad's session browser, current statement, Waits tab. See what the
client session is actually waiting on.
- Etc.
Good hunting.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
