sql server 事务
In general, a Transaction is a single unit of work consists of multiple related tasks that should succeed or fail as one atomic unit. To make the concept of the transaction more familiar and why it should go all or none, imagine one of the most critical transaction examples in our daily life, which is withdrawing money from the ATM.
通常， 事务是一个工作单元，由多个相关任务组成，这些任务应该作为一个原子单元成功或失败。 为了使交易的概念更加熟悉，以及为什么不进行全部交易或不进行交易，请想象一下我们日常生活中最关键的交易示例之一，即从ATM取款。
After a hard-working month, you are happy that your salary is released. As it is late to visit the bank after the working hours, your destination now is the ATM. All of us are familiar with the steps of withdrawing money from the ATM, but it deserves listing the common ones below:
经过一个月的努力工作，您很高兴自己的工资被释放了。 由于下班后去银行晚了，所以现在您的目的地是ATM。 我们所有人都熟悉从ATM取款的步骤，但是值得在下面列出以下常见的步骤：
- Insert your card 插入您的卡
- Select the language 选择语言
- Enter your PIN 输入密码
- Select the transaction type, which is withdraw in our example here 选择交易类型，在我们的示例中将其提取
- Enter the amount 输入金额
- Pull the card 拉卡
- Take your money 拿你的钱
The previous tasks should run as one unit to keep your bank account in consistent state. This means that, if you specify the amount to withdraw from your bank account, it should be guaranteed that the money is getting out of the ATM before confirming the deduction from your account. If any task from the previous tasks fails, the overall withdraw transaction should be cancelled. For example, if any technical or mechanical error occurred after specifying the amount and deducting it from your account, that prevents the money from getting out the ATM, the operation should be canceled and the amount will be returned to your account.
先前的任务应作为一个单元运行，以使您的银行帐户保持一致状态。 这意味着，如果您指定从您的银行帐户中提取的金额，则应在确认从您的帐户中扣除该款项之前，确保该钱已从自动柜员机中取出。 如果先前任务中的任何任务失败，则应取消整体提款事务。 例如，如果在指定金额并将其从您的帐户中扣除后发生任何技术或机械错误，这会阻止钱从自动柜员机中取出，则应取消该操作并将金额退回到您的帐户中。
In SQL Server, the Transaction concept is highly required to maintain the integrity of data in a database, especially when executing multiple related tasks sequentially on different tables, databases or servers, or accessing the same records by more than one session concurrently. In all these cases, the transaction should work as one unit of failure or success.
在SQL Server中，非常需要事务处理概念来维护数据库中数据的完整性，尤其是在不同的表，数据库或服务器上顺序执行多个相关任务，或者同时通过多个会话访问同一条记录时。 在所有这些情况下，事务应作为失败或成功的一个单元来工作。
In the ATM withdraw example, the SQL Server Engine will commit the overall transaction, including the amount deduction from the bank account, when all the steps are performed successfully, and the money received by the user. To handle the concurrency, the SQL Server Engine holds a lock on the bank account during that transaction, to guarantee that no other transaction will be performed on the same account at the same time, that may result with dirty read or incorrect operation result, and release the lock when the overall transaction is committed or rolled back. Due to this lock that prevents other transactions from accessing the same records, it is better always better to narrow the scope of the transaction, so that the records will be locked for short period of time, in order not to affect the overall performance of the system.
在ATM提款示例中，当所有步骤成功执行后，SQL Server Engine将提交整个交易，包括从银行帐户中扣除的金额以及用户收到的钱。 为了处理并发，SQL Server引擎在该事务期间对银行帐户持有一个锁，以确保不会在同一时间对同一帐户执行其他任何事务，否则可能会导致读取错误或操作结果不正确，以及在提交或回滚整个事务时释放锁。 由于此锁定可防止其他事务访问相同的记录，因此最好总是缩小事务范围，以便在短时间内锁定记录，以免影响事务的整体性能。系统。
酸 (ACID)
After understanding the concept of the SQL Server Transaction, we can describe the Transaction using the four ACID properties. ACID is the acronym for Atomicity, Consistency, Isolation and Durability.
了解了SQL Server事务的概念之后，我们可以使用四个ACID属性描述事务。 ACID是原子性 ， 一致性 ， 隔离性和耐久性的首字母缩写。
Atomicity means that the transaction will succeed, as one unit, if all the separate tasks succeed with no issue. On the other hand, the failure of any single task within this transaction leads to the overall transaction failure and rollback. In other words, Atomicity guarantees that the transaction is all or none.
原子性意味着，如果所有单独的任务都没有问题，则事务将作为一个单元成功完成。 另一方面，此事务中任何单个任务的失败都会导致整个事务失败和回滚。 换句话说，原子性保证了交易是全部或全部。
Consistency guarantees that the transaction will not affect the database consistency and will leave it in a valid state by complying with all database rules, such as foreign keys and constraints, defined on the columns.
一致性保证事务不会影响数据库的一致性，并且通过遵守列上定义的所有数据库规则（例如外键和约束），可以使事务处于有效状态。
Isolation means that, each transaction has it is own boundary, that is separated from the other concurrently executing transactions, and will not be affected by these transactions’ operations.
隔离意味着，每个事务都有自己的边界，与其他同时执行的事务分开，并且不受这些事务的操作的影响。
Durability means that the result of the committed transaction that is written permanently to the database will not be lost even in the case of any abnormal system failure or termination, as there should be mechanism of recovering this data, that we will see later in the next articles of this series.
持久性意味着即使在任何异常系统故障或终止的情况下，永久写入数据库的已提交事务的结果也不会丢失，因为应该有恢复该数据的机制，我们将在下一部分中看到该系列的文章。
交易状态 (Transaction states)
The states of the transaction can be summarized as follows:
事务的状态可以总结如下：
- Active transaction 活动事务
- Committed transaction 提交事务
- Aborted transaction 中止事务
- Partially Committed transaction 部分提交的事务
- Failed transaction, that is 失败事务， Aborted without being committed 即已中止而未提交
- Partially Committed transaction completes its execution successfully, it will be 部分提交的事务成功完成其执行，它将被Committed, otherwise it will be 提交 ，否则将Failed then 失败然后Aborted 中止
提交 (Commits)
In SQL Server, each single T-SQL statement is considered as an Auto-Commit transaction, that will be committed when the statement is completed successfully and rolled back if the statement is failed. If the T-SQL query uses the BEGIN TRANSACTION statement to control the start the transaction, COMMIT TRANSACTION statement to complete the transaction successfully, and ROLLBACK TRANSACTION statement to abort the transaction, the auto-commit transaction mode will be overridden to become an Explicit Transaction.
在SQL Server中，每个单独的T-SQL语句均被视为自动提交事务，该事务将在该语句成功完成时提交 ，并在该语句失败时回滚。 如果T-SQL查询使用BEGIN TRANSACTION语句控制事务的启动，使用COMMIT TRANSACTION语句成功完成事务，并使用ROLLBACK TRANSACTION语句中止事务，则自动提交事务模式将被覆盖以成为显式事务 。
隐式与显式交易 (Implicit vs explicit transactions)
Assume that you want to perform a database change that starts but is not completed unless you explicitly indicate that. To achieve that, you can enforce the opened database connection to work in the Implicit Transaction mode by using the SET IMPLICIT TRANSACTIONS ON|OFF statements. In this case, the command specified after the SET statement will start executing and remain, and locking the database resource, until you explicitly issue a COMMIT statement to complete the transaction successfully or a ROLLBACK statement to abort the transaction and revert all changes.
假定您要执行一个数据库更改，该更改将开始但尚未完成，除非您明确指出。 为此，可以使用SET IMPLICIT TRANSACTIONS ON | OFF语句强制打开的数据库连接在隐式事务模式下工作。 在这种情况下，SET语句之后指定的命令将开始执行并保留，并锁定数据库资源，直到您显式发出COMMIT语句以成功完成事务或使用ROLLBACK语句中止事务并还原所有更改为止。
When the Multiple Active Result Sets (MARS) option is enabled, the implicit or explicit transaction that has multiple batches running at the same time will be running under the Batch-Scoped Transaction mode. In this case, the batch-scoped transaction that is not committed or rolled back after completing the batch will be rolled back automatically by the SQL Server Engine.
启用多个活动结果集（MARS）选项时，具有多个批次同时运行的隐式或显式事务将在“ 批次范围的事务”模式下运行。 在这种情况下，SQL Server引擎将自动回滚在完成批处理后未提交或回滚的批处理范围的事务。
本地交易与分布式交易 (Local vs distributed transactions)
SQL Server supports both the Local transactions that are processing data from the local database server and the Distributed transactions that are processing data from more than one database server. In distributed transactions, the transaction states, COMMIT and ROLLBACK coordination among the different servers is performed using a transaction manager component called the Microsoft Distributed Transaction Coordinator, also known as MSDTC. To commit the distributed transaction, all participants must complete their related task successfully. If even a single participant fails for any reason, the overall transaction fails, and any changes to data within the scope of the transaction will be rolled back.
SQL Server支持的是从本地数据库服务器处理数据，在本地事务和从多个数据库服务器处理数据的分布式事务。 在分布式事务中，使用称为Microsoft分布式事务协调器 （也称为MSDTC）的事务管理器组件来执行不同服务器之间的事务状态，COMMIT和ROLLBACK协调。 要提交分布式事务，所有参与者都必须成功完成其相关任务。 如果甚至单个参与者由于某种原因而失败，那么整个事务都会失败，并且事务范围内对数据的任何更改都将被回滚。
交易最佳做法 (Transaction best practices)
Using the SQL Server transaction helps maintaining the database integrity and consistency. On the other hand, a badly written transaction may affect the overall performance of your system by locking the database resources for long time. To overcome this issue, it is better to consider the following points when writing a transaction:
使用SQL Server事务有助于维护数据库的完整性和一致性。 另一方面，写得不好的事务可能会长时间锁定数据库资源，从而影响系统的整体性能。 为了克服此问题，编写事务时最好考虑以下几点：
- Narrow the scope of the transaction 缩小交易范围
- Retrieve the data from the tables before opening the transaction if possible 如果可能的话，在打开事务之前从表中检索数据
- Access the least amount of data inside the transaction body 访问事务主体内最少的数据量
- Do not ask for user input inside the body of the transaction 不要在交易主体内要求用户输入
- Use a suitable mode of transactions 使用合适的交易方式
- Use as suitable Isolation Level for the transaction 用作交易的适当隔离级别
In this article, we have tried to introduce the SQL Server Transaction concept in a simple way, that helps going through the SQL Server Transaction Log details in the coming articles of this series. Stay tuned!
在本文中，我们试图以一种简单的方式介绍SQL Server事务的概念，这有助于在本系列后续文章中详细介绍SQL Server事务日志的细节。 敬请关注！
目录 (Table of contents)
翻译自: https://www.sqlshack.com/sql-server-transaction-overview/
sql server 事务
1137
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
