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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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: Serious concurrency problems on fast systems
    ... I worked on a big Java Enterprise project a while ago that had highly concurrent deployment but made quite a number of concurrency mistakes that hugely slowed it down. ... Aside from the fact that the JVM optimizes lock acquisition in the uncontended case, once a thread blocks on a monitor, all the other threads also trying to acquire that monitor also block. ... On that big project we proved this with various enterprise monitoring products that reported on locks, wait times for locks and other performance issues. ... We did three things on that project to improve concurrency: eliminated shared data, made shared data immutable, and used 'java.util.concurrent' classes. ...
    (comp.lang.java.programmer)
  • 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: 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)