On Mon, Nov 9, 2009 at 10:56 PM, Tamas Szabo <szab...@gmail.com> wrote:
> Would something like > > UPDATE bid = new_bid WHERE id = id and bid < new_bid > > work for you? > > It is a more optimistic approach (it assumes that the case you describe is > an exception rather than what usually happens) and I think it is simpler by > not having to do any locking etc. > > > Regards, > > Tamas > > > That only works if you have transactions disabled for your database. If your connections are using transactions, you still have the race condition. For example (in MySQL): Start two DB Shells In the first shell: mysql> insert into bids (id, high_bid) values (1, 20); Query OK, 1 row affected (0.00 sec) mysql> START TRANSACTION; Query OK, 0 rows affected (0.01 sec) In the second shell: mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> update bids set high_bid = 25 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) Back in the first shell: mysql> UPDATE bids SET high_bid = 23 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from bids; +------+----------+ | id | high_bid | +------+----------+ | 1 | 23 | +------+----------+ 1 row in set (0.00 sec) Craig > > > > On Tue, Nov 10, 2009 at 2:17 PM, Continuation <selforgani...@gmail.com>wrote: > >> >> Thanks Christophe and Kenneth! >> >> Let me make sure I understand this: >> >> If I write this vew function: >> >> @transaction.commit_on_success >> def update_high_bid(request): >> .... >> cursor = connection.cursor() >> cursor.execute("SELECT high_bid FROM auctionapp_auction WHERE >> id=%s >> FOR UPDATE", [auction.id]) >> returned_rows = cursor.fetchall() >> high_bid = returned_rows[0][0] >> if new_bid > high_bid: >> auction.high_bid = new_bid >> auction.save() >> >> The entire function will be wrapped within a transaction. >> SELECT FOR UPDATE will acquire a row-level lock >> and that lock will not be released until the function update_high_bid >> () returns successfully, or until the function raises an exception and >> the whole transaction is rolled back. >> >> Is that right? >> >> Thanks. >> >> >> On Nov 10, 12:45 am, Christophe Pettus <x...@thebuild.com> wrote: >> > On Nov 9, 2009, at 9:34 PM, Continuation wrote: >> > >> > > Also does django middleware acquire database lock on my behalf, or do >> > > I need to explicitly perform the locking? >> > >> > In the example code, it's the SELECT ... FOR UPDATE that acquires the >> > lock. Django doesn't currently have any explicit knowledge of >> > locking, so you need to drop down to the custom SQL level to issue the >> > right statement to acquire the lock on the row. >> > >> > The example I wrote assumed you were using PostgreSQL as the backend; >> > you can get all sorts of details about locking in PostgreSQL here: >> > >> > >> http://www.postgresql.org/docs/8.4/interactive/explicit-locking.html#... >> > -- >> > -- Christophe Pettus >> > x...@thebuild.com >> >> > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---