Transactions in SQL Server

The meaning of the transaction:

For a transaction to have a very clear start and end point, every data manipulation statement in SQL Server, such as SELECT, INSERT, update, and delete, is part of an implicit transaction. Even if there is only one statement, the system will take this statement as a transaction, either executing all the statements or doing nothing.

After the transaction begins, the operations of the firm are written to the transaction log, the transactions written to the log, there are generally two kinds: first, the operation of data, such as inserting, modifying and deleting, the object of these operations is a large amount of data, the other is the action for the task, such as the creation of indexes. When these transaction operations are canceled, the system automatically performs the reverse operation of these operations to ensure the consistency of the system. The system automatically generates a checkpoint mechanism, which checks the transaction log for the periodic check point. If the transaction completes in the transaction log, the transaction in the checkpoint transaction log is committed to the database and a checkpoint is committed in the transaction log; If the transaction is not completed in the transaction log, the checkpoint does not commit the transaction in the transaction log to the database. And make a check point uncommitted identity in the transaction log. The recovery and checkpoint of the transaction ensure the integrity and recoverability of the system.

Properties of transaction:

A transaction is a series of operations performed as a single logical unit of work. A logical unit of work must have 4 properties, called Atomicity (Atomic), consistency (consistent), isolation (Isolated), persistence (durable), or acid property, which can constitute a transaction.

Atomic nature:

All operations in the entire transaction, either complete or incomplete, cannot be stalled in the middle of the process. An error occurs during the execution of a transaction and is rolled back (Rollback) to the state before the transaction began, as if the transaction had never been performed.

Consistency:

Database integrity constraints are not compromised until the transaction is started and after the transaction ends.

Isolation:

The execution of two transactions is mutually exclusive, and a transaction cannot see the data at a time when other transactions run.

Durability:

After the transaction is completed, the changes made by the firm to the database are persisted in the database and are not rolled back.

It is not easy to implement acid properly because an operation typically contains many child actions that may cause problems due to hardware corruption or other factors. The acid recommends that the database complete all the data that needs to be updated and modified, but it is not actually feasible.

Common statements for Transaction management:

BEGIN transaction--Establish a transaction

committransaction--COMMIT Transaction

ROLLBACK rollback operation when transaction--transaction fails

Save transaction--Saving transactions

Begin TRANSACTION and commit TRANSACTION are used concurrently to indicate the start and end of a transaction.

Isolation level of transaction:

Transactions are isolated, the time used in different transactions must be isolated from other transactions, and at the same time many transactions are processing data, but each data can only be manipulated at the same moment. If data is locked, transactions that use data must be queued to prevent multiple transactions from affecting one another. However, if there are several transactions that lock up their data while waiting for other transactions to release the data, a deadlock is caused.

To increase the efficiency of data concurrency, you can set the quarantine state for transactions while reading data, and the transaction isolation status in SQL Server is divided into 4 levels from low to high.

Unauthorized Read

Also known as READ UNCOMMITTED (READ UNCOMMITTED): Allows dirty reads, but does not allow updates to be lost. If a transaction has started to write data, another data does not allow write operations at the same time, but allows other transactions to read the row data. The isolation level can be implemented through exclusive write locks.

Authorized Read

Also known as Read Committed: Allows non-repeatable reads, but does not allow dirty reads. This can be achieved by "instantaneous shared read lock" and "exclusive write lock". Transactions that read data allow other transactions to continue accessing the row data, but uncommitted write transactions will prevent other transactions from accessing the row.

REPEATABLE READ

REPEATABLE READ (REPEATABLE Read): Prevents non-repeatable reads and dirty reads, but sometimes phantom data may occur. This can be achieved through shared read locks and exclusive write locks. Transactions that read data will prohibit write transactions (but read transactions are allowed), and write transactions prohibit any other transaction.

Serialization of

Serialization (Serializable): Provides strict transaction isolation. It requires transaction serialization execution, which can only be performed one after another, but not concurrently. If transaction serialization is not possible only through row-level locks, additional mechanisms must be used to ensure that the newly inserted data is not accessed by the transaction that just performed the query operation.