Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Kunal Lakhani
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Claude Moore
Ranch Hand
Posts: 785
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 181
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Kunal Lakhani
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 64700
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why aren't you using a sequence?
 
Kunal Lakhani
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 785
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 785
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 785
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What about a single update statement involving a sequence, like this ?



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

 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 622
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic