This site is 100% ad supported. Please add an exception to adblock for this site.

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

Deck Info

16

permalink