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!

score 6 · Answer 1 · edited May 23 '17 at 12:04

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

SWeko · Accepted Answer · 2012-03-30T08:30:03.390

5

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.

edited Mar 30 '12 at 08:30

answered Mar 30 '12 at 07:53

SWeko

30,434
10
71
106

2

This is only true for statements affecting InnoDB tables. A delete against e.g. a MyISAM table is **not** atomic and might only delete half of the tables. – Mar 30 '12 at 07:59
@a_horse_with_no_name - agree, this is correct only for ACID tables. However, I have no experience with MyISAM tables, so I'm not sure if transactions would help in that scenario. – SWeko Mar 30 '12 at 08:04
@SWeko In some cases, it isn't rolled back. In MySQL v5.1 anyway (see: http://bugs.mysql.com/bug.php?id=45309) – Mathew Thompson Mar 30 '12 at 08:06

score 5 · Answer 3 · answered Mar 30 '12 at 07:53

No, you don't need a transaction. A transaction makes multiple statements run as a unit; running a single statement in or outside a transaction will always produce identical results.

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

score 2 · Answer 4 · answered Mar 30 '12 at 07:53

2

For any INSERT, UPDATE, SELECT, DELETE statement, there is no explicit transaction commands, and the database engine will rollback the whole statement if an error happens.

answered Mar 30 '12 at 07:53

Mahmoud Gamal

78,257
17
139
164

Do i need transaction for joined query?

4 Answers4

Linked