cancel
Showing results for 
Search instead for 
Did you mean: 

COMMIT/ROLLBACK not allowed within atomic operation

Breck_Carter
Participant
7,503

What am I missing? What is "atomic" in the following code?

12.0.1.3298

SQLCODE = -267

SQLSTATE = 42W28

ERRORMSG() = COMMIT/ROLLBACK not allowed within atomic operation

CREATE PROCEDURE p()
BEGIN NOT ATOMIC

DECLARE @sqlcode                       INTEGER;
DECLARE @sqlstate                      VARCHAR ( 5 );
DECLARE @errormsg                      VARCHAR ( 32767 );

BEGIN NOT ATOMIC -- handle possible exception

COMMIT;

EXCEPTION

WHEN OTHERS THEN

SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;

MESSAGE STRING ( 
         'EXCEPTION: @@VERSION = ', @@VERSION,  
         ', SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) 
         TO CLIENT;

END;

SELECT 'Hello, world' AS c;

END;

---------------------------------------------------------------------------
BEGIN NOT ATOMIC

DECLARE @row_count                        INTEGER;

DECLARE LOCAL TEMPORARY TABLE dsn_list (
   dsn_entry  LONG VARCHAR )
   NOT TRANSACTIONAL;

INSERT dsn_list
SELECT LIST ( p.c )
  FROM p();

END;

EXCEPTION: @@VERSION = 12.0.1.3298, SQLCODE = -267, SQLSTATE = 42W28, ERRORMSG() = COMMIT/ROLLBACK not allowed within atomic operation

Accepted Solutions (0)

Answers (1)

Answers (1)

philippefbertrand
Participant

It is because the commit is being executed during the insert (not just insert select as I originally supposed) which is atomic.

The closest thing I found in the docs is:

COMMIT and ROLLBACK are not allowed within any atomic statement. Note that triggers are fired due to an INSERT, UPDATE, or DELETE which are atomic statements. COMMIT and ROLLBACK are not allowed in a trigger or in any procedures called by a trigger.

Breck_Carter
Participant
0 Kudos

ooooo... gotta check that out.... I feel a blog posting coming on.... who'da thunk it? 🙂

Breck_Carter
Participant

oops, I did it again... accidentally deleted John Smirnios' comment. John had said it was the INSERT, not the SELECT, that made it an atomic operation.

Suggestion: Either allow undo of comment deletion, or do not allow the comment delete button to be pressed by anyone who has not yet had a second cup of coffee... whichever is easier to implement.

graeme_perrow
Advisor
Advisor
0 Kudos

It does say "Are you sure?" so if you're not sure... Perhaps I can change the confirmation to say "Are you sure you want to delete this comment?" to be more specific.

0 Kudos

maybe just an early morning/late night mode that says 'Are you really sure' just after the 'Are you sure' 🙂

Breck_Carter
Participant
0 Kudos

Of course I was sure I wanted to delete my own comment, so I could re-enter it in a different place in the hierarchy. So a cosmetic change to the dialog box text would not have helped.

It's a GUI thing... when there are a bunch of short comments the screen is full of tiny icons, and most comments have one delete icon right above it and another one right below it. Nothing wrong with that BUT mistakes will be made and an undo feature lets you leave the GUI alone while at the same time allowing the user to work efficiently AND safely.

If you don't realize you have clicked on the wrong delete icon, a confirmation box isn't going to stop you... unless, perhaps, that confirmation box contains the entire text of the comment you are about to delete. Or a big warning that "THIS NOT YOUR OWN COMMENT, THIS IS SOMEONE ELSE'S COMMENT, YOU BLOODY MORON!".

Or both.

Even then... there should still be an undo. ESPECIALLY for a delete operation. A lot of enterprise business applications don't even allow deletes, with good reason... disk space is cheap, lawsuits and FBI investigations not so much.

Breck_Carter
Participant
0 Kudos

...whatever you do, please don't change the GUI. It's fine. I have messed up twice now, it is possible that I have learned my lesson.

Breck_Carter
Participant
0 Kudos

LOL... I have those in the Foxhound build process: "Before you recreate the Foxhound development database, have you looked at the runtime exception history table? Maybe there is something that needs fixing..."

...repeated five times. Sometimes, around the third or fourth "OK" click, I finally say "Aw, nuts, ok, let me look."

What it SHOULD say is, "There are N un-viewed rows in the exception table. You cannot do a build until you have looked at them all."