I have a SQL Server 2008 R2 hosting a database with which my software works through the ODBC API. The database has a table, called
Users, which has a number of columns, but there are 3 columns (3 first ones in the Table Designer, if it matters) instrumental to understanding the problem:
- UserID - INT, PK, identity, incremented by 1, starts from 10
- NetworkUserID - BIGINT
- NetworkID - INT
Fields 2 and 3 are indexed.
There is a stored procedure, which receives NetworkUserID and NetworkID through the input parameters and returns UserID through an out parameter, here is a semi-pseudocode of the procedure:
PROCEDURE [dbo].[Procedure] @NetworkID INT, @NetworkUserID BIGINT, @UserID INT OUT AS BEGIN SET @UserID = 0 -- check whether the user exists IF NOT EXISTS ( SELECT [UserID] FROM [Users] WHERE [NetworkID] = @NetworkID AND [NetworkUserID] = @NetworkUserID ) BEGIN -- user does not exist, create a new record for him INSERT INTO [Users] ([NetworkUserID], [NetworkID], [some_other_fields]) VALUES (@NetworkUserID, @NetworkID, @some_other_values) END ELSE BEGIN -- user has already registered, update his profile UPDATE [Users] SET [some_irrelevant_fields_are_being_updated_here, UserID_is_not_touched] WHERE [NetworkID] = @NetworkID AND [NetworkUserID] = @NetworkUserID END -- get the internal user ID SELECT @UserID = [UserID] FROM [Users] WHERE [NetworkID] = @NetworkID AND [NetworkUserID] = @NetworkUserID RETURN END
Here is the problem - when the physical server running this SQL server starts to get low on memory, the SQL server starts to slam the disk due to massive swapping.
This results in overall, pretty significant, slowing down of query execution and here the problem pops up:
occasionally the SQL server returns the wrong UserID for the specified pair of NetworkID and NetworkUserID. This happens very randomly, so I cannot track it down. But it only happens when the SQL server slows down due to swapping.
I have instructed the SQL server not to consume more than 3/4 of available RAM, but it doesn't help, because the remaining memory is eventually eaten up by a leaky system service, which eventually leads to swapping anyway. From time to time I see the following messages in the logs of my software produced by the ODBC API:
SQLFetch: state = 40001, native error = 1205, message = [Microsoft][SQL Server Native Client 10.0][SQL Server]Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
SQLDriverConnect: state = 08001, native error = 258, message = [Microsoft][SQL Server Native Client 10.0]Shared Memory Provider: Timeout error .
Apparently, these messages have something to do with the described above error.
Did anyone experience anything like that?
How can the SQL server return a totally wrong record by a uniqie pair of values NetworkID + NetworkUserID?