SQL Server Things

The definition of a thing:

The object of the database is atomic, and the concept of atomicity refers to something as a unit.

From a database point of view, it is the smallest combination of one or more statements that are executed or not executed at all; In order to understand the concept of a transaction, it is necessary to be able to define very explicit boundaries. The transaction should have a very clear start and end point. Each 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 executing all the contents of the statement or doing nothing. But what if you need more than just one, maybe multiple statements? In this case, there is a need to have a way to mark the start and end of a transaction, as well as the success or failure of a transaction. You can use some T-SQL statements to "Mark" these points in a transaction.

-Begin TRAN: Sets the starting point.

-COMMIT TRAN: Make a transaction a permanent, irreversible part of the database.

-ROLLBACK TRAN: Essentially, you want to forget it ever happened.

-SAVE TRAN: Creates a specific token that allows only partial rollback.

Begin TRAN: Set start point

The beginning of a transaction can be the easiest concept to understand in a transactional process. Its only purpose is to represent the beginning of a unit. If, for some reason, you cannot or do not want to commit a transaction, this is the starting point for all database activities that will be rolled back. In other words, the database ignores all statements that are not ultimately committed after this starting point.

(denotes the beginning of a unit, the starting point at which things begin to execute, when a thing is submitted from here, when things are rolled back from here;)

Grammar:

begintran[saction][<transaction name> | < @transaction variable>]

[With MARK [< ' description ';]

COMMIT TRAN: Make a transaction a permanent, irreversible part of the database

The commit of a transaction is the end of a transaction. When the Commit TRAN command is issued, the transaction can be considered persistent. In other words, the impact of a transaction is now persistent and persistent, even if a system failure occurs (as long as there is a backup or the database file is not physically corrupted). The only way to undo a completed transaction is to issue a new transaction. Functionally, the transaction is a reversal of the first transaction.

(representing a unit that makes things permanent and immutable from begin to here.) What happens in time hardware, things can be done; Want to change this thing, you need to start a new one again)

Things.

Grammar:

Committran[saction][<transaction name> | < @transaction variable>]

ROLLBACK TRAN: Essentially, to forget that it ever happened.

The thing rollback do is to go back to the beginning. Anything that happens from the associated begin statement is actually forgotten.

(indicates that the database ignores it from begin to where the error occurred, and rolls back to where it started, as if the unit had not occurred; similar to the syntax of commit;)

Syntax: (except for savepoint, rollback syntax looks similar to begin and commit syntax)

rollbacktran[saction][<transaction name> | <save point name> | < @transaction variable> | < @savepoin T variable>]

SAVE TRAN: Create a specific token that allows only partial rollback

Saving a transaction is essentially creating a bookmark. Create a name for the bookmark that you can reference in a rollback after you have created a bookmark. The advantage of creating bookmarks is that you can roll back to a specific point in your code-just name the savepoint you want to roll back to.

(indicates that a specific token is created, which is equivalent to a bookmark (bookmark), and rolled back from the beginning of the bookmark that was created.) )

Grammar:

savetran[scation][<save Point name> | < @savepoint variable>]

Example:

Begin TRAN Tran_money--Start 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 is reduced and whether Guan Yu's money will increase

--set @tran_error = 1;

UPDATE Tb_money SET Mymoney = Mymoney + WHERE Name = ' Guan Yu ';

SET @tran_error = @tran_error + @ @ERROR;

END TRY

BEGIN CATCH

PRINT ' unexpected exception, error number: ' + CONVERT (Varchar,error_number ()) + ', error message: ' + error_message ()

SET @tran_error = @tran_error + 1

END CATCH

IF (@tran_error > 0)

BEGIN

--Performing an error, rolling back the transaction

ROLLBACK TRAN;

PRINT ' transfer failed, cancel transaction! ';

END

ELSE

BEGIN

--no exception, COMMIT transaction

COMMIT TRAN;

PRINT ' transfer successful! ';

END

Definitions in the code:

Code:

IDbTransaction transaction = Csqlhelper.gettrans (Cconstants.db_dataexchange);//define a database thing

try{

Database Manipulation Section

Transaction.commit ();

Transaction. Dispose ();

}

catch{

Transaction. Rollback ();

Transaction. Dispose ();

}

SQL Server Things