What is a transaction: maintaining data consistency and recovery

1. Definition of a transaction

Transactions provide a way to group T-SQL statements so that they can be treated as a unit: All statements in a group are executed, or none are executed.

A transaction is a mechanism that ensures that a collection of one or more SQL statements is not treated as a single unit of work, and SQL Server automatically modifies all data-modification commands, including a step-change request, as a transaction, by default, each insert, UPDATE, and DELETE statement is treated as a transaction.

2. Role of the transaction

Transactions enable SQL Server to guarantee that:

• Consistency-simultaneous queries or change requests do not conflict with each other, and users cannot view or manipulate data that is in the process of being changed.

• Recovery-When the system fails, the recovery of the database is complete and automatic.

3. Using transactions

A. Initiation and confirmation of services

The BEGIN TRANSACTION and commit transaction can encapsulate any number of SQL statements, and the simple syntax for these two names is:

BEGIN TRANSACTION

Commit TRANSACTION

B. ROLLBACK TRANSACTION

The transaction can be canceled or rolled back at any time before the Commit TRANSACTION command is committed, and the simple syntax for the command is:

ROLLBACK TRANSACTION

4. Check transaction status

The global variable @ @transtate records the current state of the transaction. After executing a statement, SQL Server determines what state to return by logging all transaction changes.

@ @transtate can contain the following values:

Value meaning

0 Transaction in progress: an explicit or implicit transaction is valid; Last statement executed successfully

1 Transaction complete: Transaction complete and submit its changes

2 Statement exception aborted: last statement exception terminated; no effect on transaction

3 Transaction Abort: Transaction aborted and fallback all changes

For example: In a transaction, you can use the @ @transtate after a statement (such as insert) to determine the effect of a statement's success or failure on a transaction. Commit TRANSACTION

Begin Transaction

Insert into Publishers (pub_id) VALUES (' 9999 ')

(1 row affected)

SELECT @ @transtate

.....................

0

Commit TRANSACTION

SELECT @ @transtate

.....................

0

(1 row affected)