15

I have a mysql query on an InnoDB table like this:

UPDATE items SET qty = qty + 5 WHERE item_id = 1234 LIMIT 1;

Do I need to use a transaction for this? Could anything undesirable happen by not using a transaction?

Tony H
  • 165
  • 1
  • 4
  • No you don't need a transaction for this. http://stackoverflow.com/questions/1171749/what-does-a-transaction-around-a-single-statement-do?answertab=votes#tab-top – Morten Strand-Langbakk Jul 25 '13 at 11:20

1 Answers1

23

Nothing serious can happen. By default, MySQL wraps all single update/insert/delete commands in a transaction. If something goes wrong in the update, then the transaction should be rolled back correctly.

You really only need transactions when you are combining multiple changes and want them all to take effect "at the same time" or "not at all".

You can read more about this in the documentation.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    @Gordon, What's the isolation level for this default transaction? If we need to `update t set c=c+1` will we have a lost update if 10k users run that statement concurrently? – Pacerier Dec 17 '14 at 07:35
  • @Gordon The documentation you provided a link to doesn't mention that MySQL wraps all single commands in a transaction, so where did you get that information from? – Scadge Oct 19 '17 at 10:56
  • @Scadge . . . It quite clearly states: "By default, MySQL runs with autocommit mode enabled. ". – Gordon Linoff Oct 19 '17 at 11:58
  • 1
    @GordonLinoff So it means if you disable autocommit and execute single update with 10 fields, the data may become corrupted if anything goes wrong in the middle? Doesn't sound OK, assuming that turning off autocommit is not a rare use-case. – Scadge Oct 19 '17 at 12:23