question

PradiptaKhuntia avatar image
0 Votes"
PradiptaKhuntia asked ErlandSommarskog commented

Update blocks another update on same table due to non clustered index

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
);

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The width of the table as such does not really matter.

I'm afraid that we need to see a complete repro that demonstrates the issue. Else we would only be guessing wildly.

1 Vote 1 ·

Please let me know if you need more information.

I cannot understand why this is happening in big table.

0 Votes 0 ·

0 Answers