Solved

Locking a table from a stored procedure

Posted on 2007-11-20
2
2,010 Views
Last Modified: 2012-08-13
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
Comment
Question by:IUAATech
2 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
>> 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
 

Author Comment

by:IUAATech
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now