Database Reference
In-Depth Information
But if we go into another session and attempt to delete the first parent record, we'll find that session gets
immediately blocked.
EODA@ORA12CR1> delete from p where x = 1;
It is attempting to gain a full table lock on table
C
before it does the delete. Now no other session can initiate a
DELETE
,
INSERT
, or
UPDATE
of any rows in
C
(the sessions that had already started may continue, but no new sessions
may start to modify
C
).
This blocking would happen with an update of the primary key value as well. Because updating a primary key is
a huge no-no in a relational database, this is generally not an issue with updates. However, I have seen this updating
of the primary key become a serious issue when developers use tools that generate SQL for them, and those tools
update every single column, regardless of whether the end user actually modified that column or not. For example,
say that we use Oracle Forms and create a default layout on any table. Oracle Forms by default will generate an update
that modifies every single column in the table we choose to display. If we build a default layout on the
DEPT
table and
include all three fields, Oracle Forms will execute the following command whenever we modify any of the columns of
the
DEPT
table:
update dept set deptno=:1,dname=:2,loc=:3 where rowid=:4
In this case, if the
EMP
table has a foreign key to
DEPT
and there is no index on the
DEPTNO
column in the
EMP
table,
then the entire
EMP
table will be locked during an update to
DEPT
. This is something to watch out for carefully if you are
using any tools that generate SQL for you. Even though the value of the primary key does not change, the child table
EMP
will be locked after the execution of the preceding SQL statement. In the case of Oracle Forms, the solution is to
set that table's
UPDATE CHANGED COLUMNS ONLY
property to
YES
. Oracle Forms will generate an
UPDATE
statement that
includes only the changed columns (not the primary key).
Problems arising from deletion of a row in a parent table are far more common. As I demonstrated, if I delete a
row in table
P
, then the child table,
C
, will become locked during the DML operation, thus preventing other updates
against
C
from taking place for the duration of the transaction (assuming no one else was modifying
C
, of course; in
which case the delete will wait). This is where the blocking and deadlock issues come in. By locking the entire table
C
,
I have seriously decreased the concurrency in my database to the point where no one will be able to modify anything
in
C
. In addition, I have increased the probability of a deadlock, since I now own lots of data until I commit. The
probability that some other session will become blocked on
C
is now much higher; any session that tries to modify
C
will get blocked. Therefore, I'll start seeing lots of sessions that hold some preexisting locks on other resources getting
blocked in the database. If any of these blocked sessions are, in fact, locking a resource that my session also needs, we
will have a deadlock. The deadlock in this case is caused by my session preventing access to many more resources
(in this case, all of the rows in a single table) than it ever needed. When someone complains of deadlocks in the database,
I have them run a script that finds unindexed foreign keys; 99 percent of the time we locate an offending table. By simply
indexing that foreign key, the deadlocks—and lots of other contention issues—go away. The following example
demonstrates the use of this script to locate the unindexed foreign key in table
C
:
EODA@ORA12CR1> column columns format a30 word_wrapped
EODA@ORA12CR1> column table_name format a15 word_wrapped
EODA@ORA12CR1> column constraint_name format a15 word_wrapped
EODA@ORA12CR1> select table_name, constraint_name,
2 cname1 || nvl2(cname2,','||cname2,null) ||
3 nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
4 nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
5 nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
6 columns
