SQL Server Performance

Granularity of Locks in Snapshot Isolation

Discussion in 'SQL Server 2005 General Developer Questions' started by coniechan_fsktm, Jul 11, 2006.

  1. coniechan_fsktm New Member

    Concurrency in SQL Server 2005: How to use granularity of Locks in Snapshot isolation level transaction?

    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.

Share This Page