MySQL Forums
Forum List  »  Oracle

Re: How to generate sequence in MySql
Posted by: Roland Bouman
Date: March 16, 2009 09:18AM

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/

Options: ReplyQuote


Subject
Views
Written By
Posted
167157
March 13, 2007 08:05AM
53451
March 26, 2007 09:31PM
30719
April 04, 2007 03:00AM
26348
November 21, 2007 03:50AM
17535
February 06, 2008 12:16PM
18331
October 15, 2008 01:03PM
12259
November 05, 2008 09:50AM
14071
November 10, 2008 06:32AM
15451
December 14, 2008 07:08AM
18243
January 23, 2009 05:38PM
Re: How to generate sequence in MySql
10205
March 16, 2009 09:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.