Isolation Levels

Question 1

What are the isolation levels in t-sql?

Answer 1

Read Uncommitted
Read Committed
Repeatable Read
Serializable
Snapshot

Question 2

What is the default isolation level for SQL?

Answer 2

Read Committed

Question 3

Read Uncommitted - dirty read?

Answer 3

Yes

Question 4

Read Uncommitted - Non Repeatable Read

Answer 4

Yes

Question 5

Read Uncommitted - Phantom Read?

Answer 5

Yes

Question 6

What is a Dirty Read?

Answer 6

When one transaction is permitted to read another that is changing data but has not committed yet.

Question 7

What is a Non Repeatable Read?

Answer 7

Data read twice inside the same transaction cannot be guaranteed to contain the same value.

Question 8

What is a Phantom Read?

Answer 8

In the course of a transaction, new rows are added or removed by another transaction to the records being read.

Question 9

Read Committed - Dirty Read?

Answer 9

No

Question 10

Read Committed - Non Repeatable Read

Answer 10

Yes

Question 11

Read Committed - Phantom

Answer 11

Yes

Question 12

Repeatable read - Dirty Read

Answer 12

No

Question 13

Repeatable read - Non Repeatable Read

Answer 13

No

Question 14

Repeatable read - Phantom

Answer 14

Yes

Question 15

Snapshot - Dirty Read

Answer 15

No

Question 16

Snapshot - Non Repeatable Read

Answer 16

No

Question 17

Snapshot - Phantom

Answer 17

No

Question 18

Serializable - Dirty Read

Answer 18

No

Question 19

Serializable - Non Repeatable Read

Answer 19

No

Question 20

Serializable - Phantom

Answer 20

No

Question 21

What are the Lock modes?

Answer 21

Exclusive Lock (X)
Shared Lock (S)
Update Lock (U)
Intent Lock (I)

Question 22

What are the Regular Intent Locks?

Answer 22

Intent Exclusive (IX)
Intent Shared (IS)
Intent Update (IU)

Question 23

What are the Conversion Locks?

Answer 23

Shared With Intent Exclusive (SIX)
Shared with Intent Update (SIU)
Update with Intent Exclusive (UIX)
Bulk Update Locks (BU)

Question 24

What is the Locking Hierarchy?

Answer 24

DATABASE -> TABLE -> PAGE -> ROW

Question 25

What is the Locking Hierarchy for a Select Statement?

Answer 25

Database - Shared Lock (S) Table - Intention Shared Lock (IS) Page - Intention Shared Lock (IS) Row - Shared Lock (S)

Question 26

What is the Locking Hierarchy of a DML statement?

Answer 26

Database - Shared Lock (S) Table - Intent Exclusive (IX) or Intent Update (IU) Page - Intent Exclusive (IX) or Intent Update (IU) Row - Exclusive (X) or Update (U)

Question 27

What lock types can you have with Intent Shared?

Answer 27

``` Intent Shared Shared Update Intent Exclusive Shared with Intent Exclusive ```

Question 28

What lock types can you have with Shared?

Answer 28

Intent Shared Shared Update

Question 29

What lock types can you have with Update?

Answer 29

Intent Shared | Shared (already existing, not new)

Question 30

What lock types can you have with Intent Exclusive?

Answer 30

Intent Shared | Intent Exclusive

Question 31

What lock types can you have with Shared with Intent Exclusive?

Answer 31

Intent Shared