CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqltransactions
Léster
Léster

Reputation: 1279

PL/SQL transactions when invoking procedures

I have 2 tables in a master-detail composition, User and Employee (this is because Employee is a type of user and there are other types of users). When an user is added to the system, in many cases I must also add details to Employee, and the same happens when updating those tables. That means I must guarantee atomicity for those 2 operations (insert and update).

One requirement is to use stored packages and procedures to do CRUD (tables are never directly consumed).

I wrote the following simple example in PL/SQL to try to understand how transactions work in this context:

CREATE TABLE test_a
(
    campo_1 VARCHAR2(10) NOT NULL
);
CREATE TABLE test_b
(
    campo_2 VARCHAR2(10) NOT NULL
);
/
CREATE PROCEDURE ins_a
(
    texto_1 IN VARCHAR2
)
IS
BEGIN
    INSERT INTO test_a (campo_1) 
    VALUES (texto_1);
END;
/
CREATE PROCEDURE ins_b
(
    texto_2 IN VARCHAR2
)
IS
BEGIN
    INSERT INTO test_b (campo_2) 
    VALUES (texto_2);
END;
/
CREATE PROCEDURE ins_todos
(
    texto_1 IN VARCHAR2,
    texto_2 IN VARCHAR2
)
IS
BEGIN
    ins_a(texto_1);
    ins_b(texto_2);
END;
/

If I run 

begin
ins_todos('alfa', '1234567846513216549');
end;
/

the procedure obviously fails because of the length of the second parameter, but the first procedure does not insert the first parameter, which surprised me because I expected the first procedure to succeed anyways.

My questions are:

Upvotes: 2

Views: 2145

Answers (2)

APC
APC

Reputation: 146179

"I expected the first procedure to succeed anyways."

It did succeed, in its own scope. But it is called from another procedure, ins_todos() which failed because ins_b() failed.

"Is this because of an implicit transaction running somewhere?"

A transaction is the sum of all the operations executed up to the issuance of a COMMIT or a ROLLBACK. Your code does not contain an explicit COMMIT, so yes it operates as an implicit transaction.

"If so, does this free me from explicitly managing transaction in my PL/SQL code? (I still think I should explicitly use transactions in the code.)"

Freedom is a tricky concept. You need to have a clear understanding of which operations constitute a transaction i.e. what is the Unit Of Work. You need to manage the transactions properly, to ensure that the database remains in a valid state. This means you need to be clear about when to issue COMMIT statements and how to handle execptions.

"How can I ensure that calling multiple procedures inside another procedure will respect rollbacks and commits? My purpose is that all inner procedures must rollback if one of them throws an exception."

Your test code demonstrates that this is the default behaviour. So all you need do is nothing.

"can I do all of this in PL/SQL or do I need to manage it in my C# data access layer?"

This is an architectural decision. Usually it is the code at the top of the callstack which is responsible for finalizing the transaction. So it depends on how you write your application: using your example, maybe the COMMIT should go in the ins_todos() procedure or maybe it should belong to the C# which calls it.

All PL/SQL programs should have exception handling, including logging. Whether that includes ROLLBACK is a similar architectural decision. It is common for lower-level PL/SQL programs merely to raise or re-raise exceptions up the callstack.

In general avoid the use of pragma autonomous_transaction. This creates nested transactions which will leave your database in an inconsistent state unless you know what you are doing. There are very few genuine use cases for autonomous transactions: logging to a table is the most common one.

Upvotes: 3

TeamDitto
TeamDitto

Reputation: 507

When you execute the ins_todos procedure as you would expect the call to ins_b fails because of the varchar2 length constraint of 10 characters. This failure rollbacks all sql statements in the current transaction. The transaction is started with the first insert into the test_a table.

If you really want the first insert sql to be committed independently of the second then you would use the pragma autonomous_transaction.

For example:

CREATE or replace PROCEDURE ins_a
(
    texto_1 IN VARCHAR2
)
IS
  pragma autonomous_transaction;
BEGIN
    INSERT INTO test_a (campo_1) 
    VALUES (texto_1);
  commit;
END;
/

This will commit the insert into test_a no matter if the second insert succeeds or fails.

The important information to understand here is that any exception unless caught and handled will cause the transaction to rollback all work except the work committed within an autonomous transaction.

Upvotes: 0

Related Questions