SQL Server Transactions

Characteristics of a transaction

Atomicity: Either all executes, or none of them executes.

Consistency: When a transaction completes, all data remains in a consistent state.

Isolation: A concurrent transaction either modifies the state before another transaction, or modifies its state after it is run, and cannot be modified while the transaction is running.

Persistence: After a transaction is successfully committed, it cannot be rolled back to its pre-commit state.

The transaction begins with a "begin TRAN" statement and ends with a "commit tran" or "rollback TRAN" statement.

Considerations when Transaction execution

You check the @ @ERROR and @ @ROWCOUNT values after each operation . @ERROR: The current statement encountered an error, return an error number, or return 0;@ @ROWCOUNT: Returns the number of rows affected by the previous statement. After each statement is executed, the two variables are reset immediately. When there are no qualifying records, the number of records affected is 0, but there is no error, so the @ @ERROR value is also 0.

You cannot roll back a statement that cannot be used in a transaction, the following is a statement that cannot be used in a transaction:

Create Database,Alert Database,DROP Database,Load Database,Restore Database,BACKUP LOG,Restore Log,Load Transaction,DUMP TRANSACTION,DISK INIT,Reconfigure,UPDATE STATISTICS

How a transaction works

At the beginning of the transaction,SQL Server locks the data that is being modified, creating a temporary transaction log that stores the changed data and the change process in the temporary transaction log. When a transaction is uncommitted, all data operations in the transaction are temporary, and once the data operation fails, the data in the temporary log is used to rollback the transaction operation and unlock it. After the transaction is successfully committed, the database stores the contents of the temporary transaction log into the transaction of the database, at which point the transaction operation is completed.

Pattern of transaction execution

Show transactions: Explicitly define the start and end of a transaction, showing that the transaction begins with a "begin TRAN" statement, with "commit tran" or "rollback Tran "statement ends the transaction.

Implicit transaction: An implicit transaction is a transaction that is started automatically after a transaction is committed or rolled back, and the implicit transaction produces a continuous chain of transactions, and when the "SET implicit_transactionson" statementis executed, theSQL The Server enters implicit transaction mode and executes "SET implicit_transactions OFF" to end the implicit transaction pattern.

Statements that start a transaction automatically in implicit transaction mode:

ALTER TABLE,Create,Open,Insert,Select,Update,Delete,Drop,TRUNCATE TABLE,Fetch,Grant,Revoke

Autocommit transactions: Is the default transaction mode for SQL Server , and is rolled back to a state that was not executed until the start and acceptance of the transaction is explicitly defined, as long as an error occurs.

Statement that commits the transaction automatically:

ALTER TABLE,TRUNCATE TABLE,Create,Select,Insert,Update,Delete,Drop,Open,Fetch,Revoke,Grant

Principles followed by transaction writing

L keep things as short as possible.

L The amount of data involved in the transaction should be as small as possible

• Try not to open transactions while browsing data

• Try not to request user input during transaction processing

The database management system retains many resources before the end of the transaction to ensure the atomicity, consistency, isolation, and durability of the transaction. When a transaction needs to modify data, the system uses locks to protect the modified records, at which time other transactions cannot manipulate the records until the transaction ends. In multi-user systems, the possession of large resources and long-time locking is intolerable.

Considerations for nesting transactions

A nested transaction commits only the commit statement of the outermost transaction, and the commit in the nested inner transaction is meaningless, but it cannot be omitted, because the transaction is a commit or rollback statement as the closing flag; The rollback statement in the inner transaction is still valid, The rollback of a transaction, however, is a rollback to the state before the outermost transaction begins (you can use a transaction hold point if you want the inner transaction to roll back to the beginning of the inner transaction).

Get nested hierarchies: Use the global variable @ @TRANCOUNT to get the hierarchy of the current code.

Transaction hold Point

In SQL Server , a transaction savepoint is provided for the transaction to hold the current location of the transaction, and after the transaction hold point is set, the transaction can be rolled back to the state of the savepoint.

The code to set the transaction savepoint is as follows:

Save {Tran | transaction} {savepoint_name | @savepoint_vatiable}

The rollback code is as follows:

Rollback {Tran | transaction} {savepoint_name | @savepoint_vatiable}

Note:savepoint_name is the save point name,@savepoint_variable A variable that holds the name of the point.

Isolation level of a transaction

Transactions are isolated, and locking the data when the transaction is processed prevents other transactions from affecting the current operation, so that other transactions are queued, which can affect the efficiency of the database. Sometimes several transactional colleagues lock their own data while waiting for other transactions to release the data, causing a deadlock.

In SQL Server , the transaction level is divided from low to high at 5 levels:

Read Uncommitted:

Read Committed:

Repeatable Read:

Snapshot:

Serializable:

Syntax code to change the isolation level of a transaction:

Set TRANSACTION ISOLATION LEVEL

{READ UNCOMMITTED

| Read Committed

| Repeatable Read

| Snapshot

| Serializable

}[ ; ]

Example Transaction code:

-- I. Preparation of transactions

Use DB

BEGIN Tran

Insert S (sname,age,sex,department) VALUES (' Zhang Xiao ', ' 1 ', ' computer ')

If @ @error >0

Goto Tranroolback

-- Here the sex ' male ' does not meet the constraints , will be wrong

Insert S (sname,age,sex,department) VALUES (' John Doe ', ' m ', ' male ', ' computer ') )

If @ @error >0

Goto Tranroolback

tranroolback:-- Custom Name

If @ @error >0 or @ @rowcount <>1

Rollback Tran

Else

Commit Tran

SELECT * FROM S

--------------------------------------------------------------------------------------------------------------- -------

-- Two, nested transactions

Use DB

BEGIN Tran

Insert S (sname,age,sex,department) VALUES (' Zhang Xiao ', ' 1 ', ' computer ')

If @ @error >0

Goto Tranroolback

Insert S (sname,age,sex,department) VALUES (' John Doe ', ' + ', 0, ' computer ')

If @ @error >0

Goto Tranroolback

-- nesting start

BEGIN Tran

Insert S (sname,age,sex,department) VALUES (' Harry ', ' + ', 0, ' computer ')

If @ @error >0 or @ @rowcount <>1

Rollback Tran

Else

Commit Tran

-- nesting end

Tranroolback:

If @ @error >0

Rollback Tran

Else

Commit Tran

SELECT * FROM S

SQL Server Transactions