Do I need a transaction for a merged request?

During sql queries with joins, do I need to use transactions?

I was thinking about something like:

Delete table1, table2, table3 
FROM table1 INNER JOIN table2 ON(...) 
LEFT JOIN table3 ON (...) 
WHERE table1.column = something...


(I don't know if the syntax is 100% correct, but I think you get it anyway)

Is there a risk that not all lines that need to be deleted are not deleted?



source to share

4 answers

Since this is the only command ( DELETE

), there is no need to explicitly use a transaction. SQL commands are by definition atomic, meaning they will either delete all rows that match the criterion, or there are no errors at all.

EDIT: This answer is correct in theory and for databases that support ACID. If databases do not support atomicity, or there are bugs that cause misbehavior on the part of the database engine, all bets are disabled. However, it is unlikely that using transactions will magically improve them in these scenarios.



I don't think using a transaction will do anything. A single request is in most cases atomic, and it works in a single transaction. For MS-SQL Server queries, such as bulk insert without logs and truncations, transactions may be required. In your case, you don't need to transass around your delete operator. If there are multiple statements, you need to wrap them in one transaction so that everything or nothing gets executed Check this question



No, you don't need a transaction. The deal makes multiple operators run as a whole; running one statement inside or outside a transaction will always give the same results.

And to answer your last question, an all or nothing statement. The only possible exception I can think of would be if there was a power outage in the middle of the statement ... and the deal didn't help.



For all operators INSERT




there is no explicit transaction commands, and the database engine rolls back all of the operator when an error occurs.



All Articles