Hacker News Reader

Comment by pmontra | Hacker News Reader

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

View on HN

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

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

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

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

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

I 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

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

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

This also has the added benefit of allowing you to issue only 1 query(just the update), instead of select + update.

Don't mix transactions and locks. Transaction just ensures that all queries within it were run successfully and atomically. Locks are used to prevent race conditions. And in your particular example neither a transaction, nor lock would help, you need to check explicitly in UPDATE if you've got any books left.

Race conditions absolutely are among the set of things transactions are supposed to help with and locks are one of the several ways they do it. It's the I from ACID, which stands for Isolation.

Transactions are not omnipotent in that regard and they fail in several well-researched ways, but it is in their purview.

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

Probably yes. Check the comments at https://apidock.com/rails/v6.0.0/ActiveRecord/Relation/updat...

Just 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

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

In SQLalchemy you can

  book = Book.get(isbn="978-0099466031")
  book.copies_in_stock = Book.copies_in_stock + 1
  book.save()

Its also way faster and less complicated. One query line, one action on the db.

Reply on news.ycombinator.com