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

Tech-Archive recommends: Fix windows errors by optimizing your registry

Urgent: 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.




.

Relevant Pages

  • Concurrency in a multi-user environment
    ... concurrency in a multi-user PHP/MySQL environment. ... does locking a record prevent file locks (when new ... numeric field in the table. ...
    (php.general)
  • Re: Locking of nodes in tree for concurrent access
    ... Some item is categorized by N criterias (a criteria is the value ... number of concurrent accesses of the tree), ... For a good understanding of Java concurrency in practice, ... Java 6 has much more efficient handling of locks than earlier versions. ...
    (comp.lang.java.programmer)
  • Re: How to update multiple rows atomically
    ... As has been said before numerous times, shared state concurrency ... It was really only a matter of re-thinking the schemas for a few high-frequency 'tables' and using very selective locking for the high-frequency transactions that the back-office ones were competing with. ... The sequence of user actions is often pre-ordained, implying that certain locks make other locks redundant. ... No experience with so-called concurrent languages nor massive internet systems but I imagine the basic techniques are similar, eg., think of an app that has both high-use and intensive functions as two apps, imagine separate schemas and then make a combination that marries them. ...
    (comp.databases.theory)
  • Re: Indexes and Logical design
    ... "One can easily imagine a unique constraint ... which results in coarser locks and degraded concurrency. ... where updates lead to creating new versions of tuples, ...
    (comp.databases.theory)
  • Re: Parallelization on muli-CPU hardware?
    ... >GIL: large granularity ... >MSL: (many small locks) would slow down the overall execution of Python ... > Python experience;) ...
    (comp.lang.python)