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