asked
Will an INSERT implicit transaction be locked during the time it takes to execute a subquery?
I've gleaned good info from this site, but now it's time I ask a question. I hope it isn't too simplistic. I have a long-running query: SELECT M2.var1, M2.var2 FROM MyTable2 AS M2 INNER JOIN MyTable3 AS M3 ON (details not important). The point is that it can be lengthy to finish, up to 60 seconds. I need to insert those results into a table MyTable1. Is it better to use: INSERT INTO MyTable1 (var1, var2) SELECT M2.var1, M2.var2 FROM MyTable2 AS M2 INNER ... OR to store the subquery results into a temp table first, then import into MyTable1: SELECT M2.var1, M2.var2 INTO #temp FROM MyTable2 AS M2 INNER JOIN MyTable3 AS M3 ON ...; INSERT INTO MyTable1 (var1, var2) SELECT var1,var2 FROM #temp; Is the implicit lock held on MyTable1 during the entire 60 seconds it takes to run the subquery, or only during the time the actual data is being inserted into the table?
Have you tested this? It isnt too tricky to mock up the scenario and look at what happens. Create 2 queries in SSMS and connect them both to a test server and in turn a test database. **Query 1** CREATE TABLE TestLock01 (ID INT, Country VARCHAR(30)) GO INSERT INTO TestLock01 VALUES (1,'England') go -- Go and run the other query - it succeeds with one row BEGIN TRAN INSERT INTO TestLock01 VALUES (2,'Wales'),(3,'Scotland'),(4,'Ireland') WAITFOR DELAY '00:01:00' COMMIT -- go run the other query - it is locked until the WAITFOR is finished and then has 4 rows **Query 2** USE [Scrap01] GO SELECT * FROM [dbo].[TestLock01] AS TL
Fatherjack, thanks, but it's not quite the same situation. However, you gave me an idea on how to test it. Create a stored procedure that returns a table, but with a WAITFOR DELAY that will artificially inflate the time it takes to return results. I tested with: CREATE PROCEDURE FakeDelay AS SELECT 1,'Hello World' WAITFOR DELAY '00:00:30' GO CREATE TABLE MyTable1 (var1 int, var2 varchar(100)) INSERT INTO MyTable1 (var1, var2) VALUES (0,'Init') -- Go check "SELECT * FROM MyTable1" in another window while this runs INSERT INTO MyTable1 (var1, var2) EXEC FakeDelay WAITFOR DELAY '00:00:30' DROP TABLE MyTable1 DROP PROCEDURE FakeDelay The table MyTable1 was NOT locked during the 30-second delay. It appears that the subquery execution time does not affect the lock time on the INSERT. Thank you
Glad it inspired you to create your exact scenario and congrats for getting it done and finding the proof that you needed.
15 People are following this question.
