Table Lock causing Stored Proc to fail

[dr486]

If a Stored Proc (SP1) calls another Stored Proc (SP2) and the called Stored Proc (SP2) hangs on a page or table lock, will SQL Server then Kill the whole process (SP1 and SP2) or will it only Kill the called Stored Proc (SP2) and return to the calling procedure (SP1)

 

[nomadicalloy]

What version of SQL server you are using? 2000 or 2005?


This is Signature

http://www.xpherion.com/

 

[mrdenny]

It depends on the failure level that is returned.

A lock on a table by it self won't cause a failure of a procedure. The procedure will wait until the lock is released.

If the procedure has the lock timeout set then again it will depend on the severity of the errorlevel.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[dr486]

Im using SQL Server 2000

On the dev servers, which are apparently a mirror of production, I receive error 1222 on a lock timeout and have the child stored proc complete and return an error to the parent stored proc

This doesn't seem to be happening on the production server. The production server is just killing my whole process.

Really appreciate the help

dr

 

[mrdenny]

Sounds like it's time to start digging through the server configurations and find the difference in the configs.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com