Tell me more ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 2 down vote favorite

Is it safe to use LAST_INSERT_ID() in a system using MySQL like Point Of Sale when there are multiple users using the system at the same time?

share|improve this question

1 Answer

up vote 10 down vote accepted

In the LAST_INSERT_ID() documentation it says:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

So, if you call that function just after the insert to know the ID you inserted, it shouldn't be affeted by other concurrent users, as they are not using the same connection.

Anyway, there is not much detail in your question, so maybe your use case is a bit more complex than that.

share|improve this answer
Thank you very much for your reply. I thought mentioning Point of Sale would give you a clear picture of what I wanted to mean or currently I am working on and that's why I skipped writing the details. Anyway you just solved my problem. Thanks :) – black_belt Aug 29 '12 at 10:31
1  
I am a bit surprised that numbers are maintained per-connection basis and not globally. Say, you have 2 connections and each started inserting numbers how could these numbers be unique unless each connection has a different start value? Also, how can one get the correct value of the last number when issuing a query such as SELECT MAX(...) FROM ...? – Emmad Kareem Aug 29 '12 at 10:58
3  
The last_insert_id is maintained globally. But the number returned is related to the connection. In other words, if you insert three records and get the last insert id for each, you may get 456, 458 and 492 with other connections getting the numbers in between. – Jaydee Aug 29 '12 at 11:38
3  
@EmmadKareem - if the LAST_INSERT_ID() returned the global most recently inserted value, it would be useless because you wouldn't know if that was the value you put in, or one someone else did. By having it return the LAST_INSERT_ID for the calling connection, it becomes a useful function - you can insert a row, then get it's id and go on using it for things. – Michael Kohne Aug 29 '12 at 16:55
@MichaelKohne +1, thanks for your explanation. – Emmad Kareem Aug 29 '12 at 17:17
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.