Stored Procedure and Locking question? 
Author Message
 Stored Procedure and Locking question?
Hi,

Does using stored procedure reduce/eliminate locking contention?  If so
how/why?

I am having a really bad locking issue using update statements.  Would
stored procedure help?

Thanks,
VJ



Thu, 10 Mar 2005 01:16:38 GMT
 Stored Procedure and Locking question?

Quote:
> Does using stored procedure reduce/eliminate locking contention?  If so
> how/why?
> I am having a really bad locking issue using update statements.  Would
> stored procedure help?

You need to have short transactions. SQL Server caches execution plans for
stored procedures for longer time than ad-hoc queries, so they could execute
a little bit faster, thus helping releasing locks quicker. But, there are
more important issues in your case, I guess. Check whether you have correct
indexes in place. SQL Server has to find the row to update quickly. Do you
have unnecessary long transactions, for example having user interaction
during the transaction? Use the Profiler as your base tool for analyzing
what's going on.

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



Thu, 10 Mar 2005 02:11:58 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Stored Procedure & Locking Question

2. QUESTION: How can I call a Stored Procedure from another Stored Procedure

3. Calling a Java Stored Procedure from another Java Stored Stored Procedure

4. Index types question & Stored procedures question

5. Row Locking In a Stored Procedure

6. ODBC Stored Procedure Locks in TEMPDB ???

7. Locked Stored Procedure

8. Locking an entire table within a stored procedure

9. How to loop recordset and lock the table at stored procedure

10. Lock record in stored procedure

11. lock up stored procedure

12. Lock Timeout In Stored Procedure


 
Powered by phpBB® Forum Software