For example, is the ALTER TABLE statement atomic?

I think the answer is "yes, every individual non-compound SQL statement is atomic in nature as far as database data is concerned."

That would exclude BEGIN (which can be ATOMIC but that's moot) and by implication procedure CALL, TRIGGER EVENT, function references, EXECUTE IMMEDIATE and the like.

Maybe it would be easier to answer this question: Which SQL statements are not necessarily atomic in nature?

Or is the answer too vague to be helpful?

asked 20 Apr '14, 09:35

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

edited 20 Apr '14, 09:38

FWIW the SQL standards speak to this topic with a little more authority and precision.

The main concept is of the "Statement Execution Context" [SQL/99 4.43] and the standards go on to say:

  • "For certain SQL statements the execution context is always atomic." [SQL/99 4.43.1]
  • and "The execution of all SQL-statements other than SQL-control statements is atomic with respect to recovery." [SQL/99 4.30.4 SQL-statement atomicity]

Other execution contexts and concepts come into the full picture but I suspect this is the synopsis Breck was looking for.

[Note: I've used SQL/Foundation references from SQL/99. Those articles may be and have been renumbered or moved in later standards but seem to remain the same for the purposes of this thread.]

permanent link

answered 23 Apr '14, 13:32

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
accept rate: 29%

I would say the ALTER TABLE statement is atomic; especially in the case of SQL Anywhere. The exclusive schema lock acquired for the duration of that statement will guarantee that.

This is not necessarily true of other statements. Conditional execution control statements (such as the begin, if, while, case, call, execute*, ... ) are not necessarily atomic in that their scope and effect are not localized to the single statement; nor do they align to or define a transactional boundary.

Maybe you can identify your area of concern a bit?

permanent link

answered 21 Apr '14, 10:59

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
accept rate: 29%


I would think all DDL, DML and DCL (GRANT, REVOKE...) statements have to be atomic in nature...

The exception being the "bracketing" CREATE SCHEMA statement that puts several DDL/DCL statements in one single transaction... - well, therefore it's no real exception, it's a kind of "macro-atomic" statement itself:)

(21 Apr '14, 11:34) Volker Barth
Replies hidden

@Nick: The exclusive schema lock might guarantee ALTER TABLE is isolated (the I in ACID) but I am interested in the all-or-nothing nature of atomicity (the A in ACID). For example, is an ALTER TABLE that updates every single row of a billion-row table guaranteed to successfully alter every row, or no rows at all? (for example, if the server asserts on the second-to-last row).

I believe the answer is "yes"... it is just used as an example that was probably-difficult-to-ensure-atomicity in its implementation.

This is not a question about multi-statement transaction boundaries. It is a question about single statements... which ones are atomic as far as the database data is concerned? The IF is moot since it does not change database data (ignoring for the moment side-effects of function expressions).

(21 Apr '14, 13:37) Breck Carter

@Volker: Thanks for pointing out CREATE SCHEMA. I've been guilty of ignoring this statement (for decades, literally... it remains unchanged since version 5.5). Perhaps I ignored it because it does not provide a mechanism for "packaging" and reexecuting schema creations... and I would have testified under oath that it provided no real benefit; i.e., that it is similar in its uselessness to CREATE DATATYPE.

BUT... the "transactional" nature of CREATE SCHEMA is very interesting... it may be part of the answer I am looking for, to my real question: "How do I make a lights-out schema change atomic in nature?"

...which is another way of asking, "How do I create a safe schema patch process?"

...which may be like asking, "How does dbugrade.exe work?"

Hmmm... time to RTFM :)

(21 Apr '14, 13:47) Breck Carter

Not as difficult as you might think. Expensive maybe. Long running, probably. Complex, quite possibly so. But we would consider it to be a serious issue if it were not atomic. DDL operations should never leave the database in such an inconsistent state.

Do you believe you have a situation where you think that may not be the case?

permanent link

answered 21 Apr '14, 14:31

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
accept rate: 29%

Oh, no, this question has NOTHING to do with bugs or performance... it is simply a quest for enlightenment... I want to know which single SQL statements are atomic in nature. For example, UPDATE is atomic; regardless of the transaction design, a single UPDATE is atomic, and if it fails its changes will be rolled back REGARDLESS of whether the whole transaction proceeds to commit or rolls back. Sounds crazy, but if you catch the exception of a failed UPDATE, you can proceed to COMMIT the transaction.

So forget I mentioned ALTER, it has become a distraction. I assume that every statement in this list is either atomic in nature, or the question is moot.

Volker's mention of CREATE SCHEMA is very useful because it means some DDL can be gathered into an atomic batch.

(21 Apr '14, 16:34) Breck Carter

Ignoring external data input (dbisql), output (dbisql), backup, and maybe some external operations ... I would say those are atomic in that sense; yes. Including (-; "Alter" ;-)

permanent link

answered 21 Apr '14, 17:19

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
accept rate: 29%

Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 20 Apr '14, 09:35

question was seen: 669 times

last updated: 23 Apr '14, 13:32