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 of his users are complaining that data is lost. He does some research and finds out that his transactions are using the lowest isolation level. What level are his transactions using? 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. A is incorrect because `READ COMMITTED` is the second lowest isolation level. C is incorrect because `REPEATABLE READ` is the second highest isolation level. D is incorrect because `SERIALIZABLE` is the highest isolation level.

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. Both transactions are rolled back because the outermost transaction is rolled back. A is incorrect because both transactions cannot be committed. B is incorrect because the first transaction will not be committed. D is incorrect because the second transaction will not be committed.

3.	If the `SET IMPLICIT_TRANSACTIONS` option is NOT set to ON, what will happen? Please select the best answer.
	A.	The database will not support implicit transactions.
	B.	The database will support explicit transactions only.
	C.	The database will support auto-commit transactions.
	D.	The database will not support transactions.
	The correct answer is C. The database will support auto-commit transactions if the `SET IMPLICIT_TRANSACTIONS` option is not set to ON (or it is set to OFF). A is incorrect because it will support auto-commit transactions, which is an implicit transaction. B is incorrect because explicit transactions are supported, along with auto-commit transactions. D is incorrect because the database WILL support transactions.

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 C. `REPEATABLE READ` is SQL Server’s default isolation level. All other answers are incorrect.

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. The `SERIALIZABLE` isolation level provides the most risk of blocking, but the lowest risk of lost data. A is incorrect because `READ COMMITTED` provides the third highest risk of blocking. B is incorrect because it provides the least risk of blocking, but the highest risk of lost data. C is incorrect because `REPEATABLE` provides the second highest risk of blocking.