Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2002
    Location
    Guadalajara
    Posts
    9

    Unanswered: Can you solve this ?

    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

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    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.

    A1. Use locking
    A2. native identity
    A3. MSSQLSERVER code continues, but VB-ADO looses control
    A4.
    --Rewrite to (only read locking) update first
    update c set actualfolio=actualfolio+1
    from CONFIGURATION c (UPDLOCK)
    select actualfolio from CONFIGURATION

    --If you do not like to do it, you must do very bad noread locking
    select actualfolio+1 from CONFIGURATION (TABLOCKX)

    A4+ Leaving SP/TR will release lock. Minimize time of lock. Lock immediatelly before insert in the end of your SP/TR

    See BOL topic "Locking Hints"

  3. #3
    Join Date
    Nov 2002
    Location
    Guadalajara
    Posts
    9

    Thanks, another question

    I have been using manually increments instead native increments of SQL Server because the folio can be restarted.

    Or there is a way to restart the native increments in SQL Server, in any number ?

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    If I understand your question, you want to know how to enable identity on an existing column and how to set the seed value for the identity.

    You can modify the column in Enterprise Manager and make it an identity as well as modifying the seed value. If you want a seed value other than the maximum value (+1) then enter you own seed value otherwise sql server will choose the maximum (+1) when the next value is entered.

    Note: If you have a large number of records this process can be quite lengthy.

  5. #5
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Re: Thanks, another question

    Originally posted by fnajar
    I have been using manually increments instead native increments of SQL Server because the folio can be restarted.

    Or there is a way to restart the native increments in SQL Server, in any number ?
    Yes, resetting can be done by running DBCC CHECKIDENT
    For example DBCC CHECKIDENT (tblFolio, RESEED, 30000)

  6. #6
    Join Date
    Nov 2002
    Location
    Guadalajara
    Posts
    9

    Thanks

    Thanks for answering.

    Ok, one problem is solved if i convert to identity the fiel.

    And, think of this: i insert the record, the problem of repeated values 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?

  7. #7
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Re: Thanks

    IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

    SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

    From BOL topic "SCOPE_IDENTITY" ...

    Failed inserts on table with identcol make fake increments of identcol.
    I found some script, how to avoid it in TR-I, but I must test it first.

  8. #8
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    When it comes to selecting the ID of the newly inserted record I usually do a "manual" select with most of the values inserted in the where-statement.

    I would do something like this:

    INSERT INTO TABLE users (Firstname, Lastname, RegDate) VALUES (@Firstname, @Lastname, @RegDate);
    Set @Inserted = (Select RecordID FROM users WHERE Firstname = @Firstname AND Lastname = @Lastname AND RegDate = @RegDate)
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  9. #9
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Dear Frettmaestro,
    guessing PK cannot be used in multiuser enviroment with less selective columns.

  10. #10
    Join Date
    Nov 2002
    Location
    Guadalajara
    Posts
    9
    I Agree.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, but Frettmaestro has given a very deep answer that deserves serious consideration
    When it comes to selecting the ID of the newly inserted record I usually do a "manual" select with most of the values inserted in the where-statement.
    granted, when the database system offers a function like @@IDENTITY you should use that

    however, in some databases, no such function exists, and in that case, use Frettmaestro's method

    theoretically, a relational table isn't a relation unless it has a primary key, so when the table contains an autonumber primary key (e.g. IDENTITY), this is only a surrogate key

    if the table doesn't have some other candidate key which uniquely identifies rows, then you gots other problems

    a big advantage of querying back the surrogate autonumber value by using candidate key values is that you do not require a transaction block, as you would, for example, when you do something like SELECT MAX(id), a method which many people resort to, which is dangerous in a multithreaded environment without a transaction block to ensure that no other inserts are done between your insert and your select of the max(id)

    rudy
    http://r937.com/

  12. #12
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    ispaleny has a point offcourse that my method woun't work in all cases, especially not in tables with few columns that are not very selective. However, it's useful to know both methods and as I tend to work on several different database-platforms this is the only one that works on all of them... but to me it sounds like @@IDENTITY will do the trick in this case.
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •