Do i need transaction for joined query?

Question

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 dont know if the syntax is 100% correct, but i guess that you understand it anyway)

Is there any risk that not all rows that should be deleted doesnt get deleted?

Thanks!

Answer 1

I dont think using transaction will do anything, A single query in most cases is atomic, and it runs in a single transaction. With respect to MS-SQL server queries like bulk insert with no logs and truncate might need transactions. In your case you don't need a transcation around your delete statement. If there are multiple statements then you need to wrap them in a single transaction so that all or none would be executed Check out this question

Answer 2

Since this is a single command (DELETE) there's no need to explicitly use a transaction. SQL commands are atomic by definition, i.e. it will either delete all the rows that match the criterion or none at all it there is an error.

EDIT: This answer is correct in theory, and for databases that support ACID. If the databases do not support atomicity, or there are bugs that trigger incorrect behaviour on the part of the database engine, all bets are off. However, it's unlikely that using transactions will magically make it better in those scenarios.