CODEWITHSUNDEEP
mysqldatabaseinnodb
Alex
Alex

Reputation: 68396

Advantages of using Mysql transactions

I just found out about this feature in innodb, and I'm a little confused.

Besides being able to do multiple queries and rollback if one of them fails, are there any others?

And are they slower than the normal queries?

Upvotes: 3

Views: 4784

Answers (3)

Marnen Laibow-Koser
Marnen Laibow-Koser

Reputation: 6337

Transactions have two major purposes:

  • Ensuring atomicity of a complex operation -- that is, if one step fails, the whole transaction is rolled back, so that the DB isn't left in an inconsistent state.
  • Ensuring consistent state. If you want to make several read queries but don't want an intervening operation to change the data in the DB, then just wrap the reads in a transaction.

Upvotes: 1

Hammerite
Hammerite

Reputation: 22340

Besides being able to do multiple queries and rollback if one of them fails, are there any others?

That is one of them. Probably the main benefit is that you can make sure you never leave the database in an inconsistent state, nor will another user accessing the database at the same time as you see inconsistent data. For example, if you were running a bank and you needed customers to be able to transfer money into one another's accounts, you would not want any snapshot of the data to have any missing money, nor any excess money. So if one customer transfers $100 to a friend, that would generally work like:

  • $100 leaves the first customer's account
  • $100 arrives in the friend's account

Without transactions, if you were looking at the two accounts at just the wrong time you might see both of them without the $100 and conclude your liabilities were $100 less, or something. With transactions, you either see the accounts as before the transaction or the accounts as afterwards - no inconsistency.

And are they slower than the normal queries?

As far as I know, transactions do not impose any significant performance penalty.

Upvotes: 8

Bruno
Bruno

Reputation: 122599

Using transactions can allow you to enforce ACID properties (atomicity, consistency, isolation, durability): http://en.wikipedia.org/wiki/ACID

Upvotes: 4

Related Questions