Salut,

Toata povestea de mai sus pare ca difera pentru view-uri.
Am facut urmatorul test :

--Sesiunea 1

--DROP TABLE Test_Old
--DROP TABLE Test_New
--DROP VIEW Test

CREATE TABLE dbo.Test_Old
(
ID INT IDENTITY PRIMARY KEY,
Col1 INT NOT NULL,
Col2 INT NOT NULL,
Col3 INT NOT NULL
);

CREATE TABLE dbo.Test_New
(
ID INT IDENTITY PRIMARY KEY,
Col1 INT NOT NULL,
Col2 INT NOT NULL,
Col3 INT NOT NULL
);

GO

create view Test as
select * from Test_Old
union all
select * from Test_New

GO

CREATE TRIGGER [dbo].[trg_Test_Update]
ON [dbo].Test
INSTEAD OF UPDATE
AS
UPDATE dbo.Test_New SET
Col1 = inserted.Col1,
Col2 = inserted.Col2,
Col3 = inserted.Col3
FROM
inserted
WHERE
dbo.Test_New.Col1 = inserted.Col1


GO

INSERT dbo.Test_Old(Col1, Col2, Col3)
VALUES (1,11,111), (2,22,222), (2,33,333), (2,44,444), (3,55,555);

INSERT dbo.Test_New(Col1, Col2, Col3)
VALUES (6,66,666), (7,77,777), (8,88,888), (9,99,999);
GO



--Sesiunea 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

UPDATE dbo.test SET
col1 = col1,
col2 = col2,
col3 = col3
where
col1 = 6

--rollback



--Sesiunea 3
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

UPDATE dbo.test SET
col1 = col1,
col2 = col2,
col3 = col3
where
col1 = 6

--rollback



--Sesiunea 4
SELECT l.resource_type, l.resource_description, l.request_mode, l.request_type, l.request_status, l.request_session_id
FROM sys.dm_tran_locks l
WHERE
l.resource_type = 'KEY'
ORDER BY l.resource_description, l.request_session_id;



Si ce am obtinut (in sesiunea 4) este

resource_type resource_description request_mode request_type request_status request_session_id
KEY (010086470766) X LOCK GRANT 74
KEY (010086470766) S LOCK WAIT 75

Acum, de unde apare acel S ? Daca peste tot se face update.


Mersi,