Hi Bill,
Thanks for your reply!
On Wed, 2008-07-16 at 00:20 -0700, Bill Karwin wrote:
>
>
> diresu wrote:
> >
> > Example:
> >
> > database state process 1 process 2
> > --------------------------------------------------------
> > 1 reading 1
> > 1 reading 1
> > 1 adding 1
> > 1 adding 1
> > 2 writing back the
> > result 2
> > 2 writing back the
> > result 2
> > --------------------------------------------------------
> > the result is 2
> > but should be 3
> > On Wed, 2008-07-16 at 00:20 -0700, Bill Karwin wrote:
>
> diresu wrote:
> >
> > Example:
> >
> > database state process 1 process 2
> > --------------------------------------------------------
> > 1 reading 1
> > 1 reading 1
> > 1 adding 1
> > 1 adding 1
> > 2 writing back the
> > result 2
> > 2 writing back the
> > result 2
> > --------------------------------------------------------
> > the result is 2
> > but should be 3
> >
>
> Process 2 experiences an update conflict. It will either error out
> immediately, or block until process 1's transaction resolves.
>
> What you have described will not result in the value 3 being written
to the
> database. You can't achieve your goal with simple database
transactions
> alone. You have to serialize the access to the tables, by using table
or
> row locking, or by managing access with an interprocess semaphore.
>
> It's likely that you can redesign your application architecture so you
don't
> encounter this conflict.
>
> Regards,
> Bill Karwin
>
> Process 2 experiences an update conflict. It will either error out
> immediately, or block until process 1's transaction resolves.
blocking should be the right thing to do I suppose...
> What you have described will not result in the value 3 being written to the
> database. You can't achieve your goal with simple database transactions
> alone. You have to serialize the access to the tables, by using table or
> row locking, or by managing access with an interprocess semaphore.
How should I do this using Zend? Are there functions to serialize
access to tables in the zend database abstraction?
Do I rather have to use some functionality proprietary to the database
(postgresql)?
Is there a semaphore functionality available in Zend?
Which one should I use?
> It's likely that you can redesign your application architecture so you don't
> encounter this conflict.
How would I do that ?
I am actually using this in the following situation:
People are allowed to access some functionality when providing a
password. Every password can be used only a limited number of times (in
most cases only once). When supplying the password the database is
checked for a corresponding password entry in the database and some
counter field is decremented. When the counter is 1 the access is
granted and the password entry is deleted from the database.
I suppose that - even if extremely rare - two users might try to use the
password in the same time and the situation described arises...
As the time between reading the counter and decrementing it is minimal,
it should be OK if the second process is forced to wait until the first
one has finished.
As this should be a very common situation (different users accessing and
manipulating the same data) I thought there should be a simple
solution :)
How do people normally deal with this kind of situation in Zend?
There should be plenty of similar situations:
- when selling things - there is only a limited amount of
available items...
- when managing bank accounts - the same maximal amount should not
be retrived twice...
- ...
:)
Thanks
Dietrich
> Regards,
> Bill Karwin
