June 21, 2002 at 1:30 pm
Hello all
We have a sproc that is essentially a single line insert stmt with a sub-select, e.g. insert into <table>... (select columns from <the rest of it>)
This sproc accepts several input parameters and ends up inserting about 190 rows. When we stress test this thing with 10 simultaneous client connections, we get lock timeouts (in profiler), but our lock_timeout value is set to the default (-1). Playing with this value makes no difference, and we have had to resort to catching errro 1222 and retrying the statement.
This is running on a compaq 6000 with 2.5 G RAM, raid 10, & separate volumes for data & system, all that good stuff, so our HW is cool.
I was under the impression that SET LOCK_TIMEOUT -1 told SQL Server to wait indefinitely, but this has not been the case. We can put up with some blocking in our app, but these timeouts and the (apparent) misbehaviour of the LOCK_TIMEOUT setting is, to put it kindly, puzzling.
Any ideas, feedback, etc would be greatly appreciated.
Vik
June 25, 2002 at 4:29 am
By default a connections LOCK_TIMEOUT is -1 and you should not have to set. You best bet would be to use Profiler to monitor a process and see if anything else is setting the value to other than -1 or forcing a lock timeout. This is especially true if you are sure the return is 1222.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 25, 2002 at 6:42 am
Thanks for the reply. I'll scan through the database script & associated source code but I'm pretty sure no one has monkeyed with this setting. There are 3 other people who have the access to do so, but none of them would even have known about LOCK_TIMEOUT.
Vik
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply