Subject Re: Store Blobs in Seperate Tables?
Author Adam
> Just for the sake of absolute and pedantic correctness, updating
> inserting or deleting) a record doesn't put a "lock" on it. These
> operations create a new version of the record (the first, in the
case of
> inserts) which carries the transaction id of the transaction that
> created the version. Thus a single record that had been updated
> times would have five versions, each with the transaction id of the
> transaction that created it. When a transaction starts to modify
> delete) a record, it checks the transaction id on the most recent
> version. If that transaction was not committed when the new update
> transaction started (if the new updater is a concurrency or
> transaction) or is not committed yet (if the new updater is
> read-committed transactions), then the update fails (no-wait
updater) or
> waits (wait updater) until the transaction that created the most
> version commits or rolls back.

Hi Ann,

Yes, it would do me good to remember that database people get a
particular image in their head when you mention the word lock, and so
it probably wasn't a clever choice of word. Depending on the
transaction type, it is possible that the second writer just has to
wait a (hopefully) short time.

I should then clarify what I was trying to say.

If you have two transactions interested in updating the same record,
one updating the BLOB and the other updating other fields in the
record, then they will be stepping on each others toes if they are in
the same table. If you put the BLOB into a separate table, they will
happily concurrently update the same "record" (in a business sense,
obviously they are two records now in FB). This is not specific to a
BLOB field, but rather for any subset of fields in a table, and is
normally an indicator of poor normalisation.

Thanks for clarifying my sloppyness. I am sure that some day someone
will draw a flow chart that easily explains it :)