October 5, 2001 at 8:46 am
Plz respone to this problem as soon as possible. I have posted this on other forums also but from there i got no reply. Thanx.
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")
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
October 5, 2001 at 5:02 pm
If you're using ADO, take a look at the pessimistic locking options. Can be tricky and due to its nature won't scale as well as optimistic locking.
Having two users select a record at the same time or try to update it at the same time won't necessarily generate a deadlock - but you may not be happy with the results either.
If you want to manage locking yourself, you might try something like this:
update tablename set lockowner=userid where ....whateveryourcriteria is
then
select * from tablename where ...your criteria....AND...lockowner=userid
Then you should get a record back that you own and can edit (assuming you code your app to respect the lockowner column) without worrying about collisions. Just remember to clear the flag when you're done with it! If you don't get back a record then you can assume that there are no available records that meet the criteria that are available for editing - might want to retry.
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply