Transactions

Question 1

What is a transaction?

Answer 1

A transaction is a unit of work that might include multiple activities that query and modify data and that can also change the data definition.

Question 2

What statement designates the beginning of a transaction?

Answer 2

BEGIN TRANSACTION;

Question 3

What statement designates the end of a transaction?

Answer 3

COMMIT TRANSACTION;

Question 4

What statement allows you to undo a transaction?

Answer 4

ROLLBACK TRAN or ROLLBACK TRANSACTION;

Question 5

Which session option allows the user to change how SQL Server handles implicit transactions?

Answer 5

IMPLICIT_TRANSACTIONS (turned off by default)

Question 6

List the four properties of a transaction.

Answer 6

atomicity, consistency, isolation, and durability (ACID)

Question 7

Define atomicity in the context of a transaction.

Answer 7

Atomicity: a transaction is an atomic unit of work; either all changes in the transaction take place or none do.

Question 8

Which session option will cause the encounter of any error, regardless if it is not severe enough to automatically trigger a rollback, to abort and rollback the transaction?

Answer 8

XACT_ABORT

Question 9

Define consistency in the context of a transaction.

Answer 9

The term consistency refers to the state of the data that the relational database management system (RDBMS) gives you access to as concurrent transactions modify and query it.

Consistency also refers to the fact that the database must adhere to all integrity rules that have been defined within it by constraints (primary keys, unique constraints, etc.)

The transaction transitions the database from one consistent state to another.

Question 10

Define isolation in the context of a transaction.

Answer 10

Isolation ensures that transactions access only consistent data.

You control what consistency means to your transactions though a mechanism called isolation levels.

Question 11

describe the two models which SQL Server provides to handle isolation.

Answer 11

A model which uses locking (Default for SQL Server)

A model which uses a combination of locking and row versioning (Default for Azure SQL Database)

Question 12

True or False:

In the model based on locking, readers require shared locks.

Answer 12

True:

If the current state of the data is inconsistent, readers are blocked until the state of the data becomes consistent.

Question 13

True or False:

In the model based on a combination of locking and row versioning, readers require shared locks.

Answer 13

False:

In this model, readers don’t take shared locks and don’t need to wait.

If the state of the data is inconsistent, the reader gets an older consistent state.

Question 14

Define durability in the context of a transaction.

Answer 14

Data changes are always written to the database’s transaction log on disk before they are written to the data portion of the database on disk.

After the commit instruction is recorded in the transaction log on disk, the transaction is considered durable even if the change hasn’t yet made it to the data portion on disk.

Question 15

Describe lock in the context of a transaction.

Answer 15

Locks are control resources obtained by a transaction to guard data resources, preventing conflicting or incompatible access by other transactions.

Question 16

What are the two main lock modes?

Answer 16

exclusive and shared.

Question 17

True or False:

lock interaction between transactions is known as lock compatibility.

Answer 17

True.

Question 18

Describe how locking works in SQL Server Box products

Answer 18

Data that was modified by one transaction can neither be modified nor read by another transaction until the first transaction finishes.

Additionally, while data is being read by one transaction, it cannot be modified by another.

Question 19

_______ are the physical data blocks that contain table or index data.

Answer 19

Pages.

Note: rows reside within pages.

Question 20

lock escalation is triggered when a single statement acquires at least _______ locks against the same object, and then for every _______ new locks, if previous attempts at lock escalation were unsuccessful.

Answer 20

5000, 1250

Question 21

You can set a table option called ________ by using the ALTER TABLE statement to control the way lock escalation behaves.

Answer 21

LOCK_ESCALATION

Question 22

In the context of SQL Server, what does DMV stand for?

Answer 22

Dynamic Management View

Question 23

Which DMV should be queried to find information about the current state of session locks?

Answer 23

sys.dm_trans_locks

The sys.dm_tran_locks view gives you information about the IDs of the sessions involved in the blocking chain.

Question 24

How do you find the database name when only its id is provided?

Answer 24

DB_NAME(dbid) AS dbname

Question 25

What is a blocking chain?

Answer 25

A blocking chain is a chain of two or more sessions that are involved in the block situation.

Question 26

SQL Server supports four isolation levels that are based on the pure locking model, name them.

Answer 26

READ UNCOMMITTED, READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE.

Question 27

SQL Server supports two isolation levels that are based on a combination of locking and row versioning, what are they?

Answer 27

SNAPSHOT and READ COMMITTED SNAPSHOT.

Question 28

What is the command to set the isolation level for the whole session?

Answer 28

SET TRANSACTION ISOLATION LEVEL

Question 29

What is the lowest available isolation level?

Answer 29

READ UNCOMMITTED

In this isolation level, a reader doesn’t ask for a shared lock. A reader that doesn’t ask for a shared lock can never be in conflict with a writer that is holding an exclusive lock. This means that the reader can read uncommitted changes (also known as dirty reads).

Question 30

What is the lowest available isolation level that prevents dirty reads?

Answer 30

READ COMMITTED

As the name indicates, this isolation level allows readers to read only committed changes. It prevents uncommitted reads by requiring a reader to obtain a shared lock. This means that if a writer is holding an exclusive lock, the reader’s shared lock request will be in conflict with the writer, and it has to wait.

Question 31

When the transactions isolation level is set to REPEATABLE READ, how long are locks held?

Answer 31

REPEATABLE READ holds shared locks until the end of the transaction; therefore, exclusive locks cannot be obtained until after the transaction is complete. Therefore, you’re guaranteed to get a repeatable read of the rows that you read the first time in the transaction.

Question 32

Describe how the isolation level SERIALIZABLE differs from REPEATABLE READ

Answer 32

For the most part, the SERIALIZABLE isolation level behaves similarly to REPEATABLE READ: namely, it requires a reader to obtain a shared lock to be able to read, and it keeps the lock until the end of the transaction.

But the SERIALIZABLE isolation level adds another facet—logically, this isolation level causes a reader to lock the whole range of keys that qualify for the query’s filter.

This means that the reader locks not only the existing rows that qualify for the query’s filter, but also future ones. Or, more accurately, it blocks attempts made by other transactions to add rows that qualify for the reader’s query filter.

Question 33

What is a deadlock?

Answer 33

A deadlock is a situation in which two or more sessions block each other.

An example of a two-session deadlock is when session A blocks session B and session B blocks session A.

An example of a deadlock involving more than two sessions is when session A blocks session B, session B blocks session C, and session C blocks session A.

In any of these cases, SQL Server detects the deadlock and intervenes by terminating one of the transactions. If SQL Server did not intervene, the sessions involved would remain deadlocked forever.