Error: 0xC001405C at SQL Log Status: A deadlock was detected while trying to lock variables 
"User::RowCount" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.

Have you ever considered variable locking when building your SSIS packages? I expect many people haven’t just because most of the time you never see an error like the one above. I’ll try and explain a few key concepts about variable locking and hopefully you never will see that error.

First of all, what is all this variable locking all about? Put simply SSIS variables have to be locked before they can be accessed, and then of course unlocked once you have finished with them. This is baked into SSIS, presumably to reduce the risk of race conditions, but with that comes some additional overhead in that you need to be careful to avoid lock conflicts in some scenarios. The most obvious place you will come across any hint of locking (no pun intended) is the Script Task or Script Component with their ReadOnlyVariables and ReadWriteVariables properties.

These two properties allow you to enter lists of variables to be used within the task, or to put it another way, these lists of variables to be locked, so that they are available within the task. During the task pre-execute phase the variables and locked, you then use them during the execute phase when you code is run, and then unlocked for you during the post-execute phase. So by entering the variable names in one of the two list, the locking is taken care of for you, and you just read and write to the Dts.Variables collection that is exposed in the task for the purpose.

As you can see in the image above, the variable PackageInt is specified, which means when I write the code inside that task I don’t have to worry about locking at all, as shown below.

public void Main()
{
    // Set the variable value to something new
    Dts.Variables["PackageInt"].Value = 199;

    // Raise an event so we can play in the event handler
    bool fireAgain = true;
    Dts.Events.FireInformation(0, "Script Task Code", 
        "This is the script task raising an event.", null, 0, ref fireAgain);

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

As you can see as well as accessing the variable, hassle free, I also raise an event. Now consider a scenario where I have an event hander as well as shown below.

Now what if my event handler uses tries to use the same variable as well? Well obviously for the point of this post, it fails with the error quoted previously. The reason why is clearly illustrated if you consider the following sequence of events.

1. Package execution starts
2. Script Task in Control Flow starts
3. Script Task in Control Flow locks the PackageInt variable as specified in the ReadWriteVariables property
4. Script Task in Control Flow executes script, and the On Information event is raised
5. The On Information event handler starts
6. Script Task in On Information event handler starts
7. Script Task in On Information event handler attempts to lock the PackageInt variable (for either read or write it doesn’t matter), but will fail because the variable is already locked.

The problem is caused by the event handler task trying to use a variable that is already locked by the task in Control Flow. Events are always raised synchronously, therefore the task in Control Flow that is raising the event will not regain control until the event handler has completed, so we really do have un-resolvable locking conflict, better known as a deadlock.

In this scenario we can easily resolve the problem by managing the variable locking explicitly in code, so no need to specify anything for the ReadOnlyVariables and ReadWriteVariables properties.

public void Main()
{
    // Set the variable value to something new, with explicit lock control
    Variables lockedVariables = null;
    Dts.VariableDispenser.LockOneForWrite("PackageInt", ref lockedVariables);
    lockedVariables["PackageInt"].Value = 199;
    lockedVariables.Unlock();

    // Raise an event so we can play in the event handler
    bool fireAgain = true;
    Dts.Events.FireInformation(0, "Script Task Code", 
        "This is the script task raising an event.", null, 0, ref fireAgain);

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

Now the package will execute successfully because the variable lock has already been released by the time the event is raised, so no conflict occurs.

For those of you with a SQL Engine background this should all sound strangely familiar, and boils down to getting in and out as fast as you can to reduce the risk of lock contention, be that SQL pages or SSIS variables.

Unfortunately we cannot always manage the locking ourselves. The Execute SQL Task is very often used in conjunction with variables, either to pass in parameter values or get results out. Either way the task will manage the locking for you, and will fail when it cannot lock the variables it requires.

The scenario outlined above is clear cut deadlock scenario, both parties are waiting on each other, so it is un-resolvable. The mechanism used within SSIS isn’t actually that clever, and whilst the message says it is a deadlock, it really just means it tried a few times, and then gave up. The last part of the error message is actually the most accurate in terms of the failure, A lock cannot be acquired after 16 attempts. The locks timed out. 

Now this may come across as a recommendation to always manage locking manually in the Script Task or Script Component yourself, but I think that would be an overreaction. It is more of a reminder to be aware that in high concurrency scenarios, especially when sharing variables across multiple objects, locking is important design consideration.

Update – Make sure you don’t try and use explicit locking as well as leaving the variable names in the ReadOnlyVariables and ReadWriteVariables lock lists otherwise you’ll get the deadlock error, you cannot lock a variable twice!