9 ms·

>Hopefully it’s clear why this is an issue. If you have an important column value, say a user’s account balance, you might query multiple different values in th

by trhway 3y ago

>Hopefully it’s clear why this is an issue. If you have an important column value, say a user’s account balance, you might query multiple different values in the same transaction and that is a feature, not a bug. During your transaction user withdrew money from his account. If you wanna see the same value, i.e. block that user from withdrawals while you process his current balance, you take a lock on that row. Similar in many ways to the concurrent programming with threads.

aphyr 3y ago
The point of isolation levels is that the database manages those locks (or other concurrency control mechanisms) for you.
- wrs 3y ago
  Bearing in mind that “handling” the locks may mean a deadlock is detected and the transaction is aborted by the DB, which the application has to handle by retrying in a correct manner. (Which is another point often missed in naive application code.)
- trhway 3y ago
  Not exactly - the database doesn't know whether you want to block that account update or not. So, speaking very roughly, it allows you to choose general level of such a blocking behavior. It manages those locks, very coarse, at serializable and some of those locks at repeatable read. At read committed it manages basically only small set of locks just to avoid dirty reads (plus of course all the WAL/rollback/etc. which is the main features and the point of the database with the isolation levels being just the icing on that cake). Isolation levels is a tool, and you choose what is needed and suitable. The serializable actually is a pain in the neck for any meaningfully serious large enterprise application. The decreased concurrency kills overall performance, and I haven't seen any such application running in serializable (and for example at my current job our customers are running our application on servers with low tens of TB RAM (largest so far was 40TB RAM) per single machine with high hundreds of cores with thousands of users - in serializable that would slow to a crawl as it wouldn't allow to achieve the concurrency the database on those machines is otherwise capable of). While not precise illustration it still communicates my point - the serializable against read committed is like the global Python lock against the fine grained locks in normal languages.
jen20 3y ago
If you're storing a balance, rather than a ledger of the transactions which caused that balance to be the case, you're probably already off to a bad start when it comes to design. Just imagine if you called your bank and asked them why my balance was $500, and they said "I don't know, hit F5 and see if it changes"...
- gav 3y ago
  When it comes to this topic, almost all examples don't match how things work in the real world where they are eventually consistent. For example, my bank lets me go negative and then if I don't settle by the end of the day, overdraft protection will kick in for a little more than that negative amount.
  - jen20 3y ago
    Indeed - seeing account balance used as an example of database transactions is a red flag to me, and would absolutely cause me to dig deeper into whether the author/speaker understands how banking actually works.
    - amw-zero 3y ago
      Hello, I wrote the post. I don’t claim to be a banking expert, but I did work at a payment processor that handled real money, and we used “select for update” extensively. We also (obviously) used a ledger, but “just let everyone overdraft whenever” wasn’t something that the executives of the company thought was a good idea. So we did try and prevent insufficient funds errors as much as possible. I personally witnessed several scenarios where the company lost a notable amount of money, (not only rated to concurrency bugs of course) and it’s a pretty bad feeling. Anyway, I’m legitimately curious to know from people who worked at larger banks perhaps. How is weak transaction isolation not a problem for banks?
  - amw-zero 3y ago
    Can you elaborate? Does your bank allow you to overdraft on $50k for example? I think it’s more complicated than “eventual consistency solves all problems.”

by trhway 3y ago

aphyr 3y ago

The point of isolation levels is that the database manages those locks (or other concurrency control mechanisms) for you.

wrs 3y ago
Bearing in mind that “handling” the locks may mean a deadlock is detected and the transaction is aborted by the DB, which the application has to handle by retrying in a correct manner. (Which is another point often missed in naive application code.)
trhway 3y ago
Not exactly - the database doesn't know whether you want to block that account update or not. So, speaking very roughly, it allows you to choose general level of such a blocking behavior. It manages those locks, very coarse, at serializable and some of those locks at repeatable read. At read committed it manages basically only small set of locks just to avoid dirty reads (plus of course all the WAL/rollback/etc. which is the main features and the point of the database with the isolation levels being just the icing on that cake). Isolation levels is a tool, and you choose what is needed and suitable. The serializable actually is a pain in the neck for any meaningfully serious large enterprise application. The decreased concurrency kills overall performance, and I haven't seen any such application running in serializable (and for example at my current job our customers are running our application on servers with low tens of TB RAM (largest so far was 40TB RAM) per single machine with high hundreds of cores with thousands of users - in serializable that would slow to a crawl as it wouldn't allow to achieve the concurrency the database on those machines is otherwise capable of). While not precise illustration it still communicates my point - the serializable against read committed is like the global Python lock against the fine grained locks in normal languages.

jen20 3y ago

If you're storing a balance, rather than a ledger of the transactions which caused that balance to be the case, you're probably already off to a bad start when it comes to design. Just imagine if you called your bank and asked them why my balance was $500, and they said "I don't know, hit F5 and see if it changes"...

gav 3y ago
When it comes to this topic, almost all examples don't match how things work in the real world where they are eventually consistent. For example, my bank lets me go negative and then if I don't settle by the end of the day, overdraft protection will kick in for a little more than that negative amount.
- jen20 3y ago
  Indeed - seeing account balance used as an example of database transactions is a red flag to me, and would absolutely cause me to dig deeper into whether the author/speaker understands how banking actually works.
  - amw-zero 3y ago
    Hello, I wrote the post. I don’t claim to be a banking expert, but I did work at a payment processor that handled real money, and we used “select for update” extensively. We also (obviously) used a ledger, but “just let everyone overdraft whenever” wasn’t something that the executives of the company thought was a good idea. So we did try and prevent insufficient funds errors as much as possible. I personally witnessed several scenarios where the company lost a notable amount of money, (not only rated to concurrency bugs of course) and it’s a pretty bad feeling. Anyway, I’m legitimately curious to know from people who worked at larger banks perhaps. How is weak transaction isolation not a problem for banks?
- amw-zero 3y ago
  Can you elaborate? Does your bank allow you to overdraft on $50k for example? I think it’s more complicated than “eventual consistency solves all problems.”