Hi to everyone.

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
as

DECLARE @intNextFolio int,
. . . . bla bla bla

SET @errInsertar = 2323
SET @serieActual = (SELECT SERIE FROM CONFIGURATION)



WHILE (@errInsert <> 0)
BEGIN
/* 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
BEGIN

/* Update the configuration with the last inserted folio */
UPDATE CONFIGURATION SET FOLIO = @intNextFolio
SET @@NewFolio = @intNextFolio
END


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.

Questions:
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.


Thanks

I don't really know SQL Server, but I imagine this code would suffer the same way as it would in Oracle more or less. At the moment, your code fails if two users run the procedure at the same time. Both get the same current value of ActualFolio, add 1 to it and then try to insert that value into the PK. One will fail.

You can resolve this by locking the table as you say - BUT then you have serialised your application. If two or more users try to run the procedure at once, all but the first will hang waiting for the first user to commit before they get the next folio number; then all but the second user will wait for the second user, etc. Your application will be S L O W ...

In Oracle you would avoid this by using a SEQUENCE - this is a special object that returns a guaranteed unique number without needing a lock. I think the SQL Server equivalent is the IDENTITY keyword in a column definition. I have seen this used like this:

CREATE TABLE t
( id INT IDENTITY(1,1)
, name VARCHAR(30)
)

You will need to look at SQL Server documentation for an explanation of the IDENTITY keyword and arguments.

Thanks for answering.
Ok, if i convert to identity the fiel, how can i do to reset the value to any number that i want ?

And, think of this: i insert the record, the problem was solved. But, if many inserts have executed, how the machine that make the insert can return the record that inserted ?


I supposed that i have to lock the tabled before i insert, but, how?

As I said, I don't really know SQL Server, so maybe someone else can jump in here and help (or maybe you should post this in the SQL Server forum).

Do you mean so that first identity value created is e.g. 63 instead of 1? That is what the first parameter of the IDENTITY is for: IDENTITY(63,1)
The second parameter is the increment (i.e. add 1 to get next number)

Do you mean, having inserted a row how do you find out its PK value that the IDENTITY created?
There are some functions called @@IDENTITY and SCOPE_IDENTITY that look like they do that - check the documentation.

If you were going to do that, I guess you would move the UPDATE of CONFIGURATION to the start of the transaction so that you must have a lock BEFORE you use the new value. But I would avoid this solution because, as I said before, it means users will have to wait to get a lock every time they want a ticket. Depending on how busy your system is, this could make your application UNUSABLE.