I am using Sql Server 2019.
I have X table (39 columns) with one primary key (clustered index).
One trigger is there for insert and which has updates the same table (to update some audit columns like update_date).
I am doing insert by using statement like below.
"Insert into X (column lists)
SELECT y.new_id,y.new_name,.....from X join Y where Y.source_id = X.id and y.execution_id = 1;
It's running fine from multiple session when there is no other non clustered index on the table.
Suppose I ran the insert statement with execution id 1 from session 1 at time 00:01 and not committed
Then I started same insert statement from another session with execution id 2 from session 2 at time 00:03
The session 2 waits until the first one not ends the transaction ( if a non clustered index added).
But same scenario not replicable if X table is a small table like below.
Create table trgtest
(id int not null,
par_id int null,
name varchar(40) not null,
status char(1) not null,
create_date datetime null,
create_user varchar(40) null,
update_date datetime null,
update_user varchar(40) null,
delete_session_id int null
);