| Every business process consists of one or more transactions. Imagine that you are managing an online store. When a customer orders a product, a predefined process must run to guarantee on-time delivery. The process must also include credit card processing to be sure that your company gets paid. If one of these tasks fails and cannot be corrected, the whole process must be canceled to ensure that the customer is not invoiced without getting the product or vice versa. In most cases, these processes are handled by computer systems, where all data is stored in databases. The data related to one business transaction should be changed to be reliable, consistent, and complete in order to map the business processes. This can be done by using transactions on the database level. A transaction is defined as a sequence of operations executed as a unit that follows the so called ACID properties: Atomicity Each transaction is a unit of work. It cannot be broken into smaller parts. This property means that either all data modifications defined in the transaction are done, or none are performed at all. Consistency A transaction cannot break any integrity checks defined in the database. To maintain consistency, all rules, constraints, checks, and triggers are applied during the transaction. Since all data modifications are applied during the transaction, the data is guaranteed to be consistent before the transaction starts and after the transaction finishes. Isolation Transactions must be isolated from data modifications done by other transactions. This means that no other operation can change data in an intermediate (not committed) state. To keep intermediate data from being altered, the transaction must either wait until the changes from the other transactions are committed or see the data in the previously committed state. Durability After a transaction is completed and the client application has been notified that the transaction completed successfully, the data changes are permanent regardless of any system failures.
The SQL Server database engine enforces the physical consistency of the transaction and ensures the durability of transactions by means of the transaction log. SQL Server also enforces all consistency checks against constraints, datatypes, and so on to guarantee logical consistency. This is all enforced automatically by SQL Server. However, in order to map business transactions to SQL Server transactions, the developer has to design some transactions very carefully. 1. | Define the boundaries of a transaction. The developer has to define where a transaction starts and where it ends. A transaction should always be as short as possible but as long as it has to be to map to the requirements of the business process.
| 2. | Define error management. Not all errors automatically roll back transactions. It is the developer's responsibility to implement error management.
| 3. | Define the isolation. Isolating different transactions always has disadvantages relating to concurrency. If you fully isolate your transaction and another transaction wants to read the same data but not the previous state, it is blocked until you end your transaction. This can be a huge problem for database systems with many concurrent connections. SQL Server implements different variations of isolation levels that have to be chosen in the right way. The general rule is to choose the level that locks as little data as possible for as short a time as possible but still gives you the transaction safety you need.
|
In the remainder of this chapter, we will see how to design and implement transactions in SQL Server 2005. |
