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