Professional Web Applications Themes

Best method for obtaining unique values? - Microsoft SQL / MS SQL Server

Hi, I am trying to generate a unique value based on a set of customer defined values plus an incremental number. I have a table that contains an integer value and contains the customer defined values as such. CREATE TABLE [dbo].[Settings] ( [AppID] [int] NOT NULL , [AppPrefix1] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [AppPrefix2] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] I am obtaining this value as such: SELECT AppID= AppPrefix1 + AppPrefix2 + right('0000' + rtrim(cast(QuickAppID as char(5))),5) FROM Settings I am then immediately updating the settings table for the next user: UPDATE Settings WITH ...

  1. #1

    Default Best method for obtaining unique values?

    Hi,

    I am trying to generate a unique value based on a set of customer defined
    values plus an incremental number. I have a table that contains an integer
    value and contains the customer defined values as such.

    CREATE TABLE [dbo].[Settings] (
    [AppID] [int] NOT NULL ,
    [AppPrefix1] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [AppPrefix2] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ) ON [PRIMARY]

    I am obtaining this value as such:

    SELECT AppID= AppPrefix1 + AppPrefix2 + right('0000' +
    rtrim(cast(QuickAppID as char(5))),5) FROM Settings

    I am then immediately updating the settings table for the next user:

    UPDATE Settings WITH (ROWLOCK) SET AppID=AppID+1

    I don't think this is the best way to generate the unique value I am looking
    for plus it looks like a simultaneous call to this procedure would generate
    duplicate values. What would be nice is if I could obtain the AppID
    generated with the UPDATE statement: UPDATE Settings WITH (ROWLOCK) SET
    AppID=AppID+1 instead of running a SELECT and then UPDATE.

    Is there a better way of going about this?

    Thanks,

    Dave



    David Guest

  2. #2

    Default Best method for obtaining unique values?

    First thing ROWLOCK is not necessary when ever an Update
    statement is used this is automatically taken care by the
    sql server itself.

    The Function or procedure which u had written works fine
    with out generating any duplicate values.

    Since Select Statment issues a shared lock
    and an Update Statment issues an exclusive lock

    suppose the same time the two users get the value 10 also
    It does not hurt nor give duplicate values since the
    Update statment issues an exclusive lock..

    therefore please take away explicit lock function in your
    query then the query is ok.

    For more Information this is from Inside sql serve book


    Shared Locks
    Shared locks are acquired automatically by SQL Server when
    data is read. Shared locks can be held on a table, a page,
    an index key, or an individual row. Many processes can
    hold shared locks on the same data, but no process can
    acquire an exclusive lock on data that has a shared lock
    on it (unless the process requesting the exclusive lock is
    the same process as the one holding the shared lock).
    Normally, shared locks are released as soon as the data
    has been read, but you can change this by using query
    hints or a different transaction isolation level.

    Exclusive Locks
    SQL Server automatically acquires exclusive locks on data
    when it is modified by an insert, update, or delete
    operation. Only one process at a time can hold an
    exclusive lock on a particular data resource; in fact, as
    you'll see when we discuss lock compatibility, no locks of
    any kind can be acquired by a process if another process
    has the requested data resource exclusively locked.
    Exclusive locks are held until the end of the transaction.
    This means that the changed data is normally not available
    to any other process until the current transaction commits
    or rolls back. Other processes can decide to read
    exclusively locked data by using query hints.

    HTH

    Rajesh Peddireddy


     
    customer defined 
    contains an integer 
    SQL_Latin1_General_CP1_CI_AS NOT NULL , 
    SQL_Latin1_General_CP1_CI_AS NOT NULL 
    next user: 
    value I am looking 
    procedure would generate 
    obtain the AppID 
    (ROWLOCK) SET 
    Rajesh Guest

  3. #3

    Default Re: Best method for obtaining unique values?


    "Rajesh Peddireddy" <com> wrote in message
    news:046001c35d50$877ba4e0$gbl... 

    Nope it's broken. The problem is that 2 users can run in the following
    sequence

    user1:
    SELECT AppID= AppPrefix1 + AppPrefix2 + right('0000' +
    rtrim(cast(QuickAppID as char(5))),5) FROM Settings

    user2:
    SELECT AppID= AppPrefix1 + AppPrefix2 + right('0000' +
    rtrim(cast(QuickAppID as char(5))),5) FROM Settings

    user2:
    UPDATE Settings WITH SET AppID=AppID+1

    user1:
    UPDATE Settings WITH SET AppID=AppID+1

    Then both try to insert a row with the same AppID.

    To make it correct you must lock the row in Settings and hold the lock until
    you have read the old value and incremented it.

    You can do this like

    begin transaction
    UPDATE Settings WITH SET AppID=AppID+1
    SELECT AppID= AppPrefix1 + AppPrefix2 + right('0000' +
    rtrim(cast(QuickAppID as char(5))),5) FROM Settings
    commit transaction

    the update will lock the row and the lock will be held until the commit. So
    if multiple users try to get ID's simultaneously, they will be queued up and
    will all wait in line. But not only will other users have to wait until the
    id is generated, if any user is issuing this call in the scope of a larger
    transaction, all users will be blocked until that transaction is completed.

    This makes the procedure correct, but it's still not a good idea. You are
    designing in a lack of scalability to the application. Multiple users
    should be able to generate id's at the same time. It's just bad design to
    prohibit that. Most people just abandon the idea of having cute and
    meaningful id's and just use integers. If you want to keep your fancy id's,
    here's a way to do it without sacrificing scalability.

    Create a Sequence table

    create table AppID_seq(id int primary key identity, dummy int null)

    Then you can use SQLServer's built-in, highly concurrent mechanism for
    generating id's, yet still make them look pretty. The following is fast,
    concurrent and correct without using a transaction.

    INSERT INTO AppID_seq(dummy) values (null)
    set AppID= AppPrefix1 + AppPrefix2 + right('0000' +
    rtrim(cast(SCOPE_IDENTITY() as char(5))),5)

    If you're a neat-freak too you can periodically clean up this table with
    delete from AppId_seq
    without affecting the sequence.

    David


    David Guest

  4. #4

    Default Re: Best method for obtaining unique values?

    this looks quite well performce wise but does not scale well enough if
    requirements change.

    usually you and up will multiple types of doents in a business system so
    you have Prefix, SeqNo pair. Prefix is PK for the column and the table
    contains a row for each type of doents.

    obviously SeqNo can't be an identity in this case and an explicit
    transaction seems the way to go.

    cheers,
    </wqw>

    "David Browne" <davidbaxterbrowne no potted com> wrote in
    message news:phx.gbl... 
    >
    > Nope it's broken. The problem is that 2 users can run in the following
    > sequence
    >
    > user1:
    > SELECT AppID= AppPrefix1 + AppPrefix2 + right('0000' +
    > rtrim(cast(QuickAppID as char(5))),5) FROM Settings
    >
    > user2:
    > SELECT AppID= AppPrefix1 + AppPrefix2 + right('0000' +
    > rtrim(cast(QuickAppID as char(5))),5) FROM Settings
    >
    > user2:
    > UPDATE Settings WITH SET AppID=AppID+1
    >
    > user1:
    > UPDATE Settings WITH SET AppID=AppID+1
    >
    > Then both try to insert a row with the same AppID.
    >
    > To make it correct you must lock the row in Settings and hold the lock[/ref]
    until 
    So 
    and 
    the 
    completed. 
    id's, 


    Vlad Guest

  5. #5

    Default Re: Best method for obtaining unique values?


    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 
    do 

    I agree. And thanks for the tip.

    David


    David Guest

  6. #6

    Default Re: Best method for obtaining unique values?


    "Vlad Vissoultchev" <myrealbox.com> wrote in message
    news:phx.gbl... 
    so 
    Huh? You can have as many sequence tables as you want.
     
    No doing it in a transaction will never scale. Users' top-level
    transactions containing id generation will be serialized. That's what won't
    scale.

    David


    David Guest

Similar Threads

  1. can I show only unique values in a List?
    By Mark.P. in forum Macromedia Flash Data Integration
    Replies: 2
    Last Post: October 2nd, 12:57 PM
  2. can I show only unique values in a dataGrid?
    By Mark.P. in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: September 26th, 04:00 PM
  3. Query selecting unique values
    By RuBot in forum Macromedia ColdFusion
    Replies: 3
    Last Post: August 22nd, 05:52 PM
  4. get_meta_tags expects unique name values
    By Peter N. Schweitzer in forum PHP Development
    Replies: 0
    Last Post: October 30th, 09:37 PM
  5. Returning multiple values for unique key
    By noon in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 3rd, 03:35 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139