如果在存储过程运行时更改它会怎样？ [英] What happens if altering a stored procedure while it is running?

问题描述

我对存储过程进行了细微的一行更改（将字符串中的错字固定），希望将其尽快部署到生产SQL Server 2005服务器。

我担心的是，如果在正确的时间运行alter语句更新我的存储过程，同时又有东西同时调用该存储过程，会发生什么情况？

它是否与存储过程的上一个副本一起运行，还是会导致某些损坏或错误？

考虑SQL的ACID性质服务器，我希望它是安全的。
可以同时运行的机会特别是SP很小，尤其是因为SP很小，但是我只是想确定一下，并且我对答案也很感兴趣，仅出于教育目的。 p>

可以说ServerFault会是一个更好的选择，如果张贴错误，对不起。

谢谢。

p>

解决方案

在对过程使用ALTER时，将设置模式修改锁。
SP仍然存在，但是客户端必须等待，直到执行ALTER。
对于ALTER同样如此，它将一直等待直到客户端未使用SP。

I have a minor, one line change (fixing a typo in a string), to a stored procedure that I would like to deploy to our production SQL Server 2005 server as soon as possible.

The worry I have is what happens if at the exact time run the alter statement to update my stored procedure, it happens that something calls that stored procedure at the same time?

Does it run with the previous copy of the stored procedure, or can it result to some corruption or errors?

Considering the ACID nature of SQL Server, I would expect that it is safe. The chances of it running at the exact same time, especially since the SP is quite small are extremely low, but I just prefer to make sure, and I am also interested in the answer, just for educational purposes.

Arguably, ServerFault would be a better place for this, sorry if it is misposted.

Thank you.

解决方案

When using ALTER for the procedure, a schema modification lock is set. The SP still exists, but clients will have to wait until the ALTER is executed. The same applies for ALTER, it will wait until the SP isn't used by clients.

这篇关于如果在存储过程运行时更改它会怎样？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！