SQL Server Performance Forum – Threads Archive

Granularity of Locks in Snapshot Isolation

Concurrency in SQL Server 2005: How to use granularity of Locks in Snapshot isolation level transaction?
Hi, I have developed a VS 2005 database application which has the concurrency problem when running batch processing with multiple users updating the records.
Isolation levels
1. Pessimistic Concurrency Control (SQL Server 2000)
Locking granularity (database, table, [age, row, index)
2. Optimistic Condurrency Control (SQL Server 2005)
Versioning (snapshot isolation, read committed isolation) Figure 1: Concurrency Models By referring to the concurrency controls (Figure 1) that provided in SQL server 2005, I would like to use the granularity of locks (rows or index locks) in the snapshot isolation transaction. My rough idea of the application processes can be illustrated as below (Figure 2). X=10; Y=20
T1 – Read X, Y (10, 20)
T2 – Read X, Y (10, 20)
T1 – Compute X=X+5 (15)
Lock Row X
T2 – Compute Y-Y+5 (25)
Lock Row Y
T1 – Write X (15)
Release X
T1 – Read Y (20) * Row versioning
T2 – Write Y (25)
*Release Y Figure 2: Application processes
Please kindly advise me on the solutions to overcome the problems of concurrency in SQL Server 2005.
Overall, my questions are as below: 1.Is it possible for me to use the granularity locks (row, index, table, database) in snapshot isolation transaction, as shown in Figure 2 above? 2.Any advises or guidelines, examples, or sample codes on how to implement the granularity locks in snapshot isolation environment? 3.Can I works with these granularity locks and snapshot isolation control in the following development environment? i. SQL Server 2005 and Visual Studio 2003
ii. SQL Server 2005 and Visual Basic 6 Thank you.

---

]]>