I want to perform cascade delete for some tables in my database, but I'm interested in what happens in case there's a failure when deleting something. Will everything rollback?
Asked
Active
Viewed 4,693 times
3 Answers
15
In general¹, yes, cascade deletes are done in the same transaction (or subtransaction) as your original delete. You should read the documentation of your SQL server, though.
¹ The exception is if you're using a database that doesn't support transactions, like MySQL with MyISAM tables.
tzot
- 92,761
- 29
- 141
- 204
-
In general? when are they not in a transaction? – hollystyles Sep 22 '08 at 10:50
-
1Whe the database doesn't support transasctions for example. See MySQL with MyISAM tables... – Grey Panther Sep 22 '08 at 10:54
-
@Cd-MaN MyISAM doesn't support foreign keys either, so you'd have to look pretty hard for a case where this exception comes into play. – Brilliand Dec 02 '14 at 21:11
5
Cascade deletes are indeed atomic, they would be of little use without that property. It is in the documentation.
hollystyles
- 4,979
- 2
- 36
- 38
-
2+1 for "they would be of little use without that property" :-) Very true. – Ashish Gupta Oct 28 '10 at 08:50
1
It's worth pointing out that any cascading event should be atomic (i.e. with in a transaction). But, as Joel Coehoorn points out, check the documentation for your database.
AJ.
- 13,461
- 19
- 51
- 63
-
Actually, it's not Joel Coehoorn that points that out. If unsure of the author, you better use "that answer" linking to its permalink. – tzot Oct 03 '08 at 11:45