Ashwini`s TechNet

5.Transaction and Concurrency

Concurrency
1. Is it an ability to that allow multiple process to access and modify shared resources read more about concurrency fundamentals
Downside of Concurrency

1. Lost update
2. Dirty Read
3. Non Repeatable Read
4. Phantom Read

Concurrency Model in SQL Server 2008

1. Optimistic Concurrency Model (Control)
  1. Pessimistic Concurrency Model (Control)
2. Cursor Concurrency Control

Transactions
1. 1. ACID property of transaction
  2. Nested Transaction
  3. A SQL Server DBA myth a day: (26/30) nested transactions are real
  4. MSDN:Nesting Transactions

Isolation Level in Transactions

1. Read Uncommitted
2. Read Committed
  1. Read Committed (Locked)
  2. Read Committed (Snapshot) RCSI
3. Repeatable Read
4. Serializable Read
5. Snapshot Isolation
6. Further Discussions

- 1. Locking Vs Row Versioning
  2. Read Committed (Snapshot) RCSI Vs Snapshot Isolation SI
  3. How SQL manages row version
  4. Row Version , Snapshot and Temp DB management

Locking Mechanism

1. Spin Lock
2. Latches

Locking Modes

1. Shared
2. Exclusive
3. Update Lock
4. Schema Lock
5. Conversion Lock
6. Key Range Lock
7. Key Lock

Lock Level

1. Database, Row , Table , Page
2. Key , Key Range

Lock Hints

Page updated

Google Sites

Report abuse