| Subject | RE: [firebird-support] Design Question: Storing and using generic text "tags" |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-06-27T04:59:08Z |
At 08:50 AM 27/06/2009, you wrote:
Don't do any sort of existence test for the tag. You're not interested in whether it existed before; or even whether another transaction already has it under way. You only want for it to exist when you commit the intersection record. So write a procedure to insert the intersection record, that handles the uniqueness violation, i.e., loses interest in creating a Tag record if it already exists. Run the procedure in a read-committed wait transaction to pre-empt a lock conflict on the Tags table. If you don't trust your code to commit pending changes in a timely enough manner then stick with NO WAIT and resolve the lock conflict by retrying.
./heLen
>Hi Helen,Why would you want to retry, if the first transaction wins the race and inserts the tag?
>
>>>> constraint un_tags_name unique(name)
>>> In a multi-user environment I'll have to be prepared for this
>>> to fail (two users added the same new tag at the same time).
>>
>> Ummm, fail? The tag only has to be entered once. If it's there
>> already, it doesn't need to be added: an exception will take care
>> of it.
>
>Two separate transactions, second fails on commit, then need to retry.
>There's at least a small window where select existing tag finds nothing andI'm starting to see why you thought this was "complex". You're making it way more complex than it needs to be.
>commit of first before commit of second causes second to fail.
Don't do any sort of existence test for the tag. You're not interested in whether it existed before; or even whether another transaction already has it under way. You only want for it to exist when you commit the intersection record. So write a procedure to insert the intersection record, that handles the uniqueness violation, i.e., loses interest in creating a Tag record if it already exists. Run the procedure in a read-committed wait transaction to pre-empt a lock conflict on the Tags table. If you don't trust your code to commit pending changes in a timely enough manner then stick with NO WAIT and resolve the lock conflict by retrying.
./heLen