Re: Database "Transactions" and HTTP statelessness

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" <bopolissimus@sni.ph> wrote in message news:3b8c4559.81082672@news.php.net... > 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