question

neerajdhimansre avatar image
neerajdhimansre asked

help me prevent this deadlock

can someone help me prevent this deadlock I have 2 Tables, Say table A and Table B, Table B has foreign key relation with Table A and while inserting data in Table B it creates deadlock involving Table A's clustered index. I have non clustered index on the Table B's referenced column also. attaching the deadlock graph here and looking for some quick help: unknown (@P0 bit,@P1 bigint,@P2 bit,@P3 bigint,@P4 datetime,@P5 bit,@P6 nvarchar(4000),@P7 float,@P8 bigint,@P9 bit,@P10 bit,@P11 float,@P12 bigint,@P13 int,@P14 datetime,@P15 nvarchar(4000),@P16 int,@P17 int)insert into table_B (acn_sent, candidate, consume_units, content_package, end_date, invalid_score, message_id, percentile, project_id, report_available, rr_applied, score, single_use_link_id, stage_no, start_date, status, timer, version) values ( @P0 , @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 , @P10 , @P11 , @P12 , @P13 , @P14 , @P15 , @P16 , @P17 ) SELECT SCOPE_IDENTITY() AS _JTDS_GENE_R_ATED_KEYS_ unknown (@P0 bit,@P1 bigint,@P2 bit,@P3 bigint,@P4 datetime,@P5 bit,@P6 nvarchar(4000),@P7 float,@P8 bigint,@P9 bit,@P10 bit,@P11 float,@P12 bigint,@P13 int,@P14 datetime,@P15 nvarchar(4000),@P16 int,@P17 int)insert into Table_B (acn_sent, candidate, consume_units, content_package, end_date, invalid_score, message_id, percentile, project_id, report_available, rr_applied, score, single_use_link_id, stage_no, start_date, status, timer, version) values ( @P0 , @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 , @P10 , @P11 , @P12 , @P13 , @P14 , @P15 , @P16 , @P17 ) SELECT SCOPE_IDENTITY() AS _JTDS_GENE_R_ATED_KEYS_
deadlockkey-lookup
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
jason_clark03 avatar image
jason_clark03 answered
You may have a look here: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/ this may help you...
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.