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

asked 08 Jul '11, 15:48

Breck%20Carter's gravatar image

Breck Carter
27.0k443614889
accept rate: 21%

edited 08 Jul '11, 15:50


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.

permanent link

answered 08 Jul '11, 16:10

PhilippeBertrand%20_SAP_'s gravatar image

PhilippeBert...
1.5k41735
accept rate: 22%

edited 10 Jul '11, 08:34

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

(08 Jul '11, 16:48) Breck Carter
Comment Text Removed
1

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.

(09 Jul '11, 05:07) Breck Carter
Replies hidden

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.

(09 Jul '11, 17:03) Graeme Perrow

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

(09 Jul '11, 17:05) Daz Liquid

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.

(10 Jul '11, 05:47) Breck Carter

...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.

(10 Jul '11, 05:50) Breck Carter

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."

(10 Jul '11, 05:55) Breck Carter
showing 2 of 7 show all flat view
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:

×102

question asked: 08 Jul '11, 15:48

question was seen: 3,519 times

last updated: 10 Jul '11, 08:34