A transaction specifies a sequence of Transact-SQL statements that is used by database programmers to package together read and write operations, so that the database system can guarantee the consistency of data. There are two forms of transactions:
- Implicit Specifies any single INSERT, UPDATE, or DELETE statement as a transaction unit
- Explicit Generally, a group of Transact-SQL statements, where the beginning and the end of the group are marked using statements such as BEGIN TRANSACTION, COMMIT, and ROLLBACK
The notion of a transaction is best explained through an example. In the sample database, the employee Ann Jones should be assigned a new employee number. The employee number must be modified in two different tables at the same time. The row in the employee table and all corresponding rows in the works_on table must be modified at the same time. (If only one of these tables is modified, data in the sample database would be inconsistent, because the values of the primary key in the employee table and the corresponding values of the foreign key in the works_on table for Ann Jones would not match.) Example 13.1 shows the implementation of this transaction using Transact-SQL statements.
USE sample;
BEGIN TRANSACTION /* The beginning of the transaction */
UPDATE employee
SET emp_no = 39831
WHERE emp_no = 10102
IF (@@error <> 0)
ROLLBACK /* Rollback of the transaction */
UPDATE works_on
SET emp_no = 39831
WHERE emp_no = 10102
IF (@@error <> 0)
ROLLBACK
COMMIT /*The end of the transaction */Code language: PHP (php)The consistent state of data used in Example 13.1 can be obtained only if both UPDATE statements are executed or neither of them is executed. The global variable @@error is used to test the execution of each Transact-SQL statement. If an error occurs, @@error is set to a negative value and the execution of all statements is rolled back. (The Transact-SQL statements BEGIN TRANSACTION, COMMIT, and ROLLBACK are defined in the upcoming section “Transact-SQL Statements and Transactions.”)
The next section explains the ACID properties of transactions. These properties guarantee that the data used by application programs will be consistent.
Properties of Transactions
Transactions have the following properties, which are known collectively by the acronym ACID:
- Atomicity
- Consistency
- Isolation
- Durability
The atomicity property guarantees the indivisibility of a set of statements that modifies data in a database and is part of a transaction. This means that either all data modifications in a transaction are executed or, in the case of any failure, all already executed changes are undone.
Consistency guarantees that a transaction will not allow the database to contain inconsistent data. In other words, the transactional transformations on data bring the database from one consistent state to another.
The isolation property separates concurrent transactions from each other. In other words, an active transaction can’t see data modifications in a concurrent and incomplete transaction. This means that some transactions might be rolled back to guarantee isolation. Durability guarantees one of the most important database concepts: persistence of data. This property ensures that the effects of the particular transaction persist even if a system error occurs. For this reason, if a system error occurs while a transaction is active, all statements of that transaction will be undone.
