Managing Transactions

When we're trying to update multiple tables, how do we make sure that our database is always going to be left in a consistent state. What happens if part of the operation succeeds and the other part of the operation fails? This is where managing transactions comes into play, and it is important to control all transactions, the different types of locking , and how we can ensure that we're going to limit our exposure to potential problems.

How a transaction works inside of SQL Server ?

Transactions are required because most of the time when we're performing operations inside of SQL having to modify data, we're going to be dealing with multiple tables. For an example we might need to delete a customer and all the orders that are associated with that customer and what happens if only part of that operation succeeds ? what happens if I delete only few orders of that customer ? How do we then have to go back in and fix that ? then you might notice that there are lot of possible problems inside the database when it comes to data integrity.

Transactions will control the entirety of an operation and ensure that each and every transaction going to succeed or everything is going to get rolled back should something fail. Transactions must meet an ACID test.

ACID

A - Atomic (Entire operation must succeed or the entire operation must fail as a single unit)
C - Consistent (Once the operation is complete, Database must be left in a consistent or in a valid state or if only part of that operation succeeds, everything was rolled back and now back in the same state in which we started.)
I - Isolated (No Other operations can impact my operation)
D- Durable (When the operation is completed, changes are safe)

SQL has a default of what's known as an automatic transaction, therefore SQL transactions do meet the atomic test. If something fail in the middle of insert, update or delete statement, maybe only one row is going to fail out, the entire operation will fail out.

Automatic Transaction

- Automatically commits after each statement. which means that each individual insert, update or delete statement is in and of itself, a transaction, meaning that should something fail in the middle of an update statement, maybe only one row is going to fail out, the entire operation will fail out.

Implicit Transactions

- Implicit Transactions must manually commit. Which means that the moment you begin modifying the database, it's automatically going to start up a transaction and it's up to you to explicitly commit that at the end.

Manual Transactions

1. BEGIN TRANSACTION ( or TRAN)
- Start a Transaction

2. COMMIT TRANSACTION
- Writes all changes to the database

3. ROLLBACK TRANSACTION
- Reverses all Operations

Implement a transaction

USE AdventureWorks2012;

BEGIN TRANSACTION ;

UPDATE HumanResources.Employee
SET JobTitle = 'Senior Manager';

let's say SET JobTitle = 'Senior Manager'. And you'll notice that it comes back and it tells me that we have modified 290 rows. The reason, for that is that we did not specify a WHERE statement. So when you just do an update, it just does everything, For An Example,

SELECT

BusinessEntityID, JobTitle 

FROM HumanResources.Employee;

You'll notice sure enough, everybody is now Senior Manager, which is not really what we want, so we've obviously made a mistake, we want to roll this back. And the way that we roll this back is by utilizing a ROLLBACK TRANSACTION statement.

ROLLBACK TRANSACTION ;

Complete Transaction

USE AdventureWorks2012;

BEGIN TRANSACTION ;

UPDATE HumanResources.Employee
SET JobTitle = 'Senior Manager'   WHERE   BusinessEntityID = 10;

COMMIT TRANSACTION;