Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : MySQL auto_number as FK in Child Table

[Rman2003]

Ok, so here's the scenario, and I'll dumb it down a bit to make it easier to understand. I already have a solution for this, but I was looking for a better way to do it if there is one and would like some input.

I have two tables, one is a parent table ( records ) and the other is the child table (items).

For each record, there may be multiple items.

The records table has an auto_number in the records.id field that is set up as a 1>M relationship in the child table (items) on the items.record_id field.

Ok, so that's all good, and works fine.


However, here's the problem. Since I'm using the auto_number as the Foreign Key in the child table, being a multi-user environment, person 2 can come along and add a record while person 1 is still creating theirs. Person 2 has just stolen Person 1's auto_number.. effectively corrupting both records as Person 2's record will have their items as well as Person 1's, and Person 1 will have none.

Example.

Person 1 chooses to create a new record, and grabs the next auto_num from the database, say 500.

Person 1 starts adding items to their record, using 500 as the FK in the items table.

----

Person 2 chooses to create new record shortly after and grabs the next auto_num from the database. Since Person 1 is still editing their record, the next auto_num is still 500.

Person 2 starts adding items using the FK 500.

Person 2 finishes and submits their record commiting their data to the parent records table with the id 500.

----

Person 1 finishes and submits their record expecting the auto_num 500. Since 500 is now already taken by Person 2, 501 is used.

If person 1 tries to view their record after creation, they will have no items listed on their record.


See the issue? Basically what I'm wondering, is if there's a way in MySQL to put a claim or reservation on the next auto_number without actually adding a record.

I could add a NULL row to effectively "claim" the next auto_number, however this would end in a lot of orphaned records if someone backs out of adding a record.

There are long ways around this, but I'm looking for suggestions.

Thanks for your help in advance.

[maiahost]

the thing you are referring to is concurency control - here it is explained in detail :
en.wikipedia.org/wiki/Concurrent_access

and here is some code that might help you

http://www.itu.dk/courses/INP/E2001/...ww/printer.php

good luck

[Burhan]

See the issue? Basically what I'm wondering, is if there's a way in MySQL to put a claim or reservation on the next auto_number without actually adding a record.

You can implement row-level locking if your table is InnoDB (which you should use anyway if you are doing FK relations).

[campbeld]

Ummm don't use auto_number! Create a separate table to store your identifier sequence and manage your own identifiers.

Use a function "get_next_identifier" to provide the next unreserved identifier. This function will either return an identifier already in your sequence table marked "unreserved" or insert a new identifier into the sequence table marked "reserved". If the user cancels the transaction, just mark the identifier as unreserved.

So requests for new identifiers will respect the fact that another user has already reserved an identifier.

Use SELECT MAX(ID) FROM YOUR_SEQUENCE_TABLE WHERE reserved=0 to quickly get the highest ID. Use indexes and this is lightning fast.

Let me know if you need further clarification but this should give you the drift.