Table of Contents
Restore your computer to peak performance in minutes!
Over the past few weeks, some of our readers have reported a transactional SQL restore error. retreat. despite the fact that Tx. The restore failed, our transaction is no longer applicable and is also not trusted to the database.
How do I rollback a SQL transaction?
You must, of course, write a ROLLBACK TRANSACTION statement followed by the name of the transaction you wish to roll back. Now try running a specific AddBook transaction to insert a tracking record named Book15 (make sure there is no book with that name already in the Books table).
Recently, I pointed out a problem where data is inserted into a table during transactions that do not completely roll back financial transactions if weWe use insert statements with a transaction a lot.
Let me give you a new demonstration of how to deal with situations like this.
Now let’s try to randomly insert data into this table in a large transaction.
Restore your computer to peak performance in minutes!
Is your PC running slow and constantly displaying errors? Have you been considering a reformat but don't have the time or patience? Fear not, dear friend! The answer to all your computing woes is here: ASR Pro. This amazing software will repair common computer errors, protect you from file loss, malware, hardware failure and optimize your PC for maximum performance. So long as you have this program installed on your machine, you can kiss those frustrating and costly technical problems goodbye!
As we can see, this ID column contains the primary key, so the ID can only contain unique values. But here we are trying to add a double value to the ID in grin of our third INSERT statement and that should also fail. Third
The instruction returned an error and we will not insert a duplicate key in the ID column. But what about two Raw statements? Will you be glued or saved?
What causes a transaction to rollback?
So if the user starts changing bandwidths, the band only detects that the wrong record level is being updated, and rolls back the capture to roll back all changes, a rollback occurs. Recovery can also be performed automatically after a database or server failure.
We can see that the records were inserted even though the transaction failed; rather it is the right way. To avoid this situation, we must reach the transaction atomically, which means that either all instructions of the entire transaction are executed, or none of them are executed successfully if one of the benefits is not fulfilled.
Here I define four methods to achieve atomicity in terms of transactions.
- Use blAbort
- Try/Catch Use XACT_ABORT
We will rewrite the query using a try/catch block. I first remove all tracks from the table.
After executing the query, we see that not a single row was inserted into its table because it returned home after encountering an error, we have achieved atomicity with a try/catch block.
We can also achieve true atomicity by setting the To ON xact_abort option. By setting XACT_ABORT ON, we can abort all transactions within a transaction when a fatal error occurs.
Part of the transaction will also be rolled back because there was an error in the third statement. So, my friends, our company can use these two methods to reverse the entire transaction, not to mention atomicity.
- Create Test_tran table
- (
- Primary key identifier Int,
- START TRANSACTION
- )
- custom class = “”> instate test_tran at (id, name) (1, ‘amit’)
- instate test_tran at (id, name) (2, ‘kapil ‘)
- INSERT Test_Tran to (ID, Name) VALUES (1, ‘Aditya’)
- COMMIT TRAN
- CHOOSE FROM test_tran
- TRUNCATE TABLE Test_tran
- START TRY
- START TRAN
- INSERT Test_Tran in (ID, Name) VALUES (1,’Amit’)
- INSERT Test_Tran into (ID, Name) VALUES (2,
- INSERT Test_Tran into (ID, name) VALUES (1, ‘Aditya’)
- COMMIT TRAN
- END TRY
- BEGIN CATCH
- ROLLBACK TRAN
- END CATCH
- SET XACT_ABORT ON
- START TRAN
- INSERT Test_Tran to (ID, Name) VALUES (1,’Amit’)
- INSERT Test_Tran into (ID, Name) VALUES (2,’Kapil’ )
- INSERT Test_Tran into (ID, Name) VALUES (1,’Aditya’)
- BOOK A TRANSFER
In my first article, we saw how the market works and fixes returns stored procedure error messages. We now need to look at how to implement transactions with exception handling. In any recovery scenario, we must correct a business error while executing SQL. With the help of TRY-CATCH we can catch the exception that occurred in the stored procedure from the statement, and with the help of and block in CATCH we can cancel the command. Below is an example. In this managed example, we might want to ROLLBACK put the TRANSACTION in a CATCH block after the error details received from Der.
Note
Use WideWorldImportersWALK/** Create a stored procedure **/CREATE PROCEDURE mtb_SampleExceptionHandling@Error NVARCHAR(MAX) OUTPUTLIKEBEGINSTART A DEAL;START TRYINGDELETE them Sales from.Customers WHERE CustomerID = 1TRY TO EXITFor startSET @Error = 'Error number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + '; lol + Char(10) +'Error severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) + '; a + char(10) +'Error state: ' + CAST(ERROR_STATE() AS VARCHAR(10)) + '; + ha Char(10) +'Error line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) + '; me + character(10) +'Error message: ' ERROR_MESSAGE()IF + @@TRANCOUNT > 0DEAL ROLLBACK;END OF CAPTUREIF @@TRANCOUNT > 0START Execute a TRADE;ENDWALK/** stored procedure **/DECLARATION @ErrorMsg NVARCHAR(MAX)EXEC mtb_SampleExceptionHndling @Error = Output @ErrorMsgSelect @ErrorMsgWALK/** Results **/-----------(0 rows affected)-----------Error number: 547;error severity: 16;Error state: 0;error string: 10;Error message: DELETE statement conflicts with REFERENCE constraint"FK_Sales_CustomerTransactions_CustomerID_Sales_Customers".The gap that occurred in "WideWorld(1 TRY-CATCH row affected)
Reference
- About and implementing transactions in stored methods in Microsoft Docs.
Articles Related Content
Tools Related To Articles
In my previous comment, we saw how to receive and return error messages from stored procedures. Now let’s see, let’s see how the downstream exception handling transactions are implemented. In all transactional circumstances, we must cancel trading errors when executing SQL. With TRY-CATCH we can catch the exception thrown in the accumulated procedure statement, and with the output in CATCH we can roll back the transaction. Below is an example. In this ideal CATCH block, after recording the error, we can prepare which details, ROLLBACK TRANSACTION.
Example
Does SQL transaction rollback on error?
Also, the transaction is rolled back again when an error occurs in the third statement. So, fans, can we use these two hints to 100% cancel the transaction and achieve atomicity.
