Deadlock in Execute SQL Task

i have stored procedure call @ beginning of each ssis package save metadata table , read id of created record. here body of proc:

 

insert into tbl (

pkgname,  pkgstartdt)

values (@pkgname, @pkgstartdt,)

 

set @pkgexeckey= ( select pkgexeckey from tbl

where pkgname = @pkgname and pkgstartdt = @pkgstartdt)

 

i call proc execute sql task transactionoption set “supported”

the task located inside container transactionoption set “required” , isolationlevel set “serializable”

 

if run packages in parallel (i reuse stored procedure @ beginning of each package) getting deadlock error:

“transaction (process id 79) deadlocked on lock resource , has been chosen deadlock victim”. pinpointed deadlock stored procedure (see above)

although see reason why deadlock can happen in proc, not understand why happening isolation level container set serializable.

 

pleas help

make index cover both columns create index ix_1 on tbl(pkgname, pkgstartdt). that's nonclustered index. i'm assuming have clustered index on pkgexeckey column (identity 1,1).

did try isolation level = read committed?

thx, jason

SQL Server  >  SQL Server Integration Services