Re: SELECT FOR UPDATE

jose wrote:
> Jan Wieck wrote:
>
> > 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.
> >
> Unfortunatelly this aproach have a problem.
> What about if the backend or the application crashes in the middle of
> editing?
>
> This could also be done by adding a field in the record itself and set it
> every time you edit it and unset it after the edit time.
> In this case you need to update the record every time you read it :(
> This job should be done by the DB itself, perhaps this is the way it
> works right now!

It is not the way it works now and if you want to pool
database connections it cannot be done by the database.

The advantage of having a central lock table where the key
contains the object type and primary key is, that
administrative functions (for resetting the user lock maybe?)
must not be touched if you add more object types. With the
correct structure you'll have the information who holds the
lock, since when and whatnot as well, to make the admin
happy. And you don't have to rely on non-SQL-standard
features!

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