April 7, 2015 at 5:22 pm
Hello
I have a procedure that takes awhile to complete. What I started noticing was the procedure was getting called multiple times with identical parameters. So I created a cache table that stored a hash of the parameters and the result. If the hash was found, the result was already known and was returned.
This worked pretty well. But I would occasionally get errors. The procedure gets called from an external web API that get used by multiple clients.
Here's a very simplified pseudo code version of what's going on
declare @param1 int
declare @param2 int
declare @param3 int
declare @hash int
declare @result int
/* Cache table has 2 columns, unique constraint using both
[Hash] int,
[Result] int
*/
-- create hash from parameters
set @hash = /* create hash from parameters */
select @result = Result
from Cache
where [Hash] = @hash
-- A
if (@result is not null)
begin
select @result
end
else
begin
/* Bunch of stuff to calculate result */
-- B
insert Cache([Hash], Result)
values (@hash, @result)
select @result
end
What would happen is 2 identical calls would come in at the same time (maybe a few ms apart). All of parameters were the same.
NOTE : The parameters are such that it's not possible for 2 clients to have identical parameters.
The 1st call would insert into the cache and then the 2nd call would also insert into the cache. The 2nd call fails because there's a unique constraint.
So the 1st call would be around line "B" and the 2nd call would be around line "A". This issue usually happens with the users that have clustered environments and identical calls would come from different machines. Ideally I'd like the users to make sure the request is only coming from 1 machine. But it's unlikely that will happen.
What I'm looking for is a way to slow down the 2nd call until the 1st completes. I don't want to have to block all of the calls, just the ones that have identical parameters.
Is there a way to lock the Cache table when this happens?
Thanks!
April 7, 2015 at 6:01 pm
If all of that is true, then just configure the key for the table to ignore dupes. Of course, that also depends on the hash algorithm that you're using. Just because something claims to be a hash doesn't necessarily mean that it's guaranteed to produce unique values for unique inputs. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2015 at 6:23 pm
I could, but there are other things stored along with the hash. Things like when the entry was created, updated, number of cache hits, etc. So I would really like to have unique entries. I was just trying to keep the example simple.
So what I think I'll just do is to make a check right before writing to the cache to see if an entry has appear since the 1st check at the beginning of the procedure.
Thanks!
April 8, 2015 at 4:20 pm
I would hope combining them into a single transaction with a held lock would prevent that problem:
begin transaction
select @result = Result
from Cache with (rowlock,updlock)
where [Hash] = @hash
-- A
if (@result is not null)
begin
select @result
end
else
begin
/* Bunch of stuff to calculate result */
-- B
insert Cache([Hash], Result)
values (@hash, @result)
select @result
end
commit transaction
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply