Re: transaction

[João Cândido de Souza Neto]

Ok Dan.

Thanks a lot for your answer.

An other doubt about transaction is in the foreign key case. If in a 
transaction i insert a certain register and get the last insert id and after 
i try to insert another register using this id because this table has a 
foreign key, it gives me a contstraint fail for the foreign key, that is, in 
a transaction, the inserts do not get inserted until commit and i can't 
insert registers in tables that has foreign key in the previous table.

Do you know how can i do this?

Thanks.


"Dan Nelson" <dnelson@stripped> escreveu na mensagem 
news:20060722045120.GC22820@ style="color:#666">stripped...
> In the last episode (Jul 22), Jo?o C?ndido de Souza Neto said:
>> I've getting something wrong in transaction in Mysql 5.0.22 on
>> windows that i don't know way it's happen.
>>
>> I've got a script in php which starts a transaction and when if fails
>> and rollbacks, the autoincrement do not back.
>>
>> e.g.
>>
>> If in transaction some field auto increment gets the value 4 and this
>> transaction fails, in the next transaction it gets the value 5.
>
> The autoincrement value is metadata outside of any table values and is
> not subject to transaction rollback.  Consider the case where you
> insert one row in each of two separate connections, getting, say,
> values 4 and 5. Then you rollback the first one.  The autoincrement
> counter is now at 6 and you're never going to get another 4.  I guess
> mysql could decrement the autoincrement counter if only one new row has
> been reserved but not committed, but that's a lot of work for little
> gain.
>
> -- 
> Dan Nelson
> dnelson@stripped