Transaction Handling

Question

Transaction Handling

Teemo

Ten Centuries

Points: 1376
More actions
October 12, 2010 at 9:12 pm

#225021

Hi, I need to consult something, Please give any suggestion for improvement or give corrections in any wrong things in the stored procedure.
These are the steps in creating the stored procedure.
1. Reads data from the VenueSeats table
2. Updates the VenueSeats table to mark seats shown as pending purchase
3. Verifies payment
4. Updates the TicketsSold table
5. Updates the VenueSeats table to mark seats sold as sold
These are the requirements.
* The data read in Step 1 must not reflect changes made by other users.
* Other transactions must not be able to read or modify VenueSeats or TicketsSold during steps 4 and 5.
* The amount of time other transactions must wait to query VenueSeats must be minimized.
And these are the suggested solution:
SET TRANSACTION_ISOLATION_LEVEL = SNAPSHOT
GO
BEGIN TRAN
GO
SELECT SeatNumber FROM VenueSeats WHERE Status = 'Available'
GO
UPDATE VenueSeats SET Status = 'Pending' WHERE SeatNumber = @SelectedSeat
GO
IF VerifyPayment
BEGIN
SET TRANSACTION_ISOLATION_LEVEL = SERIALIZABLE
GO
INSERT INTO TicketsSold (SeatNumber, CustomerID) VALUES (@SelectedSeat, @CustID)
GO
UPDATE VenueSeats SET Status = 'Sold' WHERE SeatNumber = @SelectedSeat
GO
COMMIT TRAN
GO
END
ELSE
ROLLBACK TRAN
GO
Thank you in advance,
Vincent

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mike John SSCertifiable Points: 7186 More actions · Answer 1

It is not too easy to comment on a "stored procedure" when you have posted a number of separate statements rather than the actual stored proc.

From the general description you give I was expecting to see

a) A create proc statement with some parameters defined

b) The logic to carry out what you want

c) good error handling to cater for the different things that could go wrong.

But what you have posted is a number of statements all saparated by GO - which will mean an error in one statement will be ignored and the script will continue to the next batch (ie the next GO)

Canyou explain abit more what you are trying to do?

Mike

Teemo Ten Centuries Points: 1376 More actions · Answer 2

Thanky you for the reply and sorry for not so clear information. What exactly I want to know, Is the statement will meet the requirements/condition? Regardless of error handling.