Odi's astoundingly incomplete notes

New entries | Code

Table lock on delete

I just found out about a weird behaviour of Oracle: if you delete a parent record and the child's foreign key is not indexed, Oracle will use a Full Table Lock instead of a Row Lock! It's very easy to trigger deadlocks like this. So make damn sure that all your foreign keys are indexed, even if the tables have only two rows. It's something that's easy to forget. Otherwise transactions may contend for the same lock even if they touch a completely different set of records.

posted on 2009-06-10 17:07 UTC in Code | 1 comments | permalink
you are amazing! thanks for your note it has saved my day (more like a week... you know ;)