Orphaned transactions and distributed deadlocks

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Orphaned transactions and distributed deadlocks happen when there is a session established to the database without any current running requests, but there was a request (one query or more) that holds locking on database object(s).

 

Orphaned transactions can cause a lot of locking and blocking on the database, and usually it is related to the application and how it is code is  written “badly” or in a way that meets the atomicity of the transactions: "commit all or rollback all".

 

I will give an example here, trying by it to simplify the idea:

I created a very simple and small table with two rows :

 

 

CREATE TABLE [dbo].[testtable]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](100) NULL, PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] set identity_insert testtable on insert into testtable (id,name) values (1, 'row #1'),(2,'row #2')

 

 

 

Also, I created a  simple C# desktop application, contains two forms: one to search for IDs and the other one is for deleting IDs:

Picture1.jpg

 

 

Picture2.jpg

 

Picture4.jpg

 

The C# code of the delete button:

 

private void btnDelete_Click(object sender, EventArgs e) { System.Data.SqlClient.SqlDataAdapter myadapter = new System.Data.SqlClient.SqlDataAdapter(); SqlConnection myconn = new SqlConnection("Data Source=xx.windows.net;Initial Catalog=testdb;Persist Security Info=True;User ID=xx;Password=xx"); myadapter.DeleteCommand = new System.Data.SqlClient.SqlCommand("DELETE FROM[dbo].[testtable] WHERE[id] = '" + txtid.Text + "'", myconn); myadapter.DeleteCommand.Connection.Open();//open connection with database myadapter.DeleteCommand.Transaction=myconn.BeginTransaction(); //begin transaction myadapter.DeleteCommand.ExecuteNonQuery();//the request-the query executed on the database with milliseconds DialogResult delbox; delbox = MessageBox.Show("are you sure you want to delete?", "delete ID", MessageBoxButtons.OKCancel); //dialog box with Ok Cancel option , the user can wait before deciding what to choose if (delbox == DialogResult.OK) { myadapter.DeleteCommand.Transaction.Commit();//commit the transaction , locking will be released } else { myadapter.DeleteCommand.Transaction.Rollback();//rollback , locking will be released } myadapter.DeleteCommand.Connection.Close(); }

 

 

 

The C# code of the search button:

 

private void btnsearch_Click(object sender, EventArgs e) { System.Data.SqlClient.SqlDataAdapter myadapter = new System.Data.SqlClient.SqlDataAdapter(); SqlConnection myconn = new SqlConnection("Data Source=xx.database.windows.net;Initial Catalog=testdb;Persist Security Info=True;User ID=xx;Password=xxx"); myadapter.SelectCommand = new System.Data.SqlClient.SqlCommand("select name FROM[dbo].[testtable] WHERE[id] = '" + txtid.Text + "'", myconn); System.Data.DataSet mydataset = new DataSet(); myadapter.SelectCommand.Connection.Open();//open connection myadapter.SelectCommand.ExecuteScalar();//the request will be executed on the database , it should take no time for two rows table. myadapter.Fill(mydataset); txtName.Text = mydataset.Tables[0].Rows[0][0].ToString(); myadapter.SelectCommand.Connection.Close();//close connection } }

 

 

 

Now when I run two instances of the application:

 

Here I want to delete row of ID 1, I wrote the application code with a message box as below snapshot:

Picture5.jpg

 

while the message box was still there:

 

I executed dbcc opentran() ,  and the oldest active transaction was:

Transaction information for database 'testdb'.

Oldest active transaction:

    SPID (server process ID): 104

    UID (user ID) : -1

    Name          : user_transaction

    LSN           : (1108:112408:2)

    Start time    : Sep 15 2020  6:44:15:733PM

    SID           : 0x28bb43e4bdc050459a623ea82e054fa2

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

The oldest active transaction on the database (of SPID 104)  did not appear on the sys.dm_exec_requests DMV result:

Picture6.jpg

 

 

Meanwhile, with the other instance of the application, I searched for the same row of ID 1:

Picture7.jpg

 

While I was trying to search for the ID 1, the application thrown a timeout exception as below snapshot:

Picture8.jpg

 

The timeout error caused by a blocking by the orphaned SPID 104:

Picture9.jpg

 

the issue persisted, until the user chooses between okay or cancel, in other words between rolling back or commit the delete transaction. 

 

and the result of the dbcc opentran() became different, there was no open transactions:

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

This is a simple example, the issue may become a complete chain of blocking with a major impact in some other scenarios.

 

 

 

How can you solve this issue?

 

One of the ways is to kill the SPID of the transaction during the issue occurrence:

Picture10.jpg

By running the command :

Kill 82

 

But here , be aware that you are still not able to know what is the transaction itself, what it was doing and what is the impact of the rolling back.

 

When I killed the process , the deletion failed with error:

Picture11.jpg

 

The error is : “An existing connection was forcibly closed by the remote host”:

Applications usually use a friendly custom error messages, error page redirection and try catch blocks. But in all cases killing of the process at least will waste the user effort and time spent in filling or updating the data for example.

 

And killing the SPID sometimes is not the proper solution if the occurrence of the issue is happening frequently.

 

Please note that in my example here, the delete query will not fail with a query timeout; because it is already executed in milliseconds , and finding it on query store is hard because it may not appear on "consuming resources" queries or on "queries of the high wait time".

 

after identifying the issue and where the blocking transactions come from, using read uncommited isolation level or query hints "like with(nolock)" may decrease the impact of it.

but one of the solutions is in changing the application C# code, as below:

 

 

private void btnDelete_Click(object sender, EventArgs e) { System.Data.SqlClient.SqlDataAdapter myadapter = new System.Data.SqlClient.SqlDataAdapter(); SqlConnection myconn = new SqlConnection("Data Source=xxxxx.database.windows.net,3342;Initial Catalog=testdb;Persist Security Info=True;User ID=myuser;Password=xxxx;"); myadapter.DeleteCommand = new System.Data.SqlClient.SqlCommand("DELETE FROM[dbo].[testtable] WHERE[id] = '" + txtid.Text + "'", myconn); DialogResult delbox; delbox = MessageBox.Show("are you sure you want to delete?", "delete ID", MessageBoxButtons.OKCancel); if (delbox == DialogResult.OK) { myadapter.DeleteCommand.Connection.Open(); //myadapter.DeleteCommand.Transaction = myconn.BeginTransaction(); //= new SqlTransaction(); myadapter.DeleteCommand.ExecuteNonQuery(); //myadapter.DeleteCommand.Transaction.Commit(); myadapter.DeleteCommand.Connection.Close(); } }

 

 

here open and close connection will start and end inside the IF block , only when the user selects "Okay" button of the Dialog Box.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.