Re: How to handle this race condition?

[Continuation]

Thank you.

On Nov 10, 10:03 pm, Tamas Szabo  wrote:
> You can inspect the rowcount attribute of the cursor object after you
> executed your update.
> rowcount returns the number of rows affected by your update, so it will be 1
> if your where condition was true.
>
> On Wed, Nov 11, 2009 at 10:04 AM, Continuation wrote:
>
>
>
> > > Would something like
>
> > > UPDATE bid = new_bid WHERE id = id and bid < new_bid
>
> > > work for you?
>
> > This is a great idea.
>
> > One question is, how do I know whether bid has been updated to new_bid
> > or not (ie. how to tell whether the conditional test bid < new_bid is
> > true or not)?
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Tamas Szabo]

You can inspect the rowcount attribute of the cursor object after you
executed your update.
rowcount returns the number of rows affected by your update, so it will be 1
if your where condition was true.



On Wed, Nov 11, 2009 at 10:04 AM, Continuation wrote:

>
>
> > Would something like
> >
> > UPDATE bid = new_bid WHERE id = id and bid < new_bid
> >
> > work for you?
>
> This is a great idea.
>
> One question is, how do I know whether bid has been updated to new_bid
> or not (ie. how to tell whether the conditional test bid < new_bid is
> true or not)?
>
>
> >
>

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Continuation]

> Would something like
>
> UPDATE bid = new_bid WHERE id = id and bid < new_bid
>
> work for you?

This is a great idea.

One question is, how do I know whether bid has been updated to new_bid
or not (ie. how to tell whether the conditional test bid < new_bid is
true or not)?


--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Tamas Szabo]

>
> 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.
>
>
IMHO, both approaches are valid and have their own advantages and
disadvantages.
I don't think that we have enough information to make a suggestion.
What works best for the situation is for the OP to decide.

Regards,

Tamas

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Craig Kimerer]

Ah, you're absolutely right.  I am not sure how I didn't even catch the
other thing that should have grabbed my attention this morning, which was
that the table I was using was MyISAM.  This is why I shouldn't send emails
before noon :)

Sorry about that, that was my mistake.

Craig

On Tue, Nov 10, 2009 at 3:28 PM, akaariai  wrote:

>
> 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  wrote:
> > On Mon, Nov 9, 2009 at 10:56 PM, Tamas Szabo  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  >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  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 

Re: How to handle this race condition?

[akaariai]

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  wrote:
> On Mon, Nov 9, 2009 at 10:56 PM, Tamas Szabo  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 
> > 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  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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Christophe Pettus]

On Nov 10, 2009, at 11:55 AM, Craig Kimerer wrote:
> That only works if you have transactions disabled for your  
> database.  If your connections are using transactions, you still  
> have the race condition.

The example you give isn't exactly the same as the suggestion.  In the  
suggestion, it includes a comparison of the bid amount in the WHERE  
clause:

UPDATE bid = new_bid WHERE id = id and bid < new_bid;

That's what makes it work.  Lacking that, you're absolutely right, the  
race condition remains.

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Craig Kimerer]

On Mon, Nov 9, 2009 at 10:56 PM, Tamas Szabo  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 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  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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Skylar Saveland]

Why store it in the database?  You might be able to avoid this
directly if you use a model method for this attribute.


Continuation wrote:
> I'm working on an auction app.
>
> In an Auction object I store the current_high_bid for that auction.
>
> When a new_bid comes in, I:
> 1) retrieve the current_high_bid field of the Auction object
> 2) compare it with the new_bid
> 3) if the new bid is higher than current_high_bid, I update
> current_high_bid to the value of the new_bid.
>
> but there's a (slight) chance that between steps (1) and (3) a
> different user could've submitted an even higher bid which then become
> the current_high_bid. In that case step 3 should not proceed. What can
> I do to make sure such a situation does not arise?
>
> Thanks
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Christophe Pettus]

On Nov 9, 2009, at 10:56 PM, Tamas Szabo wrote:

> UPDATE bid = new_bid WHERE id = id and bid < new_bid

That's an even better solution, if no further processing within the  
locked area is required.

--
-- 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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[bruno desthuilliers]

On 10 nov, 05:36, Continuation  wrote:
> I'm working on an auction app.
>
> In an Auction object I store the current_high_bid for that auction.
>
> When a new_bid comes in, I:
> 1) retrieve the current_high_bid field of the Auction object
> 2) compare it with the new_bid
> 3) if the new bid is higher than current_high_bid, I update
> current_high_bid to the value of the new_bid.
>
> but there's a (slight) chance that between steps (1) and (3) a
> different user could've submitted an even higher bid which then become
> the current_high_bid. In that case step 3 should not proceed. What can
> I do to make sure such a situation does not arise?

Well, the SimplestSolution would be to NOT denormalize your DB. From a
relational / SQL POV, you shouldn't store the "current_high_bid" in
the 'auction' table, but query the 'bid' table for the currently max
bid for this auction.

But I guess you have some good reasons to dismiss this solution...



--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Christophe Pettus]

On Nov 9, 2009, at 10:17 PM, Continuation wrote:

> 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.

Yes, that appears to be correct.
--
-- 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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Preston Holmes]

another option if you want to stay in just python and are on a POSIX
system is to use system file locking to ensure you don't get
concurrent calls to your updated function.

http://docs.python.org/library/fcntl.html#fcntl.lockf

-Preston

On Nov 9, 10:17 pm, Continuation  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  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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Tim Chase]

>> 1) retrieve the current_high_bid field of the Auction object
>> 2) compare it with the new_bid
>> 3) if the new bid is higher than current_high_bid, I update
>> current_high_bid to the value of the new_bid.
>>
>> but there's a (slight) chance that between steps (1) and (3) a
>> different user could've submitted an even higher bid which then become
>> the current_high_bid. In that case step 3 should not proceed. What can
>> I do to make sure such a situation does not arise?
> 
> For now, you can write custom SQL (consider this pseudo-code rather  
> than a drop-in replacement):
> 
>   """ Be sure this all runs inside a single transaction!  Use the  
> appropriate middleware or
>   decorators... """
>   cursor = connection.cursor()
>   cursor.execute("SELECT high_bid FROM auctionapp_auction WHERE id=%s  
> FOR UPDATE", [auction.id])
>   """ Until this SELECT, other users could have modified the high  
> bid, so do not trust the
>   in-memory copy. """
>   returned_rows = cursor.fetchall()
>   high_bid = returned_rows[0][0]
>   if new_bid > high_bid:
>   auction.high_bid = new_bid
>   auction.save()


If you are going to drop to raw SQL, you can let SQL handle the 
atomicity:

   UPDATE auction SET
 current_high_bid = %(new_bid)f
   WHERE id = %(auction_id)i
 AND %(new_bid)f > current_high_bid

If the new_bid is based on data in another table of 
bid-histories, you can use something like

   UPDATE auction SET
 current_high_bid = (
 SELECT Max(bid)
 FROM Bids
 WHERE auction_id = %(auction_id)s
 )
   WHERE id = %(auction_id)i
 AND (
 SELECT Max(bid)
 FROM Bids
 WHERE auction_id = %(auction_id)s
 ) > current_high_bid

Adjust for table-names and field-names.  It should be fairly 
portable SQL, working across all of the big 3 Django databases 
(sqlite, postgres, and recent versions of mysql that support 
sub-selects...it also happens to work in SQL Server, FWIW)

-tim




--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Tamas Szabo]

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




On Tue, Nov 10, 2009 at 2:17 PM, Continuation 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  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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Continuation]

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  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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Christophe Pettus]

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#LOCKING-ROWS
--
-- 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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Christophe Pettus]

On Nov 9, 2009, at 9:30 PM, Continuation wrote:
> Can you tell me which middleware/decorator should I use to make sure
> all those run within a single transaction? I'm pretty new to this.

The transaction middleware will work:


http://docs.djangoproject.com/en/dev/topics/db/transactions/#tying-transactions-to-http-requests

You can also use the @commit_on_success decorator:


http://docs.djangoproject.com/en/dev/topics/db/transactions/#django-db-transaction-commit-on-success


--
-- 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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Kenneth Gonsalves]

On Tuesday 10 Nov 2009 11:00:55 am Continuation wrote:
> > """ Be sure this all runs inside a single transaction!  Use the  
> > appropriate middleware or
> > decorators... """
> 
> Thanks for your help.
> 
> Can you tell me which middleware/decorator should I use to make sure
> all those run within a single transaction? I'm pretty new to this.
> 
transaction_commit_on_success - not too sure of the spelling.
-- 
regards
Kenneth Gonsalves
Senior Project Officer
NRC-FOSS
http://nrcfosshelpline.in/web/

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Continuation]

On Nov 10, 12:07 am, Christophe Pettus  wrote:
> Beware of deadlocks!  Keep the number of tables on  
> which you acquire locks to a minimum, and acquire them in the same  
> order in all places in your code.

Also does django middleware acquire database lock on my behalf, or do
I need to explicitly perform the locking?

Thanks.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Continuation]

On Nov 10, 12:07 am, Christophe Pettus  wrote:

>         """ Be sure this all runs inside a single transaction!  Use the  
> appropriate middleware or
>             decorators... """

Thanks for your help.

Can you tell me which middleware/decorator should I use to make sure
all those run within a single transaction? I'm pretty new to this.

Thanks again!
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

Re: How to handle this race condition?

[Christophe Pettus]

On Nov 9, 2009, at 8:36 PM, Continuation wrote:
> When a new_bid comes in, I:
> 1) retrieve the current_high_bid field of the Auction object
> 2) compare it with the new_bid
> 3) if the new bid is higher than current_high_bid, I update
> current_high_bid to the value of the new_bid.
>
> but there's a (slight) chance that between steps (1) and (3) a
> different user could've submitted an even higher bid which then become
> the current_high_bid. In that case step 3 should not proceed. What can
> I do to make sure such a situation does not arise?

This is, of course, exactly what database locking what designed to  
handle (indeed, this is pretty much a textbook case).  Right now,  
Django doesn't provide any built-in method of doing row-level locking  
through the model.  (There's an open ticket that discusses this: 
http://code.djangoproject.com/ticket/2705 
  )

For now, you can write custom SQL (consider this pseudo-code rather  
than a drop-in replacement):

""" Be sure this all runs inside a single transaction!  Use the  
appropriate middleware or
decorators... """
cursor = connection.cursor()
cursor.execute("SELECT high_bid FROM auctionapp_auction WHERE id=%s  
FOR UPDATE", [auction.id])
""" Until this SELECT, other users could have modified the high  
bid, so do not trust the
in-memory copy. """
returned_rows = cursor.fetchall()
high_bid = returned_rows[0][0]
if new_bid > high_bid:
auction.high_bid = new_bid
auction.save()

If another user attempts to update the bid, it will block when doing  
the SELECT... FOR UPDATE until the first transaction closes, and will  
then continue.  Beware of deadlocks!  Keep the number of tables on  
which you acquire locks to a minimum, and acquire them in the same  
order in all places in your code.
--
-- 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
-~--~~~~--~~--~--~---