Salut.
Planul de execuție real/efectiv de la UPDATE dbo.test (include planul pentru trigger > UDPATE dbo.test_new) este următorul:
http://s10.postimg.org/8v598lf21/Capture046.png
Planul demonstrează faptul că server-ul parcuge următoarele "etape":
1) Filtrează înregistrările după condiția col1 = 6 separat pentru fiecare dintre cele două tabele (dbo.test_new şi dbo.test_old) şi face un merge (union all) cu cele două seturi de înregistrări.
2) După activarea trigger-ului INSTEAD OF , UPDATE-ul real se execută pe dbo.test_new.
În legătură cu rezultatele obținute de către dvs.:
[ i ] Sesiunea 2 (SPID-ul 74 în cazul dvs.) execută cu succes etapa 1) și etapa 2). La etapa 2) obține și menține un loc eXclusiv pe înregistrarea care are Col1 = 6 (interogare auxiliară SELECT *, %%lockres%% FROM dbo.Test_New WITH(NOLOCK)). Tranzacţia rămâne deschisă şi lock-ul X este activ.
[ ii ] Sesiunea 3 (SPID-ul 75 în cazul dvs.) încearcă să execute etapa 1): Clustered Index Scan pe dbo.test_new şi dbo.test_old (vezi planul de execuție). Asta presupune ca operatorul Clustered Index Scan pe dbo.test_new să - virgulă - solicite un lock Shared și pe înregistrarea Col1 = 6.
[i ii ] Deoarece lock-urile X (SPID 74) și S (SPID 75) sunt incompatibile apare un blocaj, SPID-ul 75 fiind pus în stare de ”așteptare”. Acesta este și motivul pentru care reguest_status - ul pentru S este WAIT, spre deosebire de starea lock-ului X care este GRANT.
Ceea ce ar fi cu adevărat interesat la testul de mai sus (SQL 2008 R2) ar fi dacă
[ i ] sesiunea 2 ar executa UPDATE-ul pe dbo.test_new (tranzacția rămâne deschisă !!!) şi
[ ii ] sesiunea 3 ar executa un select fără NOLOCK :-)
adică
SELECT * FROM dbo.test_new;
kw: craigfr
Un articol interesant care are legătură cu UPDATE-urile folosite mai sus (SET Coloana = Coloana) a fost scris de către Aaron Bertrand aici:
http://www.sqlperformance.com/2012/10/t-sql-queries/conditional-updates
Toate acestea demonstrează ceea ce a spus actual președinte în perioada lui perioada de ministeriat.