how to generate unique value from java and mysql

[Mullin]

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?

 

[R.F. Pels]

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.

 

[jonck]

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

 

[joe]

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.