SQL Server 7 - Locking
Terms
undefined, object
copy deck
- intent lock
- a mechinism used by transactions to declare their intent of acquiring a lock
- schema lock
- used to maintain structural integrity of tables - i.e. cant drop an index while it is being used
- exclusive lock
- needed for data modification
- update locks
- intermediary lock between schared and exclusive. Indicating that a resource wants to obtain an exclusive lock
- SELECT: holdlocks
- shared locks held for duration of transactions
- shared locks
- used for read operations. Data modification is blocked till locks released - multiple shared locks are possible
- transaction isolation level-- 4 settings
-
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 - Transaction Isolation Level: read committed
- avoids dirty reads but is suceptable to non-repeatable reads and phantom data. Shared locks held when data is read
- Transaction Isolation Level: read uncommitted
- lowest locking level-- suceptable to dirty reads, non-repeatable reads, phantom data. no chared locks held, no exclusive locks
- Transaction Isolation Level: repeatable read
- prevents dirty reads and non-repeatable reads but not phantom data. locks placed on all rows used in a query
- Transaction Isolation Level: serializeable
- places a range lock on the data-- no inserts or updates. this is the highest locking setting
-
dirty reads
- reading data that is later scrolled back
- non-repeatable read
- previously read data is changed
- phantom data
- new data appears in a previously read data set
- 2 ways to avoid deadlocks
-
1. always access objects in the same order
2. attempt to keep transactions as short as possible - what is optimistic locking?
- 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