I have an vb app that generates a taxi tickes.
This tickets are generated in different modules (or stands).
I am using SQL SERVER as database.
The tickets are identified by a unique number ( folio ).
The problem is the concurrency, when many tickets are generated
at the same time.
Actually, i make a stored procedure that generated the ticket.
The table of the ticket have a restriction in the number field ( unique restriction).
The stored procedure is like this:
Create Procedure CreateTicket
DECLARE @intNextFolio int,
. . . . bla bla bla
SET @errInsertar = 2323
SET @serieActual = (SELECT SERIE FROM CONFIGURATION)
WHILE (@errInsert <> 0)
/* Load the actual folio and increments by one */
SET @intNextFolio = (select actualfolio from CONFIGURATION) + 1
/* Insert the ticket with the next folio */
INSERT INTO Tickets . . . . bla bla bla
/* Check that if there is an error in the insert */
SET @errInsert = @@ERROR
/* No errors, insert success */
IF @errInsert = 0
/* Update the configuration with the last inserted folio */
UPDATE CONFIGURATION SET FOLIO = @intNextFolio
SET @@NewFolio = @intNextFolio
END /**** WHILE ****/
I believe that the SQL server with handle the error, but when an error hapens, vb stops and send a message that say somethig like this "Error in an IX_Folio, there a duplicate record (or something)" and the aplication fails.
1) What can i do ?
2) Which other way recommends to handle the concurrency ?
3) What can i do to make that the SQL Server handles the error and continue, without sending it to the vb app ?
4) i think the best way to handle this is locking the tables, and unlock the table when the record is inserted, but i do not how.