May 5, 2009 at 1:44 pm
Anyone know why a SalesLogix user will freeze up when I'm running a huge query with multiple joins? It takes a few minutes to run and multiple users get bounced out. I'm just running a select from an OLEDB connection.
Thanks
May 5, 2009 at 3:25 pm
you are probably causing them to be blocked out and there queries are timing out. Run sp_who2 whilst you are running your query to determine this, a value in the blocked column of their connections = to spid value of your connection will confirm this.
---------------------------------------------------------------------
May 5, 2009 at 4:54 pm
I get that, but if we're just running selects which use shared locks, why would they be locked out?
May 5, 2009 at 5:43 pm
A shared lock will block a write to the same resource. If your Select is taking a table lock then noone will be able to write (Insert, Update or Delete) to that table until your Select has released its lock. If their write is running as part of a transaction or some complex query it could be a different table that they're writing to but it could still be blocked by your select.
Under SQL2k you've got limited options to address it. You could use a NoLock qualifier on your Select to force it to do a dirty read or schedule it outside of business hours to eliminate the clash.
This might be a good argument to use to persuade management of the value of an upgrade. Under SQL2k5 and SQL2k8 you can use snapshot locking to eliminate such blocks: this was one of the big selling points we used to get our PeopleSoft system upgraded and it enabled us to drop the log-shipped copy we were using for reporting as well as hugely reducing the number of deadlocks we got.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply