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

    Question Unanswered: allocating rows in a table to multiple users

    Dear all

    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.

    I use the following procedure which is creating dead lock when multiple users execute it simultaneously

    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
    Feb 2003
    Location
    India
    Posts
    216
    you are passing a number to the SP to block that many rows. this is something that is perhaps causing the deadlock. more than 1 user is getting the same serial_no value and trying to get lock on the same row. to allow successful completion of transactions user should be working with distinct set of data. try to pass a customer_id or something similar, that one user is working with, to the SP.

  3. #3
    Join Date
    Jul 2006
    Posts
    8
    Thank u Mr upalsen for ur valuable idea. I will try it.

Posting Permissions

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