3 ms·

But consider this: You are using mysql, you make a transaction with say deposit and withdraw. What happens on the mysql machine if you pull the plug exactly w

by bullen 10y ago

But consider this: You are using mysql, you make a transaction with say deposit and withdraw. What happens on the mysql machine if you pull the plug exactly when mysql has done the deposit but not the withdraw? The ONLY difference between SQL transactions and NoSQL microservice transactions is the time between the parts of a transaction. Personally I use a JSON file with state to execute my NoSQL microservice transactions and it's alot more scalable than having a pre-internet era SQL legacy design hogging all my time and resources.

lomnakkus 10y ago
> What happens on the mysql machine if you pull the plug exactly when mysql has done the deposit but not the withdraw? Nothing bad -- the deposit will never be seen. It sounds like you need to read up on what ACID means. (EDIT: Clarification)
XorNot 10y ago
This is not how database transactions work. A transaction means that the amended state is not visible to any readers until it has been fully committed. Pulling the plug means the transaction is lost, and the writer knows that it was not committed (and so can try again).
hmottestad 10y ago
>> What happens on the mysql machine if you pull the plug exactly when mysql has done the deposit but not the withdraw? Nothing happens. And the client discovers that the connection to the db was closed before the 2 phase commit was done and gives an error to the user.
matthewmacleod 10y ago
I don't mean to be unkind, but is this meant to be a parody? There are no "parts of a transaction" because a transaction is definitionally atomic. Transactional file modification is a fairly tricky problem, and I'd be surprised if you'd actually implemented a safe system in that manner. What's certain though is that spinning up MySQL or Postgres and using it to store simple records is essentially a zero-cost setup task - so I doubt it's ever going to "hog all your time and resources".
- bullen 10y ago
  How is it atomic? this goes for all 5 of the replies (one of you did not downvote discussion, I'm guessing that person is the only one not from the US). How does mysql unwrite the deposit? There are always "parts" to everything. Two phase commit does not solve anything unless you have "undo the thing I did before the power was lost". Please refer to source code to prove your argumentation.
  - smallnamespace 10y ago
    > How does mysql unwrite the deposit? Yes, the database either reverses the changes. https://en.wikipedia.org/wiki/Transaction_log https://en.wikipedia.org/wiki/Transaction_log If, after a start, the database is found in an inconsistent state or not been shut down properly, the database management system reviews the database logs for uncommitted transactions and rolls back the changes made by these transactions. Additionally, all transactions that are already committed but whose changes were not yet materialized in the database are re-applied. Both are done to ensure atomicity and durability of transactions.
  - matthewmacleod 10y ago
    Please refer to source code to prove your argumentation. I'm not digging through source code for you; transactional atomicity is a well-known and thoroughly researched problem. Transactions in an ACID system are atomic by definition. They're designed in such a way that either an entire transaction occurs, or no part of a transaction occurs – in other words, it's not possible to partially apply a transaction, by design. There are a bunch of different approaches to implementing this. I'd guess that MySQL does something like write the complete set of modifications in a transaction to disk as a separate buffer, and only once the entire transaction is complete updating some associated metadata to add the transaction to the database. A power failure at any point will result in an uncommitted transaction, which will have no effect on the database. Here's the appropriate Wikipedia article for further reading -https://en.wikipedia.org/wiki/Atomicity_(database_systems) https://en.wikipedia.org/wiki/Atomicity_(database_systems) - lets suffice to say that if you are rejecting the idea that atomicity exists then I don't know what else to tell you. It's a core concept in information systems.
hibbelig 10y ago
_You are using mysql, you make a transaction with say deposit and withdraw._ _What happens on the mysql machine if you pull the plug exactly when mysql has done the deposit but not the withdraw?_ MySQL guarantees that this cannot happen. That's what transactions are all about. For example you can put into the log the two operations and then an end of transaction marker. When the system comes back after a crash you can replay those transactions from the log that have the end marker, and roll back those that don't.

by bullen 10y ago

lomnakkus 10y ago

> What happens on the mysql machine if you pull the plug exactly when mysql has done the deposit but not the withdraw? Nothing bad -- the deposit will never be seen. It sounds like you need to read up on what ACID means. (EDIT: Clarification)

XorNot 10y ago

This is not how database transactions work. A transaction means that the amended state is not visible to any readers until it has been fully committed. Pulling the plug means the transaction is lost, and the writer knows that it was not committed (and so can try again).

hmottestad 10y ago

>> What happens on the mysql machine if you pull the plug exactly when mysql has done the deposit but not the withdraw? Nothing happens. And the client discovers that the connection to the db was closed before the 2 phase commit was done and gives an error to the user.

matthewmacleod 10y ago

I don't mean to be unkind, but is this meant to be a parody? There are no "parts of a transaction" because a transaction is definitionally atomic. Transactional file modification is a fairly tricky problem, and I'd be surprised if you'd actually implemented a safe system in that manner. What's certain though is that spinning up MySQL or Postgres and using it to store simple records is essentially a zero-cost setup task - so I doubt it's ever going to "hog all your time and resources".

bullen 10y ago
How is it atomic? this goes for all 5 of the replies (one of you did not downvote discussion, I'm guessing that person is the only one not from the US). How does mysql unwrite the deposit? There are always "parts" to everything. Two phase commit does not solve anything unless you have "undo the thing I did before the power was lost". Please refer to source code to prove your argumentation.
- smallnamespace 10y ago
  > How does mysql unwrite the deposit? Yes, the database either reverses the changes. https://en.wikipedia.org/wiki/Transaction_log https://en.wikipedia.org/wiki/Transaction_log If, after a start, the database is found in an inconsistent state or not been shut down properly, the database management system reviews the database logs for uncommitted transactions and rolls back the changes made by these transactions. Additionally, all transactions that are already committed but whose changes were not yet materialized in the database are re-applied. Both are done to ensure atomicity and durability of transactions.
- matthewmacleod 10y ago
  Please refer to source code to prove your argumentation. I'm not digging through source code for you; transactional atomicity is a well-known and thoroughly researched problem. Transactions in an ACID system are atomic by definition. They're designed in such a way that either an entire transaction occurs, or no part of a transaction occurs – in other words, it's not possible to partially apply a transaction, by design. There are a bunch of different approaches to implementing this. I'd guess that MySQL does something like write the complete set of modifications in a transaction to disk as a separate buffer, and only once the entire transaction is complete updating some associated metadata to add the transaction to the database. A power failure at any point will result in an uncommitted transaction, which will have no effect on the database. Here's the appropriate Wikipedia article for further reading -https://en.wikipedia.org/wiki/Atomicity_(database_systems) https://en.wikipedia.org/wiki/Atomicity_(database_systems) - lets suffice to say that if you are rejecting the idea that atomicity exists then I don't know what else to tell you. It's a core concept in information systems.

hibbelig 10y ago

_You are using mysql, you make a transaction with say deposit and withdraw._ _What happens on the mysql machine if you pull the plug exactly when mysql has done the deposit but not the withdraw?_ MySQL guarantees that this cannot happen. That's what transactions are all about. For example you can put into the log the two operations and then an end of transaction marker. When the system comes back after a crash you can replay those transactions from the log that have the end marker, and roll back those that don't.