SQL Server transaction Overview (I)

SQL Server transaction Overview (1)

　　Transaction attributes

Transactions have ACID properties, I .e. atomic atomicity, consistent consistency, isolated isolation, durable permanent

Atomicity

That is, the transaction should be taken as a unit of work, and the transaction is processed completely. All the work is either saved in the database or completely
Rollback, all are not retained

Consistency

After the transaction is completed or canceled, it should be in the same state.

Isolation

When multiple transactions are performed at the same time, they should not interfere with each other. When a transaction is prevented from processing data that other transactions also need to modify,
Unreasonable access and incomplete data reading

Permanent

After the transaction is committed, the work is permanently saved.

　　Problems arising from concurrent Transaction Processing

Update loss

When two or more transactions select the same row and update the row based on the originally selected value, the update will be lost,
Every transaction does not know the existence of other transactions. The last update will
Rewrite updates made by other firms, which will lead to data loss.

Dirty read

When the second transaction selects another row being updated, unconfirmed correlation issues will occur. The data being read by the second transaction has not been confirmed and may be changed by the transaction that updates this row.

Non-repeated read

When the second transaction accesses the same row multiple times and reads different data each time, an inconsistent analysis problem occurs. Inconsistent analysis is similar to unconfirmed relevance because other transactions are changing
The data being read by the second transaction. However, in an inconsistent analysis, the data read by the second transaction is committed by a transaction that has been changed. Furthermore, inconsistent analysis involves multiple (two or more) reads
The same row, and the information is changed by other transactions each time; therefore, the row is read non-duplicate.

Phantom read

A phantom reading problem occurs when a row is inserted or deleted and the row belongs to the row being read by a transaction. The row range of the first transaction read shows that one row no longer exists in
Secondary read or subsequent read, because this row has been deleted by other transactions. Similarly, due to the insert operation of other transactions, the second or subsequent read of the transaction shows that a row does not exist in the original read.

　　Three transaction processing types

Automatic Transaction Processing

By default, each T-SQL command is a transaction that is automatically started and committed by the system.

Implicit transactions

When a large number of DDL and DML commands are executed, the system starts automatically until the user explicitly commits them. You can use the SET
IMPLICIT_TRANSACTIONS sets the implicit transaction mode for the connection. When SET to ON, SET IMPLICIT_TRANSACTIONS
Set the connection to the implicit transaction mode. When it is set to OFF, the connection is returned to the automatic commit transaction mode.

User-Defined transactions

User-controlled start and end commands of transactions include: begin tran commit tran rollback tran command

Distributed transactions

Transactions that span multiple servers are called distributed transactions. SQL server can be run by DTc microsoft distributed
Transaction coordinator
To support distributed transactions, you can use BEgin distributed
The transaction command starts a distributed transaction.