Blog

lượt view: 2

“Msg 2801, Level 16, State 1, Procedure dbo.sp_alterSP_break_execution, Line 16 [Batch Start Line 0]
The definition of object ‘sp_alterSP_break_execution’ has changed since it was compiled.”

Đây là một trong những lỗi thỉnh thoảng gặp phải trong quá trình làm việc với stored procedure trong SQL Server. Tôi đã tìm kiếm trên mạng nhưng không có nhiều bài nói về lỗi kiểu này hoặc chỉ nói sơ sài kiểu như chia sẻ tình huống vô tình gặp phải chứ không giả lập lại được. Khi gặp thông báo này tức là việc thực thi của stored procedure đó đã bị kết thúc giữa chừng và trả về người dùng lỗi trên. Có thể có nhiều nguyên nhân gây ra lỗi này, bạn có thể thấy ở đây là một tình huống chia sẻ chính thức từ Microsoft và có bản cumulative updates rõ ràng. Trong bài viết này tôi chia sẻ với các bạn thêm một tình huống sẽ nhận được kết quả tương tự khi sửa đổi stored procedure trong lúc nó đang được thực thi.

Chỉ mỗi hành động sửa đổi nội dung stored procedure chưa đủ để gây ra lỗi trên. Để giả lập được tình huống này tôi đã từng suy luận rằng đơn giản là vì nội dung của stored procedure đã thay đổi (theo nghĩa đen của thông điệp lỗi gửi ra, thêm vào đó những lần gặp phải đều ngay sau khi tôi alter stored procedure trên Server – deploy code) trong quá trình nó đang được thực thi bởi SQL Server. Nhưng không, tôi đã dùng một stored procedure với nhiều statement để thử nghiệm những tình huống khác nhau như alter stored procedure ngay khi nó bắt đầu chạy hoặc sau khi chạy xong statement đầu tiên hoặc alter khi nó đang chạy statement giữa hoặc kề cuối,…không có tình huống nào gây ra lỗi trên cả. Nếu không phải là do nội dung thay đổi thì có khả năng nào là do query plan không? Bởi vì khi mình alter một stored procedure thì query plan của nó sẽ bị xóa khỏi procedure cache nên sẽ ko còn query plan cho những statement chưa kịp chạy và sẽ gây ra lỗi trên? Nhưng ở những thử nghiệm trên query plan cũng đã bị xóa khỏi cache rồi mà có thấy lỗi gì đâu? Hay là cần kết hợp thêm điều kiện gì đó nữa? Ngay lập tức tôi liên tưởng đến auto update statistics trong SQL Server, đây cũng là một hành động ảnh hưởng đến query plan của stored procedure vì mỗi khi statistics được update thì query plan liên quan trở nên mất hiệu lực.

Chỉ mỗi hành động auto update statistics chưa đủ để gây ra lỗi trên, nhưng nếu kết hợp cả hai điều kiện cùng một thời điểm thì kết quả sẽ khác, phép màu sẽ xuất hiện. Demo dưới đây sẽ cho các bạn thấy một stored procedure đang thực thi bị ảnh hưởng thế nào khi nó bị tác động bởi việc alter nội dung cùng lúc với sự xuất hiện auto update statistics (vì để đơn giản script demo tôi sẽ chạy câu lệnh update statistics chứ không phải bởi SQL Server – auto) trên một index của bảng mà stored procedure đó sử dụng.

USE tempdb
GO

 CREATE TABLE customers (
	customerId INT IDENTITY PRIMARY KEY CLUSTERED, 
	firstname VARCHAR(50), 
	lastname VARCHAR(50), 
	email VARCHAR(120) 
	)
CREATE TABLE transactions (
	Id INT IDENTITY PRIMARY KEY CLUSTERED, 
	customerId INT, 
	transactiontime DATETIME, 
	transactiondesc VARCHAR(120)  
	)
CREATE INDEX IX_customerId ON transactions(customerId,transactiontime)

GO
DECLARE @i INT = 1 
BEGIN TRAN
WHILE @i <= 100
BEGIN
	INSERT INTO customers(firstname,lastname,email)
	SELECT  CAST(@i AS VARCHAR(10)) + '-firstname', CAST(@i AS VARCHAR(10)) + '-lastname', CAST(@i AS VARCHAR(10)) + '@email.com'
	SET @i += 1 	
END
COMMIT 
GO

DECLARE @i INT = 1
BEGIN TRAN
WHILE @i <= 1000
BEGIN
	INSERT INTO transactions(customerId, transactiontime, transactiondesc)
	SELECT @i%10+1, GETDATE(), CAST(@i AS VARCHAR(10)) + '-desc'
	SET @i += 1 	
END
COMMIT 
GO

GO
CREATE PROCEDURE [dbo].[sp_alterSP_break_execution]
(
	@waittime INT = 0
)
AS
BEGIN
	SET NOCOUNT ON  
	 
	SELECT COUNT(*)
	FROM transactions t 
	WHERE t.customerid = 8 
		AND transactiontime <= GETDATE()

	WAITFOR DELAY @waittime	 

	SELECT id, t.customerid, transactiontime, c.firstname, c.lastname 
	FROM transactions t
		INNER JOIN customers c ON t.customerId = c.customerId 
	WHERE t.customerid = 8 
		AND transactiontime <= GETDATE()
	ORDER BY transactiontime 
	
END

GO

Sau khi tạo 2 bảng customers và transactions rồi đổ data vào, tôi có một stored procedure với 2 statements, giữa 2 statements có câu lệnh ‘WAITFOR DELAY @waittime’ dùng để tạo khoảng thời gian chờ đủ để tôi có thể chèn các hành động khác vào như alter stored procedure và update statistics, đơn vị tính là giây. Tiếp đến chúng ta sẽ sử dụng hai sessions, session đầu tiên dùng để execute stored procedure, session thứ hai dùng để alter stored procedure và insert vài dòng vào bảng transactions để đảm bảo có sự thay đổi data rồi mới chạy câu lệnh update statistics, sau khi hết thời gian chờ SQL Server sẽ ném ra lỗi trên khi thực thi statement 2.

Trong session thứ nhất chúng ta chạy stored procedure vài lần để tình huống giống thực tế là stored procedure đang hoạt động tốt trên production

--- session 1
USE tempdb 
GO
--- execute several times with @waittime = 0 to make sure query plan is compiled and it works well 
EXECUTE [dbo].[sp_alterSP_break_execution] @waittime = 0

Tiếp đến chúng ta execute với @waittime = 30

--- session 1
USE tempdb 
GO
--- execute SP with @waittime = 0 
EXECUTE [dbo].[sp_alterSP_break_execution] @waittime = 30

Ở session thứ hai chúng ta chạy đoạn code alter procedure như sau để thêm alias cnt cho column count(*) và chạy insert 1000 dòng dữ liệu cho bảng transactions rồi update statistics ngay sau đó.

--- session 2
USE tempdb 
GO
ALTER PROCEDURE [dbo].[sp_alterSP_break_execution]
(
	@waittime INT = 0
)
AS
BEGIN
	SET NOCOUNT ON  
	 
	SELECT COUNT(*) AS cnt
	FROM transactions t 
	WHERE t.customerid = 8 
		AND transactiontime <= GETDATE()

	WAITFOR DELAY @waittime	 

	SELECT id, t.customerid, transactiontime, c.firstname, c.lastname 
	FROM transactions t
		INNER JOIN customers c ON t.customerId = c.customerId 
	WHERE t.customerid = 8 
		AND transactiontime <= GETDATE()
	ORDER BY transactiontime 
	
END

GO

DECLARE @i INT = 1
BEGIN TRAN
WHILE @i <= 1000
BEGIN
	INSERT INTO transactions(customerId, transactiontime, transactiondesc)
	SELECT @i%10+1, GETDATE(), CAST(@i AS VARCHAR(10)) + ' - new desc'
	SET @i += 1 	
END
COMMIT 
GO
UPDATE STATISTICS transactions

Quan sát kết quả ở session thứ nhất bạn sẽ thấy như hình dưới đây.

Nếu hành động alter procedure và liền sau nó là auto update statistics không bị chen ngang bởi việc thực thi procedure đó thì bạn sẽ gặp lỗi như vậy. Đảo thứ tự của hành động trong session thứ hai tức là chạy update statistics trước rồi đến alter stored procedure kết quả vẫn giống như vậy, bạn hãy tự thử nghiệm tình huống này.

Khả năng để bạn gặp auto update statistics liên tiếp với alter stored procedure là hơi khó, mặc dù với những stored procedure có các big statement thì xác suất sẽ cao hơn một tí nhưng vẫn là cực kì hiếm gặp. Khi một statistics được update sẽ làm những query plan liên quan bị đánh dấu là phải recompile (build lại plan) ở lần chạy tiếp theo, chính sự kết hợp giữa recompile (statistics changed) và query plan bị xóa khỏi plan cache gây ra lỗi này. Nếu trong stored procedure của bạn có một statement sử dụng option (recompile) thì sẽ thế nào? Chúng ta hãy thêm OPTION (RECOMPILE) vào statement 2 của stored procedure như dưới đây.

USE tempdb 
GO
ALTER PROCEDURE [dbo].[sp_alterSP_break_execution]
(
	@waittime INT = 0
)
AS
BEGIN
	SET NOCOUNT ON  
	 
	SELECT COUNT(*) as cnt
	FROM transactions t 
	WHERE t.customerid = 8 
		AND transactiontime <= GETDATE()

	WAITFOR DELAY @waittime	 

	SELECT id, t.customerid, transactiontime, c.firstname, c.lastname
	FROM transactions t
		INNER JOIN customers c ON t.customerId = c.customerId 
	WHERE t.customerid = 8 
		AND transactiontime <= GETDATE()
	ORDER BY transactiontime 
	OPTION (RECOMPILE)
END

GO

Và sau đó lập lại những bước như demo ở trên, chạy stored procedure với @waittime = 0 vài lần, sau đó chạy với @waittime = 30, ở một session khác bạn alter stored procedure này rồi quay lại session đầu chờ xem kết quả.

Hẳn là lỗi này lại xuất hiện đúng không? kết quả thử nghiệm trên máy của tôi vẫn là những dòng lỗi đó. Tôi đã thực hiện những demo này trên các version SQL Server 2008R2, SQL Server 2016 SP1, SQL Server 2017 SP1 tất cả đều là Developer edition và đều gặp lỗi y chang nhau. Đây có thể là bug của Microsoft?

Các bạn có thể xem demo qua video dưới đây:

Ở bài viết này tôi đã chia sẻ với các bạn hai tình huống gây ra lỗi với thông điệp “The definition of object ‘sp_alterSP_break_execution’ has changed since it was compiled.” có thể mô tả ngắn gọn như sau:
1. Alter procedure kết hợp với update statistics sẽ làm gián đoạn việc thực thi của procedure bị alter
2. Alter procedure mà bản thân procedure đó đã có 1 statement với OPTION (RECOMPILE) cũng có thể làm gián đoạn việc thực thi của procedure đó.
Điều này khá là nguy hiểm quá trình xử lý data trong procedure đó chỉ mới hoàn thành một nửa.