Row Locks using SELECT for UPDATE clause
Sitarama Chakravarthy, July 07, 2002 - 11:43 pm UTC
Hi Tom,
Sorry to bother you again. In an atempt to understand this concept more clearly, I ran the example which you presented. It worked. I realised that there is something that i am grossly doing wrong. The only difference I can see in your plsql and mine is the kind of data that was selected for update. I modified my WHERE clause to include all the columns of my primary key in the table (PRS_BSNS_SGMNT, PRS_BSNS_SUB_SGMNT, PRS_DATE, PRS_WOO_PRCS_ID). The modified query is as follows.
declare
cursor c1 is
select PRS_BSNS_SGMNT, PRS_BSNS_SUB_SGMNT,
PRS_DATE, PRS_WOO_PRCS_ID,prs_sts
from prcs_sts
where PRS_BSNS_SGMNT = 'FRX'
AND PRS_BSNS_SUB_SGMNT = 'USDINR'
AND PRS_DATE = '28-MAR-02'
AND PRS_WOO_PRCS_ID = 'PF31'
for update of prs_sts;
begin
for c1_rec in c1 loop
update prcs_sts
set prs_sts = 'Y'
where current of c1;
end loop;
end;
/
PL/SQL procedure successfully completed.
declare
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c1 is
select PRS_BSNS_SGMNT,PRS_BSNS_SUB_SGMNT,PRS_DATE,
PRS_WOO_PRCS_ID,prs_sts
from prcs_sts
where PRS_BSNS_SGMNT = 'FRX'
AND PRS_BSNS_SUB_SGMNT = 'USDINR'
AND PRS_DATE = '28-MAR-02'
AND PRS_WOO_PRCS_ID = 'PF32'
for update of prs_sts;
begin
for c1_rec in c1 loop
update prcs_sts
set prs_sts = 'Y'
where current of c1;
end loop;
COMMIT;
end;
/
PL/SQL procedure successfully completed.
As shown above, it worked and it worked even when i ran this query without the AUTONOMOUS_TRANSACTION and from a different session. Then, I tried the same thing with one of primary key disabled in the WHERE clause of the plsql.
declare
cursor c1 is
select PRS_BSNS_SGMNT, PRS_BSNS_SUB_SGMNT,
PRS_DATE, PRS_WOO_PRCS_ID,prs_sts
from prcs_sts
where PRS_BSNS_SGMNT = 'FRX'
AND PRS_BSNS_SUB_SGMNT = 'USDINR'
-- AND PRS_DATE = '28-MAR-02'
AND PRS_WOO_PRCS_ID = 'PF31'
for update of prs_sts;
begin
for c1_rec in c1 loop
update prcs_sts
set prs_sts = 'Y'
where current of c1;
end loop;
end;
/
PL/SQL procedure successfully completed.
declare
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c1 is
select PRS_BSNS_SGMNT,PRS_BSNS_SUB_SGMNT,PRS_DATE,
PRS_WOO_PRCS_ID,prs_sts
from prcs_sts
where PRS_BSNS_SGMNT = 'FRX'
AND PRS_BSNS_SUB_SGMNT = 'USDINR'
-- AND PRS_DATE = '28-MAR-02'
AND PRS_WOO_PRCS_ID = 'PF32'
for update of prs_sts;
begin
for c1_rec in c1 loop
update prcs_sts
set prs_sts = 'Y'
where current of c1;
end loop;
COMMIT;
end;
/
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 17
It did not work, either in the same session with AUTONOMOUS_TRANSACTION option or in a different session.
From this it looks like the data that is selected for update has an impact on how SELECT FOR UPDATE works. My question is, is there any relation between the usage of SELECT FOR UPDATE clause and the primary key of a table? Please clarify. Thanks again for your prompt reply.
July 08, 2002 - 7:08 am UTC
supply a complete test case including
o create table statement
o any and all indexes
o inserts
My example did not even have a primary key... In any case, I cannot reproduce - you'll have to show us how to step by step by step:
ops$tkyte@ORA9I.WORLD> drop table prcs_sts;
Table dropped.
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> create table prcs_sts
2 ( PRS_BSNS_SGMNT varchar2(3),
3 PRS_BSNS_SUB_SGMNT varchar2(10),
4 PRS_DATE date,
5 PRS_WOO_PRCS_ID varchar2(5),
6 prs_sts varchar2(1),
7 constraint pk primary key(PRS_BSNS_SGMNT,PRS_BSNS_SUB_SGMNT,PRS_DATE, PRS_WOO_PRCS_ID)
8 )
9 /
Table created.
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> insert into prcs_sts values( 'FRX', 'USDINR', '28-MAR-02', 'PF31', 'N' );
1 row created.
ops$tkyte@ORA9I.WORLD> insert into prcs_sts values( 'FRX', 'USDINR', '28-MAR-02', 'PF32', 'N' );
1 row created.
ops$tkyte@ORA9I.WORLD> commit;
Commit complete.
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> declare
2 cursor c1 is
3 select PRS_BSNS_SGMNT, PRS_BSNS_SUB_SGMNT,
4 PRS_DATE, PRS_WOO_PRCS_ID,prs_sts
5 from prcs_sts
6 where PRS_BSNS_SGMNT = 'FRX'
7 AND PRS_BSNS_SUB_SGMNT = 'USDINR'
8 --AND PRS_DATE = '28-MAR-02'
9 AND PRS_WOO_PRCS_ID = 'PF31'
10 for update of prs_sts;
11 begin
12 for c1_rec in c1 loop
13 update prcs_sts
14 set prs_sts = 'Y'
15 where current of c1;
16 end loop;
17 end;
18 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> declare
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 cursor c1 is
4 select PRS_BSNS_SGMNT,PRS_BSNS_SUB_SGMNT,PRS_DATE,
5 PRS_WOO_PRCS_ID,prs_sts
6 from prcs_sts
7 where PRS_BSNS_SGMNT = 'FRX'
8 AND PRS_BSNS_SUB_SGMNT = 'USDINR'
9 -- AND PRS_DATE = '28-MAR-02'
10 AND PRS_WOO_PRCS_ID = 'PF32'
11 for update of prs_sts;
12 begin
13 for c1_rec in c1 loop
14 update prcs_sts
15 set prs_sts = 'Y'
16 where current of c1;
17 end loop;
18 COMMIT;
19 end;
20 /
PL/SQL procedure successfully completed.
for update clause
A reader, August 20, 2003 - 8:35 pm UTC
if we have two tables in a join in a select statement
and if we don't specify which table to lock in the for update clause, how does Oracle figure out which tables
to lock. What are the consequences of not specifying the
table you want to lock?
Thanx!
August 21, 2003 - 5:45 pm UTC
it'll lock em all. this simulation shows what happens when you do for update, vs for update of <columns>
ops$tkyte@ORA920> create table t1 ( x int );
Table created.
ops$tkyte@ORA920> create table t2 ( y int );
Table created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace view v
2 as
3 select *
4 from t1, t2
5 where t1.x = t2.y
6 /
View created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t1 values ( 1 );
1 row created.
ops$tkyte@ORA920> insert into t2 values ( 1 );
1 row created.
ops$tkyte@ORA920> commit;
Commit complete.
ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from v for update;
X Y
---------- ----------
1 1
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 pragma autonomous_transaction;
3 begin
4 update t1 set x = 1;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
ops$tkyte@ORA920> declare
2 pragma autonomous_transaction;
3 begin
4 update t2 set y = 1;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
<b>another transaction cannot update t1 or t2 -- both locked</b>
ops$tkyte@ORA920> commit;
Commit complete.
ops$tkyte@ORA920> select * from v for update of x;
X Y
---------- ----------
1 1
ops$tkyte@ORA920> declare
2 pragma autonomous_transaction;
3 begin
4 update t1 set x = 1;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
ops$tkyte@ORA920> declare
2 pragma autonomous_transaction;
3 begin
4 update t2 set y = 1;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
<b>here we can and did update t2, but not t1 since t1.x was the "for update" column</b>
hopefully -- that shows you the consequences?
thanx Tom!!!
A reader, August 21, 2003 - 5:56 pm UTC
That definnitely shows the consequences. One more question.
If you don't specify the table or the column name in a
"select for update" clause, in which order does Oracle
acquire the locks (first t1, then t2 or first t2, then t1)?
If the order is not fixed then this perhaps has consequences in terms of deadlock (one session getting it
in order (t1, t2) and the other getting it in the order
(t2, t1).
Thanx for an awesome site!!
August 21, 2003 - 7:29 pm UTC
consider it "atomic" for all intents and purposes.
as far as you can tell, it is.
the order for a given query would be given the same plans and all -- but it'll be a function of how the data is accessed.
ok - here is the guideline that started this q
A reader, August 21, 2003 - 9:01 pm UTC
Tom
Could you kindly comment on the validity of the
statement below:
From what you told me my conclusions are:
1. You should specify the exact table that you want
to lock in your "for update" clause because otherwise
you would end up locking all the tables - the order
of the locking itself is not important (or is
atomic - so does not come into play - it is the
fact that more locks are acquired than necessary
that is of concern.
2. I don't think the alternative of breaking up into
multiple cursors is a good idea - I would instead specify
the tables to be locked in a single query.
Thanx a million Tom!!
"SQL statements that lock rows should be analyzed carefully to insure that deadlock and lock ordering issues are avoided. ....
Consider the following cursor that attempts to lock qualifying rows:
CURSOR lock_departure(x_dep_id NUMBER) IS
SELECT DEP.STATUS_CODE,
DEL.STATUS_CODE,
LD.LINE_DETAIL_ID,
PLD.PICKING_LINE_DETAIL_ID
FROM WSH_DEPARTURES DEP,
WSH_DELIVERIES DEL,
SO_LINE_DETAILS LD,
SO_PICKING_LINE_DETAILS PLD
WHERE DEP.DEPARTURE_ID = x_dep_id
AND DEL.ACTUAL_DEPARTURE_ID(+) = DEP.DEPARTURE_ID
AND LD.DEPARTURE_ID(+) = DEP.DEPARTURE_ID
AND PLD.DEPARTURE_ID(+) = DEP.DEPARTURE_ID
FOR UPDATE NOWAIT;
The problem with this query is that the locking order is largely dependent on the execution plan and the row source order. For example, it is possible that the rows in SO_LINE_DETAILS can be locked before the rows in SO_PICKING_LINE_DETAILS. It is also possible that the rows of SO_PICKING_LINE_DETAILS are locked before the rows in SO_LINE_DETAILS. The locking order is based on the join order (i.e. execution plan). If one user ran this query under the RBO, and another user ran this query under the CBO, locking order issues could arise due to the likelihood of a plan difference. Another problem with this cursor is that it performs non-qualified locking via the FOR UPDATE. The FOR UPDATE clause can take additional optional arguments specifying the tables to be locked. For example, FOR UPDATE OF DEP means that only the rows in WSH_DEPARTURES should be locked. The solution to this query is to specify the tables to be locked in the FOR UPDATE clause via the FOR option, or break the query into separate cursors such that each cursor locks a single table only. For example, the above cursor can be rewritten as follows:
CURSOR lock_departure(x_dep_id NUMBER) IS
select departure_id
from WSH_DEPARTURES
where DEPARTURE_ID = x_dep_id
FORUPDATE NOWAIT;
CURSOR lock_deliveries(x_dep_id NUMBER) IS
select delivery_id
from WSH_DELIVERIES
where ACTUAL_DEPARTURE_ID = x_dep_id
FOR UPDATE NOWAIT;
CURSOR lock_line_details(x_dep_id NUMBER) IS
select line_detail_id
from SO_LINE_DETAILS
where DEPARTURE_ID = x_dep_id
FOR UPDATE NOWAIT;
CURSOR lock_picking_details(x_dep_id NUMBER) IS
select picking_line_detail_id
from SO_PICKING_LINE_DETAILS
where DEPARTURE_ID = x_dep_id
FOR UPDATE NOWAIT;
Begin
OPEN lock_departure(entity_id);
CLOSE lock_departure;
OPEN lock_deliveries(entity_id);
CLOSE lock_deliveries;
OPEN lock_line_details(entity_id);
CLOSE lock_line_details;
OPEN lock_picking_details(entity_id);
CLOSE lock_picking_details;
End;
In summary, do not code a SQL statement that performs an unqualified lock via the FOR UPDATE clause. You should either break up the SQL statement into multiple single table cursors or specify the FOR <table> option of the FOR UPDATE clause.
August 22, 2003 - 8:35 am UTC
I think the deadlock concerns are way overrated here. the probability of them happening is extremely small. breaking the cursor up into lots of pieces would be a really bad idea in my opinion and experience. A single query is the right answer.
thank you Tom!!
A reader, August 22, 2003 - 1:55 pm UTC
Love your site and am continuously JUST AMAZED by your
generosity, your technical knowhow and your tireless
efforts day in and day out for Oracle community!!
Reader
A reader, August 23, 2003 - 11:40 pm UTC
I tried your sample code:
I got the error
ORA-06519: active autonomous transaction detected and rolled back
The v$transaction showed no rows. How to track the
autonomous transaction with v$ views
Thanks
SQL> declare
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 cursor c1 is
4 select PRS_WOO_PRCS_ID,prs_sts
5 from prcs_sts
6 where PRS_WOO_PRCS_ID = 'PF31'
7 for update of prs_sts;
8 begin
9 for c1_rec in c1 loop
10 update prcs_sts
11 set prs_sts = 'Y'
12 where current of c1;
13 end loop;
14 end;
15 /
declare
*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at line 10
SQL> select * from v$transaction;
no rows selected
SQL> declare
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 cursor c1 is
4 select PRS_WOO_PRCS_ID,prs_sts
5 from prcs_sts
6 where PRS_WOO_PRCS_ID = 'PF31'
7 for update of prs_sts;
8 begin
9 for c1_rec in c1 loop
10 update prcs_sts
11 set prs_sts = 'Y'
12 where current of c1;
13 end loop;
14 end;
15 /
declare
*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at line 10
August 24, 2003 - 7:38 am UTC
umm, which example exactly are you running?
if you run the block of plsql code that DOES NOT GET BLOCKED -- yes, it needs either a commit or a rollback at the end of it.
reader
A reader, August 24, 2003 - 9:21 am UTC
I was running the block of pl/sql code that DOES BLOCK twice and got
the error. I like to know if there is anyway to track the
atonomous TXs from v$ views
Thanks
August 24, 2003 - 11:43 am UTC
then you unblocked the code -- the ONLY way to get that error would be for the code to not have blocked at all. the ONLY way.
an autonomous transaction is purely a "you are doing it in your session" sort of thing. externally - from any one elses perspective, it is not any different then any other transaction. they appear no differently.
A little confused
reader, August 25, 2003 - 6:36 am UTC
Hi Tom Good day to you.
Well I am a little confused, kindly help.
As you said "an autonomous transaction is purely a "you are doing it in your session" sort of thing. externally - from any one elses perspective, it is not any different
then any other transaction. they appear no differently."
The ORACLE doc A76965-01 for 8i says "Once invoked, an autonomous transaction is totally independent of the main
transaction that called it. It does not see any of the uncommitted changes made by the main transaction and does not share any locks or resources with the main
transaction. Changes made by an autonomous transaction become visible to other transactions upon commit of the autonomous transactions."
The transaction through autonomous transaction is completed opening another session or does it uses the same session which initiated the transaction if you can give and example for this it will be a great help.
Thanks for all your help.
Best Regards
August 25, 2003 - 7:07 am UTC
i see no conflict there.
someone asked "how do I identify an atrans"
i said -- you cannot. they "look" no different. It would be like trying to pick the cloned sheep daisy out of a herd of sheep. Unless you yourself placed daisy in the herd, you would have no clue which one was she.
sure, it is independent -- but so what? externally -- it is just yet another transaction.
reader, August 25, 2003 - 7:34 am UTC
Hi Tom,
With all the respect to you, don't get me wrong I only want to know if autonomous transaction opens another session or uses the same session. I feel it uses same session still we have people with different opinions just wanted to know the right thing.
Thanks as always for your helps...
August 25, 2003 - 9:26 am UTC
uses the same session -- it is just an independent sub transaction.
It is just like "recursive sql", you've been doing autonomous transactions for years -- you just never knew it. If you select a sequence and we have to update seq$ (and commit that update), we did it in an autonomous transaction if you will
Dead lock
Dillip K. Praharaj, October 22, 2003 - 4:51 pm UTC
I do not think
If I run this from 2 sessions it waits, but from the same session with PRAGMA AUTONOMOUS_TRANSACTION, it triggers a rollback,
Can someone confirm why so ?
declare
cursor c1 is
select row_id,id,sts
from test
where ID = 'PF31'
order by row_id desc
for update of sts;
begin
for c1_rec in c1 loop
update test
set sts = 'Y'
where current of c1;
end loop;
end;
October 22, 2003 - 6:27 pm UTC
umm, you deadlocked yourself -- that is why it failed in a single session. What did you want it to do? block forever????
deadlock
Dillip K. Praharaj, October 22, 2003 - 7:11 pm UTC
But I am doing a PRAGMA AUTONOMOUS_TRANSACTION for the second query, is it not same as running the query from another session ?
October 22, 2003 - 7:26 pm UTC
no
it is running a second transaction from YOUR SESSION
wouldn't you be mad if we just sat there for the rest of time???? that seems to be what you want -- that would be "bad".
deadlock
Dillip K. Praharaj, October 22, 2003 - 8:13 pm UTC
Trust me I would not like to wait. Thanks for the clarification.
Lock 2nd users for view only
Junior, November 06, 2003 - 5:33 pm UTC
Tom:
We are creating an web application. Now, question is: When more than one users work on same record, only the first one who selects the record can update it. The rest of users are view only for this record. Does Oracle has such function?
E.g. if we use 'select for update', then the application will be hang on there until first guy release or rollback the record and we don't like it.
Do you have any other solutions? Thank you for your helps.
November 07, 2003 - 8:11 am UTC
the application would use "select for update NOWAIT"
if you get it -- you got it. if you don't get it, you don't got it.
Making a sequence generator using autonomous transactions
Mohini, May 25, 2004 - 11:19 pm UTC
9.2.0.5
Tom,
I am dealing with a third party software (sitting on Oracle Database).
This software does not use sequences to generate unique numbers for their
tables. Instead..it uses a single table with two columns like this:
CREATE TABLE NEXTKEY
(
KEY_NAME VARCHAR2(30 BYTE) NOT NULL,
KEY_SEQ_NUM NUMBER(10) NOT NULL
);
Key_name column contains concatenated values of a table name and the column that
needs next sequence number...for example EMP_EMPNO..
and KEY_SEQ_NUM is the current number.
So, I wrote this function to create the next key:
CREATE
FUNCTION NEXT_KEY_GET (TABLE_NAME_P VARCHAR2,
COLUMN_NAME_P VARCHAR2)
RETURN NEXTKEY.KEY_SEQ_NUM%TYPE
AS
NEXT_KEY_V NEXTKEY.KEY_SEQ_NUM%TYPE;
REC_COUNT_V PLS_INTEGER;
KEY_NAME_V VARCHAR2(100);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
KEY_NAME_V := UPPER(TABLE_NAME_P) || '_' || UPPER(COLUMN_NAME_P);
SELECT COUNT(*) INTO REC_COUNT_V
FROM NEXTKEY
WHERE UPPER(KEY_NAME) = KEY_NAME_V;
IF REC_COUNT_V = 1 THEN
UPDATE NEXTKEY
SET KEY_SEQ_NUM = KEY_SEQ_NUM + 1
WHERE UPPER(KEY_NAME) = KEY_NAME_V;
COMMIT;
SELECT KEY_SEQ_NUM INTO NEXT_KEY_V
FROM NEXTKEY
WHERE UPPER(KEY_NAME) = KEY_NAME_V;
END IF;
RETURN NEXT_KEY_V;
END NEXT_KEY_GET;
Concerns:
After the update statement updates KEY_SEQ_NUM to next value..
then selects the same value from the nextkey table...
during that time..some other session might also run this update statement..
and the function might return that value...
So, then two sessions might return the same value
Is there a more robust way to do this?
Thanks.
May 26, 2004 - 7:40 am UTC
you have the commit in the wrong place.
but this should be a single line of code for the transaction:
ops$tkyte@ORA9IR2> create or replace function get_val( p_tname in varchar2, p_cname in varchar2 ) return number
2 as
3 pragma autonomous_transaction;
4 l_next_val number;
5 begin
6 update nextkey
7 set key_seq_num = key_seq_num+1
8 where key_name = upper(p_tname) || '_' || upper(p_cname)
9 return key_seq_num into l_next_val;
10
11 if ( sql%rowcount <> 1 )
12 then
13 raise program_error;
14 end if;
15
16 commit;
17 return l_next_val;
18 end;
19 /
Function created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_output.put_line( get_val( 't', 'x' ));
1
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_output.put_line( get_val( 't', 'x' ));
2
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_output.put_line( get_val( 't', 'y' ));
BEGIN dbms_output.put_line( get_val( 't', 'y' )); END;
*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.GET_VAL", line 13
ORA-06512: at line 1
Program_error
A reader, May 26, 2004 - 9:06 am UTC
11 if ( sql%rowcount <> 1 )
12 then
13 raise program_error;
14 end if;
Where is program_error coming from..there wasn't an exception declared...
Also...Do we have to raise an error?
An error would stop all the processing...where as if we can write it some how that...the function will return a value...if it finds only one row to update..like I guess
we can do IF SQL%ROWCOUNT = 1 THEN
RETURN THE_VARIABLE_INSERTED_INTO;
ELSE
RETURN A_NULL_VARIABLE;
END IF;
This is running through a batch insert over night...so I want to log the errors...more than raising them.
Thanks.
May 26, 2004 - 11:40 am UTC
program_error is a builtin one.
if we didn't find a row, someone called us with a sequence that does not exist -- how could you possibly continue processing?????? it is an error, it must be raised, it should be raised, if not raised -- you'll just get an error elsewhere (assuming that this sequence function is use to generate keys and keys cannot be null)
You'll have moved the error to another piece of code, and when it happens you'll have 5 people sitting staring at a piece of code wondering "how could that be null there?" instead of looking at the piece of code that actually found the error but chose to ignore it.
The caller will FAIL anyway. You'll have moved the error elsewhere.
Do you really have code that is expected to call this with an undefined sequence value???? seems fishy to me.
logging vs raising
Mohini, May 26, 2004 - 12:32 pm UTC
>Do you really have code that is expected to call this >with an undefined sequence
>value???? seems fishy to me.
O.K. The nextkey table is owned by this third party software...and it could happen that this nextkey table have same combination of table and column twice (they have it as a primary key but all lower case)...so we will get two values for a table and column combination...or it could be that a table and column combination does not exist in that table.
Considering this is a batch process that will run overnight..inserting values into these tables...
We do want to know that a record could not be inserted because..a next key could not be built..and a reason...
"BUT" we don't want to stop the whole process...for few values...that can be dealt with later with an exception report.
May 26, 2004 - 3:59 pm UTC
but the batch will stop won't it. the batch would get a null, null would fail -- what then?
seems fishy, seems flaky, seems the third party vendor should be involved....
but -- it is just code, you can code anything you want. me, i'd want failure.
A reader, November 01, 2004 - 5:24 pm UTC
Tom,
I've read your book ... It is said that deadlocks are very rare, harmless unless there is unindexed fk.
After going through the examples above, I think deadlock occurs in these situations
1)
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> declare
2 cursor c1 is
3 select PRS_WOO_PRCS_ID,prs_sts
4 from prcs_sts
5 where PRS_WOO_PRCS_ID = 'PF31'
6 for update of prs_sts;
7 begin
8 for c1_rec in c1 loop
9 update prcs_sts
10 set prs_sts = 'Y'
11 where current of c1;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9I.WORLD> declare
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 cursor c1 is
4 select PRS_WOO_PRCS_ID,prs_sts
5 from prcs_sts
6 where PRS_WOO_PRCS_ID = 'PF31'
7 for update of prs_sts;
8 begin
9 for c1_rec in c1 loop
10 update prcs_sts
11 set prs_sts = 'Y'
12 where current of c1;
13 end loop;
14 end;
15 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
ORA-06512: at line 9
-- Had there been a commit in the first procedure the deadlock wouldn't have occured i.e
end loop;
COMMIT;
end;
yes OR no?
then, is it possible that deadlocks sometimes occur when "COMMIT" is not used, delayed? Then, it is contrary to the Oracle principle that commit should be delayed ...
2) The poster (in one example above) didn't give testcase, example and he says by not including a pk deadlock woudl occur.
-- AND PRS_DATE = '28-MAR-02'
Could you please clarify?
3) If "select * from <tablename> for update" is used (without nowait), the other session might BLOCK. nowait will return an 054 error. But why did your example result in a deadlock?
ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from v for update;
X Y
---------- ----------
1 1
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 pragma autonomous_transaction;
3 begin
4 update t1 set x = 1;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
November 02, 2004 - 6:57 am UTC
1) I do not believe I said "harmless"
You used an autonomous transaction. That "deadlock" was a "self deadlock". if you use two SESSIONS -- you would have had traditional "blocker/blockee" issues -- not a deadlock. Only because of the fact that you were blocking YOURSELF was a deadlock raised. That is not a real world "issue" here, if this happens in your application (via the use of autonomous transaction) that is most definitely an "application bug". You did it to yourself.
Oracles principle is NOT (most definitely NOT) that you should 'delay' a commit. Not at all. The only principle is that a transaction should be:
a) as short as it can be
b) AS LONG AS IT NEEDS
that is, do not commit a partial transaction "because it is easier on the database" -- that would be a really bad idea (but a common practice in many other databases -- a very very bad practice). You commit immediately after executing the last statement in the series of statements that constitute your transaction. Not a moment before
2) i never got a test case, an example, so there is nothing i can clarify.
3) because i used an autonomous transaction. You cannot "block yourself" indefinitely -- it would just sit there forever!
A reader, November 02, 2004 - 12:12 pm UTC
Hi Tom,
When will an insert statement cause a deadlocked session? Also, update and delete statements. Please give some examples
Also, can a column have a unique as well as nonunique key set on it?
November 03, 2004 - 5:42 am UTC
update and delete left as an exercise for you -- all you need to do is:
session 1: lock (update/delete) primary_key=1
session 2: lock (update/delete) primary_key=2
session 1: try to lock primary_key=2
session 2: try to lock primary_key=1
here is the insert:
ops$tkyte@ORA9IR2> create table t1( x int primary key);
Table created.
ops$tkyte@ORA9IR2> create table t2( x int primary key);
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 values ( 1 );
1 row created.
ops$tkyte@ORA9IR2> prompt in another session insert into t2 values ( 1);
in another session insert into t2 values ( 1)
ops$tkyte@ORA9IR2> pause
ops$tkyte@ORA9IR2> prompt in another session insert into t1 values ( 1);
in another session insert into t1 values ( 1)
ops$tkyte@ORA9IR2> insert into t2 values (1);
insert into t2 values (1)
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
A reader, November 02, 2004 - 12:31 pm UTC
Tom,
Strangely, why wouldn't the script that detects unindexed foreign keys from your site wouldn't show one unindexed foreign key even though there is one!
November 03, 2004 - 5:48 am UTC
I have a better one in the book :)
that should find them all (assuming reasonable key lengths of 8 or less)
tkyte@TKYTE816> select table_name, constraint_name,
2 cname1 || nvl2(cname2,','||cname2,null) ||
3 nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
4 nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
5 nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
6 columns
7 from ( select b.table_name,
8 b.constraint_name,
9 max(decode( position, 1, column_name, null )) cname1,
10 max(decode( position, 2, column_name, null )) cname2,
11 max(decode( position, 3, column_name, null )) cname3,
12 max(decode( position, 4, column_name, null )) cname4,
13 max(decode( position, 5, column_name, null )) cname5,
14 max(decode( position, 6, column_name, null )) cname6,
15 max(decode( position, 7, column_name, null )) cname7,
16 max(decode( position, 8, column_name, null )) cname8,
17 count(*) col_cnt
18 from (select substr(table_name,1,30) table_name,
19 substr(constraint_name,1,30) constraint_name,
20 substr(column_name,1,30) column_name,
21 position
22 from user_cons_columns ) a,
23 user_constraints b
24 where a.constraint_name = b.constraint_name
25 and b.constraint_type = 'R'
26 group by b.table_name, b.constraint_name
27 ) cons
28 where col_cnt > ALL
29 ( select count(*)
30 from user_ind_columns i
31 where i.table_name = cons.table_name
32 and i.column_name in (cname1, cname2, cname3, cname4,
33 cname5, cname6, cname7, cname8 )
34 and i.column_position <= cons.col_cnt
35 group by i.index_name
36 )
37 /
A reader, November 02, 2004 - 12:50 pm UTC
Sorry, please ignore above post. I realized the column was not fk at all.
A reader, November 02, 2004 - 3:09 pm UTC
Tom,
1.Will deadlock occur only when plain sql statements are used? it is rare or wouldn't occur with for update (waits and nowaits)
2. I have a deadlock scenario where a user interface is deadlocking with some process, and I looked at all the queries related to deadlock (only the deadlock queries). Some of them is INSERT statements and some updates and deletes. I think INSERT should never result in a deadlock.
And I come to a conclusion that the application(s) are not using locking. Especially the user interface application is not using locking at all becuse I see plain sql statements in it. All the foreign keys are indexed.
Could you please comment?
November 03, 2004 - 6:32 am UTC
1) what are "plain" sql statements?!?
2) you think 'wrong', example is above.
you came to the wrong conclusion -- without locks, there could be no deadlocks!
A reader, November 03, 2004 - 10:18 am UTC
Tom,
The plain sqls I talked about are simple select statements with no for update, wait, nowait clauses. One application that is involved in deadlocks is using such sqls. All the queries use bind variables. The select statement is used only as a part of 'insert into select x, y, z from ...' format. I think there is no intention for locking. I agree locks are necessary for deadlocks but the sqls doesn't seem to acquire locks. Then how is it possible that deadlocks are occuring despite the reason that deadlocks are rare, will be taken care etc ....
Why are the inserts and updates resulting in deadlocks? What could the other app (process) be doing? Any comments will be helpful and appreciated.
November 03, 2004 - 10:36 am UTC
well, that is the problem with "made up terminology" -- "for update" isn't my definition of "fancy sql"
queries do not deadlock.
the inserts are. the inserts certainly lock.
i gave you an example of inserts deadlocking straight above! you have unique keys and you are duplicating values.
A reader, November 03, 2004 - 10:46 am UTC
Thank you Tom.
deadlock
Edgar, November 03, 2004 - 11:36 am UTC
In post above:
> ORA-00060: deadlock detected while waiting for resource
>
> It did not work ...
Can it be caused because of pctfree=0 maxtrans=1 during table/index creation, i.e. deadlock on block, not row level?
How presence of primary key affects locking behaviour (on block level)?
November 04, 2004 - 1:28 am UTC
i hate guessing, I'd much rather have a testcase. then we can just look and "see"
maxtrans = at least 2 for all segments in 9i.
A reader, November 22, 2004 - 3:50 pm UTC
Suppose we use %rowtype?
Will, December 06, 2004 - 5:10 am UTC
Hi Tom,
I've learned a lot about locking from reading your books. Perhaps you could clarify a point about the use of %rowtype and how it might cause an update of a primary key?
Now suppose we do something like this:
declare MyBooking Booking%rowtype;
select * into MyBooking where BookingPK = x for update;
MyBooking.CheckoutDate = '25 December 2004';
update Booking set row = NewBooking where BookingId = MyBooking.BookingPK;
Does that result in an update of the primary key? It certainly looks like it. Would you consider this to be an unsound way to use rowtype? How could I improve on it?
December 06, 2004 - 11:58 am UTC
declare
l_rec t%rowtype;
begin
select * into l_rec from t where rownum=1;
update t set row = l_rec where user_id = l_rec.user_id;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 0 0 1
********************************************************************************
UPDATE T SET USERNAME = :B1 ,USER_ID = :B2 ,CREATED = :B3
WHERE
USER_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 7 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 0 7 3 1
it updates all columns. If you have an unindexed foreign key, this'll be an issue concurrency wise. (index them)
Me, I won't be using this syntactic sugar - I'll set the columns I want to set, period.
Dynamic cursor using a db link
ht, November 03, 2005 - 6:03 pm UTC
Tom,
Is it possible to pass a db link into a stored proc to return usernames in that instance?
create or replace procedure test4 (in_dblink varchar2)
as
cursor c1 is select *from dba_users@'||in_dblink;
begin
for i in c1
loop
dbms_output.put_line(i.username);
end loop;
end;
SQL> show errors
Errors for PROCEDURE TEST4:
LINE/COL ERROR
-------- ------------------------------------------------
3/14 PL/SQL: SQL Statement ignored
3/38 PL/SQL: ORA-01729: database link name expected
TIA,
ht
November 04, 2005 - 3:32 am UTC
Delete query taking too much time
Divya, November 10, 2009 - 4:08 am UTC
Hi TOM,
I want to delete from one table which is taking too much time. Its primary key is used in many tables as foriegn key and all table are indexed. Please tell me how to reduce time.
Thanks.
November 11, 2009 - 3:07 pm UTC
you give me nothing to work with here. You don't tell me why it needs to go faster (is it blocking something? If not, why do you care?)
you don't have any sizing
you don't tell me how many child tables
you don't tell me what the access plan for the delete is
you don't tell me how many rows you are trying to remove
you don't tell us what the goal is
Query to find unindexed foreign keys
Lal, December 14, 2009 - 1:03 am UTC
Dear Tom,
The query you posted in this link for finding the unindexed foreign keys is working fine in 10g
but failing in 9i with the following error
"ORA-00979 not a group by expression."
Any idea what is the reason for this.
December 14, 2009 - 9:30 am UTC
ops$tkyte%ORA9IR2> edit
Wrote file afiedt.buf
1 select table_name, constraint_name,
2 cname1 || nvl2(cname2,','||cname2,null) ||
3 nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
4 nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
5 nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
6 columns
7 from ( select b.table_name,
8 b.constraint_name,
9 max(decode( position, 1, column_name, null )) cname1,
10 max(decode( position, 2, column_name, null )) cname2,
11 max(decode( position, 3, column_name, null )) cname3,
12 max(decode( position, 4, column_name, null )) cname4,
13 max(decode( position, 5, column_name, null )) cname5,
14 max(decode( position, 6, column_name, null )) cname6,
15 max(decode( position, 7, column_name, null )) cname7,
16 max(decode( position, 8, column_name, null )) cname8,
17 count(*) col_cnt
18 from (select substr(table_name,1,30) table_name,
19 substr(constraint_name,1,30) constraint_name,
20 substr(column_name,1,30) column_name,
21 position
22 from user_cons_columns ) a,
23 user_constraints b
24 where a.constraint_name = b.constraint_name
25 and b.constraint_type = 'R'
26 group by b.table_name, b.constraint_name
27 ) cons
28 where col_cnt > ALL
29 ( select count(*)
30 from user_ind_columns i
31 where i.table_name = cons.table_name
32 and i.column_name in (cname1, cname2, cname3, cname4,
33 cname5, cname6, cname7, cname8 )
34 and i.column_position <= cons.col_cnt
35 group by i.index_name
36* )
ops$tkyte%ORA9IR2> /
no rows selected
ops$tkyte%ORA9IR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
need help reproducing. this is a big page, please be explicit as to the query in question.
select for update and instead of triggers
Kevin, January 02, 2010 - 6:21 pm UTC
HI Tom.
In the original question about locking and SELECT FOR UPDATE, you provided an example of a select for update against a view and how locking happened to the rows selected from the view. I have noted however that when an INSTEAD-OF-TRIGGER is applied to the view, locking does not occur when the same select for update is issued.
I have noted that the contents of the instead-of-trigger itself is not material to the loss of locking. As such the following trigger is sufficient to demonstrate the situation.
create or replace trigger io_on_view
instead of insert or update or delete on your_view_name_here
for each row
begin null;
end;
/
Is this expected behavior? Said another way, is it thus so that select for update will not acquire row locks when the select is issued against a view with instead of triggers, or have I made a mistake somewhere in my work?
Thanks, Kevin CT USA
January 04, 2010 - 11:51 am UTC
are you sure about that?
ops$tkyte%ORA10GR2> create table t
2 as
3 select * from all_users;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace view v
2 as
3 select * from t;
View created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace trigger t_trigger
2 instead of insert or update or delete on v
3 for each row
4 begin
5 null;
6 end;
7 /
Trigger created.
ops$tkyte%ORA10GR2> select * from v where user_id = uid for update;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
OPS$TKYTE 563 28-SEP-09
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
2 pragma autonomous_transaction;
3 l_rec v%rowtype;
4 begin
5 select * into l_rec from v where user_id = uid for update;
6 commit;
7 end;
8 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 5
must be my test case, I'll see what I did wrong
Kevin Meade, February 12, 2010 - 1:47 pm UTC
I can't argue with your example. I'll check my test cases and see what I was doing wrong. Must have been a real stupid error on my part (what other kind are there?).
Thanks, this makes me feel way better.
Kevin
composite index scenario
john Bricks, March 18, 2010 - 11:52 am UTC
Tom,
Your script above lists the foreign key columns even though they are part of a composite index. Isn't it suffice to just have the column part of some composite index instead of an index just for itself.
As an example:
create table A(a_id int, val varchar2(32), constraint pk_a_id primary key (a_id));
create table B(b_id int, val varchar2(32), constraint pk_b_id primary key (b_id));
create table C(a_id int, b_id int,
constraint fk_a_id foreign key (a_id) references A(a_id),
constraint fk_b_id foreign key (b_id) references B(b_id));
create index idx_fk_c on C (a_id,b_id);
wouldn't idx_fk_c be suffice or do we need individual indexes on a_id & b_id in table C?
Thanks!!!
March 18, 2010 - 12:17 pm UTC
they need to be on the leading edge
You need, for table c, any set of indexes such that:
it starts with a_id
it starts with b_id
Re: Lock 2nd users for view only
Marcin Jackowski, March 25, 2010 - 7:57 am UTC
there was discussion about two separate sessions locking each other while trying to concurently update same rows.
you've recommended using SELECT ... FOR UPDATE NOWAIT to avoid second session waiting for the first one to complete (with commit).
Is there possibility to do the same with UPDATE, something like UPDATE ... NOWAIT ? or maybe using some deffered constraints would do the thing?
Point is that not in every case the second session could issue SELECT, there could be a case second session is doing direct UPDATE and application (e.g. sqlplus) should report (dead)lock to the user immediatelly.
March 26, 2010 - 1:39 pm UTC
well, think about it this way.
If you have two sessions that are both updating the same rows, and they haven't even read the data out, don't you also have a serious issue with lost updates?
seems to me incorrect application logic if you have this condition - don't you think?
Deadlocks in recursive pl/sql procedure
Kubilay, June 04, 2010 - 8:06 am UTC
Hi Tom
I am testing after how many ORA-00060 errors a recursive transaction aborts. This is a transaction which calls itself recursively when it fails, and is in a deadlock state with another transaction which also calls itself recursively when it fails.
That is, is there a limit when 2 such deadlocked procedures will give up? Is this because of recursive calls or deadlocks?
What I am seeing from the tests below is that
On Oracle 10g the number of ORA-00060 errors recorded before these procedures abort completely with ORA-00060 is significantly less than the number of errors recorded in Oracle 11g.
For Oracle 11g with 300K ORA-00060 errors in the logs
For Oracle 10g with 5K ORA-00060 errors in the logs
What inspired me to investigate this, is failures I got in batch jobs after running the same recursive procedure in 10 different sessions simultaneously updating the same table (kind of DIY parallelism) which caused deadlocks.
These 10 procedures are quite lengthy pieces of code with ‘SELECT FOR UPDATE… ‘ and ‘INSERT… ‘ AND ‘UPDATE…’ statements. Normally these 10 procedures, would complete the batch job and update the table even when producing deadlocks. Occasionally, very rarely would just ‘hang’ (fail!?) do nothing. When I look at the logs I see that they fail when they produce excessive amount of deadlocks? Is there a limit in Oracle on the amount of ORA-00060 errors that it could generate, before giving up? Or are my procedures failing because there is a limit in the amount of recursive calls a PL/SQL procedure can make?
Thank you very much for all your help.
Here is my test case:
-- an error looging table
-- drop table ERROR_LOG;
CREATE TABLE ERROR_LOG
(
LOG_TIMESTAMP TIMESTAMP (6),
ERR VARCHAR2(4000 byte)
);
-- create two test tables TC1, TC2
-- drop table tc1;
CREATE TABLE TC1
(
id NUMBER,
TYPO VARCHAR2(2000 byte)
);
-- drop table tc2;
CREATE TABLE TC2
( id NUMBER, TYPO VARCHAR2(2000 byte)
);
-- populate the tables WITH sample data:
INSERT INTO TC1
SELECT rownum,OBJECT_TYPE FROM ALL_OBJECTS;
INSERT INTO TC2
SELECT rownum, OBJECT_TYPE FROM ALL_OBJECTS;
-- create autonomous error logging procedure
-- drop procedure LOG_DEADLOCK;
CREATE OR REPLACE
PROCEDURE LOG_DEADLOCK
AS
-- error logger
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT
INTO ERROR_LOG
(
LOG_TIMESTAMP,
ERR
)
VALUES
(
systimestamp,
'Deadlock'
);
COMMIT;
END;
-- drop procedure rec_locks1;
-- create 1st procedure
CREATE OR REPLACE
PROCEDURE REC_LOCKS1
AS
DEADLOCK_DETECTED EXCEPTION;
PRAGMA EXCEPTION_INIT
(
DEADLOCK_DETECTED, -60
)
;
BEGIN
-- update records on tc1
UPDATE TC2 SET TYPO =TYPO WHERE id < 16000;
DBMS_LOCK.SLEEP(5);
-- update records on tc2
UPDATE TC1 SET TYPO =TYPO WHERE id < 16000;
EXCEPTION
WHEN DEADLOCK_DETECTED THEN
-- log deadlock with autonomous transaction
LOG_DEADLOCK;
-- restart the procedure
REC_LOCKS1;
END;
-- create 2nd procedure
-- drop procedure rec_locks2
CREATE OR REPLACE
PROCEDURE REC_LOCKS2
AS
DEADLOCK_DETECTED EXCEPTION;
PRAGMA EXCEPTION_INIT(DEADLOCK_DETECTED, -60);
BEGIN
-- update records on tc1
UPDATE TC1 SET TYPO =TYPO WHERE id < 16000;
DBMS_LOCK.SLEEP(5);
-- update records on tc2
UPDATE TC2 SET TYPO =TYPO WHERE id < 16000;
EXCEPTION
WHEN DEADLOCK_DETECTED THEN
-- log deadlock with autonomous transaction
LOG_DEADLOCK;
-- restart the procedure
REC_LOCKS2;
END;
-- in two separate session execute the procedures
-- session 1
ALTER session SET MAX_DUMP_FILE_SIZE='1m'
EXEC REC_LOCKS1
-- session2
ALTER session SET MAX_DUMP_FILE_SIZE='1m' EXEC
REC_LOCKS2
-- see the amount of deadlocks these two procedures create
SELECT err,
COUNT(*)
FROM error_log
GROUP BY err
June 09, 2010 - 7:37 am UTC
your logic here is somewhat (no, not somewhat, it just is) questionable.
Where are your rollbacks? If you get the deadlock on the second update, the first update will have "worked" and won't have rolled back at all. You'll be doing the same exact work over and over and over again.
I'm always confounded by this sort of a question - basically "an error happens, we know it is because of a design flaw in our application, but how can we make Oracle ignore this error so our flawed thing - which we admit is flawed - can run to completion a few hours or days from now"
and recursion doesn't seem to be the way to go here
for i in 1 .. number_of_retries
loop
begin
do work
commit;
exit;
exception
when deadlock
then
record_error;
rollback;
end;
end loop;would be. Potentially infinite recursion such as you have is bad because:
o it is potentially infinite. bad bad really bad idea. stop after a reasonable number of retries.
o every call will allocate more stack, you'll blow memory easily at some point.
o you are building a huge error stack with each call that gets larger
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_LOCK", line 201
ORA-06512: at "OPS$TKYTE.REC_LOCKS1", line 7
ORA-06512: at "OPS$TKYTE.REC_LOCKS1", line 12
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "OPS$TKYTE.REC_LOCKS1", line 12
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "OPS$TKYTE.REC_LOCKS1", line 12
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "OPS$TKYTE.REC_LOCKS1", line 12
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "OPS$TKYTE.REC_LOCKS1", line 12
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "OPS$TKYTE.REC_LOCKS1", line 12
each recursive call makes this larger
So, it is NOT that the deadlock eventually becomes "so bad to us that we deadlock error out", it is that you are finally exhausting some resource (stack space) after trying to recurse an infinite number of times - a recursion that once STARTED, cannot never be resolved in your case.
Look at your logic - if both routines do their first update, your 'logic' is such that the deadlock CAN NEVER BE RESOLVED - ever. You have programmed an infinite loop without using a loop
Your logic needs to be reworked as it quite simply has zero chance of working in real life.
script for foreign keys
sam, February 15, 2011 - 3:58 pm UTC
Tom:
Is this an internal bug or something with script?
Any workarounds?
select table_name, constraint_name,
2 cname1 || nvl2(cname2,','||cname2,null) ||
3 nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
4 nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
5 nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
6 columns
7 from ( select b.table_name,
8 b.constraint_name,
9 max(decode( position, 1, column_name, null )) cname1,
10 max(decode( position, 2, column_name, null )) cname2,
11 max(decode( position, 3, column_name, null )) cname3,
12 max(decode( position, 4, column_name, null )) cname4,
13 max(decode( position, 5, column_name, null )) cname5,
14 max(decode( position, 6, column_name, null )) cname6,
15 max(decode( position, 7, column_name, null )) cname7,
16 max(decode( position, 8, column_name, null )) cname8,
17 count(*) col_cnt
18 from (select substr(table_name,1,30) table_name,
19 substr(constraint_name,1,30) constraint_name,
20 substr(column_name,1,30) column_name,
21 position
22 from user_cons_columns ) a,
23 user_constraints b
24 where a.constraint_name = b.constraint_name
25 and b.constraint_type = 'R'
26 group by b.table_name, b.constraint_name
27 ) cons
28 where col_cnt > ALL
29 ( select count(*)
30 from user_ind_columns i
31 where i.table_name = cons.table_name
32 and i.column_name in (cname1, cname2, cname3, cname4,
33 cname5, cname6, cname7, cname8 )
34 and i.column_position <= cons.col_cnt
35 group by i.index_name
36* )
SQL> /
from user_ind_columns i
*
ERROR at line 30:
ORA-00600: internal error code, arguments: [12327], [16], [2], [17], [], [], [], []
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production PL/SQL Release 9.2.0.2.0 - Production
CORE 9.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.2.0 - Production NLSRTL Version 9.2.0.2.0 - Production
5 rows selected.
February 15, 2011 - 5:09 pm UTC
SQL> select table_name, constraint_name,
2 cname1 || nvl2(cname2,','||cname2,null) ||
3 nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
4 nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
5 nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
6 columns
7 from ( select b.table_name,
8 b.constraint_name,
9 max(decode( position, 1, column_name, null )) cname1,
10 max(decode( position, 2, column_name, null )) cname2,
11 max(decode( position, 3, column_name, null )) cname3,
12 max(decode( position, 4, column_name, null )) cname4,
13 max(decode( position, 5, column_name, null )) cname5,
14 max(decode( position, 6, column_name, null )) cname6,
15 max(decode( position, 7, column_name, null )) cname7,
16 max(decode( position, 8, column_name, null )) cname8,
17 count(*) col_cnt
18 from (select substr(table_name,1,30) table_name,
19 substr(constraint_name,1,30) constraint_name,
20 substr(column_name,1,30) column_name,
21 position
22 from user_cons_columns ) a,
23 user_constraints b
24 where a.constraint_name = b.constraint_name
25 and b.constraint_type = 'R'
26 group by b.table_name, b.constraint_name
27 ) cons
28 where col_cnt > ALL
29 ( select count(*)
30 from user_ind_columns i
31 where i.table_name = cons.table_name
32 and i.column_name in (cname1, cname2, cname3, cname4,
33 cname5, cname6, cname7, cname8 )
34 and i.column_position <= cons.col_cnt
35 group by i.index_name
36 )
37 /
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
COLUMNS
--------------------------------------------------------------------------------
C SYS_C003312
X
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production
might be a (really really old) 9.2.0.2 issue.
Chandu, June 15, 2011 - 11:45 am UTC
Tom ,I'm a rookie in terms of posting if there is any mistake in my post that is not intentional.
Below is my stored procedure which is call by an applivation.What it does is it provides the WF count say 500 or 1000 it gets app_id is the cursor.
CREATE OR REPLACE PROCEDURE DIRECTDEV.WF_Unprocessed
(
WFCount IN NUMBER,
R1 out sys_refcursor
)
AS
cursor R2 is
Select
A.app_id as appid
From
Application A
Where
A.App_Status_Cd='1'
And
(A.WFDTTM < To_date(TO_CHAR(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY') or A.WFDTTM is null)
And
ROWNUM <= appCount
order by app_id desc for update of WFDTTM;
app R2%rowtype;
appid number;
BEGIN
open R1 for
Select
A.app_id as appid
From
Application A
Where
A.App_Status_Cd='1'
And
(A.WFDTTM < To_date(TO_CHAR(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY') or A.WFDTTM is null)
And
ROWNUM <= appCount
order by app_id desc;
for r_R2 in R2 loop
update Application A
Set
A.WFDTTM = sysdate
Where
A.App_ID in r_R2.appid;
end loop;
END WF_Unprocessed;
/
But recently I'm getting dead locks when there is other application trying to update the same app_id.
This is the deadlock message I'M getting
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "WF_UNPROCESSED", line 9
ORA-06512: at "WF_UNPROCESSED", line 37
ORA-06512: at line 1
Please advice what modifications I need to do in Stored procedure to avoid deadlocks
June 17, 2011 - 1:06 pm UTC
Where
A.App_Status_Cd='1'
And
(A.WFDTTM < To_date(TO_CHAR(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY') or
A.WFDTTM is null)
And
ROWNUM <= appCount
order by app_id desc for update of WFDTTM;
that where clause set off alarms bells for me. You do know that it gets appCount (random) records AND THEN sorts them. The two records it gets will match the where clause - but they WON'T be the oldest records (except by pure accident).
In general, that should be:
select * from (your query and order by without the rownum predicate) where rownum <= appCount.
I'm not surprised this gets deadlocks. Each process would get some random set of records and then start processing them - they are processing similar data - some of the same rows.
You don't think that the R2 and R1 cursors contain the same result set do you? Please say no....
Also, please don't do this:
To_date(TO_CHAR(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY')
Just do this:
trunc(sysdate)
that'll strip off the time component if that is what you wanted.
Tell us, in english - not code that has some questionable things in it, exactly and precisely what the specification of this routine is to be. Then we can tell you how to do it.
Need a suggestion
Prageeth, July 04, 2011 - 7:46 am UTC
Hi TOM,
I have a situation like this, I have to develop a HR systems which includes a page to enter user to the DB. There can be multiple admins who can perform the task. Here what I do is get the maximum EMP_NUMBER which is the PK and increments sequentially, and insert the record, But when two users try to insert a record at the same time if fires a PK_violation exception, the reason is both have the same the max EMP_NUMBER with them. I used "select for update",then only one can insert at a time but after he is commit the other one still has the same EMP_NUMBER, but this doesn't happen in MSSQL, it prevent the other user from executing 'SELECT MAX(emp_number) from Emplpyee' statement, can you suggest me a solution?
Im sorry if this is not related to the current thread,,
Thanx a lot
July 05, 2011 - 11:33 am UTC
create sequence emp_seq;
insert into table ... vlaues ( emp_seq.nextval, .... );
read about sequences - that is what you are looking for.
Records Locked by a User / Who has a Specific Record Locked
Jeff, July 11, 2011 - 7:45 am UTC
It seems that every question similar to mine has to do with blocking. My specific problem is not that there is blocking, but that a user says he is (almost) always receiving a message from the application saying the record is locked by another user/process. The user is trying to lock a specific row in a table.
ex. select * from employee where employee_index = 32 for update nowait;
I would like to be able to look at v$lock, v$session, dba_ojects, and where ever else necessary to be able to say the Oracle user JOHNP has the record locked on machine PCJOHN in application SQLDEVELOPER, or something of that nature.
Thanks in advance for your help.
July 12, 2011 - 7:34 am UTC
You would have to do this:
a) in some session - attempt to lock employee_index = 32 for update WAIT
b) using some other session - query the v$ tables to see who it blocking you.
we do NOT keep a list of who has what rows locked anywhere - in order to find out who might be blocking you - you have to become blocked, then we can say who is doing the blocking of you.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:839412906735 shows you how to see how is blocking you if you do step (a)
Script for all objects
Andre, July 26, 2011 - 10:40 am UTC
What do you think of a script that can find foreign keys across all users that could cause blocking locks?
select owner, table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.owner,
b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position,
owner
from all_cons_columns ) a,
all_constraints b
where a.constraint_name = b.constraint_name
and b.owner = a.owner
and b.constraint_type = 'R'
and b.status = 'ENABLED'
group by b.owner, b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from all_ind_columns i
where i.table_owner = cons.owner
and i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
order by 1, 2, 3;
July 28, 2011 - 6:35 pm UTC
you'd really need to use the DBA_ views - since all_ views only show things YOU can see - which really isn't all things...
but sure - it would be useful if you have constraints in foreign schemas. I did not verify this query is correct - just that the concept is sound.
catch NOWAIT exception
a reader, August 29, 2011 - 2:07 pm UTC
Hi Tom,
So when do select for update nowait, it would throw exception if another session already acquired the row,but there is no predefined exception for error ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. If we want to catch this error, we would have to check SQLCODE, but if later Oracle changes the code, our application will stop working, what suggestion do you have?
Thanks
August 30, 2011 - 5:18 pm UTC
you use pragma exception init and you'll have to map it to the error code, that is the way it is done.
I would rank the chance of a code change very very very low on the probability scale, very very low - they don't really tend to change these due to the massive backwards compatibility issues.
...
is
resource_busy exception;
pragma exception_init( resource_busy, -54 );
begin
...
exception
when resource_busy....
index on more columns than Foreign key has columns
Branka, September 14, 2011 - 10:55 am UTC
If I have foreign key on one column (col1), and I have index on 3 columns (col1, col2, col3), query will not consider it as a problem.
Should I have second index just on col1 if it is foreign key, or it is fine if I have index that include that column?
September 15, 2011 - 7:26 am UTC
you just need an index with col1 on the leading edge - the index on col1,col2,col3 will do just fine, no need for another index.
Index on FK
Branka, September 15, 2011 - 12:59 pm UTC
Here is the case that I have.
I have PK that consists of 5 columns. (c1, c2, c3, c4, c5)
I have 3 FK. Each of FK is on only one column. (c1, c4, c6). C6 is not indexed at all.
When I run your script, I get FK that is on c4 and FK on c6 need to have index. Only FK on c1 do not need index.
I thought that I would need indexes
ID1 (c1, c2, c3, c4, c5)
ID2 (c1)
ID3 (c4)
ID4 (c5)
Why I do not need index on ID2 and I do need ID3?
September 16, 2011 - 1:28 pm UTC
Your index on (c1,c2,c3,c4,c5) already covers (c1) - you don't need another index on c1.
I'm repeating myself now, I already wrote that yesterday!!~!!
diff between 'for update" and "forupdate"
Biswaranjan, May 16, 2012 - 1:40 pm UTC
Hi Tom,
I was aware about "for update".when by mistake I used "forupdate" , it did not give any error (but did not lock
records).
I executed below query .
session1:
create table a as select level id from dual connect by
level<11;
commit;
SQL> select * from a forupdate;
A
----------
1
2
3
4
6
6
7
8
9
10
10 rows selected.
Then in session2:
SQL> update a set a=1 where a=2;
1 row updated.
How come it did not lock the records of table a in session2.
Can you please tell me where we should use "forupdate".
Thanks,
Biswaranjan.
May 17, 2012 - 2:46 am UTC
select * from a forupdate;
maybe this will make it clear:
select * from EMP e;
select * from a this_is_correlation_name;
select * from a santa_claus;
the word "forupdate" is just a correlation name, a table alias the way you used it.
Here is another interesting example that is sort of like what you did:
ops$tkyte%ORA11GR2> create table t ( x int );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> begin
2 for i in 1 .. 2
3 loop
4 insert into t (x) values ( i );
5 end loop
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select * from t;
X
----------
1
2
ops$tkyte%ORA11GR2> rollback;
Rollback complete.
ops$tkyte%ORA11GR2> select * from t;
no rows selected
ops$tkyte%ORA11GR2>
explain how rollback rolled back a committed transaction ;)
You have to write well formed code for the code to do what you think it should do.
thanks :) . missed it. :)
A reader, May 17, 2012 - 3:14 am UTC
thanks :) . missed it. :)
Biswaranjan, May 17, 2012 - 3:15 am UTC
select for update from different session :
Katy, May 21, 2012 - 2:45 am UTC
Hi Tom,
As per your earlier demo:
"
and we said...
I can only imagine that you made a mistake somewhere in your testing. It does not work
the way you describe. Using an autonomous transaction, I can demonstrate in a single
session that this works.
first, I'll fill up a table, then run the pf31 block. I'll re-run the pf31 block again
as an AUTONOMOUS (sub or nested transaction -- runs AS IF it were in another session)
transaction to show that it would be blocked. Then run the pf32 block successfully."
Varun- Just want to add that, before you try to run pf32 block successfully...i believe we have to first commit the transaction that updated pf31 block.Do let me know if mu understanding is correct.
Thanks
Katy
SELECT for update - any one record
Jay, July 19, 2012 - 10:39 pm UTC
Hi Tom,
We are working on a job-processing system. The jobs to be processed are kept in a table. Multiple java programs concurrently scan the table, each one takes one job that is waiting, processes it, and updated the record. How do we ensure that each java program takes only one job, and that is not being processed by another java program?
A simplified version of the table would be
CREATE TABLE "HR"."JB"
(
"ST" VARCHAR2(1 BYTE),
"ID" NUMBER(*,0)
)
Insert sample data
Insert into HR.JB (ST,ID) values ('W',1);
Insert into HR.JB (ST,ID) values ('W',2);
ST - is the column to hold status values (W- waiting, P- getting processed, S - success, F - failure)
ID - the job id
If one java program does a
select * from jb where st = 'W' and rownum =1 for update ;
and processes it, other jobs wait to acquire similar locks. So we tried executing an update immediately, setting the st column to P and committing. So other jobs will get a valid 'W' record quickly. Still, there is a very,very mall wait period for other jobs. Is there a better way? Can a SELECT statement fetch one record that is 'not locked' by another session?
July 30, 2012 - 7:27 am UTC
You should stop inventing the wheel. Queues are a long since solved problem, here is our implementation (works with JMS as well if you want to preserver the "java-ness" or your code for some reason...)
http://docs.oracle.com/cd/E11882_01/server.112/e11013/toc.htm or
<quote src=expert Oracle database architecture>
Was that the end of the story? No, not at all. My client still had a less than optimal solution on its hands. They still had to serialize on the “dequeue” of an unprocessed record. We could easily find the first unprocessed record—quickly— using select * from queue_table where decode( processed_flag,’N’,’N’) = ‘N’ FOR UPDATE, but only one session at a time could perform that operation. The project was using Oracle 10g and therefore could not yet make use of the relatively new SKIP LOCKED feature added in Oracle 11g Release 1. SKIP LOCKED would permit many sessions to concurrently find the first unlocked, unprocessed record, lock that record, and process it. Instead, we had to implement code to find the first unlocked record and lock it manually. Such code would in general look like the following in Oracle 10g and before. We begin by creating a table with the requisite index described above and populate it with some data:
ops$tkyte%ORA11GR2> create table t
2 ( id number primary key,
3 processed_flag varchar2(1),
4 payload varchar2(20)
5 );
Table created.
ops$tkyte%ORA11GR2> create index
2 t_idx on
3 t( decode( processed_flag, 'N', 'N' ) );
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t
2 select r,
3 case when mod(r,2) = 0 then 'N' else 'Y' end,
4 'payload ' || r
5 from (select level r
6 from dual
7 connect by level <= 5)
8 /
5 rows created.
ops$tkyte%ORA11GR2> select * from t;
ID P PAYLOAD
---------- - --------------------
1 Y payload 1
2 N payload 2
3 Y payload 3
4 N payload 4
5 Y payload 5
Then we basically need to find any and all unprocessed records. One by one we ask the database “Is this row locked already? If not, then lock it and give it to me.” That code would look like:
ops$tkyte%ORA11GR2> create or replace
2 function get_first_unlocked_row
3 return t%rowtype
4 as
5 resource_busy exception;
6 pragma exception_init( resource_busy, -54 );
7 l_rec t%rowtype;
8 begin
9 for x in ( select rowid rid
10 from t
11 where decode(processed_flag,'N','N') = 'N')
12 loop
13 begin
14 select * into l_rec
15 from t
16 where rowid = x.rid
17 for update nowait;
18 return l_rec;
19 exception
20 when resource_busy then null;
21 end;
22 end loop;
23 return null;
24 end;
25 /
Function created.
n Note In the above code, I ran some DDL—the CREATE OR REPLACE. Right before DDL runs, it automatically commits, so there was an implicit COMMIT in there. The rows we’ve inserted are committed in the database—and that fact is necessary for the following examples to work correctly. In general, I’ll use that fact in the remainder of the book. If you run these examples without performing the CREATE OR REPLACE, make sure to COMMIT first!
Now, if we use two different transactions, we can see that both get different records. We also see that both get different records concurrently (using autonomous transactions once again to demonstrate the concurrency issues):
ops$tkyte%ORA11GR2> declare
2 l_rec t%rowtype;
3 begin
4 l_rec := get_first_unlocked_row;
5 dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
6 end;
7 /
I got row 2, payload 2
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> declare
2 pragma autonomous_transaction;
3 l_rec t%rowtype;
4 begin
5 l_rec := get_first_unlocked_row;
6 dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
7 commit;
8 end;
9 /
I got row 4, payload 4
PL/SQL procedure successfully completed.
Now, in Oracle 11g Release 1 and above, we can achieve the above logic using the SKIP LOCKED clause. In the following example we’ll do two concurrent transactions again, observing that they each find and lock separate records concurrently.
ops$tkyte%ORA11GR2> declare
2 l_rec t%rowtype;
3 cursor c
4 is
5 select *
6 from t
7 where decode(processed_flag,'N','N') = 'N'
8 FOR UPDATE
9 SKIP LOCKED;
10 begin
11 open c;
12 fetch c into l_rec;
13 if ( c%found )
14 then
15 dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
16 end if;
17 close c;
18 end;
19 /
I got row 2, payload 2
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> declare
2 pragma autonomous_transaction;
3 l_rec t%rowtype;
4 cursor c
5 is
6 select *
7 from t
8 where decode(processed_flag,'N','N') = 'N'
9 FOR UPDATE
10 SKIP LOCKED;
11 begin
12 open c;
13 fetch c into l_rec;
14 if ( c%found )
15 then
16 dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
17 end if;
18 close c;
19 commit;
20 end;
21 /
I got row 4, payload 4
PL/SQL procedure successfully completed.
Both of the preceding “solutions” would help to solve the second serialization problem my client was having when processing messages. But how much easier would the solution have been if my client had just used Advanced Queuing and invoked DBMS_AQ.DEQUEUE? To fix the serialization issue for the message producer, we had to implement a function-based index. To fix the serialization issue for the consumer, we had to use that function-based index to retrieve the records and write code. So we fixed their major problem, caused by not fully understanding the tools they were using and found only after lots of looking and study since the system was not nicely instrumented.
</quote>
Great
Jayadevan, July 31, 2012 - 2:52 am UTC
Hi Tom,
"SKIP LOCKED" was an option I was completely unaware of. Thanks a lot.
July 31, 2012 - 12:36 pm UTC
use it carefully, use it the way I did above, it is tricky.
What to do if we get ora-00054 error
Sam To, November 19, 2013 - 10:59 pm UTC
Hi Tom,
We have an app which issues select ... for update nowait on a particular record in a table, and we are getting ORA-00054 errors (which developers don't expect as they claim there should be only 1 process updating that record).
We have been trying to debug what's wrong. We had tried a temporary patch that removes the nowait and then in a separate session run a script which queries v$session and monitors the sessions where blocking_session is not null every 100ms. So far we have not been able to track down who has the lock. The script we use is something like
declare
f1 utl_file.file_type;
begin
f1:= utl_file.fopen('MYLOG_DIR', 'monitor.log', 'A', 2000);
while (true) loop
for rec in (
select s.sid, p.spid, s.program, s.process, s.module, s.action, s.sql_id, s.prev_sql_id, s.plsql_object_id, s.plsql_subprogram_id, s.blocking_session, current_timestamp tmsp
from v$session s, v$process p
where s.blocking_session is not NULL
and s.paddr = p.addr) loop
utl_file.put_line(f1, 'BLOCKED: ' || rec.tmsp || ',' || rec.sid || ',' || rec.spid || ',' || rec.program || ',' || rec.process || ',' || rec.module || ',' || rec.action || ',' || rec.blocking_session);
if (rec.blocking_session is not null) then
for rec2 in (select s.sid, p.spid, s.program, s.process, s.module, s.action, s.sql_id, s.prev_sql_id, s.plsql_object_id, s.plsql_subprogram_id
from v$session s, v$process p
where sid = rec.blocking_session
and s.paddr = p.addr) loop
utl_file.put_line(f1, 'BLOCKER: ' || rec2.sid || ',' || rec2.spid || ',' || rec2.program || ',' || rec2.process || ',' || rec2.module || ',' || rec2.action);
end loop;
end if;
end loop;
dbms_lock.sleep(0.1);
end loop;
utl_file.fclose(f1);
end;
/
Is there a better way to debug this? We just want to figure out who has the lock if there is an ORA-00054 error, especially if the other session is holding the lock for a very short period of time?
Why do we need
A reader, May 26, 2017 - 9:28 am UTC
as per my knowledge Oracle Achieves the Row locks before transaction and release them Automatically once transaction gets completed then why do we need FOR UPDATE Clause to lock the rows?
May 27, 2017 - 6:37 am UTC
Lets say you wanted to keep a total salary val on the DEPT table for all of the child rows in the EMPLOYEE table.
To achieve that, I must serialize access on the EMPLOYEE table for the relevant department row, otherwise I'll get incorrect results.
So the process for updating a *employee* row would need to be:
- lock the relevant *department* row
- THEN allow the changes to the employee row
- THEN update the department row
Example
A reader, May 30, 2017 - 4:23 am UTC
Can you show one Example for above explanation.
May 31, 2017 - 2:13 am UTC
Here's an example that we commonly see - a failure to work correctly with the read consistency model. Assume we have a requirement to keep total salary by department under 15,000. Here is how some people may choose to implement that.
SQL> create table dept as select * from scott.dept;
Table created.
SQL> create table emp as select * from scott.emp;
Table created.
SQL>
SQL> alter table dept add tot_sal number;
Table altered.
SQL>
SQL> update dept d
2 set tot_sal =
3 ( select sum(sal) from emp
4 where deptno = d.deptno );
4 rows updated.
SQL>
SQL> select * from dept;
DEPTNO DNAME LOC TOT_SAL
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 8750
20 RESEARCH DALLAS 10875
30 SALES CHICAGO 9400
40 OPERATIONS BOSTON
4 rows selected.
SQL>
SQL> create or replace
2 procedure add_emp(p_empno number, p_ename varchar2, p_sal int, p_deptno int ) is
3 l_tot_sal int;
4 begin
5 select tot_sal + p_sal
6 into l_tot_sal
7 from dept
8 where deptno = p_deptno;
9
10 if l_tot_sal < 15000 then
11 insert into emp (empno, ename, sal, deptno)
12 values (p_empno, p_ename, p_sal, p_deptno);
13
14 update dept
15 set tot_sal = tot_sal + p_sal
16 where deptno = p_deptno;
17 else
18 raise_application_error(-20000,'Total dept salary limited exceeded');
19 end if;
20 end;
21 /
Procedure created.
SQL>
SQL> exec add_emp(1,'John',2000,20);
PL/SQL procedure successfully completed.
SQL> exec add_emp(2,'Sue',2000,20);
PL/SQL procedure successfully completed.
SQL> exec add_emp(3,'Mary',2000,20);
BEGIN add_emp(3,'Mary',2000,20); END;
*
ERROR at line 1:
ORA-20000: Total dept salary limited exceeded
ORA-06512: at "MCDONAC.ADD_EMP", line 17
ORA-06512: at line 1
SQL> select * from dept;
DEPTNO DNAME LOC TOT_SAL
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 8750
20 RESEARCH DALLAS 14875
30 SALES CHICAGO 9400
40 OPERATIONS BOSTON
At that point, all seems fine...and into Production that code goes. A month later, we do this:
SQL> select * from dept;
DEPTNO DNAME LOC TOT_SAL
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 8750
20 RESEARCH DALLAS 21875 <======= !!!!!
30 SALES CHICAGO 9400
40 OPERATIONS BOSTON
and can't think how this could possibly have happened....After all, our testing *looked* successful.
But of course, if I had done those three calls to ADD_EMP in three separate sessions, then each does not see each others uncommitted transactions, and thus the logic is broken.
They will all be *blocked* when they do the update on dept, but by then it is too late - we have already done our total salary check. Try it yourself - run the first too ADD_EMP in one session (without commit) and the third one in another session. It will block, but when you commit session 1, session 2 will *succeed* even though it should not.
We need to serialise the dept row access *before* we allow the insert. So we do this:
SQL> create or replace
2 procedure add_emp(p_empno number, p_ename varchar2, p_sal int, p_deptno int ) is
3 l_tot_sal int;
4 begin
5 select tot_sal + p_sal
6 into l_tot_sal
7 from dept
8 where deptno = p_deptno FOR UPDATE; <<=======
9
10 if l_tot_sal < 15000 then
11 insert into emp (empno, ename, sal, deptno)
12 values (p_empno, p_ename, p_sal, p_deptno);
13
14 update dept
15 set tot_sal = tot_sal + p_sal
16 where deptno = p_deptno;
17 else
18 raise_application_error(-20000,'Total dept salary limited exceeded');
19 end if;
20 end;
21 /
Dont get me wrong - I'm not saying that the procedure above is the *best* way to implement a total salary attribute on DEPT. I'm saying that the logic above is something very commonly seen in the application world, and often *without* the for-update clause as well :-(
perfect practical demo on need for FOR UPDATE
Durgaprasad, April 06, 2020 - 10:23 am UTC
So, I feel like all the cursors in anonymous/named blocks must be using FOR UPDATE, otherwise it can lead to data incorrectness as you shown in your example but then why FOR UPDATE is rarely used though its allowed to access the same table by different sessions for data update or to update other table based on the data in this table.
is this because of Developers lack of knowledge, or any serious issues with FOR UPDATE? pls explain.
Thanks.
April 07, 2020 - 2:30 pm UTC
I feel like all the cursors in anonymous/named blocks must be using FOR UPDATE
Why do you say that?
Generally you only need SELECT ... FOR UPDATE rows that you want to UPDATE (or possibly DELETE) later in the transaction. This helps avoid deadlocks when many sessions try and update the same rows in a different order.
If you only have queries or inserts, adding FOR UPDATE usually adds (unnecessary) bottlenecks in your application.