Home » SQL & PL/SQL » SQL & PL/SQL » Why, dml_locks disabled? (Oracle Database,10g,Windows)
icon4.gif  Why, dml_locks disabled? [message #291737] Sun, 06 January 2008 08:08 Go to next message
leo-fan.aq
Messages: 4
Registered: January 2008
Junior Member
Hello everyone, There is a question.
--------------------------------------------------
The parameter dml_locks disabled for "for update"?

SQL> show parameter dml_locks;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dml_locks integer 748

I create a table like this, and Insert 10000 rows data.
--------------------------------------------------
CREATE TABLE DML_LOCKS_TEST_A
(
A NUMBER(6),
B VARCHAR2(1)
)
--------------------------------------------------

And now, I use like this,
--------------------------------------------------
SELECT * FROM DML_LOCKS_TEST_A WHERE ROWNUM<3000 FOR UPDATE
--------------------------------------------------
It works.

no commit, and get a new session, do like this,
--------------------------------------------------
SELECT * FROM DML_LOCKS_TEST_A WHERE ROWNUM<6000 FOR UPDATE SKIP LOCKED
--------------------------------------------------
It works too.

My question is,
Why It works without error? Does The parameter dml_locks disabled for "for update"?

and another question is,
Why
--------------------------------------------------
SELECT * FROM DML_LOCKS_TEST_A WHERE ROWNUM<3000 ORDER BY A FOR UPDATE
--------------------------------------------------
...
1673 N
1674 N
1675 N
1676 N
1677 N
1678 N
1679 N

A B
------- -
1981 N
1982 N
1983 N
1984 N
1985 N
1986 N
1987 N
1988 N
...

1680-1980 lost?
but it fine until setted column A as Primary Key.

Thanks very much.
Re: Why, dml_locks disabled? [message #291738 is a reply to message #291737] Sun, 06 January 2008 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Why It works without error?

Why should there be an error?
In addition SKIP LOCKED is not supported until 11g.

Quote:

Does The parameter dml_locks disabled for "for update"?

It enables this statement as well as other DML.

Quote:

Why 1680-1980 lost?

There are not lost, there are just not selected.
Quote:

but it fine until setted column A as Primary Key.

It depends on the execution plan and/or luck.

Regards
Michel
Re: Why, dml_locks disabled? [message #291744 is a reply to message #291737] Sun, 06 January 2008 09:19 Go to previous messageGo to next message
leo-fan.aq
Messages: 4
Registered: January 2008
Junior Member
Thanks Michel Cadot,and

1 Does 3000 Row Exclusive Locks created by "SELECT * FROM DML_LOCKS_TEST_A WHERE ROWNUM<3000 FOR UPDATE"? It's bigger than the value of dml_locks, A error about locks lacking should come out, shouldn't it?

2 You said "There are not lost, there are just not selected."
but I wrote the SQL with "order by A", why 1680-1980 has not been selected, they are in the range of 1 to 3000 rows.
Thanks a lot.
Re: Why, dml_locks disabled? [message #291746 is a reply to message #291744] Sun, 06 January 2008 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. It is counted per DML statement not per row
2. rownum is set BEFORE order by, so you just order by the 3000 rows you selected and not order by the whole table and select 3000 rows.

Regards
Michel
Re: Why, dml_locks disabled? [message #291748 is a reply to message #291746] Sun, 06 January 2008 10:06 Go to previous messageGo to next message
leo-fan.aq
Messages: 4
Registered: January 2008
Junior Member
Aha, So that's how it is!
Thank you very much.
Re: Why, dml_locks disabled? [message #291751 is a reply to message #291748] Sun, 06 January 2008 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually it is one DML_LOCK per each table modified in a transaction.
A transaction can contain several DML statements on different tables and new INSERT ALL statement can modified several tables in one shot.

Regards
Michel


Re: Why, dml_locks disabled? [message #291752 is a reply to message #291737] Sun, 06 January 2008 10:17 Go to previous message
leo-fan.aq
Messages: 4
Registered: January 2008
Junior Member
I benefited greatly by talking with you.
Previous Topic: download sample schema
Next Topic: large data deletion
Goto Forum:
  


Current Time: Tue Dec 06 08:36:55 CST 2016

Total time taken to generate the page: 0.09340 seconds