MS SQL Server - 53 - Concurrency Models and Transactions MCQ

53 – Concurrency Models and Transactions MCQ

Best of Luck!

1 / 25

1. What is a transaction in MS SQL Server?

A user-defined function

An index on a table

A unit of work performed on a database

A stored procedure

2 / 25

2. Which isolation level ensures that a transaction does not read uncommitted data?

SERIALIZABLE

READ COMMITTED

REPEATABLE READ

READ UNCOMMITTED

3 / 25

3. What is the default isolation level in MS SQL Server?

READ UNCOMMITTED

READ COMMITTED

SERIALIZABLE

REPEATABLE READ

4 / 25

4. Which keyword is used to start a transaction in MS SQL Server?

CREATE TRANSACTION

START TRANSACTION

INITIATE TRANSACTION

BEGIN TRANSACTION

5 / 25

5. What happens to a transaction when the ROLLBACK statement is executed?

It is terminated and cannot be rolled back.

It is rolled back and changes are undone.

It is left in a pending state.

It is committed.

6 / 25

6. Which isolation level provides the highest level of data consistency but can lead to more contention?

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

7 / 25

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

To initiate a new transaction.

To commit a transaction.

To unlock data for other transactions.

To prevent other transactions from accessing data.

8 / 25

8. Which command is used to release a lock explicitly in SQL Server?

ROLLBACK

RELEASE LOCK

COMMIT

UNLOCK

9 / 25

9. What is an example of optimistic concurrency control in SQL Server?

Automatically rolling back transactions on conflicts.

Locking records when they are read.

Preventing all transactions except one from accessing data.

Checking for conflicts before committing changes.

10 / 25

10. In SQL Server, what is a deadlock?

A type of index.

A type of encryption.

A data type.

A situation where two or more transactions are waiting for each other to release locks.

11 / 25

11. Which isolation level allows a transaction to read data without acquiring locks?

READ UNCOMMITTED

SERIALIZABLE

REPEATABLE READ

READ COMMITTED

12 / 25

12. What is the purpose of the COMMIT statement in SQL Server?

To release all locks.

To save changes made during a transaction.

To start a new transaction.

To roll back a transaction.

13 / 25

13. Which statement is used to permanently remove changes made by a transaction?

TRUNCATE

COMMIT

DELETE

ROLLBACK

14 / 25

14. What is the primary purpose of the SAVEPOINT in SQL Server?

To save a point within a transaction to which you can later roll back.

To release locks.

To create a new transaction.

To save the entire transaction.

15 / 25

15. Which isolation level allows a transaction to read data without acquiring locks, but it may encounter dirty reads?

READ UNCOMMITTED

REPEATABLE READ

READ COMMITTED

SERIALIZABLE

16 / 25

16. What is a serializable transaction in SQL Server?

A transaction that can be easily rolled back.

A transaction that cannot be rolled back.

A transaction that is not isolated from other transactions.

A transaction that ensures all other transactions see its changes.

17 / 25

17. Which command is used to explicitly roll back a transaction in SQL Server?

SAVEPOINT

BEGIN TRANSACTION

ROLLBACK

COMMIT

18 / 25

18. What is the purpose of the SET TRANSACTION ISOLATION LEVEL statement in SQL Server?

To explicitly lock a table.

To start a new transaction.

To release all locks.

To set the database's default isolation level.

19 / 25

19. Which isolation level ensures that a transaction cannot read data that has been modified but not yet committed by other transactions?

READ UNCOMMITTED

SERIALIZABLE

READ COMMITTED

REPEATABLE READ

20 / 25

20. What is the default behavior of SQL Server when a transaction is not explicitly started?

It automatically starts a new transaction.

It assumes READ UNCOMMITTED isolation level.

It raises an error.

It assumes READ COMMITTED isolation level.

21 / 25

21. In SQL Server, what is a distributed transaction?

A transaction that is never committed.

A transaction that involves multiple SQL Server instances or other resource managers.

A transaction that is committed as soon as it starts.

A transaction that spans multiple databases on the same server.

22 / 25

22. Which keyword is used to define a named transaction in SQL Server?

BEGIN TRANSACTION

SAVEPOINT

DECLARE TRANSACTION

START TRANSACTION

23 / 25

23. What is a transaction log in SQL Server?

A log of the database schema.

A log of user activity in the database.

A log of all SELECT statements.

A log of all data modifications made within a database.

24 / 25

24. Which isolation level allows a transaction to read and modify data without acquiring locks?

REPEATABLE READ

READ COMMITTED

SERIALIZABLE

READ UNCOMMITTED

25 / 25

25. In SQL Server, what is a two-phase commit (2PC)?

A method for optimizing queries in two phases.

A method for rolling back transactions in two phases.

A method for locking tables in two phases.

A method for committing transactions in two phases.

Your score is

MS SQL Server – 53 – Concurrency Models and Transactions MCQ