21

I just came across Transactions and I wonder

  1. what are the pros and cons of using it,
  2. how does the rollback work, are the old values kept in memory, if bigger, what happens?

For 1. I understand you have to use it for bank transfers, but would it not be better to use it for everything?!?

Markus Malkusch
  • 7,738
  • 2
  • 38
  • 67
loveNoHate
  • 1,549
  • 13
  • 21
  • http://dev.mysql.com/doc/refman/5.0/en/commit.html – Rottingham Jan 11 '14 at 01:18
  • @Rottingham, yeah been there, but that does not explain the questions. ;) And nothing else on google did, if you find me a site with these points explained, I will gladly delete this question. – loveNoHate Jan 11 '14 at 01:23
  • Notice where these originate. http://stackoverflow.com/questions/2159045/when-should-i-use-transactions-in-my-queries http://stackoverflow.com/questions/17649926/how-does-mysqlicommit-mysqlirollback-work – Rottingham Jan 11 '14 at 01:28
  • Not trying to be a jerk, but it seems like people just keep asking the same question that has been answered a billion times over, and I'm pretty sure my google works the same way yours does. – Rottingham Jan 11 '14 at 01:29
  • Sorry @Rottingham, must be that I used the search terms I described in the question then. ;) – loveNoHate Jan 11 '14 at 01:44

1 Answers1

45

The benefit of a transaction is to perform complex changes, which may require multiple updates to different tables, and be assured that they will all succeed or else all be rolled back.

The term for this is atomic, i.e. the change can't be subdivided any smaller.

In fact, MySQL's default storage engine InnoDB uses transactions for everything whether you request it or not. But most people use a mode called autocommit, where each statement implicitly starts a transaction and implicitly commits as soon as the statement finishes. In autocommit mode, you have no opportunity to choose to roll back. Either the statement succeeds, or else it if it encounters an error it automatically rolls back.

If you start an explicit transaction, perform some updates, and then roll back, InnoDB restores the original state of data. It preserves the original data by storing it in an area of the database called the rollback segment. So if you roll back, it just re-copies those pages of data to replace the ones you changed.

This might take some time, so if you try to query data that was changed but rolled back, InnoDB automatically takes a detour to read the original data out of the rollback segment, until such time as it is re-merged into the tables.

Say for example you start a transaction, and UPDATE a billion rows. This copies many pages worth of the original rows to the rollback segment, and then fills the tables with changed data -- but the changed data is uncommitted. No one should be able to read uncommitted data, so anyone who queries the table will automatically get the original data from the rollback segment.

Then you rollback your transaction. Gradually over the next few minutes, InnoDB cleans up, and eventually it all comes back into sync. But anyone can continue to query the original data in the meantime.

If you had committed your transaction, then MySQL would just mark all the changed data as committed, and anyone subsequently reading the data wouldn't experience the slight overhead of reading from the rollback segment.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 2
    Cool, rollback segment. Is that RAM or a space on disk/in a table? – loveNoHate Jan 11 '14 at 01:47
  • 2
    The rollback segment is inside the tablespace on disk. – Bill Karwin Jan 11 '14 at 06:59
  • 2
    So what if I change these billion rows and the content exceeds the rollback segment's size, does the transaction stop? – loveNoHate Jan 11 '14 at 09:35
  • 7
    Yes, it's rare, but this has affected some MySQL users. For that reason, in MySQL 5.5, the rollback segment was increased to 128 segments. In MySQL 5.6, they realized that was overkill for most users, so they set the default back to 1 segment, but you can optionally allocate up to 128 segments. Each "segment" varies in size and grows as needed, but cannot hold more than 1023 transactions' worth of changes. – Bill Karwin Jan 11 '14 at 17:06
  • Read more details on filespace management here: http://dev.mysql.com/doc/refman/5.6/en/innodb-file-space.html – Bill Karwin Jan 11 '14 at 17:07
  • 2
    Great answer. Now if those billion records have an `updated_at` field, will they all get the same timestamp at the end of the transaction? – molerat May 11 '18 at 07:50
  • 3
    CURRENT_TIMESTAMP is a synonym for NOW(). The manual says: "NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes." https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_now – Bill Karwin May 11 '18 at 15:32