| Subject | Re: [firebird-support] Use a row only once |
|---|---|
| Author | Helen Borrie |
| Post date | 2004-11-24T02:46:40Z |
At 08:41 PM 23/11/2004 -0500, you wrote:
points to destination B, then no other row in the Items table can point its
destination to destination B. Uniqueness constraints on FKs are used only
for implementing an exclusive 1:1 relationship....
If that was your intention, then as soon as the first transaction assigns
an item to Destination B, no other item can be assigned to that destination
unless the first transaction changes its mind, rolls back the change and
once again frees that destination for use by another item. Even within the
*same* transaction, if you have one item pointing at that destination, you
won't be able to post an update to point another item to it.
Can't exactly grokk what you mean by "claim". If one transaction is able
to post a change to a row, it secures a lock on the entire row. Any
another transaction that requests a change to the same row (or any row that
depends on that row) after that, will fail with a lock conflict.
If your question is - is there any way to predict the state of locks
without attempting to post and being ready to handle an exception then,
no. Something different to that would need an engine that could bend the
consistency logic and deliver a Dirty Read. Firebird doesn't. And won't.
Resolving conflicts is what multi-user programming is about. Don't be
frightened of concurrency exceptions - they are the channel through which
applications get to know about conflicts. How the conflict resolves is up
to you to decide. For example, trap the lock conflict exception, cancel
the request and make another one...and repeat until it succeeds.
./heLen
>I have a table which has "Items". Lets call it Item. Items can be linked toThe unique constraint on the FK Destination in Items means that, if Item A
>a "Destination" by the use of foreign key in the Item table. When I match
>them up, I need to find an item in the Item table with DestinationID that is
>null. Then assign it to point to a row Destination table.
>
>No problem - but concurrency. How can I "claim" the row in Item so that
>another concurrent transaction will "grab" the next one? I have a unique
>constraint on the FK, is the only way to try to post and see if the unique
>constraint is triggerred?
points to destination B, then no other row in the Items table can point its
destination to destination B. Uniqueness constraints on FKs are used only
for implementing an exclusive 1:1 relationship....
If that was your intention, then as soon as the first transaction assigns
an item to Destination B, no other item can be assigned to that destination
unless the first transaction changes its mind, rolls back the change and
once again frees that destination for use by another item. Even within the
*same* transaction, if you have one item pointing at that destination, you
won't be able to post an update to point another item to it.
Can't exactly grokk what you mean by "claim". If one transaction is able
to post a change to a row, it secures a lock on the entire row. Any
another transaction that requests a change to the same row (or any row that
depends on that row) after that, will fail with a lock conflict.
If your question is - is there any way to predict the state of locks
without attempting to post and being ready to handle an exception then,
no. Something different to that would need an engine that could bend the
consistency logic and deliver a Dirty Read. Firebird doesn't. And won't.
Resolving conflicts is what multi-user programming is about. Don't be
frightened of concurrency exceptions - they are the channel through which
applications get to know about conflicts. How the conflict resolves is up
to you to decide. For example, trap the lock conflict exception, cancel
the request and make another one...and repeat until it succeeds.
./heLen