Re: Unique Fields in Database
Re: Unique Fields in Database
- Subject: Re: Unique Fields in Database
- From: Chuck Hill <email@hidden>
- Date: Wed, 18 Mar 2009 09:39:30 -0700
On Mar 18, 2009, at 5:15 AM, Jeff Schmitz wrote:
On Mar 17, 2009, at 7:23 PM, Chuck Hill wrote:
On Mar 17, 2009, at 4:12 PM, Jeff Schmitz wrote:
Hi Andrew,
Not being a database expert, I was wondering what you meant by a
"lock in the database"?
I think he means another field that is used as an editing lock. I
think this would still leave you open to a race condition.
I could see using a Java synchronized object for a lock, but that
wouldn't work across application instances. My problem is it is
the user that is specifying the "unique" field (unique with a
context, not within the whole database),
Is the context other columns in the same table? You can make a
multiple column unique index, e.g. on firstName + lastName (that is
not a great example).
In the context of a foreign key. I'm guessing then you would get an
exception if you tried to violate the uniqueness?
Yes, when saving.
Would there be any other implications with doing this? I'll have to
look that up, I don't recall reading how to do it in eoModeler, can
you?
No.
and I need to somehow guarantee that uniqueness both before and
after the save. I was thinking that anywhere the "unique" value
was saved I could acquire the java lock, check for uniqueness by
refaulting the field, save if ok, release java lock. If not ok,
ask for a new name to be entered.
Barring that I was thinking maybe check both before and after the
save. If there did happen to be two after the save, I could add a
number after the filed to make it unique and save it again, of
course notifying the user of the new name. Of course I'd have to
check this new name again on the save in the same way as before
until I got one that stuck.
Think really carefully about that and about the same thing
happening at the same time in two instances.
Perhaps you could append a random number to the name.
That just reduces the window for the race condition. You can do it
right, or you can have an approximation of uniqueness. Your app, your
call.
Chuck
Are there other approaches to this problem?
The only reasonable approach is to use a unique index.
Chuck
On Jan 21, 2008, at 1:21 PM, Andrew Lindesay wrote:
Hello Neil;
What I tend to do is to have a lock in the database and then the
thread adding the new code has to acquire the lock and write the
record before being able to unlock the the lock. Addition of a
unique index will also ensure that the column values are not
duplicated.
cheers.
I think that your second idea of checking for the value before
insert is prone to a 0.00001% chance of another instance
inserting the same random number in between you checking the DB
and inserting into the DB. I don't think that WO supports SQL
transaction-based mode of operation (although am happy to be
educated on this point if it does). I'm not even sure if RawRow
based operations can be coerced into using transactions? Using
your first idea though, means that I don't need to go down this
route. I'd still be interested, in general, in how to avoid
these 'millisecond' windows of potential disaster between a read
and a write request.
___
Andrew Lindesay
technology : www.lindesay.co.nz
business : www.silvereye.co.nz
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve specific
problems.
http://www.global-village.net/products/practical_webobjects
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve specific
problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden