Rollback command in SQL

5 pts.
Tags:
SQL
Suppose user x insert values into table t1,and then update the table t1. After then he wants to increase the size of column c1. Then he wants to rollback to update values. He can do this or not? If yes then what is the right procedure?

Software/Hardware used:
software

Answer Wiki

Thanks. We'll let you know when a new response is added.

With most databases you begin a transaction, and you can rollback the entire transaction, or commit the entire transaction. You can’t rollback specific statements within the batch.

<pre>BEGIN TRAN
INSERT INTO T1...

UPDATE T1
...

ALTER TABLE T1
MODIFY C1 ...
</pre>

At this point you can either COMMIT the transaction or ROLLBACK the transaction. You can’t do just a single statement within the transaction.

——————

You didn’t specify what database you are using, and I think the answer could be different depending on that.

In Oracle, for example, there is the SAVEPOINT command, which marks the current point in the processing of a transaction. It can be used in conjunction with the command ROLLBACK TO <savepoint_name> to undo <b>parts</b> of a transaction.

Also, Oracle has an AUTONOMOUS TRANSACTION feature, which lets you create a stored procedure or function that will be managed separately as an autonomous transaction (i.e. it can be committed or rolled back without the need to commit or roll back the other statements that are part of the transaction from which it was called).

As for altering a table/column inside a transaction, Oracle issues an implicit COMMIT when a DDL command is executed, thus if you execute some DML commands and then you execute a DDL command, your previous operations are automatically committed and you can’t roll them back.

———————-
I would add one thing to the above answer…
ROLLBACK does away with changed data back to the beginning of the logical unit of work.
A logical unit of work is defined as “from the beginning of the program (transaction) OR to the last commit point”

If a program issues multiple COMMIT statements, ROLLBACK will return the data to the state of the last commit. But remember, you cannot go back wards beyond the last commit. Further, one does not want to issue COMMITs more often than required because they are rather expensive.

Steve

Concerning the Oracle implicit COMMIT on DDL; to work around this issue use to blocks:

CREATE OR REPLACE PROCEDURE myPROD()

IS
BEGIN

INSERT INTO T1...

UPDATE T1
...
COMMIT;

EXCEPTION
WHEN e_myException
THEN
ROLLBACK;
END; --end DML block

BEGIN
ALTER TABLE T1
MODIFY C1

EXCEPTION
WHEN e_DDLException
THEN
....;
END; --end DDL block

END myPROD;

[kccrosser]
The problem with the original question is that he isn’t asking about how to roll back from a column width increase error – he is asking how to roll back to the previous state after the DDL change succeeds. I am unaware of any database that has a roll back to undo a successful DDL action.

If I had to provide a user with this kind of capability, my approach would be to capture the table prior to the DDL, and restore the table later if needed. In Oracle, capturing the table is simply:
CREATE TABLE <newtable> AS SELECT * FROM <oldtable>
This creates a new table with the same structure as the old one, populated with the data.

There is no equivalent in SQL Server – you need to write some interesting dynamic T-SQL code or use the Management Studio tools to script the table construction and data copy.

Obviously, you also need to capture the indexes, constraints, etc. and restore those as well.

Overall – I am having trouble understanding the purpose of the question…

——————————-
” I am unaware of any database that has a roll back to undo a successful DDL action.”
DB2 does not auto-commit ANY SQL. If you issue an ALTER, you may then follow it with a ROLLBACK and it will be rolled back.

Steve

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Sql Server doesn't have the implicit commit on DDLs either (I tested it on 2008).
    69,920 pointsBadges:
    report
  • Denny Cherry
    SQL Server also has the save points and labels which can be rolled back to. Based on the brevity of the question, I assumed such an in-depth answer wasn't necessary. Granted it would be nice to know what platform the sub is talking about. Carlos is correct, SQL Server (for as far back as I can remember) doesn't to an implicit commit on DDL allowing you to rollback a successful DDL operation later on if needed. If you have SSMS or Enterprise Manager script out DDL such as adding a column to the middle of a table, the code which is generated counts on the ability to rollback DDL changes.
    66,190 pointsBadges:
    report
  • Kccrosser
    Interesting about SQL DDL not doing an implicit commit. However, I have always understood that any DDL changes are not available to further SQL transactions until the commit occurs. Is that correct? I.e., if I issue an ALTER TABLE statement, I cannot then do any SQL transactions that reference the new column (or any changes resulting from the alter?) until that DDL statement is committed. So - the only value to rolling back a DDL statement is to recover from an error that occurred during the processing of the DDL command itself. Thus, I will restate my understanding more clearly: I am unaware of any database that will allow you to roll back a transaction to before a DDL command where that DDL command was successful AND any transactions were run that used the result of the DDL command.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following