Solved

Locking a table from a stored procedure

Posted on 2007-11-20
2
2,015 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 20322710
>> 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
ID: 20322740
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Format Output of Select Statement 2 38
Creating a View from a CTE 15 49
Begin Transaction 12 26
Delete duplicates from SQL Server table 2 27
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

752 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