5 ms·
The problem with this code book = Book.get(isbn="978-0099466031") book.copies_in_stock -= 1 book.save() # WRONG! irreparable data loss here is that in
by pmontra 7y ago
The problem with this code
book = Book.get(isbn="978-0099466031")
book.copies_in_stock -= 1
book.save() # WRONG! irreparable data loss here
is that in SQL you should write it like this:
update books set
copies_in_stock = copies_in_stock - 1
where isbn = '978-0099466031';
No needs of transactions.
I don't remember how to do it with Django's ORM but in Rail's ActiveRecord I'd use directly the connection object
update_query = sanitize_sql(['update books set copies_in_stock = copies_in_stock - 1 where isbn = ?', isbn])
ActiveRecord::Base.connection.execute(update_query)
This saves a lot of programming pain, possibly skip some unnecessary bugs and get better performances.
sanitize_sql is from https://api.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html https://api.rubyonrails.org/classes/ActiveRecord/Sanitizatio...
- ZiiS 7y agoIn SQLalchemy you can book = Book.get(isbn="978-0099466031") book.copies_in_stock = Book.copies_in_stock + 1 book.save()
- oxalorg 7y agoTrue. It's even easier in Django using F Expressions book = Book.objects.get(isbn="978-0099466031") book.copies_in_stock = F('copies_in_stock ') -1 book.save() F expressions perform operations at the DB level directly using column name without ever pulling that data into Python code.
- strken 7y agoDoes ActiveRecord (the Ruby library) support e.g. Book.where({isbn: "978-0099466031"}).update_all("copies_in_stock = copies_in_stock - 1") without breaking? It's been a long time since I've used it and I can't remember whether you have to use .execute or not.
- pmontra 7y agoProbably yes. Check the comments at https://apidock.com/rails/v6.0.0/ActiveRecord/Relation/update_all https://apidock.com/rails/v6.0.0/ActiveRecord/Relation/updat...
- petepete 7y agoJust tried it and it works fine. Feels a bit un-Railslike but it does exactly what you'd expect it to > Unit.where(ccp_id: 1).update_all("position = position + 1") Unit Update All (9.4ms) UPDATE "units" SET position = position + 1 WHERE "units"."ccp_id" = $1 => 5
- calpaterson 7y ago(Author here,) I know it seems like a synthetic example but this kind of code is quite common, particularly from devs who don't know databases well. I suppose editing a json field or something would seem more plausible and I should have used that example (though there are also ways to do that in Postgres I think).
- pmontra 7y agoI know. Examples can't always be close to the real world and there are really people writing that kind of code. I also think you can update a json field in a single statement in postgresql but I didn't check how. The syntax did working on jsons can be quite unwieldy.
- falcor84 7y ago>... particularly from devs who don't know databases well I think that a lot of your argument just boils down to this. The Active Record pattern allows less experienced/knowledgeable developers to write CRUD code that works. These are the same devs which would often create unnecessary quadratic (or even worse) solutions in regular imperative code too. Their code would often be far from pretty or optimized, but using modern high level languages and libraries, they can relatively easily get the job done and provide business value. If given a dichotomous choice between libraries that are make it easier to write and to reason about out of the box, or libraries that perform much better but only "when you know what you're doing", most would choose the former. And I think they'd be right, there's enough complexity out there in the actual domain already. But it's actually even better, it's not actually a dichotomous choice, and it's generally quite straightforward to bring in a more experienced dev to help refactor and optimize such code later, when the need arises.
- GrumpyNl 7y agoIts also way faster and less complicated. One query line, one action on the db.
- JoshuaDavid 7y agoThe transaction-free approach still has potential issues though. Consider: select isbn, copies_in_stock from books where isbn = "12345678" and copies_in_stock >= 1; isbn: 12345678 copies_in_stock: 1 update books set copies_in_stock = copies_in_stock - 1 where isbn = "12345678" If you have two of the above requests happen at the same time such that the execution order is SELECT SELECT UPDATE UPDATE, you'll end up with -1 copies_in_stock. You can try adding an "and copies_in_stock >= 1" to the UPDATE and then look at the number of affected rows, and perform recovery logic if the number of affected rows is 0, but at that point you've basically implemented a transaction, probably badly if it isn't what you set out to do.
- pmontra 7y agoI understand that this is an example so I try to build some context around those queries. The issue here is that you (apparently) want to guarantee that whoever sees the result of the select (probably a customer) can safely buy the last available book with the update. This can't be solved in a simple way because minutes can pass between that select and that update. This means that you can't put those queries in a transaction. If you could you should use select for update https://www.postgresql.org/docs/current/sql-select.html https://www.postgresql.org/docs/current/sql-select.html I used code like update table set counter = counter + 1 / - 1 many times without transactions when all I have to do is to update a counter and nothing else in the system really cares about the value of the counter. If this is not the case, the queries accessing the counter should use some form of locking. A transaction is not enough. This is the first good guide I found googling for examples https://vladmihalcea.com/a-beginners-guide-to-database-locking-and-the-lost-update-phenomena/ https://vladmihalcea.com/a-beginners-guide-to-database-locki...
- Sacho 7y agoI don't see how a transaction saves you here(without locking the row), can you elaborate? In EF projects, this problem is solved through optimistic concurrency, similar to the fashion you described - a column that changes on each update is used as a canary to confirm that you're applying changes to the record you expect. https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting... This also has the added benefit of allowing you to issue only 1 query(just the update), instead of select + update.
- thom 7y agoWhile I'm not claiming the first snippet is the correct way to structure this sort of thing, all of these libraries have supported optimistic locking in some form for many years.