Transaction and Isolation Levels - Quiz Explanation

The correct answers are indicated below, along with text that explains the correct answers.

1.	Fred finds out that many users are reporting inconsistent query results. He does some research and finds out that his session is using the lowest isolation level. What isolation level is being used? Please select the best answer.
	A.	`READ COMMITTED`
	B.	`READ UNCOMMITTED`
	C.	`REPEATABLE READ`
	D.	`SERIALIZABLE`
	The correct answer is B. `READ UNCOMMITTED` is the lowest isolation level and permits dirty reads (reading uncommitted changes), which can lead to inconsistent results. A is incorrect because `READ COMMITTED` is SQL Server’s default and prevents dirty reads. C and D are higher isolation levels that reduce anomalies but can increase locking and blocking.

2.	Margaret creates a transaction within a transaction. Her first (outermost) transaction rolls back data and the second (innermost) transaction commits data. What will be the outcome? Please select the best answer.
	A.	Both transactions are committed.
	B.	Only the first (outermost) transaction is committed.
	C.	Both transactions are rolled back.
	D.	Only the second (innermost) transaction is committed.
	The correct answer is C. In SQL Server, nested `BEGIN TRAN` statements increment `@@TRANCOUNT`, but there is still a single effective transaction scope. An inner `COMMIT` only decrements `@@TRANCOUNT`. If the outer transaction rolls back, the entire unit of work is undone and all changes roll back.

3.	If the `SET IMPLICIT_TRANSACTIONS` option is NOT ON, what happens by default? Please select the best answer.
	A.	Implicit transactions are required for SQL Server to support transactions.
	B.	Only explicit transactions are supported.
	C.	The session uses autocommit behavior by default.
	D.	SQL Server does not support transactions.
	The correct answer is C. With `SET IMPLICIT_TRANSACTIONS OFF` (the default), SQL Server uses autocommit behavior: each statement is committed automatically unless you explicitly start a transaction with `BEGIN TRAN`. A and D are incorrect because SQL Server supports transactions regardless of this setting. B is incorrect because explicit transactions are always available.

4.	What is SQL Server’s default isolation level? Please select the best answer.
	A.	`READ COMMITTED`
	B.	`READ UNCOMMITTED`
	C.	`REPEATABLE READ`
	D.	`SERIALIZABLE`
	The correct answer is A. SQL Server’s default isolation level is `READ COMMITTED`, which prevents dirty reads by reading only committed data. B is incorrect because `READ UNCOMMITTED` is the lowest level and allows dirty reads. C and D are higher isolation levels that provide stronger consistency but can increase locking and blocking.

5.	Which isolation level has the highest risk of blocking? Please select the best answer.
	A.	`READ COMMITTED`
	B.	`READ UNCOMMITTED`
	C.	`REPEATABLE READ`
	D.	`SERIALIZABLE`
	The correct answer is D. `SERIALIZABLE` typically has the highest risk of blocking because SQL Server may use key-range locks to prevent phantom reads, which can reduce concurrency under contention. A and C can also block (especially in long transactions), but they are generally less restrictive than `SERIALIZABLE`. B is the least restrictive and tends to have the lowest blocking risk, but it permits dirty reads.