| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Bad theory
"Alexander Chupin" <chupin_at_hotmail.ru> wrote in message
news:98ln0a$cc1$1_at_soap.pipex.net...
> HI All and Sybrand Bakker,
>
> Many thanx for you attention, but it seems you
> not quite careful read my first letter.:(
>
> > Some people just shout 'bug' too often.
>
> I completely agree with you :-)
>
> > When you start the second session the first session has an exclusive
lock
on
> > table d because of your uncommitted insert.
>
> Hm, what do you mean when you say "exclusive lock on table d".
> I think you wanted to say ROW EXCLUSIVE mode!
>
> ok, I make run following statement
>
> insert into d values( 1, 1 );
>
> and in the dba_lock view I can see for this session
>
> 27 Transaction Exclusive None 65578 29406 3 Not Blocking
> 27 DML Row-X (SX) None 25756 0 3 Not
Blocking
>
> where is here exclusive lock for table?
>
> > Hence when it comes to foreign key checking, and as you didn't define an
> > index on your foreign key column, your second session will too try to
obtain
> > a table level exclusive lock on your table d. Evidently, it won't get
that,
> > and it will wait forever, as this is not a deadlock.
>
> My second session was not going make any changes of field "a"
> which is primary key for master table. Also at the end of my first
> letter I mentioned that this effect doesn't occur IF SIMPLE REMOVE
> TRIGGER FOR MASTER TABLE. Could you explain the fact?
>
> > You need to create an index on your foreing key column
> > and/or issue appriopate update commands
>
> Ok. But why I must not create this index if trigger
> for master table doesn't exists or disabled ;-)?
>
> > (as you probably are not aware, an update without select for update or
> > without a previous lock table in row share mode also results in a table
> > level exclusive lock)
>
> could you explain this thought more exactly with example
> (I mean the select * from dba_locks).
>
> WBR, Alexander Chupin
>
>
>
First of all : you are very quick to make an assertion like the one in the
first sentence, and to immediately discredit my explanation. This is quite
annoying. The trigger has nothing to do with, it doesn't do anything as in
all cases the select into :new.a is *NOT* executed.
So,
I started two sessions as you outlined, and a third to monitor whatever
locking was going on.
This are the results with my comments.
If you still don't believe me, the need to index foreign keys because of the
locking behavior is documented in the Oracle manuals, either in the concepts
manual or in the application developers guide.
SQL> prompt after insert on d, and update on m in second session after insert on d, and update on m in second session SQL> select * from dba_locks where session_id in (7,8) 2 /
SESSION_ID LOCK_TYPE MODE_HELD
---------- -------------------------- --------------------------------------
--
MODE_REQUESTED LOCK_ID1
---------------------------------------- -----------------------------------
-----
LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS
---------------------------------------- ------------ ----------------------
------------------
7 Transaction Exclusive
None 196608
938 169 Not Blocking
7 DML Row-X (SX)
None 16289
0 169 Blocking!!!!!!!!!!
8 DML None
Share 16289
0 129 Not Blocking
rem session 2 wants a share lock because
a integrity needs to be checked on table d
b an index on d(a) doesn't exist
SQL> prompt after rollback in first session
after rollback in first session
SQL> /
SESSION_ID LOCK_TYPE MODE_HELD
---------- -------------------------- --------------------------------------
--
MODE_REQUESTED LOCK_ID1
---------------------------------------- -----------------------------------
-----
LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS
---------------------------------------- ------------ ----------------------
------------------
8 Transaction Exclusive
None 262162
767 21 Not Blocking
8 DML Row-X (SX)
None 16269
0 21 Not Blocking
8 DML Share
None 16289
0 21 Not Blocking
SQL> prompt locks from session 1 have been released and session 2 obtained
the share lock
locks from session 1 have been released and session 2 obtained the share
lock
SQL> select name from sys.obj$ where id = 16269
2 /
select name from sys.obj$ where id = 16269
*
SQL> c/id/obj#
1* select name from sys.obj$ where obj# = 16269
SQL> /
NAME
SQL> prompt session has now obtained a row share lock on m
session has now obtained a row share lock on m
SQL> prompt HOWEVER lock on table d (16289) is a TABLE level lock
HOWEVER lock on table d (16289) is a TABLE level lock
SQL> prompt after creation of index on d(a)
after creation of index on d(a)
SQL> select * from dba_locks where session_id in (7,8)
2 /
SESSION_ID LOCK_TYPE MODE_HELD
---------- -------------------------- --------------------------------------
--
MODE_REQUESTED LOCK_ID1
---------------------------------------- -----------------------------------
-----
LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS
---------------------------------------- ------------ ----------------------
------------------
8 Transaction Exclusive
None 196627
937 52 Not Blocking
8 DML Row-X (SX)
None 16269
0 52 Not Blocking
SQL> prompt repeat of insert on d
repeat of insert on d
SQL> /
SESSION_ID LOCK_TYPE MODE_HELD
---------- -------------------------- --------------------------------------
--
MODE_REQUESTED LOCK_ID1
---------------------------------------- -----------------------------------
-----
LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS
---------------------------------------- ------------ ----------------------
------------------
7 Transaction Exclusive
None 262158
767 13 Not Blocking
7 DML Row-X (SX)
None 16289
0 13 Not Blocking
SQL> prompt repeat of update on m
repeat of update on m
SQL> /
SESSION_ID LOCK_TYPE MODE_HELD
---------- -------------------------- --------------------------------------
--
MODE_REQUESTED LOCK_ID1
---------------------------------------- -----------------------------------
-----
LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS
---------------------------------------- ------------ ----------------------
------------------
7 Transaction Exclusive
None 262158
767 43 Not Blocking
7 DML Row-X (SX)
None 16289
0 43 Not Blocking
8 Transaction Exclusive
None 327697
1163 15 Not Blocking
8 DML Row-X (SX)
None 16269
0 15 Not Blocking
SQL> spool off
Regards,
Sybrand Bakker, Oracle DBA Received on Tue Mar 13 2001 - 13:55:56 CST
 |
 |