Solved

Locking a table from a stored procedure

Posted on 2007-11-20
2
2,011 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl Query 4 31
SQL for Frequently Bought With 11 45
How to upload /Create/manage SQL database on Azure 3 29
Updating a table from a temp table 4 23
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

947 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

22 Experts available now in Live!

Get 1:1 Help Now