SQL Server Performance Forum – Threads Archive
Write conflict…
Okay, I need quick help here… As mentioned prior, I created a stored proc to bring up records 1 by 1 as users request the next record in a queue. However, here is the problem… I do this in a stored proc and inside a transaction. I write a lock and date time on the record in the transaction and check for success. If it is successful, I commit the transaction and return a unique key. This is returned from the stored proc and then used to open that record for the user. I am going to post my code.CREATE PROCEDURE wmspGetNextRecdefaultGroup (
@user int, @date datetime, @return int OUTPUT
) AS
declare @recno as integer
declare @error as integer
declare @rowcount as integer
set @return = 0 begin transaction Set rowcount 1
Select @recno = callList_uno from vwCallList_TZ
Where
IsComplete <> -1 And CanCall = -1 and (LockDateTime < DateAdd(n,-180,@date) or LockDateTime is null)
And (
IsInactive = 0
or (IsInactive = -1 and days_120 > 0)
)
And (CharIndex(‘[email protected]‘,STAALL) + CharIndex(‘GC’,STAALL)=0) ORDER BY CallCount, delTotal DESC, billingname
set rowcount 0
UPDATE tblCallList Set LockDateTime = @date, user_cd = @user
WHERE calllist_uno = @recno select @error = @@error, @rowcount = @@rowcount if @error = 0
begin
commit tran
select @error = @@error, @rowcount = @@rowcount
end else
begin
rollback tran
set @recno = 0
— set @return = 0
end
Set @return = @recno
return @return
GO
I use the first select to ensure that the queue is ordered properly. The ordering works properly but apparently if multiple users hit the system simultaneously, it allows each to retrieve the same ID. This is very, very bad.. How do I remedy this? How can I ensure that if 20 users hit the system simultaneously, that each retrieves their own unique record? Thanks. Matthew Moran
The IT Career Builder’s Toolkit
http://www.cbtoolkit.com
I’d suggest something like the following Cheers
Twan CREATE PROCEDURE wmspGetNextRecdefaultGroup (
@user int, @date datetime, @return int OUTPUT
) AS
declare @error as integer
declare @rowcount as integer
set @return = 0 begin transaction Set rowcount 1 UPDATE vwCallList_TZ
Set
@return = callList_uno,
LockDateTime = @date,
user_cd = @user
Where
IsComplete <> -1
AndCanCall = -1
and( LockDateTime < DateAdd(n,-180,@date) or LockDateTime is null)
And( IsInactive = 0 or ( IsInactive = -1 and days_120 > 0) )
And( CharIndex(‘[email protected]‘,STAALL) + CharIndex(‘GC’,STAALL)=0)
ORDER BY
CallCount,
delTotal DESC,
billingname set rowcount 0 select @error = @@error, @rowcount = @@rowcount if @error = 0
begin
commit tran
end
else
begin
rollback tran
set @return = 0
end return @return
GO
Cheers
Twan
Thanks. Originally, I started with the update, as above, but I get an error on the ORDER BY… Is there a way arouund this because it must select records by an internal callcount and then by the amount of money owed. After a call is made on an account, it is either completed, dropping it off the list, or its callcount is incremented and it drops to the bottom of the list. I suppose I could order the view that I am updating/calling, vwCallList_TZ? Thanks. Matthew Moran
The IT Career Builder’s Toolkit
http://www.cbtoolkit.com
Just tested attempting to order the view, vwCallList_TZ and remember why I could not. When ordered, it adds the TOP 100 Percent to the select clause. The recordset does not allow writes when this is the case?? Oh, woe is me. Any suggestions? Matthew Moran
The IT Career Builder’s Toolkit
http://www.cbtoolkit.com
Hi Matt, ah yes sorry the joy of writing sql without being able to test it… You’d probably need to use a subselect as in UPDATE tblCallList
[email protected] = tblCallList.callList_uno,
LockDateTime = @date,
user_cd = @user
from tblCallList
inner join ( select top 1 calllist_uno
fromvwCallList_TZ
WhereIsComplete <> -1
AndCanCall = -1
and( LockDateTime < DateAdd(n,-180,@date) or LockDateTime is null)
And( IsInactive = 0 or ( IsInactive = -1 and days_120 > 0) )
And( CharIndex(‘[email protected]‘,STAALL) + CharIndex(‘GC’,STAALL)=0)
ORDER BY
CallCount,
delTotal DESC,
billingname
) as vwCallList
on vwCallList.calllist_uno = tblCallList.calllist_uno
Cheers
Twan
Ah ha moment! Maybe… As I looked over my code, I realized something. I am going to test but confirm if you are able. In my transaction, I do a select, limiting my result to records without a current lockdatetime (or null lockdatetime). I then use the record number (unique key) returned to update the lockdatetime of that record – and commit the transaction. I then return the record number to my application, which then opens the form to that "locked" record. Here is why I am running into problems. Two individuals can be running the SP, return the same unique key and in fact successfully write to the same record, because, in my update statement, I do not specify any information about the lockdatetime – only the unique id! The transaction can be successul in this case and have two users write to the same record. That is bad. Twan, your recommendation above looks like it is the way to go. I could alternatively, add the lockdatetime where clause to my update but that is requiring two distinct queries. I do not know the difference – performance wise, of a sub-select versus two different transact-SQL statements. Am I right (write)? Thanks. Matthew Moran
The IT Career Builder’s Toolkit
http://www.cbtoolkit.com
Hi Matt, adding lockdatetime to the update statement will prevent a 2nd user from updating the same row, but it won’t solve the chance that two users running the proc at the same time both have the same record number returned. as select statement doesn’t stop another user also doing the same select at the same time Cheers
Twan
Understood. I actually got it to work using an isolation level of readrepeat. If there is a lock on the record, it returns 0 and the process is retried. With several people hitting the DB, 5-7 seconds is the most time we have had to wait for a record. I am playing with some indexes and some other rowlocking. I am using the sub select statement as you indicated above. Thanks. Matthew Moran
The IT Career Builder’s Toolkit
http://www.cbtoolkit.com
]]>