Deadlock

Question

I have procedure for handling transaction detail there is aprox 60 user access my application for saving data at once(i.e.) this procedure will be hit by all user at once. My proc like this

 

Create Proc InsertUpdateData

As

Begin

Set tran Isolation Level Serializable

Begin Tran

Begin Try

Declare @SeqNo BigInt

select @SeqNo=TblVal+1 from GetSeq Where Year=2014 AND ColName='Tbl1'

update GetSeq set TblVal=@SeqNo Where Year=2014 AND ColName='Tbl1'

--- Some Insertion and updation in different different table 

 --------------------------

----- End of insertion and updation 

Commit tran

End Try

Begin Catch

return -1

Rollback

Insert into ErrorLog Select ERROR_LINE(),ERROR_MESSAGE()

End catch

End

  But In a day i got deadlock problem in line 

select @SeqNo=TblVal+1 from GetSeq Where Year=2014 AND ColName='Tbl1'

update GetSeq set TblVal=@SeqNo Where Year=2014 AND ColName='Tbl1'

while i use sql heartbeat for detecting deadlock.

I have use Transaction level Serializable for avoiding phantom,dirty and repeatable read.

 

This GetSeq  table has 

TableName column

TblVal(this will increase when i use update query to Getseq) 

 

 

Can Any one help me how can i avoid deadlock with using  Serializable isolation level for this