Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, parag jayant.

Asked: October 11, 2004 - 10:40 am UTC

Last updated: July 31, 2013 - 6:27 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

In your book 'Expert one-on-oracle' on page 119 you said that "(for details see dml_locks init.ora parameter defination in Oracle 8i server reference manual) it may set to 0. this does not mean that your database becomes read-only database (no-locks ) but rather that DDL is not permitted".

But I observed that I have set DML_LOCKS to 0 and I had given DDL command to create table command, found working. e.g.

19:17:06 ops$atlas@INFOD> conn system/manager@infod
Connected.
19:17:17 ops$atlas@INFOD> show parameters dml

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
dml_locks integer 0

19:17:37 ops$atlas@INFOD> conn atlas/atlas@infod
Connected.
19:18:21 ops$atlas@INFOD> create table parag1 as select * from dual;

Table created.

19:19:13 ops$atlas@INFOD> update parag1 set dummy='Y';

1 row updated.

19:19:28 ops$atlas@INFOD> COMMIT;

Commit complete.

Kindly tell me how is it possible ?

thanks & regards
pjp

and Tom said...


ops$tkyte@ORA9IR2> show parameter dml_locks

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dml_locks integer 0
ops$tkyte@ORA9IR2> create table tttt ( x int );

Table created.

ops$tkyte@ORA9IR2> alter table tttt add y int;
alter table tttt add y int
*
ERROR at line 1:
ORA-00062: DML full-table lock cannot be acquired; DML_LOCKS is 0



A case whereby I should have been "more clear" i suppose

A create table need not lock anything, there isn't anything "there" yet.

So, the create table "worked" -- since there wasn't anything needing a table lock. However, all other ddl after that would. You cannot alter it, you cannot drop it, you cannot "DDL" it.




Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent

parag jayant patankar, October 12, 2004 - 2:25 am UTC

Hi Tom,

Excellent response as usual

best regards
pjp

Pl explain more about locks

Parag Jayant Patankar, November 04, 2004 - 12:35 pm UTC

Dear Tom,

I am trying to understand concepts of locks for this reason from one SQL window I have given update for table and transaction I have not committed or rollback.

In another session I had given request for v$locked_object

21:48:29 atlas@ATP1P1> show user
USER is "SYSTEM"

21:48:32 atlas@ATP1P1> select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
34117B64 34117B74 5 TS 2 1 3 0 52717 0
2025DAD0 2025DB9C 7 TX 131118 907280 6 0 511 0
343D3E94 343D3EA8 7 TM 424355 0 3 0 511 0

For SID 7 one lock is Table Level Lock ( TM ) and another is ROW level lock ( TX ). I can understand that DML requires at least two locks one in table level lock and another is row level lock.

In v$lock what is ID1 value representing ? ( not clear from view defination of documentation ). For TM it is object no but for TX it is not.

21:48:13 atlas@ATP1P1> select * from dba_objects where object_id=131118;

no rows selected

21:52:34 atlas@ATP1P1> select * from dba_objects where object_id=424355;

OWNER OBJECT_NAME
------------------------------ ------------------------------------------
LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
ATLAS PARAG
04-NOV-04 2004-11-04:21:35:43 VALID N N N

My Second Question is why it is not showing data in dba_blockers and dba_waiters

21:57:42 atlas@ATP1P1> select * from dba_blockers;

no rows selected

22:02:44 atlas@ATP1P1> select * from dba_waiters;

no rows selected

Q3 : In utllockt.sql it is giving me an error for

set charwidth 17;

What is correct syntax for this ?

thanks for explaining

regards
pjp



Tom Kyte
November 05, 2004 - 2:43 pm UTC

do you have my book "Expert one on one Oracle"

the TX is not a row lock, TM is the "row lock" actually.

Consider:

ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select sid, type, id1, id2 from v$lock where sid = (select sid from v$mystat where rownum = 1);
 
no rows selected
 
<b>nothing in there, lets start a transaction but not really modify anything:</b>

ops$tkyte@ORA9IR2> exec dbms_output.put_line( dbms_transaction.local_transaction_id(true) )
5.41.388
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select sid, type, id1, id2 from v$lock where sid = (select sid from v$mystat where rownum = 1);
 
       SID TY        ID1        ID2
---------- -- ---------- ----------
        10 TX     327721        388

<b>now we have a transaction and a TX lock.  this is "us", our transaction.  Anyone that gets blocked on "me" (sid=10) -- will block on THIS lock which stays until we commit. 

Lets get a TM lock:</b>

 
ops$tkyte@ORA9IR2> update t set x = 0;
 
0 rows updated.
 
ops$tkyte@ORA9IR2> select sid, type, id1, id2 from v$lock where sid = (select sid from v$mystat where rownum = 1);
 
       SID TY        ID1        ID2
---------- -- ---------- ----------
        10 TX     327721        388
        10 TM      32625          0

<b>that tm just says "we did something to that object identified by ID1 (see below).  We may have some rows locked -- we don't, there are not any -- but we have that lock.
</b>
 
ops$tkyte@ORA9IR2> select object_id from user_objects where object_name = 'T';
 
 OBJECT_ID
----------
     32625

<b>and when type = TM, id1 = object id of locked object</b>

 

dml_locks restriction

Rob, November 04, 2006 - 12:28 pm UTC

I want to see, what will happen to the transactions that want to take TM lock after reaching max DML_LOCKS value. I have set DML_LOCKS to 20 and created 22 TM locks by running following DMLS on 2 different schema tables in 2 transactions:

insert into r10 values(1);
insert into r11 values(1);
insert into r12 values(1);
insert into r13 values(1);
insert into r14 values(1);
insert into r15 values(1);
insert into r16 values(1);
insert into r17 values(1);
insert into r18 values(1);
insert into r19 values(1);
insert into r20 values(1);

But everything went fine and I could see 22 TM locks in v$lock (lmode=3).

1. I was expecting some error or hang after reaching the DML_LOCKS max value. Why I didn't get this? How can I simulate this?

2. I saw all the lmode for TM were in mode 3. What other modes are possible for TM lock and what particular condition (I would appreciate if you can give me example for different TM Lmode).

Thanks,



Tom Kyte
November 04, 2006 - 2:44 pm UTC

1) you'd have to go back to 8i probably, in 9i and above, it is sort of a "soft limit" used by monitoring tools to show when you are near this "soft limit".  They are dynamically allocated in the SGA as needed (or as space is available to do so)

2) the "text" version is easier to read then the number:

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3097.htm#sthref2040

ops$tkyte%ORA9IR2> lock table t1 in share mode;

Table(s) Locked.

ops$tkyte%ORA9IR2> exec print_table( 'select * from dba_dml_locks' );
.SESSION_ID                   : 14
.OWNER                        : OPS$TKYTE
.NAME                         : T1
.MODE_HELD                    : Share
.MODE_REQUESTED               : None
.LAST_CONVERT                 : 0
.BLOCKING_OTHERS              : Not Blocking
-----------------

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> insert into t1 values (1);

1 row created.

ops$tkyte%ORA9IR2> exec print_table( 'select * from dba_dml_locks' );
.SESSION_ID                   : 14
.OWNER                        : OPS$TKYTE
.NAME                         : T1
.MODE_HELD                    : S/Row-X (SSX)
.MODE_REQUESTED               : None
.LAST_CONVERT                 : 0
.BLOCKING_OTHERS              : Not Blocking
-----------------

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> lock table t1 in exclusive mode;

Table(s) Locked.

ops$tkyte%ORA9IR2> exec print_table( 'select * from dba_dml_locks' );
.SESSION_ID                   : 14
.OWNER                        : OPS$TKYTE
.NAME                         : T1
.MODE_HELD                    : Exclusive
.MODE_REQUESTED               : None
.LAST_CONVERT                 : 0
.BLOCKING_OTHERS              : Not Blocking
-----------------

PL/SQL procedure successfully completed.



ops$tkyte%ORA9IR2> exec print_table( 'select * from v$lock where sid = (select sid from v$mystat where rownum = 1)' );
.ADDR                         : 5EB8F210
.KADDR                        : 5EB8F31C
.SID                          : 14
.TYPE                         : TX
.ID1                          : 327711
.ID2                          : 22722
.LMODE                        : 6
.REQUEST                      : 0
.CTIME                        : 36
.BLOCK                        : 0
-----------------
.ADDR                         : 5EB3A448
.KADDR                        : 5EB3A45C
.SID                          : 14
.TYPE                         : TM
.ID1                          : 89907
.ID2                          : 0
.LMODE                        : 6
.REQUEST                      : 0
.CTIME                        : 36
.BLOCK                        : 0
-----------------

PL/SQL procedure successfully completed.
 

Killing processes with Locks

Bob, June 04, 2007 - 6:42 am UTC

Hi Tom - I have a quick question for you: we have a script that we run that gives us locking information:

select
type || '-' || id1 || '-' || id2 "RESOURCE",
sid,
decode(
lmode,
1, ' N',
2, ' SS',
3, ' SX',
4, ' S',
5, ' SSX',
6, ' X'
) holding,
decode(
request,
1, ' N',
2, ' SS',
3, ' SX',
4, ' S',
5, ' SSX',
6, ' X'
) wanting,
ctime seconds
from
sys.v_$lock
order by
type || '-' || id1 || '-' || id2,
ctime desc;

From this we can find transactions waiting on a resource.So
we had a scenario with the following:

TX-589831-224645 48 X 35935
59 X 35802
55 X 35792
60 X 35761
33 X 35743
52 X 35440
43 X 35047
49 X 34605
54 X 34396
47 X 33349
25 X 32461
57 X 27245
32 X 27139

I was able to "home in" on SID 48 by querying:

select
sql_text
from
v$sqltext
where
address in (
select
sql_address
from
v$session
where
sid=&&V_SID
)
order by
piece;

This procedure was doing a simple insert statement into a table. If I kill this process SID:48 using alter system kill.. will this commit or rollback the transaction. I presume/hope it would be rollback'd. How can I find the root cause of why this table is locked in the future? Why could this process be INACTIVE in OEM. We are using OLTP, Java Front End JSP/OLTP.

Thanks!
Tom Kyte
June 05, 2007 - 8:38 am UTC

killing rolls back.


the table is not locked. Well, there will be a TX lock on the table to prevent DDL but the table is not locked.


Probably it has

a) inserted a row
b) into a table with a primary / unique key
c) someone else is trying to insert that same value in another session

that someone else will block on (a) until (a) commits (and then (c) will fail) or rollsback (and then (c) will succeed)

The root cause of this problem - the JAVA PROGRAM has a serious bug in it, a developer has started a transaction and is just leaving it hang there.

The session is inactive because the session isn't doing anything, sort of like a logged in sqlplus session will show "inactive" when sqlplus is waiting for you to type stuff in

shared locks

Bob, June 05, 2007 - 9:55 am UTC

Beautiful explanation!

Table lock?

A reader, July 17, 2013 - 6:56 pm UTC

DROP TABLE m;
DROP TABLE f;

CREATE TABLE f (
fID INTEGER NOT NULL PRIMARY KEY,
name VARCHAR2(10)
);

CREATE TABLE m (
mID INTEGER NOT NULL,
fID INTEGER NOT NULL,
CONSTRAINT m_f_fk FOREIGN KEY(fID) REFERENCES f(fID)
);


INSERT INTO f VALUES (1, 'Folder 1');
INSERT INTO f VALUES (2, 'Folder 2');

INSERT INTO m VALUES (1, 1);
COMMIT;


-- CREATE INDEX m_FK1 ON m(fID);

-- -----------------------------------------------------------------------------
-- SEssion #1
-- -----------------------------------------------------------------------------
DELETE FROM f
WHERE NOT EXISTS (
SELECT 1
FROM m
WHERE m.fID = f.fID
);

-- -----------------------------------------------------------------------------
-- SEssion #2
-- -----------------------------------------------------------------------------
INSERT INTO m (mID, fID) VALUES (2, 2);

-- hangs, waiting for session #1 to end transaction

Why does session #1 block session #2? Does the delete statement lock every single row of table m? If yes, why does it do that? It's deleting the record with fID = 1, which should not affect the insert, right?

Unindexed Foreign Key, but...

A reader, July 17, 2013 - 9:43 pm UTC

I'm actually aware of the potential problems that could be caused by unindexed foreign keys, but...

SQL> DROP TABLE m;

Table dropped.

SQL> DROP TABLE f;

Table dropped.

SQL> 
SQL> CREATE TABLE f (
  2     fID INTEGER NOT NULL PRIMARY KEY,
  3     name VARCHAR2(10)
  4  );

Table created.

SQL> 
SQL> CREATE TABLE m (
  2     mID INTEGER NOT NULL,
  3     fID INTEGER NOT NULL,
  4     CONSTRAINT m_f_fk FOREIGN KEY(fID) REFERENCES f(fID)
  5  );

Table created.

SQL> 
SQL> 
SQL> INSERT INTO f VALUES (1, 'Folder 1');

1 row created.

SQL> INSERT INTO f VALUES (2, 'Folder 2');

1 row created.

SQL> 
SQL> INSERT INTO m VALUES (1, 1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> 
SQL> 
SQL> CREATE INDEX m_FK1 ON m(fID);

Index created.

SQL> 
SQL> -- -----------------------------------------------------------------------------
SQL> -- SEssion #1
SQL> -- -----------------------------------------------------------------------------
SQL> DELETE FROM f
  2  WHERE  NOT EXISTS (
  3            SELECT 1
  4            FROM   m
  5            WHERE  m.fID = f.fID
  6         );

1 row deleted.


SQL> -- -----------------------------------------------------------------------------
SQL> -- SEssion #2
SQL> -- -----------------------------------------------------------------------------
SQL> INSERT INTO m (mID, fID) VALUES (2, 2);


hangs...doesn't seem to make a difference, hence my question.

Tom Kyte
July 18, 2013 - 3:32 pm UTC

if you were actually aware of the issue, why post a test case that had an unindexed foreign key and ask "why is it blocking"?

this has nothing to do with foreign keys or anything like that. this has only to do with the primary key constraint on the F table.


You have a primary key on F.fid.

In one session - you have deleted but not commited fid = 2.

In another session, after the delete, you attempt to insert fid = 2.

that insert is blocked at the row level by the delete - it cannot insert the fid=2 value yet because the other session could ROLL BACK. The fid=2 row is not yet deleted. The inserting session must wait for that transaction to either commit, upon which time it would be able to insert - or roll back - at which time it would receive a unique key violation.



A reader, July 18, 2013 - 8:24 pm UTC

Sorry Tom. Both time I posted the wrong test case. Note that this time the INSERT comes before DELETE. My questions are

* Why does the DELETE (session #2) wait for INSERT (session #1) to end its transaction whether the FK index is present or not?

* Why is the result of the DELETE different depending on the existence of the FK index?

DROP TABLE m;
DROP TABLE f;

CREATE TABLE f (
fID INTEGER NOT NULL PRIMARY KEY,
name VARCHAR2(10)
);

CREATE TABLE m (
mID INTEGER NOT NULL,
fID INTEGER NOT NULL,
CONSTRAINT m_f_fk FOREIGN KEY(fID) REFERENCES f(fID)
);


INSERT INTO f VALUES (1, 'Folder 1');
INSERT INTO f VALUES (2, 'Folder 2');

INSERT INTO m VALUES (1, 1);
COMMIT;

-- CREATE INDEX m_FK1 ON m(fID);

-- -----------------------------------------------------------------------------
-- SEssion #1
-- -----------------------------------------------------------------------------
INSERT INTO m (mID, fID) VALUES (2, 2);

-- -----------------------------------------------------------------------------
-- SEssion #2
-- -----------------------------------------------------------------------------
DELETE FROM f
WHERE NOT EXISTS (
SELECT 1
FROM m
WHERE m.fID = f.fID
);

-- hangs, waiting for session #1 to end the transaction
-- whether the FK index exists or not.

-- -----------------------------------------------------------------------------
-- SEssion #1
-- -----------------------------------------------------------------------------
COMMIT;

-- -----------------------------------------------------------------------------
-- SEssion #2
-- -----------------------------------------------------------------------------
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- If no FK index exists
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
0 rows deleted.

-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- If the FK index exists
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
ERROR at line 1:
ORA-02292: integrity constraint (PROTECT.M_F_FK) violated - child record found


Tom Kyte
July 19, 2013 - 1:06 pm UTC

think about it.


you have inserted a row with (mid,fid) = (2,2). So, you have a child row in M that needs the parent row "2" to exist.


Now, that is not committed so no one else can see it. But there is in fact a row that NEEDS the parent = 2 to exist.


Now, in another session you come along and you try to delete all parent records that have no children. Well - guess what. according to this sessions view of the database - parent = 2 should be deleted. there is after all NO child =2 record from his perspective.

but you know and I know and the database knows there is one pending. IF we allowed this second session to delete it and commit - what would happen to the first session, it already validated parent=2 was there.

or what would happen if we let this session delete everything but parent=2? then we just did dirty inconsistent reads and corrupted your data.


So we do the only logical thing. We wait to see what the outcome of session 1 is. If they commit - we restart our delete, get a consistent set of rows and delete them (see
http://asktom.oracle.com/Misc/something-different-part-i-of-iii.html
http://asktom.oracle.com/Misc/part-ii-seeing-restart.html
http://asktom.oracle.com/Misc/part-iii-why-is-restart-important-to.html
for details on what a restart is)

if they rollback - we just continue deleting.



A reader, July 22, 2013 - 9:43 pm UTC

I went through all three of your articles (on restart). It makes sense. However, there is still one thing that still baffles me. If you look at the latest example above (July 18, 2013), the DELETE in session #2 returns different results based on whether there is an index on the foreign key. When there is no foreign key index, it seems like Oracle does a restart and deletes no record. When the index is present, the delete ends up with a "ORA-02292: integrity constraint (PROTECT.M_F_FK) violated - child record found" error. Can you please explain why Oracle would behave differently?
Tom Kyte
July 31, 2013 - 6:27 pm UTC

it has something to do with the locking on the index on the foreign key - even if I make the plans for the delete identical - a hash join anti - the same results are observed.

The delete against the unindexed child table succeeds, it sees the insert, it restarted.

the delete against the indexed child table fails, it does not see the insert, it did not restart and reset it's SCN.

the plan used has nothing to do with it (I forced them to both use the "successful" plan). The locking logic against the index - so we row level lock instead of table level lock - is doing this.

it goes something like this:

with the unindexed foreign key - we

a) lock the table
b) do the delete - delete is done "as of now"

with the indexed foreign key - we

a) do the delete as of right now

b) get blocked, we are blocked on a row we "cannot see" right now - but we cannot proceed further - we have to await the outcome of this situation. if this row we cannot see goes away (rollback) we'll be dandy and can continue.

c) however when we get unblocked - we can see the row that was blocking us that we could not see is there now - we could see it if we were to query right now. However, it was not there when the delete started. We know this delete cannot just ignore this entry, but this delete cannot see this entry either. Hence, this delete must start over