I'm using SQL Server 2019 and I have a simple stored procedure that refreshes a large table on my Reporting database, which gets used a lot by various reports and data visualisations throughout the company:
TRUNCATE TABLE Reporting.dbo.TempA
INSERT INTO Reporting.dbo.TempA WITH(TABLOCKX)
SELECT RowID,
ISNULL(CustomerData,''),
FROM [PROD].LiveData.dbo.A WITH(NOLOCK)
WHERE RowID IS NOT NULLIt only takes a couple of minutes to run, but other users or reports might try to access this table while the INSERT INTO is running. To them, it would appear either empty or locked, depending on what kind of locking I do for my INSERT INTO, neither outcome is really acceptable.
What I'd like to do instead of truncating TempA is create a new table named TempB, fill that, then Drop TempA and rename TempB to TempA.
However, I think there's still a chance that some other request on to TempA might be executed in the split second between me dropping it and renaming TempB. In this case, the error would be more severe than a lock or an empty table, it would be a "Table does not exist" error.
Am I wrong? Will that never be possible? Should I just shut up and execute the drop command and rename command in the same transaction and let SQL Server do its thing? Should I wrap both commands in a BEGIN TRAN to be extra safe?
Or if I'm right, is there any way to prevent that behaviour? Could I put TempA into a "Locked" state even though I just dropped it and I'm about to rename another table to TempA in its place? When the "Locked" state expired, could any other process or query just carry on as normal accessing TempA, as if it had never been dropped?
Any help appreciated.
2条答案
按热度按时间wgx48brx1#
I think there's still a chance that some other request on to TempA might be executed in the split second between me dropping it and renaming TempB
Yes that can happen unless you prevent it. And it's not even unlikely. The first sp_rename will wait and acquire an exclusive schema lock on T. While it holds that lock, any other session wanting to query T will become blocked. So when the rename commits there are likely to be sessions that have been waiting and will immediately try to query it.
To prevent that you need to hold the lock until you've replaced T with the new table. To do this just use a transaction:
The first
sp_renamewill acquire an exclusive table lock (Sch-M) on the table, and other sessions will be blocked until the COMMIT TRANSACTION.o3imoua42#
Your first approach i.e. Truncate/Load is simple and better than creating a new table, renaming and dropping the old one. Schedule your ETL truncate/load in off business hours to minimize the impact. Another way is to use table partition, load data in new partition and drop the old one, in this way on any given point in time your table will never be empty.