Record Locking SQL SERVER

[shahzadssh]

Hello,

I m Software Engineer in a National Organization in Pakistan. I need information about Record Locking mechanism in SQL SERVER. (Although I have read the MSDN Documentaion about record locking in SQL SERVER, but i think that it does not fulfil our requirements, or may b i m wrong. plz help me in this regard). The explaination is as follows.

We have developed an Application Software for Multi-User Environment. The Software is Client Server based with MS SQL SERVER 2000 at back-end & MS Visual Basic at front-end. For simplicity I m just providing an example to understand the program flow.

Suppose there is a table named tbl_uids with only two fields named 'uid','status'. The uid is actually referencing an image file (jpg file) reside on the server, and status shows that wether this record is free for user or someone has picked it). Now Suppose that only two users(Actually there are 300+ users) are working on that software. They request the record like this.

dim rs as new adodb.recordset

rs.open "select top 1 uid from tbl_uids where status=0",ActiveConnection,adOpenDynamic, adLockPessimistic

if the record found then it saves the uid value as

strUid=rs("uid&quot

It sets the status=1 as so record should not b taken by any other user.

cnn.execute "update tbl_uids set status=1 where uid='" & strUid & "'"

That image file is then displayed on the user terminal. The user then enter the information about that file, save it & new record is automatically requested.

if rs.EOF or rs.BOF then

User waits until record is available

Now suppose that the request by both the users becomes synchronize i.e both users request the record exactly at the same time suppose at 10.00.00 AM

user1. rs.open "select top 1 uid from tbl_uids where status=0",... at 10.00.00.AM

user2. rs.open "select uid from tbl_uids where status=0",.... at 10.00.00 AM

Now what is the result? if someone think that both users get different records they r wrong. Both users will get the same record. Now when saved by both users the error occured
"Transaction DeadLock ...."

I want that the row should b immediately locked at select statement. so other user cannot picked it.

What i have try to solve this problem is as follows

I have try SET TRANSACTION ISOLATION LEVEL READ COMMITED ETC
before requesting the record

I have also try (READPAST) caluse the in select statement

cnn.execute "SET TRANSACTION ISOLATION LEVEL READ COMMITED"
rs.open "select top 1 uid from tbl_uids (READPAST) where status=0",.....

but invain. The problem reamains there.

Now is there anything in SQL SERVER that can handle this kind of situation or do i have to program it. Plz send me some code also if possible or plz give me the comprehensive solution. Bcoz it is very critical to us. Thanx. I shall b very gratefull to u.

Shahzad Hassan
Software Engineer

NADRA (NATIONAL DATABASE AND REGISTRATION AUTHORITY)
Sector G-5/2, Shahrah-e-Jamhuriat
State Bank Building, Islamabad, Pakistan