3 ms·

> What if Alice and Bob both try to transfer money to each other at the same time? > Let’s map out the transactions again: > T1: Acquire a lock on Alice’s acc

> What if Alice and Bob both try to transfer money to each other at the same time? > Let’s map out the transactions again: > T1: Acquire a lock on Alice’s account > T2: Acquire a lock on Bob’s account...... ::loud sigh:: In MySQL, if you acquire a lock on Alice, then attempt acquire on Bob... then another session acquire Bob, then acquires Alice: The engine notices. Nothing bad happens. One of the threads gets marked as a deadlock and is rolled back, the other succeeds. So... yeah, not really a problem. MySql chooses at "random" which one lives. Actually... hang on, pause, let me remind you what you learned in your undergrad CS 400 class: Acquire Locks in consistent order. You could sort by account id, or even their first names. Hell sort by their cats name. It doesn't matter, it just has to be deterministic, so: Alice comes before Bob. So doing this properly: T1: Acquire a lock on Alice’s account with SELECT FOR UPDATE T2: Acquire a lock on Alice’s account with SELECT FOR UPDATE... ::waits:: Annnnnnd, yeah, magic. Problem solved. I don't find any of this too difficult and I really the only thing SQL engines do poorly is reporting on what locks are being held. Adding in XA Transactions can make things even more intuitive and gives you a nice building block if you need to coordinate sending messages to a broker or something else. Oh and lastly, you should be using a ledger table for this; not storing balances as columns. Lock on both accounts, insert parallel rows into the ledger. And don't forget to use fixed point decimal datatypes. I'll show myself to the door now. Thank you.

JohnyTex 5mo ago
> In MySQL, if you acquire a lock on Alice, then attempt acquire on Bob... then another session acquire Bob, then acquires Alice: The engine notices. Nothing bad happens. One of the threads gets marked as a deadlock and is rolled back, the other succeeds. So... yeah, not really a problem. MySql chooses at "random" which one lives. That my program terminates and must be restarted by an external process is something I would consider "a problem", actually. This is not to cast shade on MySQL, it's just that if you really want deterministic transactions it's hard / impossible with most SQL databases. For most use cases optimistic concurrency is perfectly fine though! > Actually... hang on, pause, let me remind you what you learned in your undergrad CS 400 class: Acquire Locks in consistent order. The problem is that most SQL database engines acquire locks in the order that the rows are read, not in the order that they are sorted. So lock ordering depends on the query plan, not on the query itself. This is definitely true in SQL Server; I'm pretty sure it's the same in Postgres, but I haven't found anything to corroborate that claim yet. However, it's very clear from reading the MySQL / InnoDB documentation that there's basically no way to guarantee consistent lock ordering: https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks.html https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks.htm...
- exabrial 5mo ago
  >That my program terminates and must be restarted by an external process Your program terminates for absolutely _regular_ SQL signals that are literally documented in the API? This is a 'not knowing how the tool works', knowledge comprehension issue. This is like complaining your car wont run because you have to put gas in it. >The problem is that most SQL database engines acquire locks in the order that the rows are read, not in the order that they are sorted. Most in fact are undefined. Read order is a convenient optimization. > So lock ordering depends on the query plan, not on the query itself. Right, so don't do that. My example is 100% deterministic. Go try it right now on your favorite ANSI Sql Database ;) It works every single time.

JohnyTex 5mo ago

> In MySQL, if you acquire a lock on Alice, then attempt acquire on Bob... then another session acquire Bob, then acquires Alice: The engine notices. Nothing bad happens. One of the threads gets marked as a deadlock and is rolled back, the other succeeds. So... yeah, not really a problem. MySql chooses at "random" which one lives. That my program terminates and must be restarted by an external process is something I would consider "a problem", actually. This is not to cast shade on MySQL, it's just that if you really want deterministic transactions it's hard / impossible with most SQL databases. For most use cases optimistic concurrency is perfectly fine though! > Actually... hang on, pause, let me remind you what you learned in your undergrad CS 400 class: Acquire Locks in consistent order. The problem is that most SQL database engines acquire locks in the order that the rows are read, not in the order that they are sorted. So lock ordering depends on the query plan, not on the query itself. This is definitely true in SQL Server; I'm pretty sure it's the same in Postgres, but I haven't found anything to corroborate that claim yet. However, it's very clear from reading the MySQL / InnoDB documentation that there's basically no way to guarantee consistent lock ordering: https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks.html https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks.htm...

exabrial 5mo ago
>That my program terminates and must be restarted by an external process Your program terminates for absolutely _regular_ SQL signals that are literally documented in the API? This is a 'not knowing how the tool works', knowledge comprehension issue. This is like complaining your car wont run because you have to put gas in it. >The problem is that most SQL database engines acquire locks in the order that the rows are read, not in the order that they are sorted. Most in fact are undefined. Read order is a convenient optimization. > So lock ordering depends on the query plan, not on the query itself. Right, so don't do that. My example is 100% deterministic. Go try it right now on your favorite ANSI Sql Database ;) It works every single time.