Transactions and Interdependent INSERTS

[tsilihin]

Hi there

This question is based on MySQL, using the ADODB abstraction layer (and I'm kind of new to transactions).

I'm just wondering about how transactions work with multiple INSERT statements, where inserted records reference previously inserted records.

For example:

I have 2 tables -

people (
id (primary, auto_increment),
full_name,
email,
photograph
)

photographs (
id (primary, auto_increment),
filename
)

people.photograph corresponds to photographs.id

A new profile is inserted from one page, which includes field entry for:

people.full_name
people.email
photographs.filename

Normally what I do without transactions is initialise an ADODB connection object ($conn), insert the photograph in one statement, then insert the person in the next, using $conn->InsertID() for the photograph field.

It would be mighty handy to use transactions on such a query set, in the event that for some reason the person record fails to insert.

Would it howerver be possible to do using transactions, given that in the time between the photograph being added and its id being assigned to people.photograph, a second transaction instance could have been committed, throwing people.photograph in the first transaction instance out by one?

Any remarks / comments appreciated.

 

[QatQat]

You should do so by using a dynamic cursor and lockpessimistic in your ADODB connection.

A transaction would get the ID by using last_insert_ID() function which does not guarrantee 100% accuracy and it is subject to error if somebody else inserts another record before your transaction is committed.


Bye


Qatqat




Life is what happens when you are making other plans.

 

[cdlvj]

Take auto increment off the photograph table id field.

while {
lock tables people write;
if successful
insert people record
get insert id
insert photograph record
unlock tables
get out
Loop = loop + 1
if (loop > 10)
notify user
sleep
}

should do it.
You can also define a control table with just a id field.
put
select id from controltable for update in your loop.
do the inserts, update control table and commit.

 

[cdlvj]

The Lock Tables should be READ, so that the other users can read while the insert is being done.

They are also some semaphore type functions get_lock and Release Lock, so that there can be only one client adding rows at a time.

 

[sleipnir214]

Since MySQL maintains a per-connection value for the last automatically-generated autoincrement id, why not just insert the record then immediately perform a "SELECT last_insert_id()", and use the value returned when inserting the related record?

Want the best answers? Ask the best questions:

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

TANSTAAFL!!