This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Locking”.
1. Which of the following resource can be locked in SQL Server?
a) TID
b) RID
c) PID
d) SID
View Answer
Explanation: RID stands for Row identifier and is used to lock a single row within a table.
2. Point out the correct statement.
a) Deadlocking is same as normal blocking
b) No other transactions can read or modify data locked with shared lock
c) A deadlock occurs when there is a cyclic dependency between two or more threads for some set of resources
d) All of the mentioned
View Answer
Explanation: Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system.
3. ______________ allow concurrent transactions to read (SELECT) a resource.
a) Update locks
b) Shared locks
c) Exclusive Locks
d) All of the mentioned
View Answer
Explanation: Shared (S) locks on a resource are released as soon as the data has been read.
4. Which is the most restrictive isolation level in transaction?
a) Repeatable Read
b) Snapshot
c) Read Committed
d) SERIALIZABLE
View Answer
Explanation: When SERIALIZABLE isolation level is set – a range lock is placed on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete.
5. Point out the wrong statement.
a) Schema modification (Sch-M) locks are used when a table data definition language operation is being performed
b) By default, SQL Server transactions times out after a certain time
c) Disallowing a locking level can affect concurrency adversely
d) Table-level locking hints can be used when a finer control of the types of locks acquired on an object is required
View Answer
Explanation: SQL Server transactions do not time out (unless LOCK_TIMEOUT is set).
6. __________ locks allow processes to bulk copy data concurrently into the same table.
a) Bulk update
b) Bulk import
c) Bulk export
d) Bulk copy
View Answer
Explanation: Bulk update (BU) locks are used when bulk copying data into a table and either the TABLOCK hint is specified.
7. Which of the following locking hint is similar to NOLOCK?
a) PAGLOCK
b) READCOMMITTED
c) READUNCOMMITTED
d) SERIALIZABLE
View Answer
Explanation: Do not issue shared locks and do not honor exclusive locks.
8. Which of the following mode indicates the intention of the transaction to read all of the resources lower in the hierarchy?
a) IS
b) IX
c) SIX
d) All of the mentioned
View Answer
Explanation: SIX stands for Shared with intent exclusive and can modify some (but not all) resources lower in the hierarchy by placing IX locks on those individual resources.
9. Which of the following lock is incompatible with all lock modes?
a) Intent exclusive (IX)
b) Schema stability
c) Schema modification
d) Bulk update
View Answer
Explanation: SQL Server allows only one Sch-M lock on an object at a time.
10. Which of the following mode has exclusive range and exclusive resource lock?
a) RangeX_X
b) RangeS_U
c) RangeS_S
d) RangeI_N
View Answer
Explanation: RangeX-X locks are exclusive resource locks acquired on Index key values from a specific range when the transaction modifies a key value from the Index.
Sanfoundry Global Education & Learning Series – SQL Server.
To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.
- Check Information Technology Books
- Practice Programming MCQs
- Check SQL Server Books
- Apply for Programming Internship
