Deadlock detected while trying to lock variables. The locks cannot be acquired after 16 attempts. The locks timed out. We couldn't acquire all the locks after 16 attempts; they all timed out

Community Notes

This error happens when two processes are trying to get a lock on the same variable. It is not a deadlock in the same way as a SQL Server, to use that terminology it is just a blocking scenario. When blocked, the process will have 16 attempts and then fail with this error. Certain problems exacerbate this issue, the obvious being time the locks are held, and write locks over read locks. Several issues have been addressed with the post-SP1 update, KB918222 Cumulative hotfix package (build 2153) for SQL Server 2005. System variables are now only read locked, which makes sense as they are always read-only anyway. If you encounter this problem, try SP1 + KB918222 to start with, then try and look at your package design and minimise locks taken, and minimise parallel processes that use the same variables. Using local scoped variables can often help as well.

When seen through a managed component it will be wrapped in a COM Exception, usually with error 0xC0047062, for example-

 Error: 0xC0047062 at Data Flow Task, Component Transformation [1]: System.Runtime.InteropServices.COMException (0xC001404D): Exception from HRESULT: 0xC001404D
   at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariableDispenser90.LockOneForWrite(String Variable, IDTSVariables90& ppVariables)
   at Namespace.Component.Method()
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPostExecute(IDTSManagedComponentWrapper90 wrapper)

Checklist-

• Apply SP1 and post-SP1 updates.
• Minimise time locks are held for, including use of manual lock control in scripts.
• Minimise parallel running tasks, run in series if possible.
• Use lower scoped variable. Do not share variables unless value sharing is required, instead scope to container that requires them, and repeat for similar containers.

A simple example of controlling variable locking within code-

 Dim variablesList As Variables
 Dts.VariableDispenser.LockForWrite("User::Var1")
 Dts.VariableDispenser.LockForWrite("User::Var2")
 Dts.VariableDispenser.GetVariables(variablesList)


 variablesList("User::Var1").Value = 0
 variablesList("User::Var2").Value = 0


 variablesList.Unlock()

Add your experiences and workarounds here. If you have solved this issue, why not help the next person?

Work Arounds:

I had this problem in a Data Flow task. The variables I was assigning values to were scoped to the Data Flow task. I moved the scope of the variables to the package, this seemed to resolve the issue.

RuntimeErrorCodes

I found that this error occurred when I had added the variable name to the list of ReadWriteVariables on the Script Component properties. By removing it from that list I was able to access it to write to within the script component code without error. See here for more details on how I resolved: http://blog.cybner.com.au/2008/03/ssis-script-component-error-with.html