Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Federico Iori,
Sql Server DBMS is build such that selecting from a table requires a share lock on the table ( except if hint NOLOCK is used) , and in some situations this can lead to deadlocks .
On the other side, the hint NOLOCK can lead to dirty reads.
Typical case , if we have in DWH a dimension that can filled simultaneosly by more than one ETL process .
The solution is to take a table share lock before inserting, this way
insert into dim_IMSI
select distinct a.SERVED_IMSI from Source_table_A a
left join dim_IMSI exi with(tablock) on
a.SERVED_IMSI= exi.imsi where 1=1 and
exi.imsi_id is null
This insert statement is deadlock safe and is not blocked by long running selects from dim_imsi, because a select takes a share page lock while the insert takes a table exclusive lock , which does not require all the pages of the table to be locked
Insert - Exec Select statements
Insert blocking select statements
DYNAMIC SELECT & INSERT QUERY HELP
a technique that allows large insert .... select but blocks no other queries
INSERT INTO SELECT and SELECT INTO may be very similar commands but they have some important differe...