Menu
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?
953
It does nothing. All individual SQL Statements, (with rare exceptions like Bulk Inserts with No Log, or Truncate Table) are automaticaly "In a Transaction" whether you explicitly say so or not.. (even if they insert, update, or delete millions of rows).
You use transactions when you have a group of actions that must be atomic (either all succeed or none succeed) Wrapping these actions in a transaction allows you to rollback actions that have already succeeded when you encounter an error.
A SQL statement always runs in a transaction. If you don't start one explicitly, every SQL statement will run in a transaction of itself. The only choice is whether you bundle multiple statements in one transaction.
The primary benefit of using transactions is data integrity. Many database uses require storing data to multiple tables, or multiple rows to the same table in order to maintain a consistent data set. Using transactions ensures that other connections to the same database see either all the updates or none of them.
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.
167
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
