AUTO_INCREMENT and threading...

[shadedecho]

say i have the table

mytable:

+-----+-----+
|  A  |  B  |
+-----+-----+
|  1  |  2  |
|  2  |  4  |
|  3  |  6  |
|  4  |  8  |
+-----+-----+

where A is the primary key and is an auto_increment field, and the value that gets stored in B is 2 * whatever value gets stored in A from the auto_increment.

what I would like to do is be able to insert (with one insert SQL statment) and have that relationship continue, something like:

INSERT INTO `mytable` (`B`) ((LAST_INSERT_ID() + 1) * 2))

When I do this FOR THE FIRST TIME ON AN EMPTY TABLE, I do get the desired results, over and over again, in that same connection.

HOWEVER, my real question is this: Is this method multi-thread-safe... by that, I mean, would it be possible for mysql to receive 2 "INSERT" commands to the same table, starting the first command first, but then simultaneously executing the second insert, finishing with the second one before the first? Or does mysql process the INSERTS incrementally as it receives them, like putting them in a queue and doing them one at a time.

And, the bigger question is this: the mysql documentation indicates that LAST_INSERT_ID() returns an integer that is maintained "per connection", so,

what if connection "A" is created, and then connection "B" is created. now "A" starts doing something for awhile, maybe to another table, and meanwhile, "B" goes ahead and "INSERT"s into the table, and then "B" quits. Now, "A" finishes what he's doing, and he comes over and "INSERT"s into that table... IS "A"s copy of LAST_INSERT_ID() going to be updated BEFORE that insert, or after?

In my testing, this is what I've found:
It appears that "LAST_INSERT_ID()" is not set at all (defaults to 0, like all integers) for a connection until an INSERT on the table in question has been done in that connection... so, no matter what the actual last auto_increment is in a table, LAST_INSERT_ID() returns 0 for the FIRST insert, then it gets set to the correct auto_increment value. This is very troubling.

starting with a blank table, if you connected, ran the query 5 times, then disconnected, then reconnected, and ran it 5 more times, your table would look like this:

+-----+-----+
|  A  |  B  |
+-----+-----+
|  1  |  2  |
|  2  |  4  |
|  3  |  6  |
|  4  |  8  |
|  5  |  10 |
|  6  |  2  |
|  7  |  14 |
|  8  |  16 |
|  9  |  18 |
|  10 |  20 |
+-----+-----+

Notice how after the disconnect and reconnect, the first insert (A = 6) had a LAST_INSERT_ID() = 0 --> (0 + 1) * 2 --> 2, which got put in B. then, it got 6 assigned to it, so on the next INSERT, it was back to the sequence correctly (6 + 1) * 2 --> 14.

It also appears (as would follow from that observation) that LAST_INSERT_ID gets updated from an insert to the table by another thread ONLY after that first insert from the current thread is done, but returns 0 that first time.

So, why doesn't "LAST_INSERT_ID()" get updated AT LEAST at creation of a connection to the database? It also does not get updated if you read from the table in question first, like with a "SELECT". why not?

or better yet, why doesn't it (at the time its used) check the table for the value of "next auto_increment" and return - 1 of that?

from my testing, it appears that not only is LAST_INSERT_ID() not multi-thread-safe... its not even safe to use for the first insert to a table in ONE thread.

So, LAST_INSERT_ID(), in its non-multi-threaded, non-connection-updated form, is hardly useful for anything!!! The only way LAST_INSERT_ID() could be useful is in a SINGLE-THREADED (how many mysql applications are actually single-user?) application, and even then, only when you had already executed at least one INSERT to the table!!!!

Anyone got any thoughts? I need to be able to "know" in an INSERT statement what value is being assigned to the auto_increment field in that same INSERT statement. anyone got a workaround? Anyone know why MySQL would have a function like that which is so deliberately NON-MULTI-THREAD-SAFE?

 

[swampBoogie]

There is no way of knowing the next auto_increment value before the insert is done. If you want to set the value of B properly you must do this with an update statement after the insert.

It is only relevant to use last_insert_id() after an insert statement. That it returns 0 if no insert has been done is poor design in my view. It is thread safe, as it returns the value generated by the latest insert in your connect, not the latest insert in the entire system.

Last_insert_id is useful when dealing with foreign keys, i.e. you are inserting a parent post along with some children in one form.

I don't understand why you wish to store the value of B as it can easily be calculated when retrieving data or doing other operations.

 

[sleipnir214]

It is because last_insert_id() is multi-thread safe that it does not provide you with the last insert id created, tablewide, on connection.

What do you want MySQL to do, provide you with the last ID generated, even if that were for another connection?

Let's walk through that idea. You connect and ask for last_insert_id(). That atomic operation ends. Then another user inserts a row, which increments the insert id. That atomic operation ends. Then you perform your insert or update operation, using and increment of the last id you got earlier. Collision possible.


You can either:
Perform the insert, then immediately update the record:
insert into mytable set b=0;
update mytable set b=last_insert_id() * 2 where a=last_insert_id();

Or

Realize that if the value of B can be deduced from the value of A, your table is not in normal form. You should then drop column B altogether, and instead of storing redundant data, perform a query like:
SELECT a, a * 2 as b from mytable;

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[shadedecho]

sleipnir214-

OK, well, let me say this: I wanted a function that returned to me the value of the "next autoincrement", or even just the last auto-increment'd value added to the table. in other words, I want a function that returns the GLOBAL answer to the question, "what is the next auto-increment that will be used?" or "what is the last auto-increment that WAS used?".

I thought this function would do that. so, apparently this function is not for that purpose, instead its for getting the last one that YOUR thread added.

obviously, there is SOME way for mysql to report the value of the "next autoincrement" because in phpMyAdmin, it shows you what the next one will be. how does it do that? is there a MySQL function for such a thing?



WHY I WANT TO DO THIS:

I have a tracking system which encrypts pieces of data (using, in this case, MD5) and creates a "unique" signature for that data. these signatures are put, along with other relevant data, into a transaction_log table, who's primary key is an auto_increment field. in certain circumstances, I would like the signature to actually incorporate the value of it's log's primary key into it.

This is much like when you digitally sign an email, it adds a key to the end of the email, and then creates a checksum on the whole thing. I want to create a record, with one of the fields being a "checksum" on that record encrypt'ing all the data from each field in it (including its primary key auto_increment field).

I would really rather not have to do this in two steps, as it is kinda clumsy in this application.

anyone with thoughts on how to do this in one step?

 

[sleipnir214]

You can't insert both current data and future data in a single operation in MySQL. In PostgreSQL, maybe -- it handles the automatically incrementing fields differently through its use of sequences.

If you want to do it in MySQL, I think the minimum number of queries is two.

But again, you are violating first normal form by having a column's value derived from another column's value. Whatever you're going to do with this hash, if you need that id to be a part of it, then calculate it in scripting before display or transmission. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[shadedecho]

no, i can't insert "future" data, but i can mathematically calculate what the future data will be (last_auto_increment + 1) and INSERT that, if only mysql had a way to determine GLOBALLY the last auto_increment value via a function

This CAN be done with a SELECT statement using the magic "auto_increment_field" field in MySQL if the appropriate server variable is set, and it can also be gleaned via a SHOW TABLE STATUS command -- but neither of these can be called as a function inside an INSERT (or any other) command, which is what I need.

If I wasn't on 3.23x, I could either do a sub-select, or write a PROCEDURE to do this, I guess, but that doesn't help me now.

 

[sleipnir214]

Use the Form, Luke.

(First Normal, that is.) Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!