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?
+11
A:
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.
ΤΖΩΤΖΙΟΥ
2008-09-22 10:35:59
In general? when are they not in a transaction?
HollyStyles
2008-09-22 10:50:24
Whe the database doesn't support transasctions for example. See MySQL with MyISAM tables...
Cd-MaN
2008-09-22 10:54:22
+1
A:
Cascade deletes are indeed atomic, they would be of little use without that property. It is in the documentation.
HollyStyles
2008-09-22 10:45:11
+1 for "they would be of little use without that property" :-) Very true.
ydobonmai
2010-10-28 08:50:44
+1
A:
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
2008-09-22 13:13:06
Actually, it's not Joel Coehoorn that points that out. If unsure of the author, you better use "that answer" linking to its permalink.
ΤΖΩΤΖΙΟΥ
2008-10-03 11:45:33