Hi, I'm confused on how to encapsulate a stored procedure within a transaction.

For example, see the following procedure bit. It calls a number of stored procedures. As it is, the last stored procedure called fails.

BEGIN CALL sp_Trans_InsertRow(128620,15); CALL sp_Trans_InsertRow(76626,115); CALL sp_Trans_InsertRow(76859,25); CALL sp_Trans_InsertRow(3,25); -- This fails END

If use this as is, I get 3 rows inserted. The last one fails as I previously said.

Now, what if I want the whole set to fail when the last one fails, and have NO records inserted???

I tried to use BEGIN TRANSACTION, but I get an error.

BEGIN TRANSACTION; -- This gives an error... CALL sp_Trans_InsertRow(128620,15); CALL sp_Trans_InsertRow(76626,115); CALL sp_Trans_InsertRow(76859,25); CALL sp_Trans_InsertRow(3,25); -- This fails COMMIT;

Could you guys give me some pointers on how this works?

Thanks, Edgard

asked 01 Dec '10, 20:58

elriba's gravatar image

elriba
51334
accept rate: 0%

edited 02 Dec '10, 21:06

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297

Which version and build are you using?

(01 Dec '10, 21:59) Siger Matt
Comment Text Removed

Do you have the text of the error?

Why does the last insert fail?

(01 Dec '10, 22:01) Siger Matt

Try adding ATOMIC, as in:

BEGIN ATOMIC
  CALL sp_Trans_InsertRow(128620,15); 
  CALL sp_Trans_InsertRow(76626,115);
  CALL sp_Trans_InsertRow(76859,25);
  CALL sp_Trans_InsertRow(3,25); -- This fails
END

This assumes sp_Trans_InsertRow is not causing commits. All of the calls must succeed, or they will all be undone. You must then commit explicitly after the block completes.

permanent link

answered 01 Dec '10, 21:59

Bruce%20Hay's gravatar image

Bruce Hay
2.6k1510
accept rate: 48%

Another approach is to use savepoints (which are essentially subtransactions).

For your requirements, BEGIN ATOMIC seems easier, but for more complex situations, you can use (usually named) savepoints to make sure parts of a transaction are either completely done or not at all.

Simple (untested!) sample with a named savepoint (which could be omitted here) - it expects that the failing statement signals any kind of exception:

BEGIN
  SAVEPOINT MySavepoint;
  CALL sp_Trans_InsertRow(128620,15); 
  CALL sp_Trans_InsertRow(76626,115);
  CALL sp_Trans_InsertRow(76859,25);
  CALL sp_Trans_InsertRow(3,25); -- This fails
  RELEASE MySavepoint;                  -- if no error occurs, "sub-commit"
EXCEPTION WHEN OTHERS THEN
  ROLLBACK TO SAVEPOINT MySavepoint;    -- otherwise, "sub-rollback"
END

As Bruce states, it is assumed that none of the procs does any commit itself, and it's necessary to do a final commit when the block has finished.


@Bruce: Am I right that ATOMIC blocks are implemented via savepoints internally (in a similar fashion)?

permanent link

answered 02 Dec '10, 08:41

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

2

@Volker: Yes, ATOMIC blocks are implemented using savepoints.

(02 Dec '10, 19:21) Bruce Hay
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×125
×59

question asked: 01 Dec '10, 20:58

question was seen: 5,557 times

last updated: 02 Dec '10, 21:06