how to generate unique value from java and mysql

Discussion in 'Java' started by Mullin, Apr 8, 2005.

  1. Mullin

    Mullin Guest

    I need to generate a unique no. in the format like
    yyyymmddxxxxxx (xxxxxx 6-digitl running number)

    I think of create a table with two columns

    date value
    20050405 120
    20050406 99

    Everytime, the application will increase the value+1 based on the same
    date. If not date found, create one record and value = 1

    The problem is that since there's no transaction at mysql 4.1.x that
    even the application
    1. insert value
    2. max() to get
    max() may not get the value just insert BECAUSE there may be
    MULTI-threads/users

    How can implement so with Java and mysql?
     
    Mullin, Apr 8, 2005
    #1
    1. Advertising

  2. Mullin

    R.F. Pels Guest

    Mullin wrote:

    > The problem is that since there's no transaction at mysql 4.1.x that
    > even the application
    > 1. insert value
    > 2. max() to get
    > max() may not get the value just insert BECAUSE there may be
    > MULTI-threads/users


    Besides the fact that - IIRC - mySQL is transactional, even if
    transactional, this method is subject to race conditions and it therefore
    is not guaranteed to yield unique numbers.

    > How can implement so with Java and mysql?


    Either you use the autonumbering feature of mySQL or you generate GUIDs and
    use those as a unique value. Secondly, I suspect from the description of
    the field that you want to use it as a primary key. DO NOT DO THAT. It will
    almost always lead to trouble. In your case, for example, using up more
    than 1 million numbers in a day. That might seem far fetched, however, best
    practice in database design stipulates that primary keys must never have
    semantics.

    --
    Ruurd
    ..o.
    ...o
    ooo
     
    R.F. Pels, Apr 8, 2005
    #2
    1. Advertising

  3. Mullin

    jonck Guest

    MySQL 4.1 has InnoDB which is fully ACID compliant. So in other words,
    if what you are looking for is a way to generate a unique number, all
    you have to do is (in this order):
    - set the transaction level appropriately high
    - start a transaction
    - get the last unique number from the db
    - increase this unique number by 1
    - commit the transaction

    Regards, Jonck
     
    jonck, Apr 8, 2005
    #3
  4. Mullin

    Guest

    Mullin wrote:
    > I need to generate a unique no. in the format like
    > yyyymmddxxxxxx (xxxxxx 6-digitl running number)
    >
    > I think of create a table with two columns
    >
    > date value
    > 20050405 120
    > 20050406 99
    >
    > Everytime, the application will increase the value+1 based on the

    same
    > date. If not date found, create one record and value = 1
    >
    > The problem is that since there's no transaction at mysql 4.1.x that
    > even the application
    > 1. insert value
    > 2. max() to get
    > max() may not get the value just insert BECAUSE there may be
    > MULTI-threads/users
    >
    > How can implement so with Java and mysql?


    I implement a mock sequence using:

    UPDATE MySequence SET NextValue = LAST_INSERT_ID(NextValue + 1)

    Followed by:

    SELECT LAST_INSERT_ID() FROM MySequence

    The LAST_INSERT_ID stuff is tied to an individual connection, so is
    safe to use in a multi-connection environment.

    Don't know if this helps with your particular problem or not though.

    Check out http://dev.mysql.com/doc/mysql/en/getting-unique-id.html for
    more info.
     
    , Apr 8, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Max
    Replies:
    5
    Views:
    32,820
    Sudsy
    Feb 28, 2004
  2. Carmine [www.thetotalsite.it]
    Replies:
    1
    Views:
    1,142
    Carmine [www.thetotalsite.it]
    Dec 1, 2007
  3. Sanjeev
    Replies:
    3
    Views:
    1,524
    Amit Jain
    Jul 11, 2008
  4. ToshiBoy
    Replies:
    6
    Views:
    885
    ToshiBoy
    Aug 12, 2008
  5. Token Type
    Replies:
    9
    Views:
    391
    Chris Angelico
    Sep 9, 2012
Loading...

Share This Page