You missed the 'where bid < new_bid' part of the update. In that case
there is no race I am aware of. Either the transaction sees the update
of the other transaction in which case the highest bid will stay in
effect or it will not see the effect of the other transaction in which
case there is concurrent update and the transaction is aborted.
Anyways, as concurrency is hard and taking row level locks is cheap
and easy way to get the concurrency right I would suggest you use the
select for update method. Also, this way you will avoid the need to
retry failed transactions because of concurrent updates.
On Nov 10, 9:55 pm, Craig Kimerer <craig.kime...@gmail.com> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
