Maximum Performace with Concurrency and without Deadlocks or Dirty

Hi guys + gals,
I've come to a point in redesigning a database that is basically the reason
we chose to redesign it is for International support and to rid the db and
application code of permitting dirty reads against the database. However,
I'm at the point where I just cannot find a suitable solution other than to
still permit dirty reads due to the nature of the service the db provides.
Here's what 1 table in a database needs to do, but also needs to be able to
support a whole load of concurrent users / connections
A packet of data identifying the userId, the serviceId and a integer value
that the user has selected. The Stored Proc needs to insert the data, then
check that value against the other rows in the table for the same serviceId
which indicates that its either unique, lowest unique or how many other user
s
have that same integer value logged?
As you see from the code below, from the time I check the current status of
the lowest unique number, to adding a value from the user and to recheck the
data, there will have been other users possibly in the hundreds each adding
their own values. Because of this, I either lock the table until the
transaction is completed or break the code up into smaller modular procedure
s
that allow Dirty reads. The main aim is to always feedback the most accurate
data whilst still processing other concurrent users. Is there a better way
of achieving maximum performance and concurrency than what I'm doing without
resorting to dirty reads to prevent deadlocking. I have thought about
denormalising the data to keep current references to the user and value of
the current lowest unique data in another table but I find that I could end
up out of sync with the real data or again I would have to allow dirty reads
or impliment some kind of row versioning all which have cost to performance.
I would appreciate if anyone could offer a good solution or point me in the
right direction to further investivate my problem.
Thanks everyone...
Andy
Here's the DDL
CREATE TABLE [dbo].[UserChoice]
(
[userChoiceId] [int] IDENTITY(1,1) NOT NULL,
[fkUserId] [int] NULL,
[fkServiceId] [int] NULL,
[userValue] [int] NULL,
CONSTRAINT [PK_UserChoice] PRIMARY KEY CLUSTERED
(
[userChoiceId] ASC
)
)
CREATE PROCEDURE UserChoiceInsert
@.UserId INT,
@.ServiceId INT,
@.UserValue INT
AS
IF EXISTS
(
SELECT userValue
FROM UserChoice
WHERE (fkServiceId = @.ServiceId)
AND (fkUserId = @.UserId)
AND (userValue = @.userValue)
)
BEGIN
RAISERROR (N'User already has the value recorded', 10, 1);
RETURN 0
END
DECLARE @.previousLowestUnique INT
SET @.previousLowestUnique = (
SELECT TOP 1 userValue
FROM UserChoice
WHERE (fkServiceId = @.ServiceId)
GROUP BY userValue
Having Count(userValue) = 1)
INSERT INTO UserChoice
(
fkUserId,
fkServiceId,
userValue
)
VALUES
(
@.UserId,
@.ServiceId,
@.userValue
)
DECLARE @.LowestUnique INT
DECLARE @.NumberAtValue INT
DECLARE @.previousUserAtValue INT
SET @.LowestUnique = (
SELECT TOP 1 userValue
FROM UserChoice
WHERE (fkServiceId = @.ServiceId)
GROUP BY userValue
Having Count(userValue) = 1)
SET @.NumberAtValue = (
SELECT COUNT(userValue)
FROM UserChoice
WHERE (fkServiceId = @.ServiceId)
AND (userValue = @.userValue))
PRINT CAST(@.NumberAtValue AS VARCHAR(3)) + ' Number at Value'
PRINT CAST(@.previousLowestUnique AS VARCHAR(3)) + ' Previous Lowest Unique
Value'
PRINT CAST(@.LowestUnique AS VARCHAR(3)) + ' Lowest Unique Value'
PRINT CAST(@.userValue AS VARCHAR(3)) + ' User Value'
DECLARE @.IsLowestUnique BIT
DECLARE @.IsUnique BIT
--Is Lowest Unique
IF ((@.LowestUnique = @.userValue) AND @.NumberAtValue = 1)
BEGIN
PRINT 'Is Lowest Unique'
SET @.IsLowestUnique = 1
END
--no longer lowest unique
ELSE IF ((@.previousLowestUnique = @.userValue) AND @.NumberAtValue = 2)
BEGIN
PRINT 'No longer lowest unique'
SET @.IsLowestUnique = 0
SELECT @.previousUserAtValue = fkUserId
FROM UserChoice
WHERE (fkServiceId = @.ServiceId )
AND (userValue = @.userValue) AND (fkUserId < @.UserId OR fkUserId > @.UserId)
END
--no longer unique
ELSE IF ( @.NumberAtValue > 1)
BEGIN
PRINT 'No longer unique'
SET @.IsLowestUnique = 0
SELECT @.previousUserAtValue = fkUserId
FROM UserChoice
WHERE (fkServiceId = @.ServiceId )
AND (userValue = @.userValue) AND (fkUserId < @.UserId or fkUserId > @.UserId)
END
--Is unique not lowest
ELSE IF ((@.LowestUnique < @.userValue) AND @.NumberAtValue = 1)
BEGIN
PRINT 'Is unique not lowest'
SET @.IsUnique = 1
END
ELSE
BEGIN
PRINT 'Is not unique'
SET @.IsUnique = 0
END
SELECT
@.IsUnique AS IsUnique,
@.IsLowestUnique AS IsLowestUnique,
@.previousUserAtValue as PreviousUserAtValueAndy Furnival (AndyFurnival@.discussions.microsoft.com) writes:
> I've come to a point in redesigning a database that is basically the
> reason we chose to redesign it is for International support and to rid
> the db and application code of permitting dirty reads against the
> database. However, I'm at the point where I just cannot find a suitable
> solution other than to still permit dirty reads due to the nature of the
> service the db provides.
>...

> CREATE TABLE [dbo].[UserChoice]
> (
> [userChoiceId] [int] IDENTITY(1,1) NOT NULL,
> [fkUserId] [int] NULL,
> [fkServiceId] [int] NULL,
> [userValue] [int] NULL,
> CONSTRAINT [PK_UserChoice] PRIMARY KEY CLUSTERED
> (
> [userChoiceId] ASC
> )
> )
I realize that there may be more to the system than you show here, but
judging from the procedure UserChoiceInsert, this table should really
look like:
CREATE TABLE [dbo].[UserChoice]
(
[fkServiceId] [int] NOT NULL,
[userValue] [int] NOT NULL,
[fkUserId] [int] NOT NULL,
CONSTRAINT [PK_UserChoice] PRIMARY KEY CLUSTERED
(
[fkServiceId, userValue, fkUserId] ASC
)
)
That is, these three colunms are are unique, and it appears that it
makes no sense for them to permit NULL.
But what is more important is that with an index on (fkServiceId,
userValue), you can reduce the possibilities for deadlocks considerably.
Your queries now scan the tables several times, which is not good at
all. If two users insert the value for the same service ID simultaneous,
there is still a risk have a risk for deadlocks. One way to avoid this
is to move and modify where you compute @.NumberAtValue:
SELECT @.NumberAtValue = COUNT(*) + 1
FROM UserChoice WITH (UPDLOCK)
WHERE fkServiceID = @.serviceid
AND userValue = @.userValue
The UPDLOCK is shared lock, that only one process at a time can have.
Thus if two users tries into insert the same @.serviceId, userValue at
the same time, one will be held up, until the other commits.

> SET @.previousLowestUnique = (
> SELECT TOP 1 userValue
> FROM UserChoice
> WHERE (fkServiceId = @.ServiceId)
> GROUP BY userValue
> Having Count(userValue) = 1)
You need an ORDER BY here, or else you will just get any unique value.

> INSERT INTO UserChoice
> (
> fkUserId,
> fkServiceId,
> userValue
> )
> VALUES
> (
> @.UserId,
> @.ServiceId,
> @.userValue
> )
Save the insert to the end of the procedure and the transaction. This
means that you reduce the amount of time you hold locks. You would of
course have to modify the logic for this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp