Transactions are used to ensure that a series of statements written to modify data will 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 data file 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 it 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.