Thursday, April 09, 2015

enq: TM - contention

This example was tested in an Oracle 11.1 database. I created a DEPT (or parent) table, added a couple of departments then made it available to other database users:

SQL> conn andrew/reid

Connected.

SQL> create table dept

2 (dept_code varchar2(2),

3 dept_desc varchar2(10))

4 /

Table created.

SQL> insert into dept

2 (dept_code, dept_desc)

3 values

4 ('10','IT')

5 /

1 row created.

SQL> insert into dept

2 (dept_code, dept_desc)

3 values

4 ('20','Sales')

5 /

1 row created.

SQL> select * from dept

2 /

DEPT_CODE DEPT_DESC

---------- ----------

10 IT

20 Sales

SQL> grant all on dept to public

2 /

Grant succeeded.

SQL>

I created an EMP (or child) table and tried to cross-check the EMP_DEPT column against the DEPT_CODE column in the DEPT table. This failed with an ORA-02270 as DEPT_CODE in the DEPT table was not unique. This was quite understandable because, if I had joined the EMP and DEPT tables to get the description (DEPT_DESC)
for each employee’s department, I needed to be sure that only one value
would be returned. I have covered this before but decided it would do
no harm to repeat it here. I made the DEPT_CODE column unique in the DEPT table and the problem went away. I added two names to the EMP table with misspelt names (you will see why later). Then I made the table available to other database users:

SQL> create table emp

2 (emp_name varchar2(10),

3 emp_dept varchar2(2),

4 constraint fk1

5 foreign key(emp_dept)

6 references dept(dept_code))

7 /

references dept(dept_code))

*

ERROR at line 6:

ORA-02270: no matching unique or primary key for this

column-list

SQL> alter table dept

2 add constraint dept_no_pk

3 unique(dept_code)

4 /

Table altered.

SQL> create table emp

2 (emp_name varchar2(10),

3 emp_dept varchar2(2),

4 constraint fk1

5 foreign key(emp_dept)

6 references dept(dept_code))

7 /

Table created.

SQL> insert into andrew.emp

2 (emp_name, emp_dept)

3 values

4 ('Dayvid', '10')

5 /

1 row created.

SQL> insert into andrew.emp

2 (emp_name, emp_dept)

3 values

4 ('Edwood', '10')

5 /

1 row created.

SQL> grant all on emp to public

2 /

Grant succeeded.

SQL>

USERA logged into the database, noticed one of the spelling mistakes, corrected it but did not commit the change:

SQL> conn usera/usera

Connected.

SQL> select * from andrew.emp

2 /

EMP_NAME EMP_DEPT

---------- ----------

Dayvid 10

Edwood 10

SQL> update andrew.emp

2 set emp_name = 'David'

3 where emp_name = 'Dayvid'

4 /

1 row updated.

SQL>

USERB logged into the database and tried to change the DEPT_CODE for Sales from 20 to 30 but nothing appeared to happen:

SQL> conn userb/userb

Connected.

SQL> update andrew.dept

2 set dept_code = '30'

3 where dept_desc = 'Sales'

4 /

USERB reported the problem to a DBA who investigated it as follows:

SQL> conn / as sysdba

Connected.

SQL> select event, state from v$session

2 where username = 'USERB'

3 /

EVENT STATE

------------------------------ -------------------

enq: TM - contention WAITING

SQL>

This showed her that the problem was caused by a foreign key locking issue so she looked at the locks in Enterprise Manager:

This showed that the child table was EMP.

Eventually, USERA committed his change:

SQL> conn usera/usera

Connected.

SQL> select * from andrew.emp

2 /

EMP_NAME EMP_DEPT

---------- ----------

Dayvid 10

Edwood 10

SQL> update andrew.emp

2 set emp_name = 'David'

3 where emp_name = 'Dayvid'

4 /

1 row updated.

SQL> commit

2 /

Commit complete.

SQL>

And USERB’s transaction finished moments later so he was able to commit it too:

SQL> conn userb/userb

Connected.

SQL> update andrew.dept

2 set dept_code = '30'

3 where dept_desc = 'Sales'

4 /

1 row updated.

SQL> commit

2 /

Commit complete.

SQL>

The DBA looked to see how the EMP (child) table had been created. (I won’t go into how she knew Andrew’s password):

She noticed that the EMP_DEPT column was used for referential integrity checking against the DEPT
table. For this to work efficiently, there needs to be an index on this
column but Oracle does not enforce this requirement. The DBA looked for
indexes on the EMP table but did not find any:

SQL> l

1 select index_name, column_name, column_position

2 from dba_ind_columns

3 where table_owner = 'ANDREW'

4 and table_name = 'EMP'

5* order by 1,3

SQL> /

no rows selected

SQL>

She created the appropriate index:

SQL> l

1 create index andrew.emp_ind1

2* on andrew.emp(emp_dept)

SQL> /

Index created.

SQL>

USERA noticed the other spelling mistake, corrected it but did not commit the change:

SQL> conn usera/usera

Connected.

SQL> select * from andrew.emp

2 /

EMP_NAME EMP_DEPT

---------- ----------

David 10

Edwood 10

SQL> update andrew.emp

2 set emp_name = 'Edward'

3 where emp_name = 'Edwood'

4 /

1 row updated.

SQL>

USERB decided to change the DEPT_CODE for the Sales department to 40 and this time, the update worked immediately: