Are all individual SQL statements atomic in nature?

[Breck_Carter]

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?

[Former Member · Accepted solution]

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

[Former Member]

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?

[VolkerBarth]

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:)

[Breck_Carter]

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

[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 🙂

[Former Member]

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

[Former Member]

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?

[Breck_Carter]

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.