aspose file tools*
The moose likes JDBC and the fly likes Generate next Id as per the max id in database using Java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Generate next Id as per the max id in database using Java" Watch "Generate next Id as per the max id in database using Java" New topic
Author

Generate next Id as per the max id in database using Java

Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
I am not sure as if i am posting the query in right forum

I am working on a web application. It will have multiple users. I am using mysql as database.

In my application i am fetching the latest id (from the database, using max(id)) , and then generating the next id for the new registration. This approach is incorrect, as the id might change between the time i update an id
and i ask for the latest id. I googled up , and found "last_insert_id()"

But, i tried "SELECT last_insert_id() FROM `rdtype` ", but its gives the same number of 0 as the no of records.

How should i proceed?

kunal
Claude Moore
Ranch Hand

Joined: Jun 24, 2005
Posts: 493
    
    1

You need to use a proper transaction isolation level in order of preventing scenarios like the ones you correctly figured out.
Try, for example, TRANSACTION_SERIALIZABLE level to prevent all issues (but be aware: such isolation level may be
unnecessarily heavy for your needs)
Arun Giridhar
Ranch Hand

Joined: Mar 10, 2012
Posts: 148

Its on wrong section . If you read the documentation you would know why it gives 0 mysql doc.


if A and B two process simultaneously carries out insert operation
where as per A retrieves MAX(id) as 7 and inserts the record
meanwhile process B too retrieve MAX(id) as 7 on insert gets contraint violation error.

for
works on connection based which takes the possible concurrency into account.


hate Professionalism . Join the http://2014.hack.lu/index.php/Main_Page
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Thanks for your replies

Arun Giridhar, I have tried last_insert_id() , but, it works just after an insert, i guess. I have posted its working too.

I found out
LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column.


Claude Moore, Thanks, googling it. Someone suggested for a lock before saving, but that would not be good one i guess.

I found this problem interesting one, did a lot of googling, but, couldn't find any solution. Lets learn something new then.
Thanks
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61656
    
  67

Why aren't you using a sequence?


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Let me explain the complete thing.

There are 2 columns in my db, one is ID (generator type is increment, via hibernate), and the other is Registration id. I am getting the max id, then getting the registration id for that max id, then adding 1 to that registration id to get the new registration Id.... Moreover, i need to get this new registration id before saving it to the database.

Bear Bibeault, delighted to see your reply as i am here in this forum after a very long time.
Sequence in mysql? I guess its implemented by AUTO_INCREMENT. how will it solve the generation of next id?

Claude Moore
Ranch Hand

Joined: Jun 24, 2005
Posts: 493
    
    1

Bear Bibeault wrote:Why aren't you using a sequence?


Great idea Bear. Doing so, you let the DBMS manage all issues regarding concurrent access.
At each access, i get a new unique value. The problem is: what if I don't want to get another token,
or if I cannot let gaps in my sequences, when rollbacks may happen ?

For example let's considerate a Invoice table. I cannot have a missing Invoice number, but - at least with DB2 -
if I run


and then I rollback, the InvoceID i got is lost.

I guess that in such case, one should update the InvoiceID only after transaction commits; maybe,
with an autogenerated column one can keep track of the unique record, and after, run an update
with such recordId as key -- update should be atomic.
What do you think about ?



Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Claude Moore wrote:or if I cannot let gaps in my sequences, when rollbacks may happen ?

Sequences do have gaps. Avoiding gaps is very costly, as it causes serialization of all writers, which in turn makes the application using them inherently unscalable. Might not be a problem for invoices, as long as you're not, say, Amazon

Sequences have gaps even in real life - for example, when an invoice is wrongly issued and subsequently cancelled, there will be a gap in their numbering. Databases are no different. I believe that your energy would be better spend explaining the users that they don't need gapless sequences, instead of trying to create one.
Claude Moore
Ranch Hand

Joined: Jun 24, 2005
Posts: 493
    
    1

Martin,
i respectfully disagree about the fact that gaps in sequences don't matter.. i don't know about USA but in other countries leaving holes in your company invoices record is tax fraud. So, it'd be interesting a pattern to adopt to avoid gaps in sequences.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Well, if it is a legal requirement, then you have to bite the bullet. But implementing such sequence correctly requires good knowledge about the given database concurrency and locking models (solution that works in MySQL or SQL Server might not work in Oracle or Postgres, for example, due to the very different approach to concurrency these databases take), and, although you can try to hurt the performance as little as possible, it will scale terribly.

One of the most basic approaches would be to lock the table that contains the IDs exclusively, read the max(ID) using SQL, insert the new record using max(ID)+1 and then unlock the table (commit). Note the need to lock before reading the current maximal ID, otherwise in some databases you might get a race condition.
Claude Moore
Ranch Hand

Joined: Jun 24, 2005
Posts: 493
    
    1

What about a single update statement involving a sequence, like this ?



This statement should be run atomically by every database...

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

The problem with that is that sequences might not be gapless. In Oracle, for example, they aren't - rollback and database bounces can (and do) create gaps. Furthermore, if your database runs on a cluster (the RAC), you can encounter further difficulties with ordered sequences.

I don't know (in sufficient detail) how sequences in other databases work, though.
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Thanks for your replies members
I dont want gaps in my ids.
UPDATE INVOICE SET INVOICE_NBR = NEXT VALUE FOR INVOICE_COUNTER.


Not understood this concept.

I need to get the max id and the next id before saving anything in the database

Should i go for locking and unlocking ?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Generate next Id as per the max id in database using Java