Board index » delphi » How to avoid 2 users changing the same record

How to avoid 2 users changing the same record

 In a client server database setup where it is possible for 2 users to
change the same records at the same time and then post the results back,
can anyone tell me what is the best or most standard way to avoid this
which I presume will use some form of record locking.

--
Nice One! Sorted!

Sent via Deja.com http://www.deja.com/
Before you buy.

 

Re:How to avoid 2 users changing the same record


It depends on the client/server database you are using. To lock records
several syntaxes excist, i.e. for
oracle 'select for update....'
However, lots of databases have methods to solve this problem. A commonly
used (default!) technique is
that by retrieving data, a copy is kept in the program memory. Before
writing the data back to the database it is checked that the original data
is still valid (No other user has changed it). If not, the update will cause
some kind of error message from the database.

Regards, Albert Drent

Quote
Paul Duke <p...@icm-computer.co.uk> wrote in message

news:8juttv$s3l$1@nnrp1.deja.com...
Quote
> In a client server database setup where it is possible for 2 users to
> change the same records at the same time and then post the results back,
> can anyone tell me what is the best or most standard way to avoid this
> which I presume will use some form of record locking.

> --
> Nice One! Sorted!

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Re:How to avoid 2 users changing the same record


Let's step back from this discussion and review the differences between
the two ways that various database-systems handle "how to avoid two
users changing the same record."

These strategies are called "pessimistic" and "optimistic."

A fileserver-based system such as Paradox for Windows uses the
"pessimistic" strategy. It "locks" the record before updating it, and
holds that lock until updates to the record have been completed.  No one
else can edit the record while the lock is held.  This is an acceptable
strategy -- but it does involve a lot of network-traffic to communicate
the locking in addition to the traffic used to communicate the data
changes.

A client-server (SQL) system traditionally uses an "optimistic"
strategy.  It is called optimistic because "we're optimistically hoping
that no one will ACTUALLY try to change THIS record RIGHT now -- we're
optimistically hoping that we'll be able to get away with it."  The user
is allowed to retrieve the record and make changes to it on his own
workstation.  AT THE TIME THAT THE USER WISHES TO COMMIT HIS CHANGES,
the computer [1] makes sure that the record really DIDN'T get changed,
and if so, [2] goes ahead and makes the changes.

In the optimistic strategy, the record is actually locked only briefly
... while steps [1] and [2] above are carried out.  The lock might be
held for only milliseconds.  

In the pessimistic strategy, the lock is held from the moment that the
user first decides to edit the record, all the way through to final
posting on disk.  That could be minutes or hours.

The optimistic strategy opens the possibility that, at the time the user
tries to save changes, the attempt-to-save might be rejected because
"another user changed the record."  It obligates the user or the program
to resolve the situation... having bet, and having lost the bet, that
"this won't happen very often."

The pessimistic strategy removes the possibility that "this will ever
happen" but at the cost of locking the record for the entire duration of
the retrieval, editing, <<going to the bathroom, talking on the phone,
having lunch, checking stocks and ebay, having a smoke, going to the
bathroom again oops shouldn't have had so much beer at lunch, ;-),
and>> posting the updates.

Quote
>albert drent wrote:

> It depends on the client/server database you are using. To lock records
> several syntaxes excist, i.e. for
> oracle 'select for update....'
> However, lots of databases have methods to solve this problem. A commonly
> used (default!) technique is
> that by retrieving data, a copy is kept in the program memory. Before
> writing the data back to the database it is checked that the original data
> is still valid (No other user has changed it). If not, the update will cause
> some kind of error message from the database.

> Regards, Albert Drent

> Paul Duke <p...@icm-computer.co.uk> wrote in message
> news:8juttv$s3l$1@nnrp1.deja.com...
> > In a client server database setup where it is possible for 2 users to
> > change the same records at the same time and then post the results back,
> > can anyone tell me what is the best or most standard way to avoid this
> > which I presume will use some form of record locking.

------------------------------------------------------------------
Sundial Services :: Scottsdale, AZ (USA) :: (480) 946-8259
mailto:i...@sundialservices.com  (PGP public key available.)

- Show quoted text -

Quote
> Fast(!), automatic table-repair with two clicks of the mouse!
> ChimneySweep(R):  "Click click, it's fixed!" {tm}
> http://www.sundialservices.com/products/chimneysweep

Re:How to avoid 2 users changing the same record


<<going to the bathroom again oops shouldn't have had so much beer at
lunch, ;-), >>

You just came back from a holiday in England <g> ???

--
Mike Irwin                    It's not because things are hard that we
[Volunteer CTech Sysop,       don't dare them; it's because we don't
not a Corel employee]         dare them that they are hard - Seneca.

Re:How to avoid 2 users changing the same record


 The optimistic strategy opens the possibility that, at the time the
user  tries to save changes, the attempt-to-save might be rejected
because  "another user changed the record."  It obligates the user or
the program  to resolve the situation... having bet, and having lost
the bet, that  "this won't happen very often."
====================
Will this happen automatically or we need to do some programing for
this ?

In article <3963A638.3...@sundialservices.com>,

Quote
  i...@sundialservices.com wrote:
> Let's step back from this discussion and review the differences
between
> the two ways that various database-systems handle "how to avoid two
> users changing the same record."

> These strategies are called "pessimistic" and "optimistic."

> A fileserver-based system such as Paradox for Windows uses the
> "pessimistic" strategy. It "locks" the record before updating it, and
> holds that lock until updates to the record have been completed.  No
one
> else can edit the record while the lock is held.  This is an
acceptable
> strategy -- but it does involve a lot of network-traffic to
communicate
> the locking in addition to the traffic used to communicate the data
> changes.

> A client-server (SQL) system traditionally uses an "optimistic"
> strategy.  It is called optimistic because "we're optimistically
hoping
> that no one will ACTUALLY try to change THIS record RIGHT now -- we're
> optimistically hoping that we'll be able to get away with it."  The
user
> is allowed to retrieve the record and make changes to it on his own
> workstation.  AT THE TIME THAT THE USER WISHES TO COMMIT HIS CHANGES,
> the computer [1] makes sure that the record really DIDN'T get changed,
> and if so, [2] goes ahead and makes the changes.

> In the optimistic strategy, the record is actually locked only briefly
> ... while steps [1] and [2] above are carried out.  The lock might be
> held for only milliseconds.

> In the pessimistic strategy, the lock is held from the moment that the
> user first decides to edit the record, all the way through to final
> posting on disk.  That could be minutes or hours.

> The optimistic strategy opens the possibility that, at the time the
user
> tries to save changes, the attempt-to-save might be rejected because
> "another user changed the record."  It obligates the user or the
program
> to resolve the situation... having bet, and having lost the bet, that
> "this won't happen very often."

> The pessimistic strategy removes the possibility that "this will ever
> happen" but at the cost of locking the record for the entire duration
of
> the retrieval, editing, <<going to the bathroom, talking on the phone,
> having lunch, checking stocks and ebay, having a smoke, going to the
> bathroom again oops shouldn't have had so much beer at lunch, ;-),
> and>> posting the updates.

> >albert drent wrote:

> > It depends on the client/server database you are using. To lock
records
> > several syntaxes excist, i.e. for
> > oracle 'select for update....'
> > However, lots of databases have methods to solve this problem. A
commonly
> > used (default!) technique is
> > that by retrieving data, a copy is kept in the program memory.
Before
> > writing the data back to the database it is checked that the
original data
> > is still valid (No other user has changed it). If not, the update
will cause
> > some kind of error message from the database.

> > Regards, Albert Drent

> > Paul Duke <p...@icm-computer.co.uk> wrote in message
> > news:8juttv$s3l$1@nnrp1.deja.com...
> > > In a client server database setup where it is possible for 2
users to
> > > change the same records at the same time and then post the
results back,
> > > can anyone tell me what is the best or most standard way to avoid
this
> > > which I presume will use some form of record locking.

> ------------------------------------------------------------------
> Sundial Services :: Scottsdale, AZ (USA) :: (480) 946-8259
> mailto:i...@sundialservices.com  (PGP public key available.)
> > Fast(!), automatic table-repair with two clicks of the mouse!
> > ChimneySweep(R):  "Click click, it's fixed!" {tm}
> > http://www.sundialservices.com/products/chimneysweep

Sent via Deja.com http://www.deja.com/
Before you buy.

Re:How to avoid 2 users changing the same record


Hello,

perhaps a pessimistic strategy with a time out function for the user to held
the record, is a good compromize.
A pop up warning with 'If you're not posting in 30 seconds, you will be
thrown out!' Like a auto disconnect from internet.

:)
Kai Inge

Quote
<ttus...@my-deja.com> wrote in message news:8m9hib$3f$1@nnrp1.deja.com...
> The optimistic strategy opens the possibility that, at the time the
> user  tries to save changes, the attempt-to-save might be rejected
> because  "another user changed the record."  It obligates the user or
> the program  to resolve the situation... having bet, and having lost
> the bet, that  "this won't happen very often."
> ====================
> Will this happen automatically or we need to do some programing for
> this ?

> In article <3963A638.3...@sundialservices.com>,
>   i...@sundialservices.com wrote:

<Snip>
 > The pessimistic strategy removes the possibility that "this will ever
Quote
> > happen" but at the cost of locking the record for the entire duration
> of
> > the retrieval, editing, <<going to the bathroom, talking on the phone,
> > having lunch, checking stocks and ebay, having a smoke, going to the
> > bathroom again oops shouldn't have had so much beer at lunch, ;-),
> > and>> posting the updates.

<Snip>

Other Threads