MS SQL Server - 55 - Lock Modes MCQ

55 – Lock Modes MCQ

1 / 25

1. What is the purpose of a lock in MS SQL Server?

To commit a transaction.

To unlock data for other transactions.

To prevent other transactions from accessing data.

To initiate a new transaction.

2 / 25

2. Which SQL Server lock mode allows other transactions to read but not modify locked data?

Intent lock (INTENT)

Exclusive lock (EXCLUSIVE)

Update lock (UPDATE)

Shared lock (SHARED)

3 / 25

3. What is lock granularity in SQL Server?

The size of the data being locked.

The level of isolation of a transaction.

The type of lock being used.

The number of locks in a transaction.

4 / 25

4. What is lock escalation in SQL Server?

The automatic release of locks.

The process of acquiring more locks in a transaction.

The process of converting lower-level locks to higher-level locks.

The process of downgrading locks.

5 / 25

5. In SQL Server, which command is used to explicitly release locks held by a transaction?

UNLOCK

RELEASE LOCK

ROLLBACK

COMMIT

6 / 25

6. Which system view in SQL Server can be used to display information about current locks?

sys.databases

sys.objects

sys.locks

sys.transactions

7 / 25

7. What is a shared lock (SHAREin SQL Server?

It allows only one transaction to read and modify data.

It prevents all transactions from accessing the data.

It allows multiple transactions to read data concurrently but prevents any transaction from modifying it.

It allows multiple transactions to modify data concurrently.

8 / 25

8. Which lock mode in SQL Server allows a transaction to both read and modify data while preventing other transactions from accessing it?

Intent lock (INTENT)

Update lock (UPDATE)

Shared lock (SHARED)

Exclusive lock (EXCLUSIVE)

9 / 25

9. What is the primary purpose of an intent lock (INTENT) in SQL Server?

To indicate an intention to lock a row.

To indicate an intention to lock a page.

To indicate an intention to lock an entire table.

To indicate an intention to commit a transaction.

10 / 25

10. What is a page-level lock in SQL Server?

It locks a specific row.

It locks a data page in a table.

It locks an index.

It locks an entire table.

11 / 25

11. Which statement is true about deadlocks in SQL Server?

Deadlocks are resolved automatically by the database system.

Deadlocks can never occur in SQL Server.

Deadlocks occur when two or more transactions are waiting for each other to release locks.

Deadlocks are always fatal errors.

12 / 25

12. What is the purpose of a lock timeout in SQL Server?

To prevent locks from being acquired.

To escalate locks.

To release all locks held by a transaction.

To specify a maximum duration for a lock.

13 / 25

13. Which lock mode in SQL Server is typically used to prevent other transactions from modifying data while a transaction is reading it?

Shared lock (SHARED)

Exclusive lock (EXCLUSIVE)

Update lock (UPDATE)

Intent lock (INTENT)

14 / 25

14. What is lock compatibility in SQL Server?

The ability to grant locks to any transaction.

The ability of different lock modes to coexist without blocking each other.

The ability to acquire locks without waiting.

The ability to perform backups without locking the database.

15 / 25

15. What happens when a deadlock is detected in SQL Server?

The database system automatically rolls back one of the involved transactions.

The database system automatically kills one of the involved transactions.

The database system waits indefinitely for the deadlock to resolve.

The database system sends a notification to the database administrator.

16 / 25

16. Which command is used to forcibly terminate a SQL Server session to resolve a deadlock?

ROLLBACK

COMMIT

KILL

LOCK

17 / 25

17. What is lock contention in SQL Server?

The process of acquiring locks.

The process of releasing locks.

The competition for acquiring and holding locks.

The process of upgrading locks.

18 / 25

18. What does the “WITH (NOLOCK)” hint do in SQL Server?

Allows a transaction to read data without acquiring locks.

Prevents a transaction from acquiring any locks.

Allows a transaction to acquire exclusive locks.

Forces lock escalation.

19 / 25

19. Which system dynamic management view (DMV) in SQL Server provides information about deadlocks?

sys.dm_tran_locks

sys.dm_exec_query_stats

sys.dm_exec_requests

sys.dm_os_wait_stats

20 / 25

20. In SQL Server, what is the purpose of an update lock (UPDATE)?

It prevents other transactions from reading or modifying data.

It allows multiple transactions to read and modify data concurrently.

It is used for system-level operations.

It allows a transaction to read data without acquiring locks.

21 / 25

21. What is the purpose of a schema modification lock in SQL Server?

It locks entire tables.

It locks schema objects.

It locks data pages.

It locks system tables.

22 / 25

22. Which statement is true regarding lock escalation in SQL Server?

Lock escalation always improves performance.

Lock escalation can only occur at the table level.

Lock escalation reduces the number of locks held by a transaction.

Lock escalation can occur at different levels, including table and page levels.

23 / 25

23. What is the purpose of the “SET DEADLOCK_PRIORITY” statement in SQL Server?

To set the priority of a transaction during a deadlock.

To set the maximum duration of a deadlock.

To set the number of locks a transaction can hold.

To set the isolation level of a transaction.

24 / 25

24. Which lock mode in SQL Server allows a transaction to acquire locks, but it does not block other transactions from reading data?

Exclusive lock (EXCLUSIVE)

Update lock (UPDATE)

Shared lock (SHARED)

Intent lock (INTENT)

25 / 25

25. In SQL Server, what is a deadlock graph?

A graphical representation of the locks held by transactions involved in a deadlock.

A graphical representation of the query execution plan.

A graphical representation of the database schema.

A graphical representation of the transaction log.

Your score is

MS SQL Server – 55 – Lock Modes MCQ