I fixed an issue today which surprised me and so I'm looking for some insight?
ASP.NET app using DAAB 2.0 SQL Server 2008 R2 database
I had some custom code that was getting a 'SqlException: Timeout expired' error on update when the update value was large in size.
After investigating I found what I believe to be the problem code
The code pattern is
Dim dr As IDataReader = GetCustomerSetting(SettingName)
If dr.Read Then
The datareader is always getting either 1 or 0 records
NOTE: SettingValue is an nTEXT datatype!
The problem is that if the DataReader locks the database record/table then the Update is blocked.
This blocking only occurs when the Settingvalue exists already (i.e. Update) and is large enough.
The solution was of course to move the dr.Close() up above the Update.
I personally don't like this pattern , however it used throughout the app without problem?
Any insight as to why SQL
View Complete Post