Hi guys - I have a weird condition here I'm trying to get resolution
on, and unfortunately I'm not getting anywhere with my friends at
Oracle. I'm hoping it's something simple, or at the very least it's a
documented bug that I can simply reference and move on.
The following code and session output (I think) documents a condition
where a procedure marked for an automomous commit is commiting an
INSERT over a database link that is outside of its transactional
boundry. In a nutshell, my INSERT is committing when it shouldn't,
*only* in situations where I have an autonomous transaction, *and* I
have an error handler that doesn't gracefully rollback the
transaction. My test case:
--setup only - the actual test run is further down
create table cca_autonomous_commit_test_9i (
id number,
creation_date date
)
/
create or replace procedure autonomous_insert is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
--insert into local database table
insert into cca_autonomous_commit_test_9i values ( 1 ,sysdate );
commit;
end;
/
create or replace procedure dloomis_test
is
begin
--reset environment
delete from cca_autonomous_commit_test_8i@xxcts_sjoe_ccais;
delete from cca_autonomous_commit_test_9i;
commit;
--insert over database link.
insert into cca_autonomous_commit_test_8i@xxcts_sjoe_ccais values (
1, sysdate );
--call my autonomous procedure, which should *NOT* commit the record
above
autonomous_insert;
--raise error, for testing purposes only
raise NO_DATA_FOUND;
end;
/
--the test output
Session 1:
--Execute dloomis_test, which should throw no_data_found by design:
SQL> exec dloomis_test
BEGIN dloomis_test; END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "APPS.DLOOMIS_TEST", line 20
ORA-06512: at line 1
Session 2 - everything looks ok:
SQL> select count(*) from cca_autonomous_commit_test_9i;
COUNT(*)
----------
1
SQL> select count(*) from
cca_autonomous_commit_test_8i@xxcts_sjoe_ccais;
COUNT(*)
----------
0
Now, if I Control-C out of session 1, the 8i record is commited
somehow:
Session 2:
SQL> select count(*) from
cca_autonomous_commit_test_8i@xxcts_sjoe_ccais;
COUNT(*)
----------
1
This is not always reproducable, but if you run the test several times
over you will get the results above. My question is - how is the 8i
record being commited, if the only commit being issued is in the
autonomous procedure? I realize an explicit ROLLBACK command in the
WHEN OTHERS block will fix the issue, but I'd still like an answer on
why this happens under the above condition.
Thanks a bunch -
Dan Loomis
IT Engineer, Cisco Systems 1 4454 dl*****@gmail.com (Dan Loomis) wrote in message news:<e2**************************@posting.google. com>... Hi guys - I have a weird condition here I'm trying to get resolution on, and unfortunately I'm not getting anywhere with my friends at Oracle. I'm hoping it's something simple, or at the very least it's a documented bug that I can simply reference and move on.
The following code and session output (I think) documents a condition where a procedure marked for an automomous commit is commiting an INSERT over a database link that is outside of its transactional boundry. In a nutshell, my INSERT is committing when it shouldn't, *only* in situations where I have an autonomous transaction, *and* I have an error handler that doesn't gracefully rollback the transaction. My test case:
--setup only - the actual test run is further down create table cca_autonomous_commit_test_9i ( id number, creation_date date ) /
create or replace procedure autonomous_insert is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
--insert into local database table insert into cca_autonomous_commit_test_9i values ( 1 ,sysdate ); commit;
end; /
create or replace procedure dloomis_test
is
begin
--reset environment delete from cca_autonomous_commit_test_8i@xxcts_sjoe_ccais; delete from cca_autonomous_commit_test_9i;
commit;
--insert over database link. insert into cca_autonomous_commit_test_8i@xxcts_sjoe_ccais values ( 1, sysdate );
--call my autonomous procedure, which should *NOT* commit the record above autonomous_insert;
--raise error, for testing purposes only raise NO_DATA_FOUND;
end; /
--the test output Session 1:
--Execute dloomis_test, which should throw no_data_found by design:
SQL> exec dloomis_test BEGIN dloomis_test; END;
* ERROR at line 1: ORA-01403: no data found ORA-06512: at "APPS.DLOOMIS_TEST", line 20 ORA-06512: at line 1
Session 2 - everything looks ok:
SQL> select count(*) from cca_autonomous_commit_test_9i;
COUNT(*) ---------- 1
SQL> select count(*) from cca_autonomous_commit_test_8i@xxcts_sjoe_ccais;
COUNT(*) ---------- 0
Now, if I Control-C out of session 1, the 8i record is commited somehow:
Session 2:
SQL> select count(*) from cca_autonomous_commit_test_8i@xxcts_sjoe_ccais;
COUNT(*) ---------- 1
This is not always reproducable, but if you run the test several times over you will get the results above. My question is - how is the 8i record being commited, if the only commit being issued is in the autonomous procedure? I realize an explicit ROLLBACK command in the WHEN OTHERS block will fix the issue, but I'd still like an answer on why this happens under the above condition.
Thanks a bunch -
Dan Loomis IT Engineer, Cisco Systems
Dan, the execution of an anonymous transaction as part of a
distributed transaction in 8i is unsupported. This is stated in the
8i manuals and normally results in an error being raised. Version 9+
does support performing an anonymous transaction as part of a
distributed transaction, but I would expect this to work only when
connecting to another version 9+ database.
It would appear each database is following the rules for its version.
IMHO -- Mark D Powell -- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Sam |
last post by:
Guys,
I have a question regarding oracle.
I have a stored procedure executing a number of statements including
delete , select and lots of inserts in one big procedure and main
transaction
I...
|
by: Brent Mondoux |
last post by:
Hey everyone,
I have a set of stored procedures that call each other to perform a
set of tasks. I have decided to do this for database performance
reasons rather than doing it from a ColdFusion...
|
by: Andrew Mayo |
last post by:
Here's a really weird one for any SQL Server gurus out there...
We have observed (SQL Server 2000) scenarios where a stored procedure
which
(a) begins a transaction
(b) inserts some rows into...
|
by: _link98 |
last post by:
On Linux or Solaris with UDB 8.2.2, can an SQL/PL stored-procedure
use two-phase-commit with nicknames and if DB2 provides the
Transaction-manager?
i.e. in the same transaction, can an SQL/PL...
|
by: Daniel Daoust |
last post by:
Hi, knowing that "autonomous transaction" (Oracle
concept of) are not yet implemented in PostgreSQL, has
anyone found a work-around. I need to preserve
database states from a potential rollback...
|
by: dilippanda |
last post by:
Hi Experts,
I want to know what is the use of pragma autonomous transaction in PL/SQL.
In which condition should we use this?
Please guide me with an example.
Thanks,
Dilip
| |
by: Dan Loomis |
last post by:
Hi guys - I have a weird condition here I'm trying to get resolution
on, and unfortunately I'm not getting anywhere with my friends at
Oracle. I'm hoping it's something simple, or at the very...
|
by: Serge Rielau |
last post by:
John Hopfield wrote:
If you were Pinocchio I'd say: "Listen to the grasshopper".
Locks have be held until the transaction commits.
Ideally your application should do this. But you add the COMMIT...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |