Page 1 of 1

Transactions and MySQL last_insert_id

Posted: Thu Apr 06, 2006 10:10 am
by gravyface
Trying to get my head around transactions and guaranteeing that the last_insert_id is the same id that I just inserted.

Right now, I'm using PEAR:DB and my table type is InnoDB. I connect, insert a new record, select last_insert_id, and return it, to populate an edit form.
I'm concerned that because I'm not using transactions, if anyone else (I'm assuming each db session is determined by connection?) inserts a new record before the first last_insert_id is returned, then the first connection/user would have the wrong last_insert_id.
Secondly, do transactions handle row-level locking? i.e. in an insert on a particular id is in progress and hasn't been committed, is that row locked? Should it be?

Thanks

Posted: Thu Apr 06, 2006 10:16 am
by feyd
I wouldn't store a reference to the record to use for the submission until data to stick in it is submitted. Maybe that's a personal choice, likely is, but it seems silly to create a record in the hopes that someone will fill it. What happens if they decide not to submit data?

Posted: Thu Apr 06, 2006 11:28 pm
by gravyface
I think I need to clarify:

when Bob submits the form, I connect to db, insert the POST data into a table (yes, I validate it/scrub for injection etc.), return the last_insert_id, and disconnect.

I then populate the response (the same form) with the POST data (input type=text value=$_POST['foo']), with the exception of a hidden field (input type=hidden name=fooId value=$fooId), which is the value of the last_insert_id. This is so Bob's "Create New Foo" form becomes an "Edit Your Foo" form after he submits, using the hidden field to identify the record to update on subsequent POSTs.

However, because Sally could've submitted the same form before Bob's SELECT last_insert_id() statement is executed, I believe I could be retrieving Sally's last_insert_id, and thus populating Bob's form with Sally's data.

With one or two concurrent users of the Web application, this sort of thing is probably not going to happen, but if hundreds of users are inserting records simultaneously (or close to it), I believe that this could be a problem.

Is this because I'm not a) using transactions b) using row-level locking, or c) both? I'm vaguely familiar with these conceptually, but not practically, which is why I'm posting on this forum. :wink:

As a side question, I really don't like the idea of stuffing a primary key in a hidden field; I thought of encrypting/decrypting it, or perhaps creating a key/value pair *somewhere* like key=GUID, value=1001, but I'm not sure what's the most practical.

Thoughts?

Thanks

Posted: Thu Apr 06, 2006 11:41 pm
by feyd
I don't think row locking will help at all in this case, nor would transactions. When I need to do something like your more detailed description, I just query for the data I just submitted. It's far more guaranteed and works on far more databases than hoping transactions or row/table locking is supported.

Posted: Thu Apr 06, 2006 11:47 pm
by gravyface
Oops, you replied before I edited :)

Okay, so you're saying instead of SELECT last_insert_id, use some discerning values from the POST that can uniquely-identify Bob's row and then fetch the id using that? i.e. SELECT id from foo_table where name=bob and foo=bar ?

Please comment on my "side question" I added to my edit posted above too. Thanks.

Posted: Fri Apr 07, 2006 12:02 am
by feyd
It's a personal choice, most often, for how you place the record ID in the form. I often just leave it with the primary key unencoded or anything. When processing the submission, I verify that the person doing the update has rights to edit the referenced record (whether it's the creator or an admin or someone else).

As for the selection, you have all the data you inserted, select using all that data. It should be unique. However there is potential for it not to be as unique, when this happens, because the insertion would generate the highest possible primary key, I order by the primary key (descending) in the selection. The first record is the one inserted.