Lesson 1

Transactions are a very important topic, as they allow for multiple Transact-SQL statements to be treated as a whole. This allows for any failure of an individual statement to revert to the point before the transaction began. In this module, you will learn all about transactions and a closely-related topic, locks.

After completing this course, you will be able to:

1. Identify and define transactions
2. Define isolation levels
3. Identify and describe nested transactions
4. Describe locking and deadlocks
5. Create local and distributed transactions
6. Handle errors

The next lesson introduces you to transactions.

Transactions in SQL Server allow multiple Transact-SQL statements to be treated as a single unit of work by grouping them together. This ensures that either all of the statements in the transaction are successfully executed, or, if any of them fails, the entire transaction is rolled back to maintain data consistency. A transaction is initiated with the BEGIN TRANSACTION statement and is completed with either a COMMIT statement, which makes the changes permanent, or a ROLLBACK statement, which undoes all the changes made by the statements within the transaction. The key benefits of using transactions in SQL Server include:

1. Atomicity: The transaction is treated as a single unit, ensuring that all statements within it either succeed or fail as a group.
2. Consistency: Transactions ensure that the database remains in a consistent state by only committing changes if all the statements within the transaction are successful.
3. Isolation: Transactions allow multiple users to access the same data concurrently without interfering with each other's work.
4. Durability: Once a transaction is committed, the changes are made permanent and are not lost even in the event of a system failure.

In summary, transactions in SQL Server allow for multiple Transact-SQL statements to be treated as a whole by grouping them together and ensuring data consistency, atomicity, isolation, and durability.

Specifies that statements can read rows that have been modified by other transactions but not yet committed. Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels. In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

1. The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
2. The SNAPSHOT isolation level.