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?
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.
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.
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.
I don't know (in sufficient detail) how sequences in other databases work, though.