Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    8

    Unhappy Unanswered: cuncurency problem in stored procedure

    hello friends

    I am having a main table from which i want the users to retrive some specific no of rows and update a flag in the main table so that same rows can not be retrived by other users. I wrote the following procedure which is giving locking problem when multiple users execute it simultaneously. please help me.

    create procedure get_from_maintab @cnt int
    as
    declare @slno int,@curcnt int
    select @curcnt=1
    set rowcount 1
    while @curcnt<=@cnt
    begin
    begin tran
    select @slno=serial_no from maintab where getflag=''
    if @@rowcount=0
    begin
    rollback
    return
    end
    update maintab set getflag='Y' where serial_no=@slno and getflag=''
    if @@rowcount = 0
    begin
    rollback
    return
    end
    insert into detab select fld1,fld2,fld3 from maintab where serial_no=@slno
    if @@rowcount = 0
    begin
    rollback
    return
    end
    commit tran
    select @curcnt=@curcnt+1
    end
    set rowcount 0
    return

  2. #2
    Join Date
    Aug 2004
    Posts
    42
    Ignoring for now the question of what exactly you are trying to accomplish here...

    Do you have an index on maintab.getflag so that the select immediately finds a row? If the select must evaluate many rows before finding one with a blank then it will lock many rows, etc, etc.

    That said, what are you really trying to do here?

  3. #3
    Join Date
    Jul 2006
    Posts
    8
    thank u hawkins for you reply

    in our organization there is a call center where operators entry call details and custormer qruery in a table using front end application. For these queries there are multiple no of executive who will reply the query using a front end application. using the application the exectives retrive specific no of records from call center table(if there are unreplied call exists) and reply them. When a excutive retrives specfic no of rows, i want to update a flag in that rows, so that others do not get them.

    when multple exectives try to retrive data simultaneously the above procedure is creating deadlock. is there any other way. please help me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •