October 5, 2001 at 1:18 am
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. plz reply ASAP 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 7:32 am
Please do not post a question more than once - I am removing your duplicate posts. Thanks.
Andy
October 5, 2001 at 8:10 am
quote:
I know sir, but i did'nt post it explicitly it was mistakenly posted twice. Sorry for inconvenience.Please do not post a question more than once - I am removing your duplicate posts. Thanks.
Andy
October 5, 2001 at 1:39 pm
Hi,
'Read committed' is the default isolation level so you probably won't have changed anything there.
I don't know if you want to set the isolation level to repeatable read or serializable with that many users.
From the possible time delay between the selecting of the next row and the updating on the screen by the user, follpwed by the update, you don't really want to set optimistic locking and use a rowversion (previously called timestamp) column to the table (see BOL for description and usage). This could mean a great deal of wasted user time, to discover someone else had already updated the row worked on by a user.
Could you possibly put a transaction around a call to a sp to get the next row to process, which also updated the row to a separate status (9?) which indicated 'in progress'. Then when the user has finished the screen amends, either update to set the status to 1, or reset the status to 0 if the changes do not want to be applied to the row. This row will then become available to the next user, etc.
I hope this gives you some ideas.
Good luck.
Regards
Tony Healey
http://www.SQLCoder.com - Code generation for SQL Server 7/2000
Regards
Tony Healey
www.SQLCoder.com - Free Code generation for SQL Server 7/2000
October 5, 2001 at 2:05 pm
Sounds like a job for updlock.....(note the super hero undertones - mighty dog generation anyone....)
From BOL...
UPDLOCK Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.
Anyone else have ideas....
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 5, 2001 at 2:19 pm
What you should do is in fact wrap this in a transaction and a stored proc.
create proc GrabRec
as
begin transaction
declare @id int
select @id =
top 1 uid
from tbl_uids (holdlock)
where status=0
update uid
set status = 1
where uid = @id
commit transaction
return @id
It sounds like you are trying to use a flag to mark something as locked. I am skeptical this is a good design, but this should work.
Steve Jones
October 6, 2001 at 12:51 am
quote:
Hello Steve,
Thanx for your reply. Yes u r right that
i m trying to use a flag to mark something as locked.
I agree with u that this is not a good design. Is there any other solution better than this. Because more than one user (300+) requesting simultaneously so there is a possiblity that there request becomes synchronize & they will get the same record.
But Is there any mechanism to lock the row/record immediately at select so other user cannot picked it. Although I know that the request time of both the user cannot b exactly the same, there must b a difference of milli seconds at least but I think that this milli second difference is ignored by SQL Server, and the result is that more than one user get the same record.
Anyway, the solution you have provided seems convincing let me try to implement it & then i will let you know wether it works or not. Thankx.
quote:
What you should do is in fact wrap this in a transaction and a stored proc.create proc GrabRec
as
begin transaction
declare @id int
select @id =
top 1 uid
from tbl_uids (holdlock)
where status=0
update uid
set status = 1
where uid = @id
commit transaction
return @id
It sounds like you are trying to use a flag to mark something as locked. I am skeptical this is a good design, but this should work.
Steve Jones
October 6, 2001 at 1:34 am
quote:
Hello David,Thanx 4 ur reply. Yes UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.
But I need the mechanism that the row should b immediately locked at select (blocking other readers) so other users cannot read it.
Anyway Thanx 4 ur reply.
quote:
Sounds like a job for updlock.....(note the super hero undertones - mighty dog generation anyone....)From BOL...
UPDLOCK Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.
Anyone else have ideas....
David
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply