SQL server 2005 transaction persistence

Atomicity: a transaction must be an atomic unit of work. Modifications to its data must be either executed or not executed.
Consistency: when the transaction is completed, all data must be consistent.
Isolation: modifications made by a concurrent firm must be isolated from those made by any other concurrent firm.
Durability: After the transaction is completed, its impact on the system is permanent. This modification will be maintained even if a system failure occurs.

The following uses a simple example (Northwind database) to illustrate this point.
Atomicity:
Under normal circumstances, the order detail table is deleted first, and the order information is deleted. In order to demonstrate the atomicity of the transaction, such writing in production is a logical error.

The code is as follows:	Copy code
USE Northwind; GO Begin tran; Delete from db. Orders WHERE OrderID = 10249; Delete from dbo. "Order Details" WHERE OrderID = 10249; Commit tran; GO

Execution result:
Table Orders failed to delete because it violates the integrity of the reference. Table Order Details successfully deleted two rows. from this example, the execution of the first T-SQL Statement failed, but the second T-SQL succeeded, which does not clearly prove that the transaction is not atomic. Don't rush

Next, because the statements between TRAN in TRAN and end tran are a transaction, either the operation is successful or all operations are rolled back, in fact, this transaction is not rolled back because the XACT_ABORT option of the session is OFF by default, resulting in a running error.

Only roll back the T-SQL statement that produces the error, the transaction continues to process, if the error is serious, the entire transaction may be rolled back. Therefore, you need to set the XACT_ABORT option to ON in most cases to roll back the transaction when a runtime error occurs.
Run the following T-SQL

The code is as follows:	Copy code
SET XACT_ABORT ON; Begin tran; Delete from db. Orders WHERE OrderID = 10250; Delete from dbo. "Order Details" WHERE OrderID = 10250; Commit tran; GO

Execution result: Orders, Order Details table deletion failed because an error occurred while deleting Orders table data, so the transaction rolled back without continuing to execute the second T-SQL, this proves that the transaction is atomic.

Consistency:
All related rules must be applied to transaction modifications to ensure transaction integrity, such as user-defined integrity (business logic) and data integrity provided by the database engine (such as entity integrity, domain integrity, reference integrity, NULL, DEFAULT ). It can also be understood that the transaction operation must comply with the existing database rules.

Isolation: isolation mainly indicates that transactions cannot affect each other (mainly through locks and row version control ).
Two new query windows are opened:
Enter the following T-SQL in the 1st window and execute

The code is as follows:	Copy code
Begin tran; UPDATE dbo. Products SET ProductName = n' iphone4' WHERE ProductID = 1; GO

Enter the following T-SQL in the 2nd window and execute

The code is as follows:	Copy code
SELECT * FROM dbo. Products; GO

The status of the second window is "querying... ", this is because the first transaction is not completed (commit or rollback), and the second transaction needs to read the resources that the first transaction depends on, the transaction does not recognize the intermediate state (we can also handle

Resolved to "unknown state", because we cannot know whether the final state of the first transaction is committed or rolled back before it is completed, the second transaction cannot be read. This example directly describes the transaction

Isolation.

Persistence: persistence mainly depends on the logging device. SQL Server 2005 refers to transaction logs. 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. The specific implementation is not described in detail here. You can refer to the relevant materials.