Inquiry about SET LOCK_TIMEOUT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Inquiry about SET LOCK_TIMEOUT

Author

Topic

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-27 : 00:55:44

SET LOCK_TIMEOUT 18000

does it prevents deadlock?. let say I have a stored procedure with SET LOCK_TIMEOUT on the beginning, when deadlock occurred does the SP wait until the lock is release then continue executing again?

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede™

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-05-27 : 09:48:54

Lets take this example:

Open Query Analyzer 1:
---------------------

begin transaction
update employee set job_Id =13 where job_Id = 13

Execute the Query

Open Query Analyzer 2:
---------------------

Create procedure PEmp as
Begin
SET LOCK_TIMEOUT 18000
select * from employee
end

Execute :

exec pEmp

If the First transaction is not COMMITTED / ROLLBACKED with in the 18000 milliseconds it will give the error like

Server: Msg 1222, Level 16, State 50, Procedure PEmp, Line 4
Lock request time out period exceeded.

Other wise it will work. It is just like simple suspension of execution of query for some milliseconds (18000) once other transaction is on LOCK.

:-) any queries please feel free .....

With Regards
Sreenivas Reddy B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources