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_CastBa llot
(
@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,@Machin eIP,@Machi neDNS,@Ser verVariabl es)
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
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_CastBa
(
@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,@Machin
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.