MS SQL Server - 52 - Locking Commands MCQ

52 – Locking Commands MCQ

Best of luck!

1 / 25

1. Which SQL Server command is used to explicitly begin a transaction and acquire a transaction-level lock?

OPEN TRANSACTION

START TRANSACTION

BEGIN LOCK

BEGIN TRANSACTION

2 / 25

2. What is the purpose of the SQL Server command “ROLLBACK”?

To undo and cancel the changes made in a transaction

To save the transaction to a backup file

To commit changes made in a transaction

To release all locks acquired by a transaction

3 / 25

3. Which SQL Server command is used to explicitly release all acquired locks for the current transaction?

ROLLBACK

END TRANSACTION

COMMIT

RELEASE LOCKS

4 / 25

4. In SQL Server, what does the “SAVE TRANSACTION” command do?

It releases all locks acquired by the transaction.

It saves the current transaction to a backup file.

It commits all changes made in the transaction.

It marks a point within the current transaction to which you can later roll back.

5 / 25

5. What is the primary purpose of the “SET TRANSACTION ISOLATION LEVEL” command in SQL Server?

To specify the timeout for locks

To create a new transaction

To set the database recovery model

To define the isolation level for the current transaction

6 / 25

6. In SQL Server, what is the default isolation level when you start a new transaction?

REPEATABLE READ

READ UNCOMMITTED

READ COMMITTED

SERIALIZABLE

7 / 25

7. Which SQL Server command is used to explicitly acquire an exclusive lock on a resource within a transaction?

LOCK EXCLUSIVE

HOLD EXCLUSIVE

ACQUIRE EXCLUSIVE

SET LOCK EXCLUSIVE

8 / 25

8. What is the purpose of the “SET DEADLOCK_PRIORITY” command in SQL Server?

To assign a priority to a session during deadlock resolution

To specify the maximum number of allowed deadlocks

To prevent deadlocks from occurring

To resolve deadlocks automatically

9 / 25

9. Which SQL Server command is used to explicitly request and acquire a lock on a resource without specifying a transaction?

LOCK RESOURCE

BEGIN LOCK

ALTER LOCK

ACQUIRE LOCK

10 / 25

10. In SQL Server, what is the purpose of the “KILL” command?

To commit a transaction

To release all locks acquired by a transaction

To forcefully terminate a session or query

To create a new transaction

11 / 25

11. What is the primary purpose of the “SET LOCK_TIMEOUT” command in SQL Server?

To set the database recovery model

To commit changes made in a transaction

To define the isolation level for the current transaction

To specify the timeout for locks within a transaction

12 / 25

12. In SQL Server, what is the default lock timeout value if “SET LOCK_TIMEOUT” is not specified?

5 seconds

1 second

10 seconds

30 seconds

13 / 25

13. Which SQL Server command is used to release a specific lock on a resource?

RELEASE LOCK

UNLOCK

KILL LOCK

ALTER LOCK

14 / 25

14. What is the primary purpose of the “DBCC TRACEON” command in SQL Server?

To create a new transaction

To release all locks acquired by a transaction

To enable or disable a specific trace flag

To commit changes made in a transaction

15 / 25

15. In SQL Server, what is the purpose of the “SET XACT_ABORT” command?

To release all acquired locks for a transaction

To assign a priority to a session during deadlock resolution

To enable or disable automatic transaction abort

To specify the timeout for locks

16 / 25

16. Which SQL Server command is used to explicitly release a specific savepoint within a transaction?

SET SAVEPOINT

ROLLBACK SAVEPOINT

RELEASE SAVEPOINT

COMMIT SAVEPOINT

17 / 25

17. What is the primary purpose of the “SET IMPLICIT_TRANSACTIONS” command in SQL Server?

To specify the timeout for locks

To enable or disable implicit transactions for the current session

To release all locks acquired by a transaction

To set the isolation level for the current transaction

18 / 25

18. In SQL Server, what is the purpose of the “SET TRANSACTION READ ONLY” command?

To mark a transaction as read-only

To define the isolation level for the current transaction

To specify the timeout for locks

To enable automatic transaction abort

19 / 25

19. Which SQL Server command is used to list all currently executing processes and their associated information?

SELECT PROCESS

SP_WHO

LIST PROCESSES

SHOW PROCESSES

20 / 25

20. What is the primary purpose of the “ALTER TABLE” command in SQL Server?

To change the structure of an existing table

To commit changes made in a transaction

To create a new table

To release all locks acquired by a transaction

21 / 25

21. In SQL Server, what is the purpose of the “SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED” command?

To enable automatic transaction abort

To specify the timeout for locks

To define the isolation level for the current transaction as read uncommitted

To release all acquired locks for a transaction

22 / 25

22. Which SQL Server command is used to explicitly acquire a lock on a resource for the current transaction?

SET LOCK

HOLD LOCK

LOCK RESOURCE

ACQUIRE LOCK

23 / 25

23. What is the primary purpose of the “SET TRANSACTION ISOLATION LEVEL READ COMMITTED” command in SQL Server?

To enable or disable automatic transaction abort

To define the isolation level for the current transaction as read committed

To specify the timeout for locks

To release all acquired locks for a transaction

24 / 25

24. In SQL Server, what is the purpose of the “SET TRANSACTION ISOLATION LEVEL REPEATABLE READ” command?

To enable automatic transaction abort

To release all acquired locks for a transaction

To define the isolation level for the current transaction as repeatable read

To specify the timeout for locks

25 / 25

25. Which SQL Server command is used to explicitly release all acquired locks for a transaction and commit the changes?

COMMIT TRANSACTION

END TRANSACTION

ROLLBACK TRANSACTION

RELEASE ALL LOCKS

Your score is

MS SQL Server – 52 – Locking Commands MCQ