Scripts and Transactions in Transact-SQL - Transactions
(Page 2 of 5 )
Even from the very name of the Transact-SQL language, you can conclude that transactions play a major role in SQL Server. They are an important mechanism for enforcing the consistency and integrity of the database.
A transaction is the smallest unit of work in SQL Server. To qualify a unit of work as a transaction, it must satisfy the following four criteria, often referred to as the ACID test:
- Atomicity All data changes must be completed successfully, or none of them will be written permanently to the database.
- Consistency After a transaction, the database must be left in a consistent state. All rules must be applied during processing to ensure data integrity. All constraints must be satisfied. All internal data structures must be left in an acceptable state.
- Isolation Changes to the database made by a transaction must not be visible to other transactions until the transaction is complete. Before the transaction is committed, other transactions should see the data only in the state it was in before the transaction.
- Durability Once a transaction is completed, changes must not revert even in the case of a system failure.
Autocommit Transactions
In fact, every Transact-SQL statement is a transaction. When it is executed, it either finishes successfully or is completely abandoned. To illustrate this, let’s try to delete all records from the EqType table. Take a look at the following diagram:
A foreign key relationship exists between the EqType and Equipment tables. The foreign key prevents the deletion of records in the EqType table that are referenced by records in the Equipment table.
Let’s try to delete them anyway. You can see the result of such an attempt in Figure 5-5.
Two Select statements that will count the number of records in EqType are placed around the Delete statement. As expected, the Delete statement is aborted because of the foreign key. The count of records before and after the Delete statement is the same, which confirms that all changes made by the Delete statement were canceled. So the database remains in the state that it was in before the change was initiated.
If there were no errors, SQL Server would automatically commit the transaction (that is, it would record all changes) to the database. This kind of behavior is called autocommit.
Figure 5-5. Complete failure of attempt to delete records
In this case, SQL Server deleted records one after the other from the EqType table until it encountered a record that could not be deleted because of the foreign key relationship, at which point the operation was canceled.
Next: Explicit and Implicit Transactions >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Buy this book now.
|
|
/ 4 
