by Ianvdl on 5/29/24, 2:48 PM with 62 comments
by chasil on 5/29/24, 5:07 PM
This problem is common.
"Obviously, Oracle considers deadlocks a self-induced error on part of the application and, for the most part, they are correct. Unlike in many other RDBMSs, deadlocks are so rare in Oracle they can be considered almost non-existent. Typically, you must come up with artificial conditions to get one.
"The number one cause of deadlocks in the Oracle database, in my experience, is un-indexed foreign keys. There are two cases where Oracle will place a full table lock on a child table after modification of the parent table: a) If I update the parent table’s primary key (a very rare occurrence if you follow the rules of relational databases that primary keys should be immutable), the child table will be locked in the absence of an index. b) If I delete a parent table row, the entire child table will be locked (in the absence of an index) as well...
"So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met: a) You do not delete from the parent table. b) You do not update the parent table’s unique/primary key value (watch for unintended updates to the primary key by tools! c) You do not join from the parent to the child (like DEPT to EMP). If you satisfy all three above, feel free to skip the index – it is not needed. If you do any of the above, be aware of the consequences. This is the one very rare time when Oracle tends to ‘over-lock’ data."
-Tom Kyte, Expert One-on-One Oracle First Edition, 2005.
by ltbarcly3 on 5/29/24, 4:53 PM
SELECT CONCAT('CREATE INDEX ', relname, '_', conname, '_ix ON ',
nspname, '.', relname, ' ',
regexp_replace(
regexp_replace(pg_get_constraintdef(pg_constraint.oid, true),
' REFERENCES.*$','',''),
'FOREIGN KEY ','',''),
';') AS query
FROM pg_constraint
JOIN pg_class
ON (conrelid = pg_class.oid)
JOIN pg_namespace
ON (relnamespace = pg_namespace.oid)
WHERE contype = 'f' AND
NOT EXISTS (
SELECT 1
FROM pg_index
WHERE indrelid = conrelid AND
conkey::int[] @> indkey::int[] AND
indkey::int[] @> conkey::int[]);by dagss on 5/29/24, 7:25 PM
That way a lot of SQL index creation -- something considered a black art by surprisingly many -- would just be prompted by test suite failures. If you don't have the right indices, your test fails. Simple.
In this case, have TestDeleteCustomer fail, realize you need to add index, 5 minutes later done and learned something. Would be so much easier to newcomers... instead of a giant footgun and obscure lore that only becomes evident after you have a oot of data in production.
Google Data Store does this, just assumes that _of course_ you did not look to do a full table scan. Works great. Not SQL, but no reason popular SQL DBs could not have an option to have query planners throw errors at certain points instead of always making a plan no matter how bad.
SQL has a reputation for a steep learning curve and I blame this single thing -- that you get a poor plan instead of an error -- a lot for it.
by hn_throwaway_99 on 5/29/24, 4:39 PM
I think it would be much better to create indexes for foreign keys by default, and then allow skipping index creation with something like a `NO INDEX` clause if explicitly desired.
by SoftTalker on 5/29/24, 4:48 PM
by mbb70 on 5/29/24, 4:58 PM
by mypalmike on 5/29/24, 4:38 PM
by EdSchouten on 5/29/24, 4:37 PM
by hakanderyal on 5/29/24, 7:54 PM
It's almost always needed, and it's easier to remove them if they somehow become a problem.
by 3pm on 5/30/24, 3:42 AM
by saisrirampur on 5/29/24, 4:42 PM