Link to home
Start Free TrialLog in
Avatar of IUAATech
IUAATech

asked on

Locking a table from a stored procedure

I am trying to create a small online voting application in ASP.NET and I have the following stored proc that inserts an record into the VOTERS table and then updates the vote count in the CANDIDATE table.

Here is my question:
When I am updating the vote count, do I need to lock the CANDIDATE table? Every now and then, I will have 100+ users on the site simultaneously.

Also, if you have any other feedback/suggestion about my stored proc, please let me know.

Thanks.

--------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.TrusteeElection_CastBallot
(
      @ID varchar(11),
      @Candidate      int,
      @Type char(1),
      @Status char(1),
      @MachineIP varchar(50) = null,
      @MachineDNS varchar(50) = null,
      @ServerVariables nvarchar(1500) = null,
      @Success      bit OUTPUT
)
AS
      SET NOCOUNT ON;
      SET @Success = 0
      
BEGIN
      DECLARE @TranStarted   bit
      SET @TranStarted = 0
            
      IF( @@TRANCOUNT = 0 )
      BEGIN
            BEGIN TRANSACTION
            SET @TranStarted = 1
      END
      ELSE
            SET @TranStarted = 0
      
      INSERT INTO VOTERS
                  (ID, Type, Status, MachineIP, MachineDNS, ServerVariables)
      VALUES     (@ID,@Type,@Status,@MachineIP,@MachineDNS,@ServerVariables)
      IF( @@ERROR <> 0 )
      BEGIN
            GOTO Cleanup
      END

      UPDATE CANDIDATES SET TotalVotes = (SELECT TotalVotes + 1 FROM CANDIDATES WHERE ID = @Candidate) WHERE ID = @Candidate
      IF( @@ERROR <> 0 )
      BEGIN
            GOTO Cleanup
      END

      -- everything looks good, let's commit the transcation
      IF(@TranStarted = 1)
      BEGIN
            SET @TranStarted = 0
            SET @Success = 1
                  COMMIT TRANSACTION
      END
      RETURN

Cleanup:
      IF(@TranStarted = 1)
      BEGIN
            SET @TranStarted = 0
                  ROLLBACK TRANSACTION
      END
      RETURN
      
END
GO
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of IUAATech
IUAATech

ASKER

Thanks Scott.

This is the first time I am using TRANSACTION in a stored proc. If you get a chance, please let me know if I have missed something. Everything seems to work well when I tested it, but I still want an extra set of eyes to go through my code.