Losing records in a transaction

[Shyamalan Chemmery via DotNetMonster.com]

I'm currently using .NET 1.0 and MS .NET provider for Oracle to connect to
my Oracle9i R2(9.2.0.5) database.

I have a piece of code which in essence is as given below

Try
m_Conn = New OracleConnection(strDBConnString)
m_Conn.Open()

m_Trans = m_Conn.BeginTransaction()

'Here I have a series of inserts and updates on huge tables. I am using
ExecuteNonQuery function of the OracleCommand object for all these
operations.
InsertTb1()
InsertTb2()
UpdateTb3()

'If all goes fine I commit the whole transaction
m_Trans.Commit()

Catch ex as Exception
'Here I catch all exceptions and rollback the whole transaction
m_Trans.Rollback()
Finally
m_Conn.Close()
End Try

I have similar code doing different actions on the same tables, running
through out the application. Off late I have been getting database locks
and subsequent slowness in the application. My DBA tracks down the holding
statements and kills the session.

Now, if the script used in InsertTb1() happens to be the statement of the
assocated session that was killed, I expect Oracle to throw an exception,
which inturn should be caught in the catch block and the whole transaction
shoud be rolled back. But, it looks like I do not get an exception, due to
which the code proceeds to fire the subsequent statements (InsertTb2(),
UpdateTb3()) and I end up with incomplete records in the database.

I have had an instance before, where Oracle database was throwing a 'No
Data found' exception, but the exception never bubbled to my code i.e. The
..NET provider ignored the excepton. Could this be a similar case?

 

[Angel Saenz-Badillos[MS]]

Seems like a very bad issue, I will try to figure out what exception is
being thrown on this case and why it is not getting bubbled up.

Can you tell me how your dba is tracking down the holding statements and
killing it?
Thanks,
Angel
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/

 

[Shyamalan Chemmery via DotNetMonster.com]

Thank you for responding.

The DBAs are using Oracle standard scripts for tracing blockers. These
scripts run in every 5 minutes in the database. The DBAs kill only sessions
which have caused other sessions to wait for more than 20-30 minutes.

The script below details the blocker query.

----------------------------------------------------------------
-- Name : id_blockers.sql
-- Desc : Identify blockers and waiters
--
-- Changes : Steveg 03-OCT-03
-- : Get sql_text
--

set feedback off
set echo off
set heading off
set pages 66
set linesize 200

select 'Date : '|| to_char(sysdate,'DD/Mon/YYYY HH24:MI') from dual
/

select 'Instance Name : '|| instance_name || chr(10) ||
'Host Name : '|| host_name || chr(10) ||
'Instance Role : '|| instance_role
from v$instance
/
set linesize 132
set heading on

select /*+ rule */ decode(request,0,'Holder: ','Waiter: ') Lock_type,
l.sid sid,
serial#, s.username,s.osuser, s.client_info,
id1,
id2,
lmode,
request,
l.type type,
sql_text
from v$lock l, v$session s, v$sqlarea t
where (id1, id2, l.type) in (select id1, id2, l.type from v$lock where
request>0) and
l.sid = s.sid and
((sql_address = address and sql_hash_value = hash_value) or
(prev_sql_addr = address and prev_hash_value = hash_value))
order by id1, request
/

select /*+ rule */ decode(request,0,'Holder: ','Waiter: ') Lock_type,
sid,
mode_held,
mode_requested,
name
from v$lock , dba_dml_locks
where (id1, id2, type) in (select id1, id2, type from v$lock where
request>0) and
session_id =sid
/

--
-- eof
------------------------------------------------------------------------

How we kill holder session: - Pick up holder session SID and SERIAL#

SQL> ALTER SYSTEM KILL SESSION ?SID, SERIAL# ?;

There is one more thing that we have observed. Apparently, it looks like
whole set of records which were committed to the database when the blockers
occurred, are missing from the database. Has such a behaviour been observed
in Oracle?

PS: We donot handle any Oracle errors in our stored procedures.