• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2027
  • Last Modified:

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
0
IUAATech
Asked:
IUAATech
1 Solution
 
Scott PletcherSenior DBACommented:
>> When I am updating the vote count, do I need to lock the CANDIDATE table? <<

No, SQL will automatically lock (at least) the row(s) being UPDATEd.  
0
 
IUAATechAuthor Commented:
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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now