U-SQL error while table rebuild

Question

Error occurs when I call ALTER TABLE REBUILD in one stored procedure and try to SELECT data in another simultaneously.

CREATE PROCEDURE IF NOT EXISTS RebuildContent()
AS  
BEGIN 

INSERT INTO dbo.Log (Date, Message)
VALUES ( DateTime.UtcNow, "Starting Content table rebuilding ..." );

ALTER TABLE dbo.Content REBUILD;

ALTER TABLE dbo.ContentCrc REBUILD;

INSERT INTO dbo.Log (Date, Message)
VALUES ( DateTime.UtcNow, "Completed Content table rebuilding ..." );

END;

Are there any solutions to avoid it? Thank you in advance!

Michael Rys Michael Rys · Accepted Answer · 2017-08-21T20:10:51

You are running into a race condition between rebuilding and reading from the same table.

Rebuilding a table creates a new file by compacting the files that got created by the insertion. Unfortunately right now, once the rebuild is deleting the old file, you will lose access to the old version and you will get an error message.

We are aware of this issue and have created a work item to preserve access to the old file for the started queries (providing snapshot semantics). However, I do not have an ETA at the moment.

Thus until then, please schedule your rebuild and read jobs without overlaps.

Note: You can still concurrently rebuild and insert or insert and read.

U-SQL error while table rebuild

1 Answers