Search code examples
sqlsql-serversqltransaction

Transaction rollback VS Delete Records

Looking for some insights on using Transaction or Delete queries when the subsequent request fails. In brief, in my application, I'm inserting into two tables by calling two stored procedures and the inserted data would be uploaded into two REST APIs. If anyone of the REST API is failed I have to rollback the details entered into database.

So which approach is suitable? Either to use SQL transaction or Delete the inserted records through database Procedure.

Solution

  • This is and ideal situation to use transaction. How do you know it?
    Let's say you insert some rows, then do API call, then try to delete inserted rows. What will happen in that case?

    1. Inserted rows are readable already (even without dirty read enabled) - they are just normal rows in database. So all the queries made until you finish you request, will relate to this rows as well.
    2. What will happen if you fail to delete the rows? Exactly, they will just stay in database. Here you have improper data. Bad.

    Use transaction approach - start transaction and commit it only when you finished API call, this way you will ensure, that your database contains proper data at all times.