August 2, 2013 at 8:30 am
I have a stored procedure that takes a number of parameters. It will first check to see if a record exists based on the parameters. If a record exists, it will be updated with the values passed in. If a record doesn't exist, one will be created with the values passed in. Each record has a primary key/clustered index to prevent the same record from being inserted more than once.
Also, there's a C#/ADO.NET/webservice that calls the SP.
In our production environment I'm getting errors. There errors are because the same record is trying to be added more than once. It doesn't happen very often (about 1 for every 100,000 calls). I have never been able to duplicate this error in a test environment. I did one time catch this error while doing a profile.
What's happening is there are 2 SPIDs making the EXACT same call to the SP. So when the SPs execute they both think a record needs to be created. This first one works and the second one fails.
I'm trying to figure out why there are 2 SPIDs making the exact same SP call.
Any ideas or suggestions?
Thanks!
August 2, 2013 at 9:30 am
cgreathouse (8/2/2013)
I have a stored procedure that takes a number of parameters. It will first check to see if a record exists based on the parameters. If a record exists, it will be updated with the values passed in. If a record doesn't exist, one will be created with the values passed in. Each record has a primary key/clustered index to prevent the same record from being inserted more than once.Also, there's a C#/ADO.NET/webservice that calls the SP.
In our production environment I'm getting errors. There errors are because the same record is trying to be added more than once. It doesn't happen very often (about 1 for every 100,000 calls). I have never been able to duplicate this error in a test environment. I did one time catch this error while doing a profile.
What's happening is there are 2 SPIDs making the EXACT same call to the SP. So when the SPs execute they both think a record needs to be created. This first one works and the second one fails.
I'm trying to figure out why there are 2 SPIDs making the exact same SP call.
Any ideas or suggestions?
First... the offending process has to be analyzed so the relevant part of it can be wrapped in a single transaction.
Second... code has to be smart enough to avoid inserting the same tuple twice, if not possible to prevent it then code has to identify the error and proceed accordingly rather than just miserable failing.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 2, 2013 at 9:59 am
cgreathouse (8/2/2013)
I have a stored procedure that takes a number of parameters. It will first check to see if a record exists based on the parameters. If a record exists, it will be updated with the values passed in. If a record doesn't exist, one will be created with the values passed in. Each record has a primary key/clustered index to prevent the same record from being inserted more than once.Also, there's a C#/ADO.NET/webservice that calls the SP.
In our production environment I'm getting errors. There errors are because the same record is trying to be added more than once. It doesn't happen very often (about 1 for every 100,000 calls). I have never been able to duplicate this error in a test environment. I did one time catch this error while doing a profile.
What's happening is there are 2 SPIDs making the EXACT same call to the SP. So when the SPs execute they both think a record needs to be created. This first one works and the second one fails.
I'm trying to figure out why there are 2 SPIDs making the exact same SP call.
Any ideas or suggestions?
Thanks!
I don't see this as particularly surprising, if you have two processes (or more) that can generate calls to this sproc then the potential for this exists.
What seems to be happening is the check logic runs and says no record so it wants to insert it while at the same time or perhaps a fraction of a second later another process is doing the same thing. They both think the record has not been written and they are BOTH correct since it hasn't so each goes and tries to write it, one will succeed and the other fail.
You can get around this a couple of ways, a MERGE statement may do the trick or do a NOT EXISTS in the where clause and check for the record when you try to insert it, if it DOES exist at that moment the @@ROWCOUNT will be zero. Either way you need to tighten up the logic.
CEWII
August 2, 2013 at 10:59 am
Right, that's my question. Why are there 2 processes (i.e. more than 1 SPIDs)?
What's happening is a single web service call is turning into 2 identical SP calls (2 SPIDs). I cannot get this to happen in a test environment and doesn't happen very often in the production environment.
Example
public bool Foo(string username, string password, int param1, int param2)
{
SqlConnection con = null
// setup SqlConnection....
SqlCommand command = null
// setup SqlCommand and add parameters...
// Execute command
SqlDataReader reader = command.ExecuteReader();
// process results...
}
on the profiler I will see the SP getting called on 2 different SPIDs with the exact same parameters (username, password, param1,param2).
Most of the time the same SPID is used by the webservice. But it will occasionally create and use a different SPID.
So I guess the basic question is...
Why will a new SPID get created sometimes and other times use an existing one?
The exact same connection string is used.
Thanks!
August 2, 2013 at 12:11 pm
I can't answer why there are two spids, that clearly says TWO connections to me, if your web service can get two calls at one I bet it can create two connections. When a new connection gets created is more a function of your web service than SQL. My first thought is you have not been able to duplicate it because you haven't been able to generate enough traffic to approximate prod, that's why concurrency scenarios often don't show themselves until you hit prod if you haven't done sufficient load testing..
CEWII
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply