There is no need to use transactions if you are dealing with 1 table.

Have a version column in the table which is numeric. When you first
select/edit the record, store the version number.

When you update, use:

    update table set version = version + 1, data = ...  where key=$id and
version = $version

If the version number is different (eg. someone has updated the record)
affected_rows() will be zero.

This technique is sometimes called an optimistic locking strategy.

Regards, John

"Bopolissimus Platypus" <[EMAIL PROTECTED]> wrote in message
> hello all,
> I've got a question about what's got to be a common problem.
> I'm sort of doing a survey to see how others have solved this
> problem before so that I can choose one that's best for me.
> Basics:
>     The software is a web based data entry system.
>     Sessions are maintained (so that users don't have to
>          login after every database operation, and for
>          security - users can only perform actions that
>          are allowed to their login or group0.
> Use Case:
> 0.   User A loads Record R.
> 1.   User B loads the same Record R.
> 2.   User B edits and saves Record R.
> 3.   User A edits and saves Record R.
>     at this point, User B's changes are probably lost either
>     in whole or in part.
> I can think of at least two or three ways to deal with this, but
> the two are ugly hacks that I'd rather avoid and the third risks
> running out of a vital and limited resource.
> The simplest solution I can think of would have the backend
> system start a transaction for A, at step 0.  the transaction
> would end at step 3.  User B would also try to start a
> transaction but since User A's transaction is still running,
> B would block until A ends it or rolls it back (since A is holding
> a lock).  for databases that don't have transactions, we would
> use record locking.
> The problem with this, of course, is that step 0 and step 3
> don't occur during the same HTTP request, so the transaction
> (or the locks) would have to be held/remembered between
> requests.  Further, the user need not actually save the record.
> he might just be viewing it.  After he's done, he might just
> close the browser.  So there would have to be a timeout
> so that locks would expire after a while.
> using SQL transactions as outlined above is messy but
> doable (there would be a middle-tier application layer that
> the PHP calls instead of calling database access functions
> directly).  i don't like it though because transactions are
> one per connection.  each possible database update
> would require a separate socket connection to the
> database.  and if our timeout is liberal (e.g., 30 minutes),
> then we're going to run out of sockets very quickly.
> i'd be very interested in hearing what others have
> done about this.  i've got other ideas that don't involve
> transactions, but i'm not going into those since they're
> too ugly to mention.  if i get desperate enough though,
> i may just ignore the ugliness and implement something
> just to get something working.
> tiger

PHP Database Mailing List (
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to