4

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 ?

MDCCL
  • 8,530
  • 3
  • 32
  • 63
Amit Tomar
  • 183
  • 1
  • 5

2 Answers2

6

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.

LowlyDBA - John M
  • 11,059
  • 11
  • 45
  • 63
3

If your code does not affect Isolation Level of your queries, non of your apps can make queries to Rollback. They can only Suspend each other.

Use a SQL Server Profiler to track your app and see what makes your queries Rollback.

To be added, what queries they run when the Rollback happens? I mean a SELECT, INSERT, DELETE or UPDATE?

El.Hum
  • 182
  • 8