Lesson 12

In this module, we discussed why there are many things to consider when using transactions.
Along with transactions, you need to consider what happens when SQL Server locks the records associated with transactions.
This module discussed transactions, how they lock records, and the record locking options. After have examined the topics of this module, you should be able to:

1. Identify and define transactions
2. Understand isolation levels
3. Use nested transactions
4. Learn about locking and troubleshoot deadlocks
5. Create local and distributed transactions
6. Handle errors in your transactions

In SQL Server, isolation levels are used to control the behavior of concurrent transactions and to determine the level of isolation between them. The purpose of isolation levels is to prevent different transactions from interfering with each other, while still allowing them to access the same data.
There are four isolation levels in SQL Server:

1. READ UNCOMMITTED: This isolation level allows transactions to read data that is being modified by other transactions, even if those transactions have not yet been committed. This can lead to dirty reads, where a transaction reads data that is later rolled back.
2. READ COMMITTED: This isolation level ensures that transactions can only read data that has been committed by other transactions. This is the default isolation level in SQL Server.
3. REPEATABLE READ: This isolation level prevents other transactions from modifying data that has been read by a transaction, ensuring that the data remains consistent throughout the transaction. However, other transactions can still insert new rows.
4. SERIALIZABLE: This isolation level provides the highest level of isolation by preventing any other transactions from accessing the data that is being read or modified by a transaction. This can lead to blocking and deadlocks if not used carefully.

This module introduced you to the following terms:

1. ACID:In computer science, ACID (atomicity, consistency, isolation, durability) is a set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps.
2. Distributed transaction: A transaction distributed across multiple SQL Servers
3. Deadlock:A deadlock is a situation wherein two or more competing actions are waiting for the other to finish, and thus neither ever does.
4. Isolation level:
5. Nested: A nested transaction occurs when a new transaction is started by an instruction that is already inside an existing transaction. The new nested transaction is said to be nested within the existing transaction, hence the term.
6. Optimistic locking:
7. Pessimistic locking:
8. Timeout:
9. Two-phase commit:In transaction processing, databases, and computer networking, the two-phase commit protocol (2PC) is a type of atomic commitment protocol (ACP). It is a distributed algorithm that coordinates all the processes that participate in a distributed atomic transaction on whether to commit or abort (roll back) the transaction (it is a specialized type of consensus protocol)
10. Transaction:A set of SQL commands that are grouped together and either all succeed or all fail as a unit.

In the next module, SQL Server events will be discussed.

Exclusive locks are just what they sound like. Exclusive locks are not compatible with any other lock. They cannot be achieved if any other lock exists, nor will they allow a new lock of any form to be created on the resource while the exclusive lock is still active. This prevents two people from updating, deleting, or whatever at the same time.

Update locks are something of a hybrid between shared locks and exclusive locks. An update lock is a special kind of placeholder. In order to do an update, you need to validate your WHERE clause (assuming there is one) to figure out just which rows you are going to be updating. That means that you only need a shared lock, until you actually go to make the physical update. At the time of the physical update, you will need an exclusive lock. Update locks indicate that you have a shared lock that is going to become an exclusive lock after you have done your initial scan of the data to figure out what exactly needs to be updated. This acknowledges the fact that there are two distinct stages to an update:

1. The stage where you are figuring out what meets the WHERE clause criteria (what is going to be updated). This is the part of an UPDATE query that has an update lock.
2. The stage where, if you actually decide to perform the update, the lock is upgraded to an exclusive lock. Otherwise, the lock is converted to a shared lock.

What is nice about this is that it forms a barrier against one variety of deadlock. A deadlock is not a type of lock in itself, but rather a situation in which a paradox has been formed. A deadlock arises if one lock cannot do what it needs to do in order to clear because another lock is holding that resource. The problem is that the opposite resource is itself stuck waiting for the lock to clear on the first transaction. Without update locks, these deadlocks would crop up all the time. Two update queries would be running in shared mode. Query A completes its query and is ready for the physical update. It wants to escalate to an exclusive lock, but it cannot because Query B is finishing its query. Query B then finishes the query, except that it needs to do the physical update. In order to do that, Query B must escalate to an exclusive lock, but it cannot because Query A is still waiting. This creates an impasse. Instead, an update lock prevents any other update locks from being established. The instant that the second transaction attempts to achieve an update lock, they are put into a wait status for whatever the lock timeout is. The lock is not granted. If the first lock clears before the lock timeout is reached, the lock is granted to the new requester and that process can continue. If not, an error is generated. Update locks are compatible only with shared locks and intent shared locks.

Before moving on to the next module, click the Quiz link below to check your knowledge of the material covered in this module with a short, multiple-choice quiz.
Transaction Locks - Quiz