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.

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.