Database operations and calling API in single transaction

user_1094387 0
Viewed 128

We have 2 systems, one Identity Management System that handles authentication and another is an application (say UserApp) (website) that user access. When a user registers, the user account is created in Identity Management System and UserApp database. The data should be in sync between these 2 systems. So the current code does the following when user registers

  1. the data is inserted into database (using Entity Framework)
  2. Account is created in IAM using an API call

Scenarios:

  1. If the database insert is failed then API is not called
  2. If database insert is successful, API fails then we delete the record. Question is what needs to be done if the delete fails, then the data is not in sync.

What is the best way to handle? The application is developed in C# with SQL Server.

entity-framework c#
1 Answers

You could make use of database transactions. You could create a database connection and open it.. The first line should be BEGIN TRANSACTION. This means any subsequent SQL INSERTS/UPDATES you execute wont be commited until you run the statement COMMIT TRANSACTION.. If you want to roll back the transaction you would call ROLLBACK TRANACTION.

So you could:

Step 01: BEGIN TRANSACTION
Step 02: Perform INSERT Statement.

If the SQL statement succeeds, you know the databse is up and accessible and this step has succeeded. It just that the row has not been commited to the database yet.

Step 03: On success of the INSERT statement, then Call the API
Step 04: If API SUCCEEDS then COMMT TRANSACTION.
Step 05: If API FAILS or there is an exception, then ROLLBACK TRANACTION

That way:

  • If the SQL statement fails in any way (DB down, T-SQL error, etc), you exit early
  • If the API call fails in any way, you exit early
  • You only commit the SQL statement when the INSERT and the API succeeds

If the COMMIT Fails

Now there might be a slim chance the COMMIT fails due to power loss or network outage at that second, etc.. In that case you you would need to call the API to remove/deactivate the user you just created.

user_25343780
Related