Re: Unique Fields in Database
Re: Unique Fields in Database
- Subject: Re: Unique Fields in Database
- From: Jeff Schmitz <email@hidden>
- Date: Wed, 18 Mar 2009 07:15:23 -0500
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? 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?
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.
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
_______________________________________________
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