Skip to Main Content
  • Questions
  • row level transaction lock, table level shared lock, table level exclusive lock

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Sam.

Asked: December 15, 2001 - 5:52 pm UTC

Last updated: November 14, 2012 - 8:19 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom

I was reading your book.

From what I understood on locks

When I give a select for Update, to update certain rows.

1.A row level exclusive lock is put on the rows I want to update.
2.A table level shared lock is put on the table.
3.when I actually issue the update statement the 2 will become an table level exclusive lock.


From the above I acan understand that

in 1.(TX) other rows apart from the ones we have locked are available for updates from other sessions.

in 2 .(TM) no changes can be either to the table structure or the objects which the table refers to i.e. referenced objects( does referenced objects mean the the tables on which we have the foreign keys etc).


and what is the 3rd kind of lock i.e. table level exclusive lock.


Thank you






and Tom said...

The 3rd kind of lock is a slightly more restrictive lock then the one from #1.

In #1, we got a row share lock on the table.
In #3, we got a row exclusive lock on the table.

In #1, someone else could get a table level share or share row exclusive lock on the table... That is, after we select for update, someone else can come along and lock table in share mode if they like.

In #3, we can no longer get the table level share or share row exclusive lock.


You may want to refer to
</code> http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76965/c23cnsis.htm#2937 <code>

for a handy table ( i tried not to replicate that which was easily obtainable in the core oracle documention in my book) that details this stuff...


Rating

  (43 ratings)

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

Comments

?

Kulguru, December 15, 2001 - 10:12 pm UTC

"That is, after we select for update, someone else can come along and lock table in share mode if they like."

Lets say I lock 10 rows for update, by issuing a select for update on those rows.

in my context what do you mean by saying that someone else can come along and lock table in share mode if they like.

Do you mean that they can lock the rows(for update or delete) which I have selected for update.

and if I actually issued an update statement , then they cannot do the above.

Thank you


Tom Kyte
December 16, 2001 - 9:52 am UTC

do this, fire up two sqlplus sessions as scott/tiger and execute the following:


session1 session2 commit
---------- ------------ --------------
select * from
emp for update; reserves the rows in emp for
us, no one can update these
rows

lock table emp in
share mode; succeeds. the table is locked
in share mode. No other
session is allowed to MODIFY
the contents of the EMP table.

update emp
set sal = sal; BLOCKS. the share mode table
lock prevents the promotion
of our lock to the type needed
when doing an update.

commit; releases the update.

commit; locks all gone.


Now, try it out with different scenarios (do an update instead of select for update for example) and observe the behavior.




how to lock table emp in share mode in session 2

Kulguru, December 16, 2001 - 10:30 am UTC

Tom, how can we lock the table in share mode in session 2, after selecting all the rows in emp for update from session 1.

What kind dml statement would do that.


Btw , I tried the following, and Iam surprised that from the same session Iam able to update the structure of the table after giving the update statement before committing the update.

scott@ORAI817.US.ORACLE.COM>select * from emp for update;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1000 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1000 500 30
7566 JONES MANAGER 7839 02-APR-81 1000 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1000 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 1000 30
7782 CLARK MANAGER 7839 09-JUN-81 1000 10
7788 SCOTT ANALYST 7566 19-APR-87 1000 20
7839 KING PRESIDENT 17-NOV-81 1000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1000 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1000 20
7900 JAMES CLERK 7698 03-DEC-81 1000 30
7902 FORD ANALYST 7566 03-DEC-81 1000 20
7934 MILLER CLERK 7782 23-JAN-82 1000 10

14 rows selected.

scott@ORAI817.US.ORACLE.COM>alter table emp add (demo varchar2(20));

Table altered.

scott@ORAI817.US.ORACLE.COM>update emp set sal =1000;

14 rows updated.

scott@ORAI817.US.ORACLE.COM>alter table emp add (demo1 varchar2(20));

Table altered.


And surprise-o-rama

I can even alter the table structure from my second session(connected again as scott)

scott@ORAI817.US.ORACLE.COM>alter table emp add( demo2 varchar2(20));

Table altered.


Why is this happening.

In my opinion once a session locks rows update or gives the actual update statement, we should not be able to change the structure of the table, as there will be a table level share lock.




Tom Kyte
December 16, 2001 - 11:37 am UTC

See the example right above you for HOW to lock it.

See the link in the comment above for the table that shows what kind of locks exist with other kinds of locks.

Opinions sort of don't count here (not being nasty, just stating fact).  


You did something funky in your example.  I started two sessions, did an update in session1 and in session2, I get:

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table emp add ( y int );
alter table emp add ( y int )
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


I believe you forgot that the ALTER you did right after the update committed the update.  The update was done, committed, finished -- before you did the alter in the other session. 

Follow up

Kulguru, December 16, 2001 - 12:56 pm UTC

Tom

I went through the document and

1.select ... for update
2.row share lock on rows selected by 1
3.Table level row share lock

4.Actual update given
5. 2 will change to row exclusive lock
6.3 will change into table level row exclusive lock

Is the above right?

and

from your example you gave earlier

Tom, how can we lock the table in share mode in session 2, after selecting all
the rows in emp for update from session 1.

What kind dml statement would do that.


Tom Kyte
December 16, 2001 - 1:30 pm UTC

You can test it out for yourself -- just TRY it. Use v$lock to see what happens. Use the table in the URL to the concepts manual -- you can see what happens with each statement.

Please, read the example above -- I gave you the EXACT sql in the form of "lock table in share mode". Everything you need to do the example is typed in exactly as you should type it.

Select for update locking

reader, February 07, 2003 - 4:54 pm UTC

Tom,
In your book on page 112, you say :
'So, as soon as the command to actually update that row is issued, Oracle will convert the ROW SHARE TABLE lock into the more restrictive ROW EXCLUSIVE TABLE and the modification will proceed.'

My question is when does the later lock gets released?

Thank you so much.

Tom Kyte
February 07, 2003 - 5:11 pm UTC

when you commit or rollback.

Blocking situation

reader, February 10, 2003 - 11:25 am UTC

I am trying to resolve a blocking or locking situation in our batch cycle. And I am wondering if ROW EXCLUSIVE TALBE lock is the cause that application is locking out other transactions.

Could this be the case?

Thank you.

Tom Kyte
February 11, 2003 - 7:39 am UTC

sure it could be, is it? i don't know. look for the blockers and waiters and look at what the waiters are waiting for what look at what the blockers have been doing.

Blockers/waiters after same table

reader, February 11, 2003 - 10:30 am UTC

Tom,
From what I am told, I understand that no to transaction/process will go after the same row. I have looked at the tkprof/statspack reports and the select for update query is using bind variables, implying that it is going after diferent rows in the same table-hopefully.

I just want to confirm my understanding, that ROW EXCLUSIVE TABLE lock will not create a lock situation as long as processes are trying to access different row/s in the same table?

thanks for your time.

Tom Kyte
February 11, 2003 - 4:48 pm UTC

a "blocking" situation -- by definition it causes a lock.

Try to have the application put something useful in v$session with dbms_application_info -- the client_info, application and module columns -- something like, the bind variable values is useful.


Also -- look for unindexed foreign keys! And then index them IF you

update the parent primary key
delete from the parent

why does it lock table in exclusive mode

A reader, August 04, 2003 - 1:51 am UTC

Hi Tom,
You said
In #3, we can no longer get the table level share or share row exclusive lock.
Why can't we get the table level share lock when doing the update


Tom Kyte
August 04, 2003 - 8:46 am UTC

that is the rule -- that is why.

if you have that type of lock (row exclusive), no one else can get those other lock types. the concepts guide has a table of lock type and what other lock types are permitted with it

Hi Tom

A reader, October 08, 2003 - 5:34 pm UTC

I have a situation where I want to lock a record in a table such that a different user or a different session should not be able to acess the record through a SELECT statement.

I am trying to find out a method such that the SQL should read past the locked record and proceed to rest of the rows.

thanks


Tom Kyte
October 09, 2003 - 3:53 pm UTC



Not really the way this particular database works.

How about you tell us your "goal", what your application is trying to accomplish. Then we might be able to tell you "how to do that"

Sounds like you might be trying to build a "queue" where by processes each get their own records to "process"?

Hi tom

A reader, October 09, 2003 - 4:33 pm UTC

Our goal is that a user selects data from a single table based on the value of the primary key. This SQL joins to 3 other tables. After successful execution of the query a record is inserted into a temp table.
During the phase of selection and inserting if another user issues a same query (this query is within a Package)then he should not be able to access the value what the other user has accessed for.
Right now, we have used the DBMS_Lock package, this works but there is a drawback.
If the SQL statement takes 45 seconds to execute and insert a record into the temp table, and if the second user executes almost at the same time, he has to wait for almost 45 seconds and then another 45 seconds for his query to execute (total 90 seconds), third user 135 seconds. This is causing us a big problem as the number of users in our system is around 20.

I heard in SQL Server there is a feature of reading past the locked record. I am trying something similar in Oracle 9.2.0.1.

Thanks



Tom Kyte
October 09, 2003 - 7:02 pm UTC

where do these primary keys come from? not understanding the processing here.

If two sessions have the same primary key -- what then?

give more details.

Hi

A reader, October 21, 2003 - 6:46 pm UTC

I am sorry if I had not conveyed things properly. However, I have found in Oracle code tips about "SKIP LOCK" feature that the other user goes past the locked record and selects the remaining. SKIP LOCK feature was very helpful in my case.

thanks

Tom Kyte
October 21, 2003 - 9:48 pm UTC

skip lock - undocumented
skip lock - doesn't work the way everything thinks "it should"

skip lock - something I WOULD NEVER USE in any program i ever developed (seriously, not just "saying" that - i mean it)

skip lock -- something used by AQ internally and only works in the way they designed it to work.

skip lock - defines a system that is doing something it really shouldn't and will have MAJOR upgrade/support issues in the future

dont go there -- really.

good explanation

Raja, October 22, 2003 - 4:01 pm UTC


Holding a Lock Through DDL

John Gilmore, December 09, 2003 - 6:54 am UTC

Hi Tom,

I want to load some data into a table which has triggers on it.

Due to the nature of what the triggers do, it is necessary to disable them during the data load. However, I also need to ensure that users do not insert or update data in this table while the triggers are disabled.

I can take an exclusive lock on the table; however, when I issue the "alter table" statement to disable the triggers the lock is released. If I disable the triggers before taking the lock then there is always a chance that a user transaction can sneak in between these two operations.

Can you see any way around this problem?

Tom Kyte
December 09, 2003 - 7:09 am UTC

why not code the trigger as:


begin
   if ( NOT pkg_owned_by_loader.I_am_the_loader )
   then 
       trigger code
   end if;
end;
/


and then in the loader session:

exec pkg_ownerd_by_loader.i_am_the_loader := true;
do the load


secure the package so that only the loader schema can set the variable.  that is one approach.  that way -- the triggers stay enabled forever (no chance of the load failing and leaving the triggers "disabled" which you have big time now)


alternatively, an autonomous transaction can be used to disable the trigger without committing the lock:

ops$tkyte@ORA10G> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create or replace trigger t_trigger
  2  before insert on t for each row
  3  begin
  4     :new.x := 1;
  5  end;
  6  /
 
Trigger created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> lock table t in exclusive mode;
 
Table(s) Locked.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> declare
  2    pragma autonomous_transaction;
  3  begin
  4    execute immediate 'alter trigger t_trigger disable';
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> declare
  2    pragma autonomous_transaction;
  3  begin
  4    insert into t values ( 1 );
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

<b>that shows the table is still locked and this:</b> 
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> insert into t values ( 1 );
 
1 row created.
 
<b>shows you can load it... and this:</b>

ops$tkyte@ORA10G> alter trigger t_trigger enable;
 
Trigger altered.


<b>is your commit -- but remember -- if the insert "fails" and the session goes away -- the trigger is left disabled, so I like the other method</b>

 

Holding a Lock Through DDL

John Gilmore, December 09, 2003 - 8:43 am UTC

The first method is probably not practical in my case since I will be loading about 60 tables and each has several triggers.

I have tried to be pedantic with my use of exception handlers wherein I always call a subroutine which enables the triggers. In what cases would you see this method failing to work?

Tom Kyte
December 09, 2003 - 1:58 pm UTC

power failure (when database comes up, bummer).
system failure (for whatever reason).
software failure (database "crash").

you'd only need ONE exception block -- at the top level -- to enable the triggers.

Reader

A reader, December 09, 2003 - 10:10 pm UTC

Is "I_am_the_loader" a global variable in the
package section of Sp

Tom Kyte
December 10, 2003 - 2:57 am UTC

yes, it would have to be if you wanted to access it like that in the trigger.

the other way would be to have a "getter", "setter" function/procedure to get the value of a hidden global in the body or set it.

What DML will lock a table in SHARE mode?

Robert, November 24, 2004 - 2:54 pm UTC

Tom,

Please fogive my dense-ness but I am trying to understand the same question Kulguru asked 3 years ago.

"What kind dml statement would [lock a table in SHARED mode]?"

Fact: I know we can do this with a "lock table..." command.
Fact: I studied the chart in the link you provided but apparently didn't understand it completely.

Please give an example (or 2 or 3) of a DML statement that would lock a table in SHARED mode.

Thanks,

Robert.


Tom Kyte
November 24, 2004 - 3:38 pm UTC

select for update does.

Conflicting unique keys only?

A reader, November 29, 2004 - 10:48 am UTC

Tom,

I have been studying your site, Metalink, Oracle docs, etc. for several hours and for several days and am still puzzled on why WE are getting a TX deadlock with 'no rows'.

FACT: Our Developers break thier programs up into parallel segments based on ACCOUNT_ID. The driving cursor in the program contains...

...where ACCOUNT_ID between v_from_id and v_to_id...

Then they run the program in parallel by calling...
session A: exec progx(1, 1000)
session B: exec progx(1001, 2000)
session C: exec progx(2001, 3000)
...etc.

FACT: These programs do selects/inserts/updates/deletes
FACT: The tables on which these programs operate have UNIQUE KEYS based on ACCOUNT_ID
FACT: We are getting TX deadlocks with 'no rows'.

All the (TX-Deadlock-'no rows') examples I am seeing have two sessions each operating on the OTHER SESSION's record.

My understanding is that it is NOT possible to get a deadlock IF each session operates on ITS OWN unique range of ACCOUNT_IDs....
...THEREFORE the Developers must be OVERLAPPING the ranges of ACCOUNT_IDs among thier parallel processes.

QUESTION: Is it even *POSSIBLE* for this type of deadlock to occur if each session is operating ONLY on its own unique range of account_ids?

Thank you,

Robert.

Tom Kyte
November 29, 2004 - 3:19 pm UTC

QUESTION: Is it even *POSSIBLE* for this type of deadlock to occur if each 
session is operating ONLY on its own unique range of account_ids? 

ANSWER: of course cause your transaction probably touches many tables.  it is not simple inserts updates and deletes of your single account table is it...

consider:

set echo on
                                                                                                                                         
drop table t;
                                                                                                                                         
create table t ( x int primary key, y int unique );
                                                                                                                                         
                                                                                                                                         
insert into t values ( 1, 1 );
insert into t values ( 2, 2 );
insert into t values ( 3, 3 );
insert into t values ( 4, 4 );
commit;
                                                                                                                                         
                                                                                                                                         
update t set y = 0 where x = 1;
rem in another session
rem update t set y = 5 where x = 3;;
rem update t set y = 0 where x = 4;;
pause
update t set y = 5 where x = 2;


<b>session 1 works on 1..2, session 2 works on 3..4 but the deadlock trace shows:</b>

*** SESSION ID:(14.4027) 2004-11-29 15:16:06.146
DEADLOCK DETECTED
Current SQL statement for this session:
update t set y = 0 where x = 4
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0008002f-00005efc        18      14     X             17      18           S
TX-0007002f-00005e85        17      18     X             18      14           S
session 14: DID 0001-0012-00000068  session 18: DID 0001-0011-00000127
session 18: DID 0001-0011-00000127  session 14: DID 0001-0012-00000068
Rows waited on:<b>
Session 18: no row
Session 14: no row</b>
Information on the OTHER waiting sessions:
Session 18:
  pid=17 serial=31406 audsid=2655 user: 182/OPS$TKYTE
  O/S info: user: tkyte, term: pts/1, ospid: 31920, machine: xtkyte-pc.us.oracle.com
            program: sqlplus@xtkyte-pc.us.oracle.com (TNS V1-V3)
  application name: SQL*Plus, hash value=3669949024
  Current SQL Statement:
  update t set y = 5 where x = 2
End of information on OTHER waiting sessions.


 

....Quick Addendum to previous response (TX - Deadlock)....

Robert, November 29, 2004 - 12:15 pm UTC

Tom,

I didn't mean to send my last response anonymously.
I am running 8.1.7.4

Thanks,

Robert.

Follow up...

Robert, November 29, 2004 - 3:38 pm UTC

Tom,

But you have to cross over ACCOUNT_IDs between sessions in order to cause the deadlock, don't you (update account_id to another session's account_id) ?

Is it possible to cause this deadlock where each session operates on ONLY its own ACCOUNT_IDs (unique key) and does not UPDATE any unique key?
Thank you for your detailed explaination, above!

Robert.

Tom Kyte
November 29, 2004 - 3:53 pm UTC

no i didn't -- reread the example please.

session one worked on account_ids X=(1,2)
session two worked on account_ids X=(3,4)

they did not cross records at all.


You have other unique keys out in your system I am sure.
You have other tables in involved in this transaction I am sure.

Question needs to be more focused......

Robert, November 29, 2004 - 5:01 pm UTC

Tom,

I see your point... I apologize for my unclear question.
(yes, there are several tables involved)

Your exampe shows the 2 sessions each knocking heads on the unique key (y = 0).

......<QUOTE>.......
update t set y = 0 where x = 1; <--------- y = 0 !!!
rem in another session
rem update t set y = 5 where x = 3;;
rem update t set y = 0 where x = 4;; <--------- y = 0 !!!
pause
update t set y = 5 where x = 2;
......<QUOTE>.......

To restate and focus my question....

IF each session stays only within it's given ACCOUNT_ID range...
THEN to cause this deadlock...
1) There MUST be another UNIQUE KEY on one or more of the tables in the transaction.
AND
2) Two or more sessions *MUST* be trying to update one of these UNIQUE KEYs to the same value? ... correct?

Thank you,

Robert


Tom Kyte
November 29, 2004 - 7:04 pm UTC

or child tables, or other tables, or.....


for you see -- you haven't said yet "the only table involved is this table and we only insert/update/delete rows with our account range against this single table"

I'm having a very very hard time believing that it is a single table transaction.


can you share the trace file header with us like I've done with you?

Here is the Deadlock Header you requested....

Robert, November 30, 2004 - 10:24 am UTC

Tom,

Here is the deadlock header from one of the deadlocks...

(1) Yes there are several tables involved in the transaction.

(2) My question is simply this:

To cause this type of deadlock, at least 2 processes MUST be 'butting heads' on the same UNIQUE KEY ... correct? .... I mean, if you have the following...

* Several sessions running identical processes (i.e. transactions)
* Each session operating on a distinct range of IDs
* Each session's transaction operating on (the same) several tables

THEREFORE --> To get this type of deadlock, at least one of the sessions MUST be 'crossing over' into another session's UNIQUE 'territory'.

I just want to hear you say categorically that this type of deadlock is IMPOSSIBLE otherwise. (e.g. a single session CANNOT cause a deadlock with itself, this would not be caused by the ITL running out of space, etc.).

NOTE: The only unique key on the NAMES table is on (ACCOUNT_ID, NAME_ID).

*** 2004-11-22 19:20:17.567
*** SESSION ID:(73.26588) 2004-11-22 19:20:17.567
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE NAMES SET DECEASED_FLAG='Y',STS='I',PRIMARY_NAME_FLAG=:b1 WHERE ACCOUNT_ID = :b2 AND NAME_ID = :b3
----- PL/SQL Call Stack -----
object line object
handle number name
cc2d96c4 1033 package body xxxxx.xxxxxx_DECEASED_OVERLAY_PKG
cc2d96c4 472 package body xxxxx.xxxxxx_DECEASED_OVERLAY_PKG
cc0a5550 1 anonymous block
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-000f0022-000c3972 22 73 X 18 89 S
TX-00180007-000a2974 18 89 X 22 73 S
session 73: DID 0001-0016-00005742 session 89: DID 0001-0012-000053D2
session 89: DID 0001-0012-000053D2 session 73: DID 0001-0016-00005742
Rows waited on:
Session 89: no row
Session 73: no row
===================================================

Thank you!

Robert

Tom Kyte
November 30, 2004 - 11:39 am UTC

no, not a unique key, you asked me "Is it even *POSSIBLE* for this type of deadlock to occur if each 
session is operating ONLY on its own unique range of account_ids? "

and i said NO -- and the first example i came up with was that one.  

Categorically the only thing that can be said (i don't think anyone has said differently) <b> there are other cases where it'll happen. </b> ITL's could be a cause, primary/foreign keys can be a cause.



run this:
----------------------------------------------------------
drop table t;
set echo on
create table t ( x int, y int ) maxtrans 2;
insert into t select rownum, rownum from all_users where rownum <= 4;
commit;
alter table t minimize records_per_block;
insert into t select rownum+4, rownum+4 from all_users where rownum <= 8;
commit;
                                                                                                                   
update t set y = y where x = 1;
update t set y = y where x = 5;
                                                                                                                   
set echo off
prompt in session 2 issue
prompt update t set y = y where x = 2;;
prompt update t set y = y where x = 9;;
prompt in session 3 issue
prompt update t set y = y where x = 6;;
prompt update t set y = y where x = 10;;
prompt in session 2 issue
prompt update t set y = y where x = 7;;
prompt in session 3 issue
prompt update t set y = y where x = 3;;
set echo on
pause
update t set y = y where x = 12;
----------------------------------------------------------


DEADLOCK DETECTED
Current SQL statement for this session:
update t set y = y where x = 3
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00050026-00005f5d        19      14     X             17      18           S
TX-00080026-00005fd6        17      18     X             19      14           S
session 14: DID 0001-0013-00000022  session 18: DID 0001-0011-0000014E
session 18: DID 0001-0011-0000014E  session 14: DID 0001-0013-00000022
Rows waited on:<b>
Session 18: no row
Session 14: no row</b>
Information on the OTHER waiting sessions:
Session 18:
  pid=17 serial=42664 audsid=2679 user: 182/OPS$TKYTE
  O/S info: user: tkyte, term: pts/0, ospid: 2248, machine: xtkyte-pc.us.oracle.com
            program: sqlplus@xtkyte-pc.us.oracle.com (TNS V1-V3)
  application name: SQL*Plus, hash value=3669949024
  Current SQL Statement:
  update t set y = y where x = 12
End of information on OTHER waiting sessions.



<b>
And a single session can easily deadlock itself, it would be trivial using autonomous transactions.</b> 

Nothing fancy going on here...

Robert, November 30, 2004 - 1:01 pm UTC

Tom,

I ran your example and it worked just as you promised.... (actually I got one for 'x=7' also)...

But...Our NAMES table has max_trans=255 (we only had 10 of these sessions running at a time)... and ... We are not using autonomous transactions.

The unique key on NAMES is only on (account_id, name_id)

Here is the statement the trace file shows got the deadlock:

UPDATE NAMES SET DECEASED_FLAG='Y',STS='I',PRIMARY_NAME_FLAG=:b1 WHERE
ACCOUNT_ID = :b2 AND NAME_ID = :b3

I'm running 8.1.7.4.

So how could the above statement cause the deadlock?
Would the 'other' deadlocking statement be somewhere in the trace file?

Robert.

Tom Kyte
November 30, 2004 - 1:24 pm UTC

(in 9i the other sessions information is in the trace file like my example)

max trans was to make this "happen" artificially.

initrans is 2 for all segments (1 for tables in 8i and before, 2 after that)...

the transaction block (23 bytes per entry) will dynamically grow as long as there is space on the block.  However, if you load up the block and there isn't room to grow -- we'll never get to max trans.

Here, maxtrans = 255, rows on this block = 2:

ops$tkyte@ORA817DEV> create table t ( x int, y varchar2(4000), z varchar2(4000) );
 
Table created.
 
ops$tkyte@ORA817DEV> insert into t values ( 1, rpad('*',4000,'*'), null );
 
1 row created.
 
ops$tkyte@ORA817DEV> insert into t values ( 2, rpad('*',1,'*'), null );
 
1 row created.
 
ops$tkyte@ORA817DEV> select dbms_rowid.rowid_block_number(rowid) from t;
 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                               27786
                               27786
 
ops$tkyte@ORA817DEV> update t set y = rpad('*',4000,'*') where x = 2;
 
1 row updated.
 
ops$tkyte@ORA817DEV> commit;
 
Commit complete.
 
ops$tkyte@ORA817DEV> update t set z = rpad('*',79,'*') where x = 2;
 
1 row updated.
 
ops$tkyte@ORA817DEV> commit;
 
Commit complete.
 
ops$tkyte@ORA817DEV> analyze table t compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA817DEV> select chain_cnt from user_tables where table_name = 'T';
 
 CHAIN_CNT
----------
         0
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> update t set x = x where x = 1;
 
1 row updated.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
  2          pragma autonomous_transaction;
  3  begin
  4          update t set x = x where x = 2;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
 
 
<b>note that I used the autonomous transaction to DEMO with -- you could use two sessions just as well.  </b>

the number of transactions could not go above one here cause of the way I very carefully filled this block. 

Didn't work for me!?

Robert, November 30, 2004 - 2:31 pm UTC

Tom,

I ran your example.. but I didn't get the same results.. and I didn't get the deadlock.....

SQL> create table t ( x int, y varchar2(4000), z varchar2(4000) );

Table created.

SQL>  
SQL> insert into t values ( 1, rpad('*',4000,'*'), null );

1 row created.

SQL> insert into t values ( 2, rpad('*',1,'*'), null );

1 row created.

SQL> select dbms_rowid.rowid_block_number(rowid) from t;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                              376938
                              376938

SQL>  
SQL> update t set y = rpad('*',4000,'*') where x = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> update t set z = rpad('*',79,'*') where x = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select chain_cnt from user_tables where table_name = 'T';

CHAIN_CNT
---------
        2

SQL> update t set x = x where x = 1;

1 row updated.

SQL> declare
  2  pragma autonomous_transaction;
  3  begin
  4          update t set x = x where x = 2;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at line 5

??????

Thanks,

Robert. 

Tom Kyte
November 30, 2004 - 8:16 pm UTC

sorry -- should have said "8k blocksize"

what is yours?

Lock a table in SHARE mode

Jon, December 01, 2004 - 5:58 am UTC

Going back to Robert's original post, when he quoted Kulguru:

>>"What kind dml statement would [lock a table in SHARED mode]?"

and you replied:

>select for update does.

I don't think that is correct.

From the doco link at the top of this question:

>>
A row share table lock is automatically acquired for a table when one of the following SQL statements is executed:

SELECT . . . FROM table . . . FOR UPDATE OF . . . ;

LOCK TABLE table IN ROW SHARE MODE;
<<

and later:

>>
A share table lock is acquired automatically for the table specified in the following statement:

LOCK TABLE table IN SHARE MODE;
<<

The table of table locks shows that there are 5 types of tables locks:

row share
row exclusive
share
share row exclusive
exclusive

of which the last three can not be obtain via DML, only by using the LOCK TABLE ... statement. (excluding DDL from this discussion)

Is my reading of this correct?

This leads me to a few questions:

1) What is the purpose of share & share row exclusive. Can you give a real world example where these could be used?

2) Why is the row share lock necessary? I realise select for update starts with a row share, but for what purpose? Surely the lock type used by insert,update,delete would do the trick? What am I missing here?

3) Is it just me, or are these lock names confusing. You have a row lock (which is always exlusive), but a row share lock and a row share exclusive lock, neither of which really lock rows, because they're table locks. Can we do something about these name? It hurts my brain running decode statements all the time...



Tom Kyte
December 01, 2004 - 9:49 am UTC

what do you think a row share table lock is?

1) different levels of locks -- each more "exclusive than the next"

2) but the lock type done by insert/update/delete is more restrictive than the select for update type. locking is always done at the lowest level possible -- to provide for the highest concurrency.

select for update on a row for example assures you of one thing: no one else will update that row. What doesn't it assure you of: that you can in fact update that row!! Now, if you updated the row instead of select for update - you could in fact be assured of two things a) no one else will update that row, b) you can update that row

the lock taken by update is more restrictive than the lock taken by select for update.

3) most of the locks are 'segment level' -- and are used to say "hey, i've got a lock of this type on some of the details of this segment". It might be confusing if you were schooled in other databases which expose row locks directly via some external lock manager. In Oracle there is no "list of locked rows" -- the row locks are attributes of the data itself. The exposed locks show you what type of "detail locks" someone has -- but a single exposed lock at the table level doesn't tell you if they have 0, 1, or 1 billion rows locked in the table -- just that they "have something locked in there".

All of the exposed locks are table level locks in Oracle really -- because they expose what sort of detailed locks you have on the segment, but not the details (because those details are not kept anywhere in a centralized fashion -- very different from the way others do, but allows us to have a billion row locks -- whereas they cannot. also, we don't have to serialize on some shared lock structure to check rows in and out (the reason db2 and others have lock ESCALACTION -- turning row locks into page into table locks as you lock more and more rows -- for them, managing thousands of locks is a hard task)


Answer to your question 2 posts up....

Robert, December 01, 2004 - 10:22 am UTC

Tom,

I do have a 4k blocksize vs. your 8k blocksize.
After running your original script, it occurred you might have an 8k blocksize so I changed all '4000' to '2000' in your original script...THIS FAILED ALSO....

Just now, I tried changing '4000' to '1950' (to accommodate for an extra few bytes of block overhead for 8k vs. 4k) and it WORKED AS ADVERTISED.....

...pedantic clarification, please....
--> This demo IS illustrating an ITL induced deadlock, correct?

SQL> create table t ( x int, y varchar2(1950), z varchar2(1950));

Table created.

SQL> insert into t values ( 1, rpad('*',1950,'*'), null );

1 row created.

SQL> insert into t values ( 2, rpad('*',1,'*'), null );

1 row created.

SQL> select dbms_rowid.rowid_block_number(rowid) from t;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                              188953
                              188953

SQL> update t set y = rpad('*',1950,'*') where x = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> update t set z = rpad('*',79,'*') where x = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select chain_cnt from user_tables where table_name = 'T';

CHAIN_CNT
---------
        0

SQL> update t set x = x where x = 1;

1 row updated.

SQL> declare
  2      pragma autonomous_transaction;
  3  begin
  4          update t set x = x where x = 2;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

--> This demo IS illustrating an ITL induced deadlock, correct?

Thanks!

Robert.
 

Tom Kyte
December 01, 2004 - 10:32 am UTC

yes, exactly - an itl deadlock.

* * * * * - Most Useful!

Robert, December 01, 2004 - 10:53 am UTC


Not clear yet

Jon, December 01, 2004 - 10:57 am UTC

>> what do you think a row share table lock is?

Not at all getting you here, but it kind of neatly shows what I'm on about.

Robert/Kulguru asked "What kind dml statement would [lock a table in SHARED mode]?"

select for update takes a ROW SHARE MODE lock, which is not the same as SHARE MODE lock (as they were asking). They are different locks.

Maybe you interpreted their question to mean what DML statement takes any form of share lock on a table, in which case insert,update,delete would also be valid answers. But given the context of their question, I think they meant the former.

But this does show how we can easily confuse semantics because of these obscure lock names, rather than discussing the principles.

And no, a background in other DBs doesn't confuse things here. In fact Oracle's row-level locking, as attributes of the data, is brilliant in its simplicity. It is the table level locks - both their names and their purpose - that confuse.

To paraphrase the DML table lock concept, it seems that there are 5 available lock levels - L1 to L5 - each level being more restrictive that the previous one.

These locks are taken by:

L1 - select for update
L2 - insert,update,delete
L3,L4,L5 - not implicitly used as yet, but explicitly accesible if required thru the LOCK TABLE command

And, to obscure the simplicity of this, a secret name generating function has be used:

select generate_random_phrase('exclusive,row,share',5) from dual;
-----------------------------------------------------------------
row share
row exclusive
share
share row exclusive
exclusive

5 rows selected

;)

Tom Kyte
December 01, 2004 - 11:03 am UTC

a row share table lock is a shared lock. they have the word share in there. that is what I took the question to be, yes.




Follow-up......

Robert, December 01, 2004 - 11:21 am UTC

Tom and Jon,

My question here about shared table locks was prompted (as I recall) by Tom's reply in this link....
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6369740147072, <code>

"...and then, I'll try to lock table t in SHARE MODE;..." [emphasis mine]

.......<QUOTE>........
Followup:
just means the resource you were trying to lock wasn't a single row.

create table t (x int);
insert into t values ( 1 );
insert into t values ( 2 );

now, you update 1
then, I'll update 2
then, you try to update 2 (block)
and then, I'll try to lock table t in share mode;

deadlock with no rows waited on.
...
...
.......<QUOTE>........

Regards,

Robert.



Could also be bitmap indexes

Ramakrishna, December 01, 2004 - 11:48 am UTC

In one of our systems we faced a similar situation where two sessions which were using distinct ID ranges still locked each other out because there were bitmap indexes on some of the columns in the table. This was because the granularity of locking for a bitmap index is more than just the row being updated.

The solution may have been under my nose the whole time!

Robert, December 01, 2004 - 2:06 pm UTC

Tom and Ramakrishna,

I just discovered (I don't know why I didn't check sooner) but the statement that triggered the deadlock IS UPDATING A COLUMN WITH A BITMAPPED INDEX!

UPDATE NAMES
SET DECEASED_FLAG='Y' <------- BITMAPPED INDEX!
,STS='I'
,PRIMARY_NAME_FLAG=:b1
WHERE ACCOUNT_ID = :b2
AND NAME_ID = :b3

What exactly is the contention which causes deadlocks when multiple sessions are updating the same bitmapped index?

Thanks,

Robert

Tom Kyte
December 01, 2004 - 3:30 pm UTC

you cannot use bitmaps in a system where you

a) insert rows
b) update rows
c) delete rows

all day long -- bitmaps are appropriate only for read only or read mostly data -- they are WHOLLY inappropriate in a transactional system, they are KILLER


I didn't even think to ask about them -- they are so far removed from what you would use in an update type system.

they are for warehousing pretty much exclusively.


In a bitmap index you have conceptually:

KEY_VALUE(N) rowid1,rowid2,rowid3,...,rowid5002


So, updating the deceased_flag from N to Y for rowid3 will effectively lock rowid1...rowid5002


A single bitmap key entry points to hundreds of rows -- update that key and lock hundreds of rows.



Excellent!!!

Robert, December 01, 2004 - 4:11 pm UTC

Tom,

This is a 3rd party app and the creation date on the index shows it was created a long time ago.

I think when I get a chance I will create a small demo to illustrate your above comments about bitmapped indexes for myself.

Thank you, sir!!!

Robert.

Bitmap index insert/deadlock example

Robert, December 01, 2004 - 6:11 pm UTC

Follow up on your tequnique, above...

Robert, December 07, 2004 - 11:22 am UTC

Tom,

In answering my question above, you used an autonomous transaction to simulate a separate session (for simplicity and ease of use, I presume)...

<Tom's quote>
note that I used the autonomous transaction to DEMO with -- you could use two sessions just as well.
<Tom's quote>

Thanks to your idea, I am adopting this method to simulate 2 or more sessions when testing requires, so I can just run a single script in a single session... instead of having to juggle between 2 or more sessions.

I am using your RUNSTATS idea to take snapshots of each session's work and then compare the results.

So far the RUNSTATS stats appear to be similar whether I am calling my scripts from individual sessions, or using your autonomous transaction trick within the same script/session.

My question is this: Are there any 'hidden' factors regarding autonomous transactions which could skew my RUNSTATS when comparing stats between sessions? ... In other words, can I be 99.9% confident that identical tests run using autonomous transactions within the same script/session will approximate the same tests run on individual sessions?

Thank you,

Robert



Tom Kyte
December 07, 2004 - 8:17 pm UTC

well, given the way run stats works:

a) snap
b) run method 1
c) snap
d) run method 2
e) snap

print out difference between c+a and e+c

I don't see where "autonomous transactions fit in" really?

Clarification...

Robert, December 08, 2004 - 11:45 am UTC

Tom,

I'm comparing the stats in two separate SESSIONS. I find it much easier to use your autonomous transaction technique (which you demonstrated earlier in this thread) and SIMULATE a separate session within a SINGLE SCRIPT instead of having to JUGGLE two separate sessions.

The nature of the test REQUIRES these statements be run in SEPARATE SESSIONS.. So I am using your autonomous transaction trick to SIMULATE two sessions.

Here is what I am doing...

NEW, IMPROVED SINGLE SESSION 'ATRANS' *RUNSTATS* METHOD
-----------
snapshot A
run statement
snapshot B
<BEGIN ATRANS IN SAME SESSION>
snapshot C
run statement
snapshot D
<END ATRANS IN SAME SESSION>

Then I compare (B-A) with (D-C)

OLD FASHIONED 2 SESSION *RUNSTATS* METHOD
------------
<SESSION 1>
snapshot A
run statement
snapshot B
<SESSION 1>

<SESSION 2>
snapshot C
run statement
snapshot D
<SESSION 2>

Should the stats gotten using the 'NEW' (ATRANS) method be similar to the stats gotten using 2 separate sessions WITHOUT using an ATRANS...In other words: Is there anything *inherent* in the use of autonomous transactions that would SKEW the stats somehow ? .. this is all I am trying to find out.

Thanks!

Robert.


Tom Kyte
December 09, 2004 - 12:36 pm UTC

I still don't understand "why" the atrans is needed or useful here.

can you give a real world example? one that if there wasn't an atrans it would not work?

Example......

Robert, December 09, 2004 - 1:28 pm UTC

Tom,

Here is the example you requested. This initial testing was prompted by a previous thread where you suggested this DBMS_LOCK.REQUEST technique to identify uncommited inserts between sessions/transactions... but my specific question here is still "Will stats gathered using an ATRANS to simulate a second session be the same as if test was performed in 2 distinct sessions?"

I have tested this exact script several times in 8.1.7 so it should work "copy/paste" with no problems.
Thanks.

define v_test_rows = 1000

prompt
prompt ===========================
prompt STEP 1 OF 3 Create objects, enable trigger
prompt ...INITIAL LOAD OF TABLE (no commit at end)
prompt ===========================
prompt
---------------------------------------
-- Create table
---------------------------------------
drop table insert_lock_demo
/

create table insert_lock_demo
(
account_id number(8)
, contact_seq number(3)
, process_id number
, constraint pk_insert_lock_demo primary key (account_id, contact_seq)
)
/

---------------------------------------
-- Create trigger
---------------------------------------
create or replace trigger insert_lock_demo_bir
before insert on insert_lock_demo
for each row
declare
v_lock_id number;
v_count number := 0;
v_return number;
begin
---------------------------
-- Create lock name (limited to 1G)
---------------------------
if substr(to_char(:new.contact_seq), -1) <> '0' then
v_lock_id :=
to_number(trim(substr(to_char(:new.contact_seq), -1)) ||
trim(to_char(:new.account_id, '00000000')));
else
v_lock_id :=
to_number('10' || trim(to_char(:new.account_id, '00000000')));
end if;

v_return := dbms_lock.request
(
id => v_lock_id,
lockmode => dbms_lock.x_mode,
timeout => 0,
release_on_commit => TRUE
);

if v_return = 0 then
return;
elsif v_return = 1 then
:new.contact_seq := :new.contact_seq + 1;
v_count := v_count + 1;
else
null; -- error handling ommitted for simplicity
end if;
end;
/

show err

declare
g_start number;
g_time number;
begin

g_start := dbms_utility.get_time;

insert into insert_lock_demo
(
account_id
, contact_seq
, process_id
)
select rownum
, 1
, 0
from dba_objects
where rownum <= &v_test_rows. ;

g_time := (dbms_utility.get_time-g_start);

dbms_output.put_line(
'*** TIME for INITIAL TABLE LOAD WITH TRIGGER...(Seconds) ----> ' ||
to_char(g_time/100, '9,999.999'));

end;
/

prompt
prompt ===========================
prompt STEP 2 of 3 : Test WITH trigger
prompt Using AUTONOMOUS TRANSACTION so we can run entire testing within a single script
prompt and so the records in the test table will be seen as UNCOMMITTED to another session.
prompt (Stats will show effect of inserting WITH trigger enabled)
prompt Get SNAPSHOTS: A (before) and B (after)
prompt ===========================
prompt

declare
PRAGMA AUTONOMOUS_TRANSACTION;
v_account_id number;
v_contact_seq number;
v_process number;
v_new_contact_seq number;
v_sqlerrm varchar2(500);
g_start number;
g_time number;
begin

g_start := dbms_utility.get_time;

-- SNAPSHOT A
runstats.rs_start; -- MY OWN VERSION OF YOUR RUNSTATS

----------------------------
-- These will cause PK conflicts which will be handled by the TRIGGER
----------------------------
for x in 1..&v_test_rows loop
v_account_id := x;
v_contact_seq := 10;
v_process := 100;
insert into insert_lock_demo
(
account_id
, contact_seq
, process_id
)
values
(
v_account_id
, v_contact_seq
, v_process
);

end loop;

-- SNAPSHOT B
runstats.rs_middle; -- MY OWN VERSION OF YOUR RUNSTATS

g_time := (dbms_utility.get_time-g_start);

dbms_output.put_line(
'*** TIME for PK conflicts WITH TRIGGER...(Seconds) ----> ' ||
to_char(g_time/100, '9,999.999'));

COMMIT;

end;
/

prompt
prompt ===========================
prompt STEP 3 of 3 : Test WITHOUT trigger
prompt (Non-Autonomous_transaction)
prompt (Stats will show effect of inserting WITHOUT trigger enabled)
prompt Get SNAPSHOTS: C (before) and D (after)
prompt ===========================
prompt

alter trigger INSERT_LOCK_DEMO_BIR DISABLE;

declare
v_account_id number;
v_contact_seq number;
v_process number;
v_new_contact_seq number;
v_sqlerrm varchar2(500);
g_start number;
g_time number;

begin

g_start := dbms_utility.get_time;

-- SNAPSHOT C
runstats2.rs_start; -- MY OWN VERSION OF YOUR RUNSTATS

----------------------------
-- No PK conflicts
----------------------------
for x in 1..&v_test_rows loop
v_account_id := x;
v_contact_seq := 999;
v_process := 200;
insert into insert_lock_demo
(
account_id
, contact_seq
, process_id
)
values
(
v_account_id
, v_contact_seq
, v_process
);

end loop;

-- SNAPSHOT D
runstats2.rs_middle; -- MY OWN VERSION OF YOUR RUNSTATS

g_time := (dbms_utility.get_time-g_start);

dbms_output.put_line(
'*** TIME for NON-PK conflicts WITHOUT trigger...(Seconds) ----> ' ||
to_char(g_time/100, '9,999.999'));

commit;

end;
/

prompt =========================================
prompt NOW COMPARE snapshots (B-A) with (D-C)
prompt =========================================


Tom Kyte
December 09, 2004 - 3:22 pm UTC

they will be marginally lower in a single session for somethings - since you have already done "some work" -- but the important things - latching, should be more or less the same. I cannot think of anything "big" that would be different.

i will say that your rs calls should be outside of the block -- some of the statistics are NOT recorded until the call completes, so you are missing CPU time for example in your examples.

v$lock understanding..

A reader, December 09, 2004 - 2:23 pm UTC

Hi tom, on the locks, ch#3 page#118 (Expert 1 on 1)

I am trying to delete about a million row in dev.
it is taking too much time, so meanwhile just to test the
stuff I understand correctly, in the other deba previllaged
session, I ran

select v.sid ,v.type,
trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 as slot , lmode, request, block,
username,machine
from v$lock v, v$session s
where v.sid = s.sid
and username = 'TRADES'

AND GOT
SID TY RBS SLOT LMODE REQUEST BLOCK USERNAME
---- -- ---------- ---------- ---------- ---------- ---------- --------
12 TM 2 35461 3 0 0 TRADER
12 TM 2 35466 5 0 0 TRADER
12 TM 2 35458 3 0 0 TRADER
12 TM 2 35754 4 0 0 TRADER
12 TX 7 86 6 0 0 TRADER


i DO NOT SEE ANY BLOCKS DO YOU ? THEH WHY IS IT TAKING SO LONG ?


Can you please explain ?

Tom Kyte
December 09, 2004 - 3:32 pm UTC

locking and blocking isn't the only thing that takes time.

you give me almost NOTHING to work with here. think about it.

doing something 1,000,000 times takes time. Perhaps you have 50 indexes on this table, index maintainence is expensive. perhaps you are deleteing 1,000,000 rows out of 5 billion and it is taking time to full scan 5 billion records.

think about it -- trace it, explain plan it, make sure you are not procedurally coding here. should be a single delete, should be CBO, but obvious things like "maintaining indexes" take time.

Thanks!

Robert, December 09, 2004 - 3:28 pm UTC

Tom,

Thanks for all your help with this problem.
(Caveat on calls to runstats noted)
Thanks for the great tricks and techniques I learn on your sight in addition to your overall philosophy and generous sharing of your knowledge, experience, time, and help.

Thanks again!

Robert.

Thanks tom

A reader, December 09, 2004 - 5:52 pm UTC

I was thinking more about foreign keys and full child table scans..

Thanks for the reply.



please answer

Roger, April 25, 2005 - 10:51 pm UTC

Hi Tom,

Thank you for your time.

I have read your book, the Oracle Concepts Manual and the Developers Mannual on locking. I understand when row share (RS) and row exclusive (RX) table locks are used. I also understand that share, share row exclusive, exclusive table locks are more exclusive in increasing order.

Question: When would you use share, share row exclusive, exclusive table locks? Can you please give a real example and show the SQL associated so I can reproduce? Please note that I have read:

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#3218 <code>

in particular, Table 20.3 and the permitted and prohibited operations for share, share row exclusive and exclusive table locks. I am *very* interested in an example. Something like how you have described the lost update problem in your book.

Thanks again. BTW, you were great in Ottawa.




Tom Kyte
April 26, 2005 - 7:23 am UTC

Not sure that you would use any but the last.

lock table in share mode; (to get the share lock) was used in Oracle transparently under the covers in versions from 7.0 to 7.3.2 to enable serializable mode isolation. Once you queried a table in that mode, we locked it in share mode. That would prevent anyone from modifying it. As you can imagine, it did not scale very well -- it was rewritten (isolation serializable) in 7.3.3 so as we could participate in the tpc-c in a scalable fashion. it would be very rare indeed to lock a table in share mode today.

lock table in share row exclusive mode; Cannot think of why you would use this in real life. It allows others to query and even LOCK rows in the table (via select for update). I've never used it.

lock table in exclusive mode; You might do this before a really big, blind modification (batch update). See </code> http://asktom.oracle.com/~tkyte/wc.html <code>for a reason "why"


Thanks for the feedback -- the Ottawa OUG was a nice turnout. I enjoyed it myself.

thanks

Roger, April 26, 2005 - 5:52 pm UTC

Hi Tom,

Thanks for your reply. As you suggested, I checked out the write consistency stuff in thread:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:11504247549852 <code>

Wow! It came as a surprise to me. Couldn't believe it. Any idea how other databases handle such situations? Do they also restart the update statement? Cheers.

Tom Kyte
April 26, 2005 - 8:03 pm UTC

they do not have the concept of multi-versioning. they read whatever happens to be in the database.

Funny -- i just submitted that chapter for the second edition of Expert one on one Oracle today. Added LOTS to the concurrency and locking and, well everything so far.

I cover it in some detail.

Share Mode Lock in Oracle 9i

Antonio Cesar Rosa, November 12, 2005 - 11:24 pm UTC

Tom,

I read this thread and the Oracle documentation about locks for Oracle9i. I have tried to use your example about EMP table with SHARE MODE lock to figure out if the documentation is correct.

Session 1
SELECT * FROM emp FOR UPDATE;

Session 2
LOCK TABLE emp IN SHARE MODE; <- my session is waiting for session 1

If you find the locks, it will show 'TX' lock for the session 1. Using the command "LOCK TABLE emp IN SHARE UPDATE MODE" the statement will work.

Did the Oracle change the SHARE MODE lock in Oracle9i (my version is 9.2.0.6 on WindowsXP)?

Thank you in advance,
Antonio Cesar Rosa


Tom Kyte
November 13, 2005 - 10:23 am UTC

yes, it did - I don't remember the exact "dot" version. I believe it was between 9.2.0.4 and 9.2.0.6.


In 9.2.0.3 - the lock table emp in share mode does not block, in 9.2.0.6 it does (bug fix as I recall)

deadlock

A reader, July 11, 2008 - 9:18 pm UTC

Tom:

If you run a simple update on a table in sql*plus and it hangs just keeps going and nothing happens.

is this more like a deadlock or table lock problem and how do you clear it? everyone (users/applications) logs in with one account to database.

Tom Kyte
July 15, 2008 - 9:08 am UTC

it is not a deadlock, deadlocks resolve themselves.

if A is waiting on B
and B is waiting on A

then one of A or B will be chosen as the victim, get the ora-60 error back, and continue processing.


What you have is just a block, a locking block. Someone has something locked already, you are waiting for it.

How do you "clear it" - you have to tell me. what do you want to have happen at that point. You could just ctl-c the update, it would rollback the update and you'd be free. You could use v$lock to see who is blocking the update from another window and then kill that session. You could wait for the resource to become available.

You tell me what you want to have happen - then it will be obvious how to "clear" it.

Perhaps you should avoid the issue in the first place by selecting for update (nowait) what you want to update and then and only then updating it. Else, you are subject to a thing called "lost updates"

tx-locks

A reader, August 30, 2012 - 2:39 am UTC

Hi,Tom
i have some confusion with the tx-lock
tx-lock is a attribute of a data row ,
now in session1
sql: update emp set ename='Mr' where empno=7788;
after this,the transaction id is active in the block header
which contains the row ,and the tx-lock is set to declare
the row is locked

assume another session also intend to update the row
where empno=7788, but oracle will tell the second session
the row is locked by the row itself
i want to know what is the effect of the transaction id which record in the block header ? dose it have some help
in the update action to the second session?


Tom Kyte
September 10, 2012 - 6:00 pm UTC

the second session will be waiting for the first transaction to complete, it will wait on that transaction id until it commits or rollsback.

locks

A reader, November 13, 2012 - 4:14 pm UTC

Tom:

If some user used the browser to run a stored procedure and then his browser crashed.

Does it keep running or it stops when browser closes?
Does oracle clear any locks caused by that session that crashed or not? Does it resolve by itself.
Tom Kyte
November 14, 2012 - 8:19 pm UTC

it probably keeps running. TCP-IP won't let the server know the client has disappeared until the server tries to write a response to it.

and when it does - it would be up to the middle tier software to decide what to do. It could commit, it could rollback, it could go into lah-lah land - you'd have to ask the question in the context of a very specific piece of software.