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.