3 ms·

> If you use this in a real life, the more obvious way to write these transactions is: Is it? Do ORMs really do that, or is it one of those "SQL was designed t

by creshal 11y ago

> If you use this in a real life, the more obvious way to write these transactions is: Is it? Do ORMs really do that, or is it one of those "SQL was designed to be used this way, but nobody using SQL read the design documents" cases?

bsaul 11y ago
Good point, but imho anyone that relies on transactionnal properties to ensure validity of its operation should really not be using ORMs ( at least not for the sensitive operations). You shouldn't need to read an ORM documentation to understand what kind of locking is happening at a given time. It should all be there right in your code.
- ealexhudson 11y ago
  This approach only works if your logic is codable in SQL format in the way this trivial balance change can be. If the change is happening in app business logic, this is always going to be a problem. The fact the balance+=25 type approach works is kind of a hack, and I don't think Aphyr is wrong to call it "corrupted" (in this specific example, in one transaction, you could say that it is inconsistent - but you only need a few inconsistent operations in the same place and suddenly your data is complete rubbish). This feels a bit like handwaving the problem away, to me. Yes, InnoDB has the problem, but with that backend you can set SERIALIZABLE isolation. You can't do that in Galera. So if you don't have the option of rewriting the SQL (either because it's not your SQL, or there is no SQL alternative available) then you're screwed.
  - bsaul 11y ago
    Sure, my remark wasn't meant to dismiss aphyr blog. I completely agree that there is a big problem, and calling it corrupted seems valid to me as well. It was more of a sidenote about orms and transactions in general.
deleted 11y ago
[deleted]
viraptor 11y ago
They don't do that automatically. In sqlalchemy for example you still have to call an extra function explicitly. (http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#new-for-update-support-on-select-query http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09...) I know of only one developer who knew about "for update", but then again they just read the mysql book with chapter dedicated to locking behaviour.
needusername 11y ago
> Do ORMs really do that I haven't seen any, but they do use optimistic locking.
- otis_inf 11y ago
  (Disclaimer: I write ORMs for a living) ORMs might issue lock hints but in general they simply start the transaction with the desired isolation level and let the RDBMS take care of the locking of affected rows/tables by statements executed. Lock hints are sometimes used by naive ORMs who think you can implement optimistic concurrency using row locking. Their users will find out eventually that this whole row-locking doesn't help anyone as it simply reverses the tables on 'first write wins' vs. 'last write wins': there's still someone who loses, so the locking doesn't solve the problem of no-one losing their changes while it does give slower performance (and on SQL Server even the risk of deadlocks). What developers often overlook is that there are two types of transactions: business transactions and DB transactions. A business transaction can span multiple DB transactions and a DB transaction can span multiple SQL statements. They're not the same, seeing a business transaction as equal to a DB transaction makes things get messed up and often gives food to the need of explicit lock hints for some queries to e.g. get the false sense of being able to implement optimistic concurrency. If you consider a business transaction, locking doesn't make any sense: it can take some time to complete it, so you have to deal with the side effects of stale data: it immediately becomes apparent that e.g. optimistic concurrency has no place here, one needs other ways to avoid people overwriting work of each other. TFA re-orders statements to get the desired locks in place, and it IS possible to do so, e.g. some ORMs offer when to start a transaction or implement Unit of works which allow you to specify which batches to execute first (e.g. first deletes, then inserts). However the developer using the ORM isn't working at that abstraction level, as the ORM offers an abstraction level above all that; so re-ordering statements to get the desired read locks or avoid dirty reads within a transaction is abusing knowledge of how the abstraction offered by the ORM internally works. IMHO one shouldn't do that: the code utilizing the ORM is db agnostic: expecting certain DB behavior in DB agnostic code is going to give unpleasant side effects when the DB agnostic code will be used to e.g. utilize another RDBMS instead of the current one.
dack 11y ago
I think in general when writing applications, you should assume that any data you keep in memory between SQL queries could become stale and change before the next query/update. Anytime you have to update multiple individual records and rely on calculating state from both of them at once, alarm bells should start going off. Yes, you can start dropping into special transactions but there's also a potential opportunity for a better design. Unfortunately, all that feels like a huge accidental complexity.
- acveilleux 11y ago
  To some extent, isn't that the whole point of specifying the transaction isolation level you need? So you can make these assumptions?
  - dack 11y ago
    Sure, but there are tradeoffs in performance then. I'm not saying transactions are bad (although maybe my strong wording in the parent implies that), just that, especially if you're using an ORM, you shouldn't make those assumptions by default.
    - rcthompson 11y ago
      The only assumption that was made is that Galera cluster's SNAPSHOT ISOLATION actually implements SNAPSHOT ISOLATION. How is this not an assumption that should be made by default? If the database user has specified a specific isolation level, then they have already selected a specific trade-off in performance vs consistency. Giving them a weaker isolation level in the name of performance is like giving someone a car when they asked for a train ticket.
- bboreham 11y ago
  > Yes, you can start dropping into special transactions Transactions aren't "special" in SQL. You expect that reads and writes within a transaction are kept consistent, unless you have deliberately chosen a weaker serialization level.
numbsafari 11y ago
The point of SNAPSHOT ISOLATION is to, essentially, implement optimistic concurrency in a transparent fashion. The fact the the OP recommends using lock manipulation, shows that he's already ceded the point about SNAPSHOT ISOLATION. That is, the whole point of supporting SI is to avoid lock twiddling and the associated complexity and overhead. It's pretty clear that the OP is missing aphyr's point entirely.

by creshal 11y ago

bsaul 11y ago

Good point, but imho anyone that relies on transactionnal properties to ensure validity of its operation should really not be using ORMs ( at least not for the sensitive operations). You shouldn't need to read an ORM documentation to understand what kind of locking is happening at a given time. It should all be there right in your code.

ealexhudson 11y ago
This approach only works if your logic is codable in SQL format in the way this trivial balance change can be. If the change is happening in app business logic, this is always going to be a problem. The fact the balance+=25 type approach works is kind of a hack, and I don't think Aphyr is wrong to call it "corrupted" (in this specific example, in one transaction, you could say that it is inconsistent - but you only need a few inconsistent operations in the same place and suddenly your data is complete rubbish). This feels a bit like handwaving the problem away, to me. Yes, InnoDB has the problem, but with that backend you can set SERIALIZABLE isolation. You can't do that in Galera. So if you don't have the option of rewriting the SQL (either because it's not your SQL, or there is no SQL alternative available) then you're screwed.
- bsaul 11y ago
  Sure, my remark wasn't meant to dismiss aphyr blog. I completely agree that there is a big problem, and calling it corrupted seems valid to me as well. It was more of a sidenote about orms and transactions in general.

deleted 11y ago

[deleted]

viraptor 11y ago

They don't do that automatically. In sqlalchemy for example you still have to call an extra function explicitly. (http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#new-for-update-support-on-select-query http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09...) I know of only one developer who knew about "for update", but then again they just read the mysql book with chapter dedicated to locking behaviour.

needusername 11y ago

> Do ORMs really do that I haven't seen any, but they do use optimistic locking.

otis_inf 11y ago
(Disclaimer: I write ORMs for a living) ORMs might issue lock hints but in general they simply start the transaction with the desired isolation level and let the RDBMS take care of the locking of affected rows/tables by statements executed. Lock hints are sometimes used by naive ORMs who think you can implement optimistic concurrency using row locking. Their users will find out eventually that this whole row-locking doesn't help anyone as it simply reverses the tables on 'first write wins' vs. 'last write wins': there's still someone who loses, so the locking doesn't solve the problem of no-one losing their changes while it does give slower performance (and on SQL Server even the risk of deadlocks). What developers often overlook is that there are two types of transactions: business transactions and DB transactions. A business transaction can span multiple DB transactions and a DB transaction can span multiple SQL statements. They're not the same, seeing a business transaction as equal to a DB transaction makes things get messed up and often gives food to the need of explicit lock hints for some queries to e.g. get the false sense of being able to implement optimistic concurrency. If you consider a business transaction, locking doesn't make any sense: it can take some time to complete it, so you have to deal with the side effects of stale data: it immediately becomes apparent that e.g. optimistic concurrency has no place here, one needs other ways to avoid people overwriting work of each other. TFA re-orders statements to get the desired locks in place, and it IS possible to do so, e.g. some ORMs offer when to start a transaction or implement Unit of works which allow you to specify which batches to execute first (e.g. first deletes, then inserts). However the developer using the ORM isn't working at that abstraction level, as the ORM offers an abstraction level above all that; so re-ordering statements to get the desired read locks or avoid dirty reads within a transaction is abusing knowledge of how the abstraction offered by the ORM internally works. IMHO one shouldn't do that: the code utilizing the ORM is db agnostic: expecting certain DB behavior in DB agnostic code is going to give unpleasant side effects when the DB agnostic code will be used to e.g. utilize another RDBMS instead of the current one.

dack 11y ago

I think in general when writing applications, you should assume that any data you keep in memory between SQL queries could become stale and change before the next query/update. Anytime you have to update multiple individual records and rely on calculating state from both of them at once, alarm bells should start going off. Yes, you can start dropping into special transactions but there's also a potential opportunity for a better design. Unfortunately, all that feels like a huge accidental complexity.

acveilleux 11y ago
To some extent, isn't that the whole point of specifying the transaction isolation level you need? So you can make these assumptions?
- dack 11y ago
  Sure, but there are tradeoffs in performance then. I'm not saying transactions are bad (although maybe my strong wording in the parent implies that), just that, especially if you're using an ORM, you shouldn't make those assumptions by default.
  - rcthompson 11y ago
    The only assumption that was made is that Galera cluster's SNAPSHOT ISOLATION actually implements SNAPSHOT ISOLATION. How is this not an assumption that should be made by default? If the database user has specified a specific isolation level, then they have already selected a specific trade-off in performance vs consistency. Giving them a weaker isolation level in the name of performance is like giving someone a car when they asked for a train ticket.
bboreham 11y ago
> Yes, you can start dropping into special transactions Transactions aren't "special" in SQL. You expect that reads and writes within a transaction are kept consistent, unless you have deliberately chosen a weaker serialization level.

numbsafari 11y ago

The point of SNAPSHOT ISOLATION is to, essentially, implement optimistic concurrency in a transparent fashion. The fact the the OP recommends using lock manipulation, shows that he's already ceded the point about SNAPSHOT ISOLATION. That is, the whole point of supporting SI is to avoid lock twiddling and the associated complexity and overhead. It's pretty clear that the OP is missing aphyr's point entirely.