Thursday, April 28, 2011

SQL Server - How to lock a table until a stored procedure finishes

I want to do this:

create procedure A as
  lock table a
  -- do some stuff unrelated to a to prepare to update a
  -- update a
  unlock table a
  return table b

Is something like that possible?

Ultimately I want my SQL server reporting services report to call procedure A, and then only show table a after the procedure has finished. (I'm not able to change procedure A to return table a).

From stackoverflow
  • Use the TABLOCKX lock hint for your transaction. See this article for more information on locking.

    David Moye : You could, alternatively, use UPDLOCK if it's okay for others to read the table while you're using it.
    Greg : Where does the transaction come in? Should I wrap my whole SP in a transaction?
    David Moye : For many SPs, it makes sense to begin a transaction at the beginning and commit it at the end. There are, of course, exceptions to this rule, but in general I find it a good practice.

0 comments:

Post a Comment