Transactions in SQL Server (meaning, attributes, management)

Meaning: the transaction must have a very clear start and end point. Each data operation 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 regards this statement as a transaction, either to execute all statements or to execute nothing. After the transaction starts, the firm

Meaning: A transaction must have a very clear start and end point. Each data operation 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 regards this statement as a transaction, either to execute all statements or to execute nothing. After the transaction starts, the firm

Meaning:

A transaction must have a very clear start and end point. Each data operation 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 regards this statement as a transaction, either to execute all statements or to execute nothing.

After the transaction starts, some operations of the transaction will be written to the transaction log and written to the transaction in the log. There are generally two types of operations: one is for data, such as insert, modify, and delete, the objects of these operations are large amounts of data, and the other is operations on tasks, such as creating indexes. When you cancel these transaction operations, the system automatically performs the inverse operations to ensure system consistency. The system automatically generates a checkpoint mechanism for checking transaction logs in this checkpoint cycle. If all transactions are completed in the transaction log, the transactions in the checkpoint transaction log are committed to the database, and a checkpoint commit ID is made in the transaction log. If the transactions are in the transaction log, if the transaction is not completed, the checkpoint does not commit the transaction in the transaction log to the database, and an uncommitted identifier is provided in the transaction log. Transaction recovery and Checkpoints ensure system integrity and recovery.

Transaction attributes:

A transaction is a series of operations performed as a single logical unit of work. A logical unit of work must have four attributes: Atomic, Consistent, Isolated, and Durable, only in this way can a transaction be formed.

Atomicity:

All the operations in the entire transaction are either completed or not completed, and it is impossible to stop at a stage in the middle. When a transaction encounters an error during execution, it will be rolled back to the state before the start of the transaction, just as this transaction has never been executed.

Consistency:

The integrity constraints of the database are not damaged before and after the transaction starts.

Isolation:

The execution of two transactions does not interfere with each other. One transaction cannot see the data at a certain time in the middle of another transaction.

Durability:

After the transaction is completed, the changes made by the firm to the database are permanently stored in the database and will not be rolled back.

Because an operation usually contains many sub-operations, these sub-operations may cause problems due to hardware damage or other factors, it is not easy to implement ACID correctly. ACID recommends that the database complete all the data to be updated and modified at one time, but it is not feasible.

Common statements for transaction management:

Begin transaction -- create a TRANSACTION

COMMITTRANSACTION -- submit a transaction

Rollback transaction -- roll back when the TRANSACTION fails

Save transaction -- SAVE the TRANSACTION

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

Transaction isolation level:

Transactions are isolated. The time used in different transactions must be isolated from other transactions. At the same time, many transactions can process data, however, each data can only be operated by one transaction at a time. If the data is locked, the transaction that uses the data must wait in queue to prevent multiple transactions from affecting each other. However, if several transactions lock their own data while waiting for other transactions to release the data, a deadlock will occur.

To improve the efficiency of concurrent data usage, you can set the isolation status for the transaction when reading data. The transaction isolation status in SQL Server 2012 is 5 levels from low to high.

Unauthorized Read

Read Uncommitted: Allows dirty reading, but does not allow loss of updates. If a transaction has started writing data, the write operation on the other data is not allowed at the same time, but other transactions are allowed to read this row of data. This isolation level can be achieved through the exclusive write lock.

Authorized read

, Also known as Read Committed: dirty reads are not allowed. This can be achieved through "instant shared read lock" and "exclusive write lock. Transactions that read data allow other transactions to continue to access this row of data, but uncommitted write transactions will prohibit other transactions from accessing this row.

Repeatable reading

Repeatable Read: Prohibit repeated reading and dirty reading, but sometimes phantom data may appear. This can be achieved through the "shared read lock" and "exclusive write lock. The transaction that reads data will prohibit the write transaction (but allow the read transaction), and the write transaction will prohibit any other transactions.

Serialization

Serializable: provides strict transaction isolation. It requires the transaction to be serialized. The transaction can only be executed one by one, but cannot be executed concurrently. If transaction serialization is not possible only through the "Row-Level Lock", other mechanisms must be used to ensure that the newly inserted data is not accessed by the transaction that just performs the query operation.