Transactions are used to ensure that a series of statements written to modify data pass the ACID test that enforces the data integrity, accuracy, concurrency, and recoverability of the data in the database. The ACID properties of a transaction are…
- Atomicity – This ties several statements together to ensure that a series of statements either all succeed or all fail together.
- Consistent – This is actually enforced by using transaction logs to ensure that only valid data that has been committed will be written to the database.
- Isolated – This is enforced by using locks to control concurrency. More specifically to make sure that a record being updated by one statement does not interfere with a separate statement trying to modify that same record or records.
- Durable – Once again transactions logs are used as a way of recovering data in case of a database failure. This is also controlled by the Recovery model of your database.
In this example we see two UPDATE statements where $200 is being subtracted from account 1 and then $200 is being added to account 2. Both statements are considered to be two separate Auto Commit Transactions. When the main datafile writes a checkpoint to the log file all statements that have been committed at the checkpoint will be written into the database. Which is fine if there are not any errors. However, if the first UPDATE statement had an error it would not commit to the database, however the second UPDATE statement would be committed and written to the database. This would cause inconsistent and incorrect data in our database. So we will add Explicit Transactions to our code.
Here we have used the Begin Transaction and Commit Transaction statements to tie the two statements together. We have also changed the code in the first UPDATE statement to cause a divide by zero run-time error to happen. When the code runs the first UPDATE statement will cause an error and will not be committed to the database, however, just like before the second UPDATE statement will still be committed. So although we have tied the statements together we still need to add some error handling to manually Rollback the transaction.
With the TRY/CATCH error handling added when there is an error anywhere in the code the entire transaction is rollback. So either all the code is successful or none of the code is successful (Atomicity), only data that is valid or committed is written to the database (Consistent), and the data can be recovered from the transaction log (Durable). Again, the Isolated part of a transaction is handled by locks which will be a post in the future.
Another way of turning on transactions is by using the SET IMPLICT_TRANSACTION ON statement. This would implicitly turn on transactions as soon as the first statement runs without having to use the Begin Transaction statement. However, it will not finish until an explicit Commit Transaction or Rollback Transaction is reached. For better control and readability, it is still best practice to use Explicit Transactions with the Begin Transaction statement.
Finally, if we did not want to include the error handling but still use the functionality of the transactions. We could use the SET XACT_ABORT ON statement. This statement would terminate the transaction once the error was reached and cancel out the rest of the code. However, we would not be able to add any other error handling functionality without using the TRY/CATCH error handling blocks.