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
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> 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 (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
