A deadlock was detected while trying to lock variables. A lock cannot be acquired after 16 attempts. The locks timed out

Question

I'm trying send a set of query results in an email using SSIS. Below is the screen shot and steps for the flow.

Screenshot:

Steps: 1. Using SQL task to get the results of the query in "queryresult" object variable 2. Using the "queryresult" object variable in the Foreach loop and getting the column Values in the string variables "ProjectRefID" and "Accountnumber"

3.Using script task inside the foreachloop container to capture the data in the object variable "query result"

Below is the code inside the script task which I copied from the internet.

using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms;

endregion

namespace ST_77e6b4ea18824b909adc5568475fcf5c
 {

   [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase



{

    public void Main()
    {
        Variables varCollection = null;
        string header = string.Empty;
        string message = string.Empty;

        Dts.VariableDispenser.LockForWrite("User::ValidationEmailMessage");
        Dts.VariableDispenser.LockForRead("User::Accountnumber");
        Dts.VariableDispenser.LockForRead("User::ProjectRefID");
        Dts.VariableDispenser.GetVariables(ref varCollection);

        if (varCollection["User::ValidationEmailMessage"].Value == string.Empty)
        {
            header = "Below are the list of Invalid ProjecRefID and Accountnumbers that are not matching with our existing data:\n\n";
            header += string.Format("{0}\t{1}\t\t\t{2}\n", "ProjectRefID", "Accountnumber");
            varCollection["User::ValidationEmailMessage"].Value = header;
            varCollection.Unlock();
        }


        //Format the query result with tab delimiters
        message = string.Format("{0}\t{1}\t{2}",
                                    varCollection["User::ProjectRefID"].Value,
                                    varCollection["User::Accountnumber"].Value);

        varCollection["User::ValidationEmailMessage"].Value = varCollection["User::ValidationEmailMessage"].Value + message;
        varCollection.Unlock();
        Dts.TaskResult = (int)ScriptResults.Success;

    }


    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };


}

}

I tried to resolve this error but somehow I'm not able to figure this out. Let me know if anyone knows how to resolve it.

Chris Mack Chris Mack · Accepted Answer · 2017-10-15T00:18:06

Unless you require the locking functionality for some reason, you should be able to write the Main method simply as this:

public void Main()
{
    string header = string.Empty;
    string message = string.Empty;

    if (Dts.Variables["User::ValidationEmailMessage"].Value == string.Empty)
    {
        header = "Below are the list of Invalid ProjecRefID and Accountnumbers that are not matching with our existing data:\n\n";
        header += string.Format("{0}\t{1}\t\t\t{2}\n", "ProjectRefID", "Accountnumber");
        Dts.Variables["User::ValidationEmailMessage"].Value = header;
    }


    //Format the query result with tab delimiters
    message =
        string.Format("{0}\t{1}\t{2}",
            Dts.Variables["User::ProjectRefID"].Value,
            Dts.Variables["User::Accountnumber"].Value);

    Dts.Variables["User::ValidationEmailMessage"].Value = Dts.Variables["User::ValidationEmailMessage"].Value + message;

    Dts.TaskResult = (int)ScriptResults.Success;
}

Also, with your string.Format code, you are specifying three indexes, {0}, {1} and {2}, yet you are only providing 2 arguments, i.e. , "ProjectRefID", "Accountnumber");.

A deadlock was detected while trying to lock variables. A lock cannot be acquired after 16 attempts. The locks timed out

endregion

1 Answers