Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    4

    Unanswered: How to avoid record inconsistency .....Help urgently needed

    hi Friends...
    I have got a ordermaster table in which the orderid field is autoincremental.Now I also have an Ordercode field which is a combination of [current (orderid)-LICON)]
    eg 1-LICON ..
    I achieve this using the following query ...
    insert into ordermaster(ordercode,orderamt,orderdate) select cast(max(orderid)+1 as varchar)+'-LICON' as ordercode,1000,'3/6/2004' from ordermaster

    But I think the above query could lead to an inconsistent state ....If say following occurs :

    1) USER1 reads max orderid as 1
    2) User2 also reads max orderid as 1
    3)USER 1 inserts record with ordercode as 2-LICON.
    4)USER 2 will also insert record for ordercode as 2-LICON since max orderid read by USER2 is also the same as USER1.

    How do I avoid this....I know I there are LOcktypes. But aren't locktypes used when we are updating same records....

    Please help me out .I really need help in ths context

  2. #2
    Join Date
    Feb 2004
    Location
    Egypt
    Posts
    12
    You must use high isolation level. I myself do not have wide expertise in isolation levels but I faced this problem and I was answered by a good
    Colleague on the follwing url "http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3310"
    Read message Posted - 03/03/2004 : 10:17:45 by bambola.

    It is he who helps you
    Thanks

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Couldn't you use an IDENTITY property ??? It is a whole lot "cleaner" than the "roll your own" type of identities.

    -PatP

Posting Permissions

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