New answers tagged locking
2
Deadlocking by SELECTs can be done in a variety of ways. I have written posts about them
You can have SELECTs get deadlocked by UPDATEs and DELETEs
Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
You can have UPDATEs and DELETEs blocked by SELECTs
How are DB locks tied to connections and sessions?
Is Oracle DB immune to the InnoDB deadlocks ...
2
Yes it does take a shared lock on the rows that it reads by default (it also takes an Intent Shared lock on all the pages of the clustered index that it will read), this is done to prevent dirty reads. However there are ways to bypass this (SQL Server has the nolock hint). If the statement is not in a BEGIN TRAN the lock is released after the SELECT ...
1
You can run you query in a transaction and run sp_lock @@spid after running the query, you will know all the locks held.
3
Posting the final SQL for this process, for the benefit of the community
/********************************************************************************************************************
* Notes: Since this approach executes in a loop inside an explicit transaction, locks will be obtained and *
* released for each iteration, thus minimizing ...
5
To speed things up, you could try
Adding a primary key to #CSpec.RowID so you don't scan it every iteration
Change the CTE to a temp table with suitable PK. See next point too
Add an index on SourceTable1 to match the CTE WHERE clause: currently the PK will be scanned, meaning all SourceTable1 rows will be scanned every iteration. All 52 million rows
...
Top 50 recent answers are included
