Sql-server – Limit the number of votes

postgresqlsql servertransaction

I have a table of content. Users can vote on a content row. They can vote up to three times. I'm looking for the most efficient way to make sure they don't go over three votes.

I can think of two main designs…

1) Each user can add one row per vote.

CREATE TABLE Vote
   int userId
   int contentId

2) Each user can add one vote row and then its quantity value is incremented until 3 has been reached.

CREATE TABLE Vote
   int userId
   int contentId
   int quantity

To do this properly (i.e. thread safe) I think the sequence for these cases would be as follows…

1)
a) Read the parent User row and hold an update lock on it.
b) Count the number of votes.
c) If less than three then insert a row.
d) Commit

2)
a) Read the parent User row and hold an update lock on it.
b) Do an exists on the vote row.
c) If not there then add it.
d) If there then check the quantity is below 3 and update if true.
e) Commit

Any ideas? Any way around holding an update lock on User? (At the very least this always seem to be awkward to do with an ORM ;))

If I forget the transaction then I think method 2 is the safest and the worst case scenario is that the odd vote gets lost or a user gets a few more than 3 votes.

Any ideas on how best to achieve this?

Best Answer

If performance is your primary concern, I suggest the second approach. No need to touch the parent User table. Use the serializable isolation level to address concurrency if the same user can have multiple sessions.

CREATE TABLE Vote(
     userId int NOT NULL
   , contentId int NOT NULL
   , quantity int NOT NULL
   , CONSTRAINT PK_Vote PRIMARY KEY CLUSTERED (userId, contentId)
   );
GO

CREATE PROC dbo.CastVote
    @userId int 
   ,@contentId int 
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;

BEGIN TRAN;

INSERT INTO dbo.Vote (userId, contentId, quantity)
SELECT @userId, @contentId, 1
WHERE NOT EXISTS(
    SELECT *
    FROM dbo.Vote WITH(UPDLOCK)
    WHERE 
        userId = @userId
        AND contentId = @contentId
        );

IF @@ROWCOUNT = 1
BEGIN
    COMMIT;
END
ELSE
BEGIN
    UPDATE dbo.Vote
    SET quantity = quantity + 1
    WHERE 
        userId = @userId
        AND contentId = @contentId
        AND quantity <= 2;

    IF @@ROWCOUNT = 1
    BEGIN
        COMMIT;
    END 
    ELSE
    BEGIN
        RAISERROR('Nor more than 3 votes may be cast', 16, 1);
        ROLLBACK;
    END;
END;

GO

If the more common case is that users will cast more than 2 votes , you could reverse the logic and update first:

CREATE PROC dbo.CastVote
    @userId int 
   ,@contentId int 
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;

BEGIN TRAN;

UPDATE dbo.Vote
SET quantity = quantity + 1
WHERE 
    userId = @userId
    AND contentId = @contentId
    AND quantity <= 2;


IF @@ROWCOUNT = 1
BEGIN
    COMMIT;
END
ELSE
BEGIN

    INSERT INTO dbo.Vote (userId, contentId, quantity)
    SELECT @userId, @contentId, 1
    WHERE NOT EXISTS(
        SELECT *
        FROM dbo.Vote
        WHERE 
            userId = @userId
            AND contentId = @contentId
            );

    IF @@ROWCOUNT = 1
    BEGIN
        COMMIT;
    END 
    ELSE
    BEGIN
        RAISERROR('Nor more than 3 votes may be cast', 16, 1);
        ROLLBACK;
    END;
END;

GO
Related Question