Home » RDBMS Server » Performance Tuning » enq: TM - contention (11.2.0.3)
enq: TM - contention [message #611796] Wed, 09 April 2014 01:48 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hello Experts,

Please explain the reason behind enq: TM - contention lock

I understand its because of un-indexed foreign key. But please explain the logic behind it.

For e.g.
Consider our traditional Scott schema with DEPT and EMP table.

DEPT table as the PARENT table with DEPTNO column as PK, EMP as the CHILD with EMPNO as PK and DEPTNO as FK(un-indexed).

Session-1

SQL> Insert into dept values(12,'TEST','TEST');

1 row created.


Session-2

SQL> delete from dept where deptno in (10);



Session-2 will wait with enq: TM - contention ,

SQL> select sid,event from v$session where username='SCOTT';

       SID EVENT
---------- -----------------------------------------------------
        73 SQL*Net message from client
       191 SQL*Net message from client
       193 enq: TM - contention



So what i don't understand in this is while INSERTING record in DEPT table session will hold a Full table lock on EMP table due to un-indexed foreign key
logically Why is it even required to look at child table because if something doesn't exist in Parent table it won't exist in child table?

Please let me know what am i missing?

Regards


Re: enq: TM - contention [message #611798 is a reply to message #611796] Wed, 09 April 2014 01:53 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I do not have your result:
orclz> delete from dept where deptno in (10);
delete from dept where deptno in (10)
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found


orclz> delete from dept where deptno in (40);

1 row deleted.

orclz>
orclz>
orclz>
orclz>
orclz> select * from v$version;

BANNER
-------------------------------------------------------------------------------- --
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

orclz>
Re: enq: TM - contention [message #611803 is a reply to message #611796] Wed, 09 April 2014 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Please let me know what am i missing?


Post content of v$lock (remove background sessions from the output).

Re: enq: TM - contention [message #611804 is a reply to message #611798] Wed, 09 April 2014 02:36 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hi John,

Try with below code

Session-1
Added a new row in DEPT table with deptno=11

SQL> Insert into dept values(11,'TEST','TEST');

1 row created.

SQL> commit;




Commit complete.

SQL> Insert into dept values(12,'TEST','TEST');

1 row created.


Session-2

SQL> delete from dept where deptno in (11);



Constraints Details
SELECT cons_col.OWNER,
  cons_col.CONSTRAINT_NAME,
  Cons_Col.Table_Name,
  cons_col.COLUMN_NAME,
  cons.constraint_type,
  cons.DELETE_RULE,
  cons.R_CONSTRAINT_NAME
FROM all_constraints cons,
  all_cons_columns cons_col
WHERE Cons.Owner            ='SCOTT'
AND cons.table_name        IN ('EMP','DEPT')
AND cons.table_name         =cons_col.table_name
AND cons_col.owner          =cons.owner
AND cons_col.CONSTRAINT_NAME=cons.CONSTRAINT_NAME;

OWNER  CONSTRAINT_NAME  TABLE_NAME COLUMN_NAME CONSTRAINT_TYPE DELETE_RULE R_CONSTRAINT_NAME
SCOTT	PK_EMP	         EMP	    EMPNO	  P		 NULL           NULL
SCOTT	PK_DEPT	         DEPT	    DEPTNO	  P		 NULL           NULL
SCOTT	FK_DEPTNO	 EMP	    DEPTNO	  R	         NO ACTION	PK_DEPT


V$LOCK Content

ADDR                     KADDR                  SID    TYPE     ID1    ID2    LMODE  REQUEST   CTIME  BLOCK
000007FF62CD6968	000007FF62CD69C0	191	AE	100	0	4	0	7006	0
000007FF62CD6A48	000007FF62CD6AA0	193	AE	100	0	4	0	7314	0
000007FF62CD7E98	000007FF62CD7EF0	193	TO	68064	1	3	0	7070	0
00000000231BF388	00000000231BF3E8	191	TM	75333	0	3	0	1047	0
00000000231BF388	00000000231BF3E8	191	TM	75335	0	3	0	1047	1
00000000231BF388	00000000231BF3E8	193	TM	75333	0	3	0	1040	0
00000000231BF388	00000000231BF3E8	193	TM	75335	0	0	4	1040	0
000007FF5FAB3B80	000007FF5FAB3BF8	191	TX	458783	3702	6	0	1047	0


SQL> select sid,event from v$session where username='SCOTT' and  sid in (191,193);

       SID EVENT
---------- ----------------------------------------------------------------
       191 SQL*Net message to client
       193 enq: TM - contention


[Updated on: Wed, 09 April 2014 02:40]

Report message to a moderator

Re: enq: TM - contention [message #611805 is a reply to message #611804] Wed, 09 April 2014 02:43 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your second example is nothing like your first example. It will cause the second session to hang on a TX enqueue.
Re: enq: TM - contention [message #611806 is a reply to message #611804] Wed, 09 April 2014 02:46 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You have changed your post. Hugely! If you do that, please say so.
Re: enq: TM - contention [message #611807 is a reply to message #611805] Wed, 09 April 2014 02:46 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
John Sir, As per my understanding it can't be TX enqueue because in Session-1 I have inserted deptno=11 in dept table and commited.
In session-2 I'm trying to delete a commited valued 11 but Oracle is waiting to get hold of object and its waiting with enq TM contention.
Re: enq: TM - contention [message #611808 is a reply to message #611807] Wed, 09 April 2014 02:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I do not know what you are doing. First session:
orclz> Insert into dept values(11,'TEST','TEST');

1 row created.

orclz> commit;

Commit complete.

orclz> Insert into dept values(12,'TEST','TEST');

1 row created.

orclz>

Second session:
orclz>  delete from dept where deptno in (11);

1 row deleted.

orclz> select sid,event from v$session where username='SCOTT';

       SID EVENT
---------- ----------------------------------------------------------------
       257 SQL*Net message to client
       264 SQL*Net message from client

orclz>



--udate: this time I edited my post Sad I copy/pasted the same thing twice. Sorry about that.

[Updated on: Wed, 09 April 2014 02:53]

Report message to a moderator

Re: enq: TM - contention [message #611811 is a reply to message #611804] Wed, 09 April 2014 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Which object are object id 75333 and 75335?
And when you post a query result it should be great you order the rows, for instance for v$lock by sid, ctime, type, lmode, request.

Re: enq: TM - contention [message #611812 is a reply to message #611811] Wed, 09 April 2014 03:44 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
@Michel

75333 = DEPT
75335 = EMP

@John,

I think referential integrity is missing in your environment.Please check with below data


---DEPT TABLE
CREATE TABLE DEPT
  (
    DEPTNO NUMBER(2,0),
    DNAME  VARCHAR2(14),
    LOC    VARCHAR2(13),
    CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
  );
--EMP TABLE
CREATE TABLE EMP
  (
    EMPNO    NUMBER(4,0),
    ENAME    VARCHAR2(10),
    JOB      VARCHAR2(9),
    MGR      NUMBER(4,0),
    HIREDATE DATE,
    SAL      NUMBER(7,2),
    COMM     NUMBER(7,2),
    DEPTNO   NUMBER(2,0),
    CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
    CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ENABLE
  ) ;
--DATA FOR DEPT TABLE
INSERT INTO dept VALUES
  (10, 'ACCOUNTING', 'NEW YORK'
  );
INSERT INTO dept VALUES
  (20, 'RESEARCH', 'DALLAS'
  );
INSERT INTO dept VALUES
  (30, 'SALES', 'CHICAGO'
  );
INSERT INTO dept VALUES
  (40, 'OPERATIONS', 'BOSTON'
  );
--DATA FOR EMP TABLE
INSERT
INTO emp VALUES
  (
    7839,
    'KING',
    'PRESIDENT',
    NULL,
    to_date('17-11-1981','dd-mm-yyyy'),
    5000,
    NULL,
    10
  );
INSERT
INTO emp VALUES
  (
    7698,
    'BLAKE',
    'MANAGER',
    7839,
    to_date('1-5-1981','dd-mm-yyyy'),
    2850,
    NULL,
    30
  );
INSERT
INTO emp VALUES
  (
    7782,
    'CLARK',
    'MANAGER',
    7839,
    to_date('9-6-1981','dd-mm-yyyy'),
    2450,
    NULL,
    10
  );
INSERT
INTO emp VALUES
  (
    7566,
    'JONES',
    'MANAGER',
    7839,
    to_date('2-4-1981','dd-mm-yyyy'),
    2975,
    NULL,
    20
  );
INSERT
INTO emp VALUES
  (
    7788,
    'SCOTT',
    'ANALYST',
    7566,
    to_date('13-JUL-87','dd-mm-rr') ,
    3000,
    NULL,
    20
  );
INSERT
INTO emp VALUES
  (
    7902,
    'FORD',
    'ANALYST',
    7566,
    to_date('3-12-1981','dd-mm-yyyy'),
    3000,
    NULL,
    20
  );
INSERT
INTO emp VALUES
  (
    7369,
    'SMITH',
    'CLERK',
    7902,
    to_date('17-12-1980','dd-mm-yyyy'),
    800,
    NULL,
    20
  );
INSERT
INTO emp VALUES
  (
    7499,
    'ALLEN',
    'SALESMAN',
    7698,
    to_date('20-2-1981','dd-mm-yyyy'),
    1600,
    300,
    30
  );
INSERT
INTO emp VALUES
  (
    7521,
    'WARD',
    'SALESMAN',
    7698,
    to_date('22-2-1981','dd-mm-yyyy'),
    1250,
    500,
    30
  );
INSERT
INTO emp VALUES
  (
    7654,
    'MARTIN',
    'SALESMAN',
    7698,
    to_date('28-9-1981','dd-mm-yyyy'),
    1250,
    1400,
    30
  );
INSERT
INTO emp VALUES
  (
    7844,
    'TURNER',
    'SALESMAN',
    7698,
    to_date('8-9-1981','dd-mm-yyyy'),
    1500,
    0,
    30
  );
INSERT
INTO emp VALUES
  (
    7876,
    'ADAMS',
    'CLERK',
    7788,
    to_date('13-JUL-87', 'dd-mm-rr'),
    1100,
    NULL,
    20
  );
INSERT
INTO emp VALUES
  (
    7900,
    'JAMES',
    'CLERK',
    7698,
    to_date('3-12-1981','dd-mm-yyyy'),
    950,
    NULL,
    30
  );
INSERT
INTO emp VALUES
  (
    7934,
    'MILLER',
    'CLERK',
    7782,
    to_date('23-1-1982','dd-mm-yyyy'),
    1300,
    NULL,
    10
  );
COMMIT;


Now try with Session-1 and Session-2, I'm sure now you'll replicate the issue.

Regards

Re: enq: TM - contention [message #611813 is a reply to message #611812] Wed, 09 April 2014 03:54 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
No, man, the problem is yours. I re-create my SCOTT schema many times every day, using the utlsampl.sql script. Absolutely standard.
Re: enq: TM - contention [message #611824 is a reply to message #611813] Wed, 09 April 2014 04:45 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
John , Smile This is even more alarming,

I used the utlsampl.sql to create the DEPT and EMP table , and I'm unable to repro the issue.

Sad, Please advise.
Re: enq: TM - contention [message #611826 is a reply to message #611824] Wed, 09 April 2014 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Now that you have no more the initial state we can no more investigate.

Re: enq: TM - contention [message #611836 is a reply to message #611826] Wed, 09 April 2014 05:44 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Michel,

With the table data that i provided in the above post, we can repro the scenario.
With utlsampl.sql may be something is missing.

But my actual question was logic behind enq: TM Contenetion Quote:
what i don't understand in this is while INSERTING record in DEPT table session will hold a Full table lock on EMP table due to un-indexed foreign key logically Why is it even required to look at child table because if something doesn't exist in Parent table it won't exist in child table?


Regards
Re: enq: TM - contention [message #611838 is a reply to message #611836] Wed, 09 April 2014 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

To prevent from any insert of an employee in this department maybe.

Re: enq: TM - contention [message #611839 is a reply to message #611838] Wed, 09 April 2014 05:53 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

...or rather move (update) to this department.
Previous Topic: DATA BASE IS WORKING VERY SLOW
Next Topic: Its confusing me lot....
Goto Forum:
  


Current Time: Thu Mar 28 06:47:42 CDT 2024