Share via

SQL connection and transaction Idle Time

Santhi Dhanuskodi 205 Reputation points
2024-06-04T16:49:15.8066667+00:00

Hi,

I am having a asp.net core application, which opens a sql connection and running commands in a sql transaction.

The transaction might take overall 15 minutes to complete, The connection and transaction might be idle as we sleep the current thread for a specific interval(11 minutes). We will rollback or commit based on certain variable values after 15 minutes.

begin transaction

--do something (about 4 minutes)

---sleep 11 minutes

commit or rollback

end

Is there a chance of closing the sql connection or transaction by sql server itself?

What happens if the sql connection closed before commit or rollback. Will it cause any issues in application and sql server?

.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,623 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,382 questions
ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,410 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,317 questions
0 comments
Accepted answer
  1. Jiale Xue - MSFT 42,731 Reputation points Microsoft Vendor
    2024-06-05T09:33:13.0966667+00:00

    Hi @Santhi Dhanuskodi , Welcome to Microsoft Q&A,

    What happens if the sql connection closed when application is trying to commit or rollback. Will it cause any issues in application and sql server?

    In the application:

    Exception thrown:

    The application catches an exception, usually a SqlException. This will indicate why an attempt to perform an operation failed when the connection was closed.

    The specific exception message may vary, but it will usually indicate that the connection was closed or lost.

    In SQL Server:

    Transaction rollback:

    If a transaction has not been committed or rolled back when the connection is closed, SQL Server automatically rolls back the transaction. This ensures that there are no partially committed transactions that cause inconsistent data.

    SQL Server has mechanisms to ensure that if the connection is unexpectedly closed, all incomplete transactions are rolled back to maintain database consistency and integrity.

    Best Regards,

    Jiale

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 43,161 Reputation points
    2024-06-04T17:48:13.6966667+00:00

    Is there a chance of closing the sql connection or transaction by sql server itself?

    No, not really, only if the transaction runs into deadlock situation, SQL Server could choose your process as victim and cancel the statement with an deadlock error.

    0 comments
  2. Erland Sommarskog 106.5K Reputation points
    2024-06-04T21:01:55.6+00:00

    The only reason SQL Server would cut the connection is that the execution runs into a fatal error with a severity >= 20. That would be a bug in SQL Server.

    There are also execution errors which may abort execution, that would not close the connection, but they would roll back the transaction. Deadlock, as mentioned by Olaf, is such an error.

    Now, it sounds like you running something asynchronously here. I have not used the async methods in ADO .Net, so I don't know exactly how they work in the case the execution completes, and you come back minutes later to check the outcome. You may want to test that.