2010年8月2日星期一

Re: [fw-db] Transactions and auto increment

On Mon, Aug 2, 2010 at 6:04 PM, Bill Karwin <bill@karwin.com> wrote:
>
> On Aug 2, 2010, at 12:34 AM, Laura Dean wrote:
>>
>> The data isn't saved to the database.  And yet, my
>> DatabaseObject thinks that it's AUTO_INCREMENT id is increasing.
>
> Auto-incrementing mechanisms must increment even if the transaction is
> rolled back.
>
> The reason is for concurrency.  If two clients concurrently start a
> transaction and each insert a row, what value is each given?
>
> If auto-increment were to roll back values it allocates, then whichever
> client inserts second could not allocate an id until it waits for the first
> client's transaction to finish.  If the first client rolls back, the second
> client can re-use the same id value.  If the firs client commits, then it
> used the id value it allocated and the second client would have to allocate
> another new value.
>
> Clearly it doesn't work this way, because concurrent clients can allocate id
> values without queueing up.  But this means that auto-increment never puts
> values it allocates back into the sequence.  If you roll back, that value is
> gone.  Gaps may occur.  But gaps may also occur if you ever delete a row.
>
> Don't worry about running out of numbers.  There are a lot of integer values
> in 32 bits (INT).  There are even more values in 64 bits (BIGINT).
>
> Regards,
> Bill Karwin
>
>

and don't forget that autoincrement fields should be always set to unsigned.

Tyrael

没有评论: