Sql-server – Can independent applications cause roll back to each other’s transactions

data-tier-applicationdbmsrollbacksql servertransaction

I have a database built on SQL Server

Application-1 [Legacy App]:

Written in C#, and runs transactions on the above database.

Application-2 [New application being developed]:

Written in Python, and runs transactions on the above database.

Considerations:

  • These apps access the same set of tables and perform similar actions.

  • We are trying to transition from older app to the newer one over time.

  • I am finding several deadlocks and rollbacks happening in the system.

Questions

  1. I want to understand if there is a possibility that transaction being run via one application can affect the ones from an independent other application.

  2. Are these scenarios handled by the DBMS itself internally or have to be handled at the application level ?

Best Answer

I want to understand if there is a possibility that transaction being run via one application can affect the ones from an independent other application.

Directly, no. Each transaction is by definition is a "single unit of work" and is local to itself. That is, my transaction cannot become part of your transaction. I can't explicitly tell yours to roll back or commit.

However, if two transactions are working on the same data, they can influence each other. If a deadlock occurs between two transactions, one is a victim (killed) and the other succeeds. In this scenario, my transaction causes the database engine to issue a rollback on your transaction, but nothing in my transaction is directly impacting yours. While deadlocks can never be 100% avoided, there are ways to mitigate the risk and impact of them.

As El.Ham mentioned, separate transactions can also cause blocking, leading to a suspended query. This could lead to application slowness or even timeouts depending on how things are configured for your connections.

Are these scenarios handled by the DBMS itself internally or have to be handled at the application level ?

It depends. If your concern is deadlocks (which I think it is), then you probably want the applications to have retry logic so that a deadlocked transaction will be re-tried automatically. This can be done with T-SQL or at the application level.

Related Question