Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to set up SQL Server transactions related knowledge, detailed and easy to understand, simple and fast operation, with a certain reference value, I believe that everyone will have a harvest after reading this SQL Server transaction how to set up the article, let's take a look at it.

Everything is atomic. The concept of atomicity means that something can be viewed as a unit. From a database perspective, it is the smallest combination of one or more statements that should all or none of be executed.

To understand the concept of a transaction, you need to be able to define very clear boundaries. A transaction needs to have a very clear beginning and end point. Every SELECT, INSERT, UPDATE, and Delete statement in SQL Server is part of an implicit transaction. Even if only one statement is issued, the statement is treated as a transaction-either everything in the statement is executed or nothing is executed. But what if you need more than one sentence, maybe more than one? In this case, there needs to be a way to mark the start and end of a transaction, as well as the success or failure of the transaction. T-SQL statements can be used to "mark" these points in the transaction.

BEGIN TRAN: Set the starting point.

COMMIT TRAN: Makes transactions permanent and irreversible part of the database.

ROLLBACK TRAN: Essentially trying to forget it ever happened.

SAVE TRAN: Create a special marker that allows only partial rollback.

I. BEGIN TRAN

The beginning of a transaction is probably the easiest concept to understand in the transaction process. Its sole purpose is to indicate the beginning of a unit. If for some reason the transaction cannot or does not want to be committed, then this is the starting point from which all database activity will be rolled back. That is, the database ignores all statements after this starting point that are not ultimately committed.

The syntax is as follows:

begin tran[saction] [ |] [ with mark [] ] 2. COMMIT TRAN

The commit of a transaction is the endpoint of a transaction. When the COMMIT TRAN command is issued, the transaction is considered persistent. That is, the impact of the transaction is now persistent and will persist even in the event of a system failure (as long as there are backups or the database files are not physically destroyed). The only way to undo a completed transaction is to issue a new transaction. Functionally, this transaction is the inverse of the first transaction.

COMMIT TRAN syntax:

commit tran[saction] [ |3. ROLLBACK TRAN

What ROLLBACK does is go back to the starting point. Anything that happens since the associated BEGIN statement is virtually forgotten.

ROLLBACK's syntax looks the same as a BEGIN or COMMIT statement, except that save points are allowed:

rollback tran[saction] [ |||] IV. SAVE TRAN

Saving transactions is essentially creating bookmarks. Create a name for the bookmark, and after you create the bookmark, you can reference it in the rollback. The advantage of creating bookmarks is that you can roll back to a specific point in your code-just name the save point you want to roll back to.

The syntax is as follows:

save tran[scation] [ |] V. Examples

Let's start with the following table:

Specific code of transaction:

begin tran Tran_Money; --begin transaction declare @tran_error int;set @tran_error = 0;begin try update tb_Money set MyMoney = MyMoney - 30 where Name = 'Liu Bei'; set @tran_error = @tran_error + @@ERROR; --Test the error code to see if Liu Bei's money decreases and Guan Yu's money increases --SET @tran_error = 1; update tb_Money set MyMoney = MyMoney + 30 where Name = ''; set @tran_error = @tran_error + @@ERROR;end trybegin catch print 'exception occurred, error number: ' + convert(varchar, error_number())+', error message: ' + error_message(); set @tran_error = @tran_error + 1;end catch;if ( @tran_error > 0 ) begin --Execution error, rollback transaction rollback tran; print 'Transfer failed, cancel transaction! '; end;else begin --No exception, commit transaction commit tran; print 'Transfer successful! '; end; about "SQL Server transaction how to set up" the content of this article is introduced here, thank you for reading! I believe everyone has a certain understanding of "how to set up transactions in SQL Server" knowledge. If you still want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.