Blocked Merges, Updates, and Deletes- Locking and Latching-2

These full table locks are a short-term occurrence in Oracle 9i and above, meaning they need to be taken for the duration of the DML operation, not the entire transaction. Even so, they can and do cause large locking issues. As a demonstration of the first point, if we have a pair of tables set up as follows, nothing untoward happens yet:

$ sqlplus eoda/foo@PDB1
SQL> create table p ( x int primary key ); Table created.
SQL> create table c ( x references p ); Table created.
SQL> insert into p values ( 1 );1 row created.
SQL> insert into p values ( 2 );1 row created.
SQL> commit;Commit complete.
SQL> insert into c values ( 2 );1 row created.

But if we go into another session and attempt to delete the first parent record, we’ll find that session gets immediately blocked:

SQL> 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:

SQL> column columns format a30 word_wrapped
SQL> column table_name format a15 word_wrapped
SQL> column constraint_name format a15 word_wrapped
SQL> select table_name, constraint_name,

cname1 || nvl2(cname2,’,’||cname2,null) || nvl2(cname3,’,’||cname3,null) || nvl2(cname4,’,’||cname4,null) || nvl2(cname5,’,’||cname5,null) || nvl2(cname6,’,’||cname6,null) || nvl2(cname7,’,’||cname7,null) || nvl2(cname8,’,’||cname8,null)
columns from ( select b.table_name,b.constraint_name,max(decode( position, 1, column_name, null )) cname1, max(decode( position, 2, column_name, null )) cname2, max(decode( position, 3, column_name, null )) cname3, max(decode( position, 4, column_name, null )) cname4, max(decode( position, 5, column_name, null )) cname5, max(decode( position, 6, column_name, null )) cname6, max(decode( position, 7, column_name, null )) cname7, max(decode( position, 8, column_name, null )) cname8, count() col_cnt from (select substr(table_name,1,30) table_name, substr(constraint_name,1,30) constraint_name, substr(column_name,1,30) column_name, position from user_cons_columns ) a,user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = ‘R’group by b.table_name, b.constraint_name ) cons where col_cnt > ALL ( select count() from user_ind_columns user_indexesui

where   i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
    cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
and ui.table_name = i.table_name
and ui.index_name = i.index_name
and ui.index_type IN ('NORMAL','NORMAL/REV')
group   by i.index_name);           

TABLE_NAME CONSTRAINT_NAME COLUMNSC SYS_C0061427 X