Transactions in SQL Server databases

What is a transaction?
A: A transaction is a series of operations performed as a single logical unit of work. A logical unit of work must have four attributes: atomicity, consistency, isolation, and persistence. Only in this way can a transaction be made.

Atomicity
A transaction must be an atomic unit of work. modifications to its data must either be performed in all or not. (It can be understood that, whether it is a separate statement or composed of multiple statements, if it is a transaction, they are a whole, a unit of work, and cannot be divided, are all together. Either execute all or not execute all)

Consistency
When the transaction is completed, all data must be consistent. In related databases, all rules must be applied to transaction modifications to maintain the integrity of all data. At the end of the transaction, all internal data structures must be correct. (Consistency means that when the transaction is completed, the data must be consistent, the modified data must be modified, the deleted data must be deleted, and the data structure should be correct .)

Isolation
Modifications made by a concurrent firm must be isolated from those made by any other concurrent firm. When a transaction identifies data, the data is in either the State before another transaction is released, or the State after the second transaction is modified. The transaction does not recognize data in the intermediate state. This is called serializability because it can reload the starting data and replays a series of transactions so that the State at the end of the data is the same as that of the original transaction execution. (Resolution: it means that the operations performed by one transaction on one data is not affected by another transaction in concurrency. Isolation level will be discussed later)

Durability
After the transaction is completed, its impact on the system is permanent. This modification will be maintained even if a system failure occurs.

SQL programmers are responsible for starting and ending transactions, while forcing logical consistency of data. Programmers must define the order of data modification so that the data is consistent with the business rules of their organizations. The programmer includes these modification statements into a transaction so that SQL Server 2005 Database Engine can force the physical integrity of the transaction.

Ms SQL SERVER provides a mechanism to ensure the physical integrity of each transaction.
1. Lock the device to isolate transactions.
2. Record the device to ensure transaction persistence. Even if the server hardware, operating system, or database engine instance fails, the instance can also use transaction logs during restart, automatically roll back all unfinished transactions to the point where the system fails.
3. Transaction Management, which forces the atomicity and consistency of transactions. After the transaction is started, it must be completed successfully. Otherwise, the database engine instance will cancel all modifications made to the data after the transaction is started.

What types of transactions are there?
Automatic transaction commit, explicit transaction, implicit transaction, [batch transaction]

Automatically submit transactions
The default transaction of the database engine. Each individual Transact-SQL statement is committed after it is completed, and no need to specify any statements to control the transaction.

Explicit transactions
Use the [API function] And the begin transaction statement that issues the Transact-SQL statement to explicitly start the TRANSACTION.

Implicit transactions
Use the [APIH function] or the SET IMPLICIT_TRANSACTIONS ON statement in Transact-SQL to SET the acute transaction mode to open. The next statement automatically starts a new transaction. When the transaction is completed, the next Transact-SQL statement starts a new transaction.

[Batch transaction]
Only applicable to multiple active result sets (MARS). explicit or implicit Transact-SQL transactions started in the MARS session will become transactions in batches. When the batch processing is complete, if the transaction in the batch range has not been committed or rolled back, SQL Server will automatically roll back the transaction.

End transaction
You can use the COMMIT or ROLLBACK statement or use the [API function] to end the transaction.

COMMIT
If the transaction is successful, it is committed. The COMMIT statement ensures that all modifications to the transaction are permanently valid in the database. The COMMIT statement also releases the resources used by the transaction (for example, the lock ).
RLLBACK
If an error occurs in the transaction or the user decides to cancel the transaction, the transaction is rolled back. The ROLLBACK statement cancels all modifications in the transaction by returning data to its status at the beginning of the transaction. ROLLBACK also releases resources occupied by transactions.