On Tue, Jun 19, 2012 at 8:29 AM, Joerg Braun <[email protected]> wrote:
> In another posting you had something with sleep 10sec. I do not think that > you want to lock a table longer than necessary. I would suggest another > approach. You are talking about auctions. So you have a table with articles > to bid for and maybe a table with user bids. So in pseudo code I would code > something like: > > UPDATE article > set current_bid = 456 > where article_id = 123 > and current_bid < <new_bid> > > <new_bid> being the users bid. This might be another select. If the update > fails somebody was faster. If not the article itself is locked until you > did your inserts and commit. > > Even the well known 3,2,1-company gives you one value and still someone > else might be higher if you bid to slow. That is life and has to be > accepted. Otherwise you let one or more bidders wait until the first (and > the second) has decided what to do or what not. Just an idea. > This is the approach I often use, although it's typically to allow one process take ownership of a task (e.g. UPDATE job SET state = 'in progress' where id = ? and state = 'pending' ); If the update succeeds you know you were (at that instant) the highest bid and can then record the bid in the transaction table. You still might have to say "Your bid was recorded, but sorry to say it is no longer the highest bid. Bid again?" I prefer to avoid locks when possible. I've got a current SELECT .. FOR UPDATE running and every once in a while I see deadlock errors from Postgresql, and we haven't spent enough time digging through the logs to figure out why. They are never fun to sort out. A question: What if you allow multiple people to bid the same amount? Is that really a problem? What if the "highest" bid is just the earliest/first of the largest bids? Then you just do an insert. Can't expect everyone else to sit and wait while one user ponders their bid. Then again, you can tell them if their bid is no longer the highest. Maybe offer a "run-off" for an action that is a tie? Bidding wars are good for commission. -- Bill Moseley [email protected]
_______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
