May 10, 2002 at 12:00 am
My company is running a CRM application with a SQL backend. At times I run into deadlocks caused by the application, which effect a certain portion of users (usually about 30-40%). By killing the process associated with this deadlock it fixes the problem and users are able to continue submitting queries.
We don't use SQL logins, so it's impossible to determine who may potentially be causing the locks.
Anyway, I was reading BOL and noticed a section on lock timeouts. They suggested using the function @@lock_timeout to produce:
DECLARE @Timeout int
SELECT @Timeout = @@lock_timeout
SELECT @Timeout
GO
This gives me a value of -1 when I execute.
Now I realise this means that no timeout has been set, and you can use SET LOCK_TIMEOUT (x) where x = time in milliseconds.
My question is this. (sorry it took so long!!) Is it a good idea to set lock timeouts, and could this potentially cause any kind of data integrity problems?
May 10, 2002 at 5:51 am
I have never heard of any issue with LOCK_TIMEOUTS in regards to data. However I believe if you set it too low and the resultset has not returned you will have people not getting their data. My suggestion is to take a look at the deadlocks with Profiler by doing a trace on the queries and try to find out what sequence of events leads up to this problem. The research how to get rid of it. Otherwise just make sure you give the lockout time enough to complete under normal circumstances.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 10, 2002 at 3:05 pm
Deadlocks are usually caused when improper transaction locking is employed. Even if proper trans locking is used, blocks can occur. Ultimately, you will need to find the root of the problem. Try using trace flag 1204(you can look it up in bol) and this will tell you what query is being run that is causing the deadlock and then you can go about optimizing once you have that info.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply