Monday, August 27, 2007

SSIS - deadlock was detected while trying to lock variables

Recently I faced one MAJOR issue in my ETL packages because of new patches deployed on Microsoft Windows 2003 Server.

"A deadlock was detected while trying to lock variables "variable names (comma separated)" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out."

OR

"The script threw an exception: A deadlock was detected while trying to lock variables "variable names (comma separated)" for read access and variables "variable names (comma separated)" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out."


It's more to do with Script component/Task used in the package and making use of "ReadOnlyVariables" and "ReadWriteVariables" properties for declaring variables that will be used in the script.

Technically if we declare variables in these two properties then there is NO need to lock these variables in the script, and this is exactly what we were doing in the past prior to these patches....

Windows 2003 Post-SP2 Hotfix - MS07-31/935840 W2K3 Server
Windows 2003 Post-SP2 Hotfix - MS07-34/929123 W2K3 Server
Windows 2003 Post-SP2 Hotfix - MS07-039/926122 W2K3 Server
Windows 2003 Post-SP2 Hotfix - KB924054 W2K3 Server
2.0 IE Update W2K3 Server

Now I don't know which one of these patches is causing the real problem but yes it's one of them for sure.

We can see these error in the SSIS logging files...

Now the quick fix to these errors are NOT using "ReadOnlyVariables" and "ReadWriteVariables" properties for declaring variables that will be used in the script. Instead a better way is to declare the same in script itself and then locking and unlocking the same using DTS object model.


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim myVar As Variables

Dts.VariableDispenser.LockForWrite("User::Var1")
Dts.VariableDispenser.LockForWrite("User::Var2")

Dts.VariableDispenser.GetVariables(myVar)

myVar("User::Var1").Value = "SomeValue"
myVar("User::Var1").Value = "SomeValue"

myVar.Unlock()

Dts.TaskResult = Dts.Results.Success

End Sub

End Class



Let me know if you know the exact source of this problem.

- Mohit Nayyar

10 comments:

Anonymous said...

If you put something in the ReadWriteVariables field, that variable is implicitly locked. So, by the time you call LockForWrite, it's already locked. Hence the deadlock.

So, either declare it in the ReadWriteVariables field OR call LockForWrite but not both.

Mohit Nayyar said...

Thanks

I fixed that long back....but the point here is that the code was working fine before the hotfixes.

ranjay said...

Hi Mohit, I am facing a similar kind of issue in SSIS. My SSIS script make a call to webservice.
e.g i am calling two package from a master package. If pakage are in series they work fine. as soon as i put em in parallel then it failed while calling the web service from script. if i put em in out of process it works fine. I want them in process . Do you have any idea on that.

Mohit Nayyar said...

Hi Ranjay, If your child packages are making use of Master package's variables then you should execute them serially. If this is not the case then there could be some script cache issue and the way you lock and release the variables.

ranjay said...

Mohit,i am not using any master package variable in child package.
I calling webservice in evry child script. So delaration of varibale of webservice is local to each script.

Mohit Nayyar said...

Hmm...ok, check one thing, if the GUID is same for both of the script task in each package (copy-paste by any chance). If that's the case then there could be script caching issue while running in-proc. So just re-create your script task from scratch and confirm that GUIDs are different for script tasks.

Anonymous said...

Thanks very helpful for a beginner SSIS user!

Mohit Nayyar said...
This comment has been removed by the author.
KOMMA RAVI said...

I found this looking for the answer to the same problem. My major problem is that the locking error happened in my Package.OnProgress event, and I had a script (actually several scripts) in the package that did this


Something = Dts.Variables("myVar").Value.ToString() + Whatever
Dts.Events.FireProgress.....

Adding a Dts.Variables.Unlock() statement right before the FireProgress statement helped quite a bit

Ben Raubenolt said...

I know this was a long time ago now, but do you recall if your server was running a 64-bit version of SQL? I've recently run into this problem on Win2008R2 Enterprise SP 1 with SQL 2005 64-bit SP4, but it doesn't seem to be reproducible on most variations of SQL. Thank you for the workaround example.