Re: SELECT FOR UPDATE

Oliver Elphick wrote:
> Jan Wieck wrote:
> > But the question itself tells that you're about to implement
> > a major design error in your application. Holding database
> > locks during user interaction IS A BAD THING. Never, never
> > ever do it that way. And anybody telling you something
> > different is an overpaid idiot.
>
> I can see arguments to support this view, but consider this classic
> scenario:
>
> User1: Read data into an interactive program
> User1: Start to make changes
> User2: Read data into an interactive program
> User2: Start to make changes
> User1: Save changes
> User2: Save changes

All ERP systems I know deal with that issue by inserting and
deleting some advisory lock information in another table.
Let's say you want to change customers 4711 address. Before
letting you do so on the edit screen, the application tries
to insert "CUST.4711" into a central lock table. Now this
thing has a unique index on that field, so if someone else is
already editing 4711, it'll fail and the application can tell
you so and won't let you do the same.

AFAIK it's the only way to deal with that problem. Think
about scaling as well. No enterprise class software has a DB
connection per interactive user. They all have some sort of
DB-middletear-presentation model where many users share a few
DB connections.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com