November 5, 2010 at 8:41 am
Hi,
We have an application and we are receiving the below error msg from SQL Server 2005
[font="Courier New"]Msg 1222, Level 16, State 56, Procedure <sprocname>, Line <#>
Lock request time out period exceeded.[/font]
I have narrowed the cause down to a long running query which was applying many table level locks.
I wanted to step through the long running query so I restored the DB to SQL Server 2008. On SQL Server 2008, however, the same problem does not occur.
When I execute [font="Courier New"]exec sp_lock [/font]against sql 2005 and sql 2008 whilst the 'long running query' is in progress I get a different list of locks which appear to have a different granularity.
I've been looking around but I'm stumped. Does anyone know why this might be?
Thanks! 😀
November 5, 2010 at 8:43 am
Can you post the query it's hanging on?
Sometimes creating better indexes can solve locking/blocking issues, and in some cases using (nolock) is needed. I'd be willing to bet some indexing would help here though.
November 5, 2010 at 8:51 am
quick response!
Well... the long running query is a view and it's returning alot of data as well as processing some of the returned data with a scalar function before outputting. While the sql for this view is executing the application is unable to update any data in tables that the the batch has a lock on, understandable.
My problem is not really this view, I accept this requires re-designing and can pass it to dev and work-around it with 'WITH (NOLOCK)'.
What is stumping me is that when restored to SQL 2008, the exact same database and same steps followed to reproduce, do not reproduce. It's as if the locks in SQL 2008 behave differently to SQL 2005 even though the sql code is exactly the same...??
November 5, 2010 at 8:58 am
The locking system really hasn't changed between 2005->2008, since there's really not many ways it can change. If a view is taking an exclusive lock on a table in 2005, it will do the exact same in 2008, because that's the only way to get consistent data. It's standard database logic and most RDBMS use similar logic and methods, regardless of version (except those oracle guys who apparently have no problem with dirty data).
So in essence: No, there should be no difference between the two unless there are other factors at play (other traffic, maxdop changing the query plan, fragmentation, disabled indexes, disk speed, etc).
November 5, 2010 at 9:01 am
Hmmm. Ok, thanks Derrick.
I will try a few more times, maybe see if I had anything set up differently but I don't think I did.
I appreciate your help anyway, and let me know if you think of anything.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply