Multiple Choice Questions - SQL Server Locks
1. ....... are those locks which are acquired during read operations such as SELECT.
A) Read locks
B) Shared locks
C) Exclusive locks
D) None of above
2. Which of the below resources cannot be locked in SQL Server:
A) Row
B) Table
C) Database
D) HOPT
E) None of above
3. Locks take up system memory, so converting many locks into a larger lock can surely free up memory resources.
A) True
B) False
4. ............... returns information on currently active lock manager resources in SQL Server 2014.
A) sys.dm_tran_lock
B) sys.dm_trans_locks
C) sys.dm_tran_locks
D) sys.dm_tran_locking
5. Which of the following is not a locking hint.
A) READPAST
B) XLOCK
C) NOLOCK
D) SLOCK
6. The more granular the lock, the more overall overhead will be needed to manage these locks, also the less concurrency the server will support.
A) True
B) False
7. Locking in SQL Server can be controlled via ....
A) transaction isolation settings
B) locking hints
C) database configurations
D) all of above
8. Key-range locks protect a range of rows. A key-range lock cannot be placed on an index.
A) True
B) False
9. ........ occurs when SQL Server realises that it is locking either too many pages or too many rows.
A) unlock initiation
B) lock hierarchy
C) lock escalation
D) threshold locking
10. ........ occur if you let two transactions modify the same data at the same time, and the transaction that completes first is lost.
A) Non-repeatable reads
B) Phantom reads
C) Lost updates
D) Dirty reads
Answers
1. ....... are those locks which are acquired during read operations such as SELECT.
A) Read locks
B) Shared locks
C) Exclusive locks
D) None of above
2. Which of the below resources cannot be locked in SQL Server:
A) Row
B) Table
C) Database
D) HOPT
E) None of above
3. Locks take up system memory, so converting many locks into a larger lock can surely free up memory resources.
A) True
B) False
4. ............... returns information on currently active lock manager resources in SQL Server 2014.
A) sys.dm_tran_lock
B) sys.dm_trans_locks
C) sys.dm_tran_locks
D) sys.dm_tran_locking
5. Which of the following is not a locking hint.
A) READPAST
B) XLOCK
C) NOLOCK
D) SLOCK
6. The more granular the lock, the more overall overhead will be needed to manage these locks, also the less concurrency the server will support.
A) True
B) False
7. Locking in SQL Server can be controlled via ....
A) transaction isolation settings
B) locking hints
C) database configurations
D) all of above
8. Key-range locks protect a range of rows. A key-range lock cannot be placed on an index.
A) True
B) False
9. ........ occurs when SQL Server realises that it is locking either too many pages or too many rows.
A) unlock initiation
B) lock hierarchy
C) lock escalation
D) threshold locking
10. ........ occur if you let two transactions modify the same data at the same time, and the transaction that completes first is lost.
A) Non-repeatable reads
B) Phantom reads
C) Lost updates
D) Dirty reads
Answers