SQL transaction Overview

The attribute transaction of a transaction has the ACID attribute, that is, Atomic atomicity, Consistent consistency, and Isolated isolation. Durable permanent atomicity means that the transaction should be taken as a unit of work and the transaction is processed completely, all the work should either be saved in the database or completely rolled back. After the consistency transaction is completed or abolished, all the work should be

The attribute transaction of a transaction has the ACID attribute, that is, Atomic atomicity, Consistent consistency, and Isolated isolation. Durable permanent atomicity means that the transaction should be taken as a unit of work and the transaction is processed completely, all the work should either be saved in the database or completely rolled back. After the consistency transaction is completed or abolished, all the work should be

Transaction attributes

Transactions have ACID properties
That is, 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, and the Hong Kong virtual host is either 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 and they do not interfere with each other, the Hong Kong virtual host should prevent a transaction from processing data that needs to be modified by other transactions,
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 overwrite the 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.
The inconsistent analysis is similar to the unconfirmed correlation because other transactions are also 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, the inconsistent analysis involves reading the same row multiple times (twice or more) and the information is changed by other transactions each time. Therefore, the row is read non-repeatedly.

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 for the first read of the transaction shows that one row no longer exists in the second read or subsequent read because the row has been deleted by other transactions. Similarly, because of the insert operation of other transactions and the no-record filing space, the second or subsequent read of the transaction shows that one 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 SET IMPLICIT_TRANSACTIONS to switch between implicit transactions.
SET the implicit transaction mode for the connection. When SET to ON, SET IMPLICIT_TRANSACTIONS sets 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, and rollback tran.

Distributed transactions
Transactions that span multiple servers are called distributed transactions. SQL server can be operated by DTc microsoft distributed transaction coordinator.
To support distributed transactions. You can use the BEgin distributed transaction command to start a distributed transaction.


Iv. isolation level of Transaction Processing

Use the set transaction isolation level to control the default TRANSACTION lock behavior of all statements sent by the connection.

From low to high is

(1) READ UNCOMMITTED

Execute dirty read or 0-level isolation lock, which means no shared lock is issued or the exclusive lock is not accepted. When this option is set, uncommitted or dirty reads can be performed on the data. Before the transaction ends, the values in the data can be changed, and the rows can also appear in the dataset or disappear from the dataset. This option is used to set NOLOCK for all tables in all statements in the transaction. This is the minimum limit among the four isolation levels.

Example