Full transaction strategy

Source: Internet
Author: User
Tags commit
Transaction attributes
Transactions have ACID properties: Atomic atomicity, Consistent consistency, 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 is either saved in the database, or completely rolled back, but not reserved.
After a consistent transaction is completed or canceled, it should be in the same state.
The isolation of multiple transactions at the same time, they should not interfere with each other. It should prevent a transaction from unreasonable access and incomplete data reading when processing data that other transactions also need to modify.
After a permanent transaction is committed, the work done will be permanently saved.
Problems arising from concurrent transaction processing
Loss update when two or more transactions select the same row and update the row based on the originally selected value, the loss update problem occurs, and each 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.
It cannot be read repeatedly. 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 occurs when a row is inserted or deleted, and the row is within the range of 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, 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 system by default, each T-SQL command is a transaction that is automatically started and committed by the system
Implicit transactions are automatically started when a large number of DDL and DML commands are executed and kept until the user explicitly commits them. To switch between implicit transactions, you can use SET IMPLICIT_TRANSACTIONS. set the implicit transaction mode for the connection. when SET to ON, SET IMPLICIT_TRANSACTIONS sets the connection to Implicit transaction mode. When it is set to OFF, the connection is returned to the automatic commit transaction mode.
The start and end commands for user-defined transactions are: begin tran commit tran rollback tran command
Distributed transactions that span multiple servers are called distributed transactions. SQL server supports distributed transactions by DTc microsoft distributed transaction coordinator. You can use the BEgin distributed transaction command to start a distributed transaction.
Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.