0

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?

0

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
| improve this answer | |
  • I don't see why serializable is sufficient. If two requests both read the absence of the vote row won't they both try the insert? One will fail on the key constraint and there'll be an exception. – Ian Warburton Apr 18 '15 at 17:24
  • 1
    The SERIALIZABLE level will acquire a key range lock to prevent concurrent insertion for the specified userid. However, SERIALIZABLE is prone to deadlocking so if you happen to have votes cast by adjacent userids, you might get a deadlock. I edited by answer to add a UPDLOCK hint to the first INSERT in order to avoid this. – Dan Guzman Apr 18 '15 at 18:48
  • Does it also need a holdlock on it? – Ian Warburton Apr 19 '15 at 14:36
  • Holdlock isn't needed in SERIALIZABLE. – Dan Guzman Apr 19 '15 at 14:43
  • 1
    @IanWarburton, yes the user table could be used to serialize access, or you could also use an app lock. With performance being your primary concern, I would leverage locking that will happen anyway. If you use the user table (unless it will accessed for another purpose in this transaction), that will add overhead. – Dan Guzman Apr 19 '15 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.