Why deadlock occurs using INSERT/UPDATE in a table with 2 sessions

hi,

i working on problem in odbc application, have isolated ms sql server query analyzer.

the problem related deadlock faced 1 of session, when 2 transactions each running 2 sessions trying insert record respective session duplicate values columns icol1 , ccol2 , 1 of session update value of column icol1 in inserted record. after completion of test, see 1 record created in table 1 of session, other session becomes deadlock victim.

following table structure:

create table [sgarg].[test_buf](

[icol1] [int] null default ((0)),
[ccol2] [varchar](30) null,
[ccol3] [varchar](30) null,
[recid] [bigint] null
) on [primary]

following index definitions

create unique nonclustered index [test_buf##ixprim] on [sgarg].[test_buf]
(
 [icol1] asc,
 [ccol2] asc
)with (pad_index  = off, statistics_norecompute  = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks  = on, allow_page_locks  = on) on [primary]

create nonclustered index [test_buf#_#recid] on [sgarg].[test_buf]
(
 [recid] asc
)with (pad_index  = off, statistics_norecompute  = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks  = on, allow_page_locks  = on) on [primary]

following t-sql, running session-1
==============================================================
declare @val int

begin transaction

 set @val = 1

 insert test_buf values (@val,'','col3val',1)

 set @val = @val + 1

 waitfor delay '00:00:30'

 update test_buf set icol1 = @val recid = 1

commit
===============================================================

following t-sql, running session-2
===============================================================
declare @val int

begin transaction

 set @val = 1

 insert test_buf values (@val,'','col3val',2)

commit

===============================================================

i run session-1 first , after session-1, start session-2. after timedelay of 30 seconds observe session-2 receives following sql server error:

msg 1205, level 13, state 47, line 2
transaction (process id 66) deadlocked on lock resources process , has been chosen deadlock victim. rerun transaction.

can me explaining above behavior of ms sql server database engine.

 

following sp_lock output

66 251 0 0 db                                                       s grant
57 251 0 0 db                                                       s grant
57 251 1483568769 2 pag 1:262                            ix grant
66 251 1483568769 2 pag 1:262                            ix grant
57 251 1483568769 3 pag 1:464                            ix grant
57 251 1483568769 2 key (0400ae3a13ad)             x grant
57 251 1483568769 2 key (03007e40b3ea)             x grant
66 251 1483568769 2 key (03007e40b3ea)             x wait
57 251 1483568769 3 key (d0006fdf650f)               x grant
66 251 1483568769 0 rid 1:7118:1                       x grant
57 251 1483568769 0 rid 1:7118:1                       u wait
57 251 1483568769 0 tab                                     ix grant
66 251 1483568769 0 tab                                     ix grant
57 251 1483568769 0 pag 1:7118                           ix grant
66 251 1483568769 0 pag 1:7118                           ix grant
57 251 1483568769 0 rid 1:7118:0                         x grant

SQL Server  >  SQL Server Database Engine