3

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 ?

|improve this question
  • 1
    What isolation level is each application using? – Michael Green Nov 2 '18 at 9:03
  • Whatever SQL server does by default. None of the application is explicitly setting that. READ COMMITTED is the default I suppose. – Amit Tomar Nov 2 '18 at 9:42
  • 1
    What version of SQL Server are you using? – LowlyDBA Nov 2 '18 at 15:57
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.

|improve this answer
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?

|improve this answer
  • 2
    While one app can't directly execute a rollback of another's transactions, it could easily cause a deadlock which would result in the other application having to rollback its transaction, a small but important distinction. – LowlyDBA Nov 2 '18 at 15:55
  • @LowlyDBA That is exactly what I wanted to understand. Can you please elaborate a bit. If the transaction from two apps get nested in each other, won't rolling back one, roll back the other as well ? or the transactions do not know about transaction part of other connection ? – Amit Tomar Nov 2 '18 at 17:42
  • Thanks to sql isolation level default settings they cant make each other to roll back. They become suspended and blocked and make a huge load to database and cause a deadlock. This so important to know what kind of transaction you have , so we can help you with changing transaction isolation level. @AmitTomar – El.Hum Nov 2 '18 at 19:02
  • 1
    @El.Ham actually, isolation does not prevent blocks entirely. Even a SELECT statement requires a very small lock to occur, potentially blocking other offline operations. So while isolation will certainly be helpful during SELECT statements, they are never foolproof. Variables to account for include the type, level of lock acquired and what leaf, page, index being utilized during the query. Those are factors you can influence. – clifton_h Nov 3 '18 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.