>> try this to see what i mean about getting blocked on transactions
>> ...

Tom,
thanks a lot for your reply.

As for your example with savepoints - yes, there's a corresponding note in SQL reference which explains it:

"ROLLBACK TO SAVEPOINT
Releases all table and row locks acquired since the savepoint. Other transactions that have requested access to rows locked after the savepoint must continue to wait until the transaction is committed or rolled back. Other transactions that have not already requested the rows can request and access the rows immediately."

And I think I got your remark: "when you get blocked, you enqueue ON THE TRANSACTION itself".

But this seems to be the answer to my original question:

>> It doesn't see that one of them will eventually give up trying,
>> that information just isn't exposed, this will raise a deadlock. Period.

Let me rephrase it and please, correct me if I'm wrong:

It works just the way it works.
Timeout information is just unavailable to deadlock detection procedures.
ITL slots simply do not have such information.

Right???

Thanks and regards,
Andrew.

This scenario shows another compelling reason for always closing TX on error:

DML statement(s)
if everything ok then
commit
else
rollback
end

I've noticed that most people (me including years ago) don't code the rollback branch when there's a single DML statement, the reasoning is "if the DML threw an error, db was not modified, so there's nothing to rollback". This seems intuitively obvious but it's wrong.

>> sort of, even if the ITL slots had that.... it wouldn't matter

Sure, it wouldn't... :) sorry for confusing issue, just don't know why did I mention ITLs - well, maybe because sometimes "ITL deadlocks" may occur due to low values of INITRANS - but that's another story in any case.

"It works the way it works" because enqueue-related memory structures do not contain timeout information.
V$ENQUEUE_LOCK does not contain it.
And even fixed tables used internally by Oracle (X$KSQEQ, X$KSQRS) do not contain it.

Or am I lying again?



Hi Alberto,
I'm afraid I missed your point here:

>> I've noticed that most people (me including years ago) don't code the rollback branch when there's a single DML statement

I always thought that need of COMMIT/ROLLBACK is dictated solely by client app or some business-logic driven demands (plus some special cases like autonomous transactions mentioned by Tom).
What did you mean then?

> Hi Alberto,
> I'm afraid I missed your point here:

It was meant to be pseudocode, not "pl/sql code in a stored procedure", as both you and Tom understood it :)

To clarify, here's that pseudocode incarnated in Java:

try {
... connection.prepareStatement ("update.../insert.../delete...");
// or, much better
... connection.preparecall ("begin storedProcedure ... end");

connection.commit();
}
catch (...) {
connection.rollback();
}

That will always close the transaction when an exception occurs, freeing (among other things) any other session that is waiting on your TX enqueue. That way the scenario described in this thread can't happen, since the ORA-30006 will trigger the rollback(), thus letting the other session continue.

I've noticed many times this fragment:
try {
// a single call:
... connection.preparecall ("begin storedProcedure ... end");

connection.commit();
} catch (...) {
// anything here, but not rollback() - WRONG
}

The developer's usual reasoning (apparently sound) is that there's no need to rollback, since any row modification made by the call has been rollbacked when the exception was raised - the reasoning is wrong, since the *statement* is rollbacked, but not the *transaction*.

just to add fuel to the fire :)

it *might* have rolled back the transaction - depending on how many statements have been executed.


ops$tkyte%ORA9IR2> create table t ( x int );

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace procedure p( p_fail_point in number )
  2  as
  3  begin
  4          insert into t values ( 1 );
  5          if ( p_fail_point = 1 )
  6          then
  7                  raise program_error;
  8          end if;
  9  end;
 10  /

Procedure created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select count(*) from v$transaction;

  COUNT(*)
----------
         0

ops$tkyte%ORA9IR2> exec p(0)

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> select count(*) from v$transaction;

  COUNT(*)
----------
         1

<b>Success - we have transaction....</b>

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2> exec p(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> exec p(1);
BEGIN p(1); END;

*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.P", line 7
ORA-06512: at line 1


ops$tkyte%ORA9IR2> select count(*) from v$transaction;

  COUNT(*)
----------
         1

<b>Success followed by failure - we have tranaction</b>

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2> exec p(1);
BEGIN p(1); END;

*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.P", line 7
ORA-06512: at line 1


ops$tkyte%ORA9IR2> select count(*) from v$transaction;

  COUNT(*)
----------
         0

ops$tkyte%ORA9IR2> commit;

Commit complete.

<b>start with failure, we have no transaction</b> 

>> You stopped waiting for the row, you started waiting for the transaction ...

... Ok. And the fact that I started waiting with timeout just isn't exposed anywhere, right?

>> start with failure, we have no transaction

Tom, it seems to be kind of optimization trick, right? After all, we don't really need this transaction, its very first DML failed, we didn't do any job so new transaction would be just a waste of resources. Maybe, I'm mistaken of course...

Anyways, this one still keeps me wondering. Consider:

SQL> commit;

Commit complete.

SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         0

SQL> savepoint s1;

Savepoint created.

SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         0

SQL> exec p(1)
BEGIN p(1); END;

*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "MAX.P", line 6
ORA-06512: at line 1


SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         1

SQL>

Or even "worse":

SQL> commit;

Commit complete.

SQL> savepoint s1;

Savepoint created.

SQL> rollback to s1;

Rollback complete.

SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         0

SQL> exec p(1)
BEGIN p(1); END;

*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "MAX.P", line 6
ORA-06512: at line 1


SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         1

No ideas - I give up :) Could you please explain this behaviour?

Regards. 

... And the fact that I started waiting with timeout just isn't exposed 
anywhere, right? ...

correct, but not "relevant".  You are waiting on a transaction now, even when that transaction "times out", you are still waiting, you do not become unblocked.



That failed "transaction" might have have thousands of SUCCESSFUL DMLS

ops$tkyte%ORA9IR2> create or replace procedure p( p_fail_point in number )
  2  as
  3  begin
  4          for i in 1 .. 2000
  5          loop
  6                  insert into t values ( 1 );
  7          end loop;
  8          if ( p_fail_point = 1 )
  9          then
 10                  raise program_error;
 11          end if;
 12  end;
 13  /

Procedure created.


That procedure has the same effect, it is because the first STATEMENT submitted by the client (the procedure call) fails - it is not a function of the number of successful dml statements.


Your explicit savepoint processing over shadowed the default one we do.


the logic is:

if you rollback to savepoint and nothing else tranactional has yet been done, we'll actually rollback the entire transction.


Your stored procedure call of:

exec p(1)

is really:

begin
savepoint internal_hidden_from_you;
p(1);
exception when others then
   rollback to internal_hidden_from_you;
   RAISE;
end;


If that savepoint internal_hidden_from_you is the first statement - the entire thing is rolled back.  When you did the savepoint/rollback to savepoint - it was no longer "the first statement" 

Well, and here are some new findings:

SQL> commit;

Commit complete.

SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         0

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
-------------------------------------------------------------



SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         0

SQL> savepoint s1;

Savepoint created.

SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         0

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
-------------------------------------------------------------

2.14.98467

SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         1

SQL>

I'm really running out of ideas... what's going on behind the scenes???

Regards. 

If I run an update statement in parallel, is there a possibility to have lock or a deadlock kind of situation among the parallel slaves?

What would be wait event in that kind of situation?

Thanks,

Tom,

I got your point about "over-shadowing the default savepoint", thanks.
No more questions about this.

However:

>> don't worry too much about it

Heh, well... I don't "worry" about it. Really I don't. Just trying to follow your principal motto "learn something new every day", and nothing else. So every time I observe things I can't explain - I just want to know WHAT is happening behind the scenes and HOW does this really work.

Ok, one more question (if you don't mind).

>> The record in v$transaction will appear if and when we need it.

Now as I recall I already asked myself similar questions months ago, and your latter statement about v$transaction just reminded me about it.

So how about this:
Record in v$transaction will appear as soon as transaction becomes ACTIVE. True?
Seems that the following simple experiment proves what I'm saying.

Let's create two helper views: the first one is the subset of V$SESSION and the second one is subset of x$ktcxb (I don't like to fiddle with X$-tables but this time I have no other choice).

SQL*Plus: Release 9.2.0.8.0 - Production on Tue Oct 10 13:34:34 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL>
SQL> create or replace view my_txn as
  2  select sid, taddr
  3    from v$session
  4   where sid = (select sid
  5                  from v$mystat
  6                 where rownum = 1);

View created.

SQL> create or replace view all_txn as
  2  select ktcxbxba taddr,
  3         decode(ktcxbsta, 0, 'IDLE',
  4                          1, 'COLLECTING',
  5                          2, 'PREPARED',
  6                          3, 'COMMITTED',
  7                          4, 'HEURISTIC ABORT',
  8                          5, 'HEURISTIC COMMIT',
  9                          6, 'HEURISTIC DAMAGE',
 10                          7, 'TIMEOUT',
 11                          9, 'INACTIVE',
 12                          10,'ACTIVE',
 13                'UNKNOWN') state
 14    from x$ktcxb;

View created.

SQL> create table test(x int);

Table created.

SQL> conn / as sysdba
Connected.
SQL>
SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         0

SQL> select * from my_txn;

       SID TADDR
---------- --------
        15

So far, we didn't do anything "transactional" so our TADDR column is empty. Now:

SQL> savepoint s1;

Savepoint created.

SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         0

SQL> select * from my_txn;

       SID TADDR
---------- --------
        15 6B30E81C

Well, v$transaction tells us that there are currently no transactions but we already have one in fact: transaction state object already exists for our session as evidenced by non-empty TADDR value.

And x$ktcxb tells that our transaction is currently in "IDLE" state:

SQL> select * from all_txn where taddr = '6B30E81C';

TADDR    STATE
-------- ----------------
6B30E81C IDLE

Ok, let's modify some data and check again:

SQL> insert into test values (1);

1 row created.

SQL> select * from all_txn where taddr = '6B30E81C';

TADDR    STATE
-------- ----------------
6B30E81C ACTIVE

SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         1

We did modify db state, we generated some undo data, our transaction became ACTIVE and, finally, we can watch it in v$transaction.
True?

Thanks in advance and ... sorry. I understand I'm way off-topic here: my original question was about deadlocks.

Kind regards. 

"(along with triggers, autonomous transactions and when others, I would do away with commit and rollback in plsql if I had my way :)"

Tom, I've seen you write several times that triggers should be done away with, but I've never stumbled across your reasoning. Could you please explain or provide a link to an explanation? Personally I find triggers very useful. Just recently I wrote a simple after update/insert trigger that updates a timestamp column. Not sure what could be wrong with that.

Sorry for getting a bit off topic, but this has been bugging me for a while. :)

Thanks,
Mark

Found the answer to my own question - </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:469621337269#45195818234738 <code>
Searching for "hate triggers" (with the quotes) did the trick.

Mark

Hi Tom,

I haved faced a very confusing deadlock situation and can not figure out how this can happen.My oracle version is 10.2.0.1.But my problem is that I can not simulate the situation as I had to disable the trigger so that development team's work is unhampered.

I have a simple table mention below without any primary/unique constraint mentioned below.

CREATE TABLE CL_BAK
(
CHANGE_LOG_PK NUMBER NOT NULL,
TABLE_NA VARCHAR2(50 BYTE),
RPT_GRP_IND VARCHAR2(1 BYTE),
CHANGE_DT DATE,
PROPERTY_PK NUMBER,
TABLE_PK NUMBER,
ACTION VARCHAR2(1 BYTE),
EXTRA_INFO_1 NVARCHAR2(256),
EXTRA_INFO_2 NVARCHAR2(256),
EXTRA_INFO_3 NVARCHAR2(256),
EXTRA_INFO_4 NVARCHAR2(256),
EXTRA_INFO_5 NVARCHAR2(256)
);

I have a trigger on change_log table which is same structure as cl_bak table.Though change_log has primary key, I am only selecting from the table in the trigger.The trigger can fire concurrently from many sessions.

CREATE OR REPLACE TRIGGER cdb_beferror
BEFORE INSERT ON CHANGE_LOG
BEGIN

DELETE FROM CL_BAK;

INSERT INTO CL_BAK
SELECT * FROM CHANGE_LOG;

END;

Now, recently I faced many deadlock situations(ora-00060)
and my trace file read like this -

/opt/oracle/admin/mcdb/udump/mcdb_ora_25997.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/oracle/10gR2
System name: Linux
Node name: CAL2DD05
Release: 2.6.11-1.1369_FC4smp
Version: #1 SMP Thu Jun 2 23:08:39 EDT 2005
Machine: i686
Instance name: MCDB
Redo thread mounted by this instance: 1
Oracle process number: 49
Unix process pid: 25997, image: oracleMCDB@CAL2DD05

*** 2006-12-18 20:37:37.138
*** SERVICE NAME:(SYS$USERS) 2006-12-18 20:37:37.062
*** SESSION ID:(128.4789) 2006-12-18 20:37:37.062
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
DELETE FROM CL_BAK
----- PL/SQL Call Stack -----
object line object
handle number name
0x3ab26ebc 5 CDB.CDB_BEFERROR
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-00050019-00008737 49 128 X 74 71 X
TX-000c0014-00007aa3 74 71 X 49 128 X
session 128: DID 0001-0031-000627D6 session 71: DID 0001-004A-00024921
session 71: DID 0001-004A-00024921 session 128: DID 0001-0031-000627D6
Rows waited on:
Session 71: obj - rowid = 00045D18 - AABF0YAAEAAAHOQADx
(dictionary objn - 285976, file - 4, block - 29584, slot - 241)
Session 128: obj - rowid = 00045D18 - AABF0YAAEAAAHOzAD/
(dictionary objn - 285976, file - 4, block - 29619, slot - 255)
Information on the OTHER waiting sessions:
Session 71:
pid=74 serial=65309 audsid=4739391 user: 242/CDB
O/S info: user: 129592, term: , ospid: 1234, machine: CAL2DC14
program:
Current SQL Statement:
DELETE FROM CL_BAK
End of information on OTHER waiting sessions.
===================================================

Now my question is,

Both the sessions involved in deadlock showing the same sql "DELETE FROM CL_BAK".It is a blind delete statement without any 'where' clause.How deadlock situation can occur here.It is worth mentioning here that this DELETE statement is fired only from the trigger.I just can not visualise that how in this situation the same statement causes a transaction deadlock or am I missing something.I am keeping aside ITL deadlock here.

Like always, please help me to get out of this puzzle!!

Many Thanks
Subhasis

Hi Tom,

I understand your point.But basiccaly the trigger is not part of the application....it is a temporary solution for trapping some record duplication problem(in test environment) in our application..so taking a snapshot(instead of running queries on main table) of the business table just before insert and comparing this after insert to trap when the event occured etc. and send a notification to us.

Basically we are taking snapshot of the business table just before insert.So for taking snapshot, we are deleting the old records from the backup table.The trigger is basically for debugging purpose, it is a temporary thing.

But my doubt is how the deadlock can be for same blind delete statement from the trigger or am I missing something!!!!

Waiting for your reply eagerly.

Thanks
Subhasis

Hi Tom,

First thanks for your reply.

To start with, I am not worried here with INTREGRITY ENFORCEMENT as this is just for testing(and the actual table is not reffered by or referring anybody..it is just a standalone table), we had no other choice for debugging.We also got hold of the real problem using the trigger and there was real issue in application.We also didn't had to use 'lock table' or using 'for update' as the change_log table only gets inserted from one place but it can be concurrent.No ther dml on table.

Now the situation you explained could have caused deadlock that is fine...but my real question is then how the same statement can come in the trace file as 'DEADLOCK CAUSING SQLS'.If I understood your reply properly, below is my simulation of the situation - 

session 1
SQL> update t2 set a=null;

6 rows updated.

session 2
SQL> delete from cl_bak;

25197 rows deleted.

session 1
SQL> update t2 set a=null; --hangs

6 rows updated.

session 2
SQL> update t2 set a=null;

Deadlock situation!!

The trace file contained - 

===============================================
*** 2006-12-20 12:06:01.062
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
delete from cl_bak
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-001e001c-00000013        84     210     X             29     151           X
TX-001d000e-00000008        29     151     X             84     210           X
session 210: DID 0001-0054-00017568    session 151: DID 0001-001D-000AD313
session 151: DID 0001-001D-000AD313    session 210: DID 0001-0054-00017568
Rows waited on:
Session 151: obj - rowid = 0005355F - AABTVfAAEAAATjsAAA
  (dictionary objn - 341343, file - 4, block - 80108, slot - 0)
Session 210: obj - rowid = 00045D18 - AABF0YAAEAAAxKOAD2
  (dictionary objn - 285976, file - 4, block - 201358, slot - 246)
Information on the OTHER waiting sessions:
Session 151:
  pid=29 serial=3801 audsid=4740202 user: 242/CDB
  O/S info: user: 136951, term: CAL2DD15, ospid: 3656:2476, machine: CTS\CAL2DD15
            program: sqlplusw.exe
  application name: SQL*Plus, hash value=3669949024
  Current SQL Statement:
  update t2 set a=null
End of information on OTHER waiting sessions.
===================================================

So trace file rightly have 'delete from cl_bak' for session 1 and 'update t2 set a=null' for session 2,which is fine for me.

But in my earlier posting of trigger, the statements for both sessions were same i.e. 'delete from cl_bak'.That is what confusing me.
Hopefull for your reply.

Thanks again
Subhasis 

Hi Tom,

In my previous posting, the deadlock situation I had simulated had a typing error.

So again writing what I did - 

session 1
SQL> update t2 set a=null;

6 rows updated.

session 2
SQL> delete from cl_bak;

25197 rows deleted.

session 1
SQL> delete from cl_bak; --hangs

session 2
SQL> update t2 set a=null;

Deadlock situation!!

Thanks
Subhasis 

Ok Tom,

Thanks anyway for your reply.Just to conclude, I also aware of the NOT-TO-SMART approach of this but this was a situation driven case and I just wanted to take help from you to understand the simulation for the deadlock.Just to let you know regarding "DON'T CARE DATA INTRGETY" AND "TRYING TO AVOID DUPLICATE ROWS"(I know you didn't like that), it was completely bussiness-driven case to solve a particular problem...I meant DATA INTRGETY for the backup table and avoiding duplicate records was the actual bug for which the triggers were written.I am repeating that with these triggers, we successfully trapped the bug(where from the duplicate rows were coming) and droped the trigger after that.

Thanks for the help and understanding as you always do!!Hope to get in touch with you soon.

Subhasis

Need your expert advice on this situation...

We have a ETL situation where we have to load every 5 minutes during the day...

The source system will insert data in to a list of staging tables and a unique job id into a log_table that pertains to that insert every 5 minutes. The
job_id will also be inserted in to each staging table.

The ETL process will then grab the list of job_ids from log_table and processes the data from the staging tables...Once the data has been processed the
data that pertains to the job_ids will be deleted from the staging tables.This also happens every 5 minutes..

My concern is since the source system is inserting new data into the same tables and i am deleting the data from the same tables, do you see any issues with deadlocks or
locks situation(table level locks..)? We are not touching the same data though..DO you foresee any problems?

ORA-06501: PL/SQL: program error

hi Tom!

I have such confusing deadlock. Few sessions running the same procedure with different input data concurrently. So they select and update the same tables.
There are no deadlocks related to user tables, but Oracle audit update deadlock. Why it happens?
How to avoid such deadlocks?

The trace file:

*** 2009-11-27 13:43:23.126
*** SESSION ID:(185.3) 2009-11-27 13:43:23.125
DEADLOCK DETECTED ( ORA-00060 )
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-0066000a-002f81dd 87 185 X 82 181 X
TX-00410001-00844c98 82 181 X 87 185 X
session 185: DID 0001-0057-00000080 session 181: DID 0001-0052-00000483
session 181: DID 0001-0052-00000483 session 185: DID 0001-0057-00000080
Rows waited on:
Session 181: obj - rowid = 00004C67 - AAAExyAEhAAEx+FAAa
(dictionary objn - 19559, file - 289, block - 1253253, slot - 26)
Session 185: obj - rowid = 00004C67 - AAAExyAEhAAEx+FAAe
(dictionary objn - 19559, file - 289, block - 1253253, slot - 30)
Information on the OTHER waiting sessions:
Session 181:
pid=82 serial=47 audsid=0 user: 0/SYS
O/S info: user: , term: , ospid: , machine:
program:
Current SQL Statement:
update sys.aud$ set ses$actions=merge$actions(ses$actions,:3), spare2=nvl(spare2,:4)
where sessionid=:1 and ses$tid=:2 and action#=103 and (priv$used=:5 or priv$used is null and :5 is null)
End of information on OTHER waiting sessions.
Current SQL statement for this session:
update sys.aud$ set ses$actions=merge$actions(ses$actions,:3), spare2=nvl(spare2,:4)
where sessionid=:1 and ses$tid=:2 and action#=103 and (priv$used=:5 or priv$used is null and :5 is null)


Thank you

Yes, this is done through dbms_job. Thanks for help.

Hi,

If we catch the Ora 60 error in exception handler of the block which gave the deadlock error and retry the transaction ..will it work ?

IF one of the session itself gets killed by oracle - the connection to that session will be lost - right ? In that case will it ever go to the exception handler - so that the transaction can be retried ?

thanks