Hi Rod!
> Does this really solve the issue with concurrency?
> I have found that MySQL can lose values such as
> what would be returned by LAST_INSERT_ID when
> there are multiple transactions going on at the
> same time.
"lose values" ? not sure what you mean exactly.
> E.g.:
> transaction 1: INSERT TO TABLE
> transaction 2: INSERT TO TABLE
> transaction 1: Call LAST_INSERT_ID... which I
> would think would return the value created by
> transaction 2, not transaction 1. My thought is
> that once transaction 2 makes it's insertion the
> value from transaction 1 is lost.
>
> Am I right about this, or is this really thread
> safe?
I think you are mixing up two concepts here, transactions and sessions. Andy's code simply stores LAST_INSERT_ID() in a user-defined variable, @R_ObjectId_val.
This variable has session-scope, it is not visible in any other session. Put another way, references to any variable of that name are separate instances across sessions, and they do not influence each other.
So regardless of transactions, if the two inserts you mentioned are executed each in their own session they won't see each other's values.
In fact - Any's code is just syntactic sugar IMHO. You could do away with the overhead of an extra function, and simply write
INSERT INTO seq_table(NULL);
SELECT LAST_INSERT_ID();
instead of nextval
and
SELECT LAST_INSERT_ID();
instead of currval.
Sure, we would grow the sequence table, but I don't think it's that big a deal (at any rate I think it will be better to periodically empty the table with delete rather than taking the hit for an additional delete each time we grab a number.)
BTW - I am interested in hearing why you need this sequence feature. What problems are you guys solving with that that you can't solve with an auto_increment. Just curious...
kind regards,
Roland Bouman
http://rpbouman.blogspot.com/