MCQs on Transactions and Concurrency | SQL Server
Discover essential concepts of Transaction Management, ACID properties, Locks, Isolation Levels, and Deadlocks. Enhance your SQL expertise with these 30 carefully designed MCQs for practical learning.
Transaction Management
- What is the purpose of a transaction in SQL Server?
a) To execute multiple operations atomically
b) To optimize performance
c) To backup the database
d) To create indexes - Which command explicitly starts a transaction in SQL Server?
a) START TRANSACTION
b) BEGIN TRANSACTION
c) TRANSACTION BEGIN
d) INITIATE TRANSACTION - What does the COMMIT statement do in a transaction?
a) Rolls back all changes
b) Saves all changes permanently
c) Starts a new transaction
d) Pauses the transaction - What is the role of the ROLLBACK command in a transaction?
a) Saves all changes made during the transaction
b) Cancels all changes made during the transaction
c) Ends the transaction with a commit
d) Logs the transaction details - Which statement is used to create intermediate checkpoints within a transaction?
a) SAVEPOINT
b) CHECKPOINT
c) PAUSEPOINT
d) STOPPOINT
ACID Properties
- Which ACID property ensures that a transaction’s changes are permanent once committed?
a) Atomicity
b) Consistency
c) Isolation
d) Durability - What does the Atomicity property guarantee in transactions?
a) All parts of a transaction are executed or none at all
b) Transactions do not interfere with each other
c) Database remains consistent
d) Changes are permanently stored - Which ACID property is related to maintaining database consistency?
a) Isolation
b) Atomicity
c) Consistency
d) Durability - What does the Isolation property ensure?
a) Transactions are committed independently
b) Transactions are isolated from other concurrent transactions
c) Transactions execute immediately
d) Transaction results are stored temporarily - Which ACID property ensures that committed transactions survive power failures?
a) Consistency
b) Durability
c) Atomicity
d) Isolation
Locks and Isolation Levels
- Which type of lock prevents any other transactions from accessing the locked resource?
a) Shared lock
b) Exclusive lock
c) Intent lock
d) Update lock - What is the purpose of a shared lock in SQL Server?
a) Allows read operations but no modifications
b) Allows write operations but no reads
c) Blocks all access to the resource
d) Prevents deadlocks - Which isolation level in SQL Server allows dirty reads?
a) READ COMMITTED
b) READ UNCOMMITTED
c) SERIALIZABLE
d) REPEATABLE READ - What is the default isolation level in SQL Server?
a) READ UNCOMMITTED
b) READ COMMITTED
c) SERIALIZABLE
d) SNAPSHOT - Which isolation level ensures no other transactions can access the locked data until the current transaction is complete?
a) READ COMMITTED
b) SERIALIZABLE
c) REPEATABLE READ
d) SNAPSHOT
Deadlocks and Troubleshooting
- What is a deadlock in SQL Server?
a) A situation where two transactions wait on each other indefinitely
b) A timeout error in a query
c) A failure to acquire a lock
d) A rollback due to an error - Which SQL Server feature automatically resolves deadlocks?
a) Deadlock Monitor
b) Lock Manager
c) Query Optimizer
d) Transaction Handler - How can deadlocks be minimized?
a) By implementing proper indexing
b) By ensuring consistent access order to resources
c) By avoiding transactions
d) By enabling dirty reads - Which command provides information about active locks in SQL Server?
a) DBCC INPUTBUFFER
b) DBCC LOGINFO
c) sp_lock
d) sp_deadlockchain - What happens when a transaction is involved in a deadlock?
a) Both transactions are rolled back
b) One transaction is chosen as a victim and rolled back
c) Both transactions are committed
d) Both transactions wait indefinitely
Mixed Topics
- What is the effect of the NOLOCK hint in SQL Server?
a) Allows dirty reads
b) Blocks all other transactions
c) Ensures exclusive access
d) Enforces consistent reads - Which isolation level prevents phantom reads?
a) READ UNCOMMITTED
b) READ COMMITTED
c) REPEATABLE READ
d) SERIALIZABLE - What is the main drawback of using the SERIALIZABLE isolation level?
a) It allows dirty reads
b) It uses more resources and reduces concurrency
c) It does not guarantee consistency
d) It cannot be used in transactions - Which system stored procedure is used to monitor blocked transactions?
a) sp_blockerstatus
b) sp_lockinfo
c) sp_blockinfo
d) sp_monitorblock - What is the effect of setting a transaction to READ UNCOMMITTED?
a) Allows dirty reads
b) Prevents phantom reads
c) Ensures full isolation
d) Disables locks - What is the purpose of a deadlock priority in SQL Server?
a) Determines which transaction to roll back during a deadlock
b) Prevents deadlocks altogether
c) Controls the order of lock acquisition
d) Resolves blocking issues - What happens when you execute a transaction without a COMMIT or ROLLBACK statement?
a) The changes are held in a pending state
b) The changes are automatically committed
c) The transaction is rolled back
d) Throws an error - Which DMV (Dynamic Management View) provides details about currently executing transactions?
a) sys.dm_tran_locks
b) sys.dm_exec_requests
c) sys.dm_tran_active_transactions
d) sys.dm_exec_sessions - Which SQL Server tool helps in analyzing deadlock graphs?
a) SQL Profiler
b) Database Engine Tuning Advisor
c) Extended Events
d) Activity Monitor - Which command rolls back to a specific SAVEPOINT?
a) ROLLBACK TO SAVEPOINT
b) ROLLBACK SAVEPOINT
c) SAVEPOINT TO ROLLBACK
d) ROLLBACK TO
Answer Key
| Qno | Answer |
|---|---|
| 1 | a) To execute multiple operations atomically |
| 2 | b) BEGIN TRANSACTION |
| 3 | b) Saves all changes permanently |
| 4 | b) Cancels all changes made during the transaction |
| 5 | a) SAVEPOINT |
| 6 | d) Durability |
| 7 | a) All parts of a transaction are executed or none at all |
| 8 | c) Consistency |
| 9 | b) Transactions are isolated from other concurrent transactions |
| 10 | b) Durability |
| 11 | b) Exclusive lock |
| 12 | a) Allows read operations but no modifications |
| 13 | b) READ UNCOMMITTED |
| 14 | b) READ COMMITTED |
| 15 | b) SERIALIZABLE |
| 16 | a) A situation where two transactions wait on each other indefinitely |
| 17 | a) Deadlock Monitor |
| 18 | b) By ensuring consistent access order to resources |
| 19 | c) sp_lock |
| 20 | b) One transaction is chosen as a victim and rolled back |
| 21 | a) Allows dirty reads |
| 22 | d) SERIALIZABLE |
| 23 | b) It uses more resources and reduces concurrency |
| 24 | a) sp_blockerstatus |
| 25 | a) Allows dirty reads |
| 26 | a) Determines which transaction to roll back during a deadlock |
| 27 | a) The changes are held in a pending state |
| 28 | c) sys.dm_tran_active_transactions |
| 29 | c) Extended Events |
| 30 | d) ROLLBACK TO |