Sql Server 7 - Locking

Front 1

intent lock

Back 1

a mechinism used by transactions to declare their intent of acquiring a lock

Front 2

schema lock

Back 2

used to maintain structural integrity of tables - i.e. cant drop an index while it is being used

Front 3

exclusive lock

Back 3

needed for data modification

Front 4

update locks

Back 4

intermediary lock between schared and exclusive. Indicating that a resource wants to obtain an exclusive lock

Front 5

SELECT: holdlocks

Back 5

shared locks held for duration of transactions

Front 6

shared locks

Back 6

used for read operations. Data modification is blocked till locks released - multiple shared locks are possible

Front 7

transaction isolation level-- 4 settings

Back 7

a users isolation level is the level of ability a user has to control a portion of data. The 4 levels are:

1. Read Uncommitted
2. Read Committed
3. Repeatable Read
4. Serializable

Front 8

Transaction Isolation Level: read committed

Back 8

avoids dirty reads but is suceptable to non-repeatable reads and phantom data. Shared locks held when data is read

Front 9

Transaction Isolation Level: read uncommitted

Back 9

lowest locking level-- suceptable to dirty reads, non-repeatable reads, phantom data. no chared locks held, no exclusive locks

Front 10

Transaction Isolation Level: repeatable read

Back 10

prevents dirty reads and non-repeatable reads but not phantom data. locks placed on all rows used in a query

Front 11

Transaction Isolation Level: serializeable

Back 11

places a range lock on the data-- no inserts or updates. this is the highest locking setting

Front 12

dirty reads

Back 12

reading data that is later scrolled back

Front 13

non-repeatable read

Back 13

previously read data is changed

Front 14

phantom data

Back 14

new data appears in a previously read data set

Front 15

2 ways to avoid deadlocks

Back 15

1. always access objects in the same order
2. attempt to keep transactions as short as possible

Front 16

what is optimistic locking?

Back 16

leaving the data unlocked until it is actually time to write the changes in the optimistic hope that nobody will change it behind your back