TL/DR: Transactions do not inherently prevent all race conditions. You still need locking, abort-and-retry handling, or other protective measures in all real-world database implementations. Transactions are not a secret sauce you can add to your queries to make them safe from all concurrency effects. Isolation What you’re getting at with your question is the I … Read more
There are a few things to keep in mind about Go’s goroutines: They are not threads in the sense of Java’s or C++ threads goroutines are more like greenlets The go runtime multiplexes the goroutines across the system threads the number of system threads is controlled by an environment variable GOMAXPROCS and defaults to 1 … Read more
Edit: I googled to check my answer: “Processing Data Queues in SQL Server with READPAST and UPDLOCK”. It’s been years since I read about and played with this solution. Original: If you use the READPAST hint, then locked rows are skipped. You’ve used ROWLOCK so you should avoid lock escalation. You also need UPDLOCK, as … Read more
The first definition you refer to is part of the kernel lock validator, aka “lockdep”. WRITE_ONCE (and others) don’t need special treatment, but the reason why is the subject of another question. The relevant definition would be here, and a very terse comment states their purpose to be: Prevent the compiler from merging or refetching … Read more
Django 1.4+ supports select_for_update, in earlier versions you may execute raw SQL queries e.g. select … for update which depending on underlying DB will lock the row from any updates, you can do whatever you want with that row until the end of transaction. e.g. from django.db import transaction @transaction.commit_manually() def add_points(request): user = User.objects.select_for_update().get(id=request.user.id) … Read more
INSERT INTO TABLE SELECT value_for_column1, value_for_column2, … FROM wherever WHERE your_special_condition If no rows are returned from the select (because your special condition is false) no insert happens. Using your schema from question (assuming your id column is auto_increment): insert into orders (product_id, qty) select 2, 20 where (SELECT qty_on_hand FROM products WHERE id = … Read more
There are already a bunch of answers here, but I would really like to dive into some details (as much as my knowledge let’s me). I will strongly advise you to run each sample that is present here in the answer to see for yourself how things are happening and why. To understand the solution, … Read more
While Erwin’s suggestion is possibly the simplest way to get correct behavior (so long as you retry your transaction if you get an exception with SQLSTATE of 40001), queuing applications by their nature tend to work better with requests blocking for a chance to take their turn at the queue than with the PostgreSQL implementation … Read more
Use an F expression: from django.db.models import F either in update(): Counter.objects.get_or_create(name=name) Counter.objects.filter(name=name).update(count=F(“count”) + 1) or on the object instance: counter, _ = Counter.objects.get_or_create(name=name) counter.count = F(“count”) + 1 counter.save(update_fields=[“count”]) Remember to specify update_fields, or you might encounter race conditions on other fields of the model. A note on the race condition avoided by using … Read more
Think of a race condition using the traditional example. Say you and a friend have an ATM cards for the same bank account. Now suppose the account has $100 in it. Consider what happens when you attempt to withdraw $10 and your friend attempts to withdraw $50 at exactly the same time. Think about what … Read more