What happens if altering a stored procedure while it is running?

Question

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.

Heinz

When you execute a stored procedure, it is loaded into memory, analyzed, a query plan is determined and then it's executed. So after it's been loaded in the first step - you can totally change it, this will not have any impact on your running execution. – Dita

Answer 1

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.

Berrios

Is this true? I understand the Schema locks and blocks with adding new columns indexes ect while waiting, but it isn't a schema lock while the proc is executing right? – Thermomotor

Answer 2

I've just tested this in SQL Server 2008 R2

I started with:

CREATE PROCEDURE dbo.Stupid
AS
WAITFOR DELAY '0:00:10'
SELECT TOP 5 * FROM dbo.UniqueId
GO

I then did the following SQL Server Query Window 1:

EXEC dbo.Stupid

SQL Server Query Window 2, while query in Query Window 1 was running:

ALTER PROCEDURE dbo.Stupid
AS
WAITFOR DELAY '0:00:05'
SELECT TOP 5 * FROM dbo.UniqueId
WHERE ID > 5
GO

EXEC dbo.Stupid

SQL Server Query Window 3, while queries in Query Window 1 and Query Window 2 were running:

EXEC dbo.Stupid

Results:

• Query Window 1 ran in 10 seconds (and therefore finished after windows 2 and 3), and returned ids 1 - 5
• Query Window 2 altered and ran the procedure in 5 seconds, and returned ids 6 - 10
• Query Window 3 ran in 5 seconds and returned ids 6 - 10

What happens:

• Already executing code will complete running on the procedure as it was when they were started
• Anything that starts run after the code is altered will run the new code

Koralie

Good research!! – Toponym