How to rollback or commit a transaction in SQL Server
The good news is a transaction in SQL Server can span multiple batches (each exec
is treated as a separate batch.)
You can wrap your EXEC
statements in a BEGIN TRANSACTION
and COMMIT
but you'll need to go a step further and rollback if any errors occur.
Ideally you'd want something like this:
BEGIN TRY
BEGIN TRANSACTION
exec( @sqlHeader)
exec(@sqlTotals)
exec(@sqlLine)
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
The BEGIN TRANSACTION
and COMMIT
I believe you are already familiar with. The BEGIN TRY
and BEGIN CATCH
blocks are basically there to catch and handle any errors that occur. If any of your EXEC
statements raise an error, the code execution will jump to the CATCH
block.
Your existing SQL building code should be outside the transaction (above) as you always want to keep your transactions as short as possible.
How to commit and rollback transaction in sql server?
Don't use @@ERROR
, use BEGIN TRY/BEGIN CATCH
instead. See this article: Exception handling and nested transactions for a sample procedure:
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction usp_my_procedure_name;
-- Do the actual work here
lbexit:
if @trancount = 0
commit;
end try
begin catch
declare @error int, @message varchar(4000), @xstate int;
select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
if @xstate = -1
rollback;
if @xstate = 1 and @trancount = 0
rollback
if @xstate = 1 and @trancount > 0
rollback transaction usp_my_procedure_name;
raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
return;
end catch
end
Can I rollback a transaction I've already committed? (data loss)
No, you can't undo, rollback or reverse a commit.
STOP THE DATABASE!
(Note: if you deleted the data directory off the filesystem, do NOT stop the database. The following advice applies to an accidental commit of a DELETE
or similar, not an rm -rf /data/directory
scenario).
If this data was important, STOP YOUR DATABASE NOW and do not restart it. Use pg_ctl stop -m immediate
so that no checkpoint is run on shutdown.
You cannot roll back a transaction once it has commited. You will need to restore the data from backups, or use point-in-time recovery, which must have been set up before the accident happened.
If you didn't have any PITR / WAL archiving set up and don't have backups, you're in real trouble.
Urgent mitigation
Once your database is stopped, you should make a file system level copy of the whole data directory - the folder that contains base
, pg_clog
, etc. Copy all of it to a new location. Do not do anything to the copy in the new location, it is your only hope of recovering your data if you do not have backups. Make another copy on some removable storage if you can, and then unplug that storage from the computer. Remember, you need absolutely every part of the data directory, including pg_xlog
etc. No part is unimportant.
Exactly how to make the copy depends on which operating system you're running. Where the data dir is depends on which OS you're running and how you installed PostgreSQL.
Ways some data could've survived
If you stop your DB quickly enough you might have a hope of recovering some data from the tables. That's because PostgreSQL uses multi-version concurrency control (MVCC) to manage concurrent access to its storage. Sometimes it will write new versions of the rows you update to the table, leaving the old ones in place but marked as "deleted". After a while autovaccum comes along and marks the rows as free space, so they can be overwritten by a later INSERT
or UPDATE
. Thus, the old versions of the UPDATE
d rows might still be lying around, present but inaccessible.
Additionally, Pg writes in two phases. First data is written to the write-ahead log (WAL). Only once it's been written to the WAL and hit disk, it's then copied to the "heap" (the main tables), possibly overwriting old data that was there. The WAL content is copied to the main heap by the bgwriter
and by periodic checkpoints. By default checkpoints happen every 5 minutes. If you manage to stop the database before a checkpoint has happened and stopped it by hard-killing it, pulling the plug on the machine, or using pg_ctl
in immediate
mode you might've captured the data from before the checkpoint happened, so your old data is more likely to still be in the heap.
Now that you have made a complete file-system-level copy of the data dir you can start your database back up if you really need to; the data will still be gone, but you've done what you can to give yourself some hope of maybe recovering it. Given the choice I'd probably keep the DB shut down just to be safe.
Recovery
You may now need to hire an expert in PostgreSQL's innards to assist you in a data recovery attempt. Be prepared to pay a professional for their time, possibly quite a bit of time.
I posted about this on the Pg mailing list, and Виктор Егоров linked to depesz's post on pg_dirtyread, which looks like just what you want, though it doesn't recover TOAST
ed data so it's of limited utility. Give it a try, if you're lucky it might work.
See: pg_dirtyread on GitHub.
I've removed what I'd written in this section as it's obsoleted by that tool.
See also PostgreSQL row storage fundamentals
Prevention
See my blog entry Preventing PostgreSQL database corruption.
On a semi-related side-note, if you were using two phase commit you could ROLLBACK PREPARED
for a transction that was prepared for commit but not fully commited. That's about the closest you get to rolling back an already-committed transaction, and does not apply to your situation.
Correct use of transactions in SQL Server
Add a try/catch block, if the transaction succeeds it will commit the changes, if the transaction fails the transaction is rolled back:
BEGIN TRANSACTION [Tran1]
BEGIN TRY
INSERT INTO [Test].[dbo].[T1] ([Title], [AVG])
VALUES ('Tidd130', 130), ('Tidd230', 230)
UPDATE [Test].[dbo].[T1]
SET [Title] = N'az2' ,[AVG] = 1
WHERE [dbo].[T1].[Title] = N'az'
COMMIT TRANSACTION [Tran1]
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION [Tran1]
END CATCH
SQL Server : BEGIN TRAN ... COMMIT without ROLLBACK does not rollback depending on the error
But I am still wondering why the BEGIN TRAN without ROLLBACK does not work all the time. Does it really depend on the type of error as I guess?
You are right,it depends on type of error.Below statement by Erland helped me understand more on different variations of error handling in SQL Server.
Error handling in SQL Server is a very messy story. I asked the same question as you did in comp.databases.sybase in 1993 or so. I don't remember exactly what answers I got, but I don't think they were very good.
But this is the story: when an error occurs in SQL Server, the batch may be aborted and the transaction rolled back. Or the statement may be terminated, and the transaction continues.
Please don't ask about the logic in this, because there isn't any. Data-integrity violations usually don't abort the batch. But a conversion error often do.
To Microsoft's defence it can be said, that many of these bad decisions were taken in California when the product was still Sybase. On the other hand, Microsoft has very much effort to straighten out this, rather the opposite.
See the below screenshot for different scenarios
This link has excellent info on each behaviour
Error and Transaction Handling in SQL Server
This error
The transaction log for database 'MyDatabase' is full due to 'ACTIVE_TRANSACTION
can occur for many reasons. Some include
- You have an active transaction which is presenting log space reuse
- Heavy transaction which may require lot of log space and your disk may be full
Additional reference:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6ae24a04-0ad3-4aba-b471-2bbbcd8d8626/with-the-transaction-primary-key-violation-error?forum=transactsql
Rollback and commit transaction SQL Server
Roughly your code will work fine:
OPEN CURSOR
BEGIN TRANSACTION
BEGIN TRY
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
END TRANSACTION
CLOSE CURSOR
However, why do you want to use cursor? as far as I know, cursor performs slow in term of performance and once your transaction fail and go to rollback, all of your transaction in cursor will be rollback. CMIWW.
Related Topics
Teradata Equivalent for Lead and Lag Function of Oracle
Sql: Select Most Recent Date for Each Category
Casting Scientific Notation (From Varchar -> Numeric) in a View
Oracle SQL - Sum and Group Data by Week
Postgresql Nested Inserts/Withs for Foreign Key Insertions
SQL Server 2008 Express Concat() Doesn't Exist
The Argument 1 of the Xml Data Type Method "Value" Must Be a String Literal
Ignore Certain Columns When Using Bulk Insert
Ms SQL Server Pivot Table with Subquery in Column Clause
Perform Join Query in Google Cloud Firestore
Shows Blanks for Repeating Values in a Result Set
How to Remove Duplicates from Space Separated List by Oracle Regexp_Replace
Calculating Days to Excluding Weekends (Monday to Friday) in SQL Server