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.
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)
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.
works on connection based which takes the possible concurrency into account.
if there is no tool make your own.
Joined: Jun 05, 2010
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.
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?
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 ?
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.
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.
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.
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.
Joined: Jun 05, 2010
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