Very Urgent: Record Locking SQL SERVER

  • 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

  • Please do not post a question more than once - I am removing your duplicate posts. Thanks.

    Andy

  • 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


  • 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

  • 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

  • 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

    steve@dkranch.net

  • 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

    steve@dkranch.net


  • 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 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply