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:
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.
Thanks
I fixed that long back....but the point here is that the code was working fine before the hotfixes.
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.
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.
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.
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.
Thanks very helpful for a beginner SSIS user!
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
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.
Post a Comment