Home » RDBMS Server » Performance Tuning » enq: TM - contention (11.2.0.3)
| enq: TM - contention [message #611796] |
Wed, 09 April 2014 01:48  |
 |
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  |
John Watson
Messages: 8965
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 #611804 is a reply to message #611798] |
Wed, 09 April 2014 02:36 |
|
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 #611808 is a reply to message #611807] |
Wed, 09 April 2014 02:50 |
John Watson
Messages: 8965
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  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 #611812 is a reply to message #611811] |
Wed, 09 April 2014 03:44 |
|
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Feb 17 02:06:27 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
This is even more alarming,